Click here to Skip to main content
14,241,865 members

How to capture Google Forms responses into a Kintone Database

This article introduces how to capture Google Form responses into a Kintone Database, instead of capturing them in a spreadsheet.

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

Screenshot of the integration image between Google Forms and Kintone.

Introduction

This article introduces how to capture Google Form responses into a Kintone Database, instead of capturing them in a spreadsheet.

Google Forms allows end users to easily create and send questionnaires and event invitations, and store results into a spreadsheet. There are many advantages though of capturing the form responses into Kintone's databases (a.k.a. Kintone Apps) instead:

  1. Access controls for responses can be set
    Kintone allows end users to set granular access controls to the data in its Apps. For example, access controls can be set so that only members of the HR department will be able to view the submitted email addresses.

    GIF of how the access controls can hide sensitive information such as email addresses from other Kintone users.

  2. Business Process Management for each response can be defined
    Workflows can be set for each record in the Kintone App, which makes actions that need to take place after receiving responses become clearer. For example, after receiving a Google Forms response into Kintone, the record can be assigned to a Design team member to work on creating creatives related to the response data.

    GIF of proceeding the status so that a user is assigned a task.

  3. Communication with team members becomes simple
    Each record inside the Kintone App has a feature for posting comments for other team members to read. For example, after receiving a Google Forms response, an Events team member can write a comment in the record, to reach out to a Sales team member who may have some connection with the submitter of the form.  

    GIF of a user posting a comment to a user, so that they will be notified and be able to read the details of the submission.

In this article, we will go through the following 4 steps to complete the integration:

  1. Preparing a Google Form
  2. Preparing a Kintone App
  3. Creating a Google Apps Script program
  4. Testing the integration

1.Preparing a Google Form

This section goes through the steps on how to build an event invitation form using Google Forms.

Step 1

A Google account will be needed for this step.
Log in to your Google account, select Google Forms from the Google app icon (or directly login from https://docs.google.com/forms/), and create a new blank form.
 

Screenshot of the Google Forms home page after logging in.

Step 2

Enter the title and description of your form.

Screenshot of a new form where the title and description are entered in.

Step 3

Click the Settings icon, check "Collect email address", uncheck all boxes under "Requires sign in", and save the changes.

Screenshot of the option in Google Forms to collect email addresses from the form submitters.

Step 4

Set up the contents of the event invitation form by clicking on the "Add Question" icon, and selecting a question type, such as "Multiple choice". Enter your question in the question field, and the response options.

Screenshot of placing various question types into the Google form.

Select "Required" as below if the question is mandatory.

Screenshot of making certain questions mandatory in Google Forms.

Step 5

Repeat the process until the event invitation form is completed.

Screenshot of adding in more questions in to Google Forms.

As an example, these are the form details used in the sample event invitation form:

Question Type Question Options
Multiple choice Would you like to participate in this event? Yes
No
Maybe
Short answer The number of participants  
Long answer Please enter the names of the participants  

Google Forms will save every change you make. Finish off your form, and move on to the next step – Preparing a Kintone App.

2. Preparing a Kintone App

This section goes through how to set up a Kintone App instance within your Kintone environment, which will capture the Google Form responses. If you don’t have a Kintone environment, you can apply for a free 1 year Kintone developer license, by joining the Kintone Developer Program.

Step 1

Log in to your Kintone environment, navigate to the portal and create a new Kintone App by clicking the [+] button in the Apps widget. Select “Create an App from Scratch”, and set out the Kintone form with fields similar to the ones placed in your Google Form.

GIF of placing in a field within a Kintone App and editing the settings.

As reference, this is the Kintone App form layout and settings used in this article.

Screenshot of fields placed into the Kintone App similar to the questions placed into the Google Forms invitation.

Field Type Field Name Field Code Other Settings
Link Email Email Type: E-mail address
Radio Button Would you like to participate in this event? attend Options:
Yes
No
Maybe
Number The number of participants number_of_participants -
Text Area The names of the participants names_of_participants -

Click on Save, once the fields in the form have been laid out.

Step 2

Click on the Apps settings tab, and click on "API Token".

Screenshot of showing where the API Token option is inside the Kintone App settings.

Generate a new API Token for the App, check the "Add records" check-box, and save the settings.

Screenshot of generating a new API Token for the Kintone App and selecting the Add records permission.

Click on "Update App" to apply the updates made to your App

3. Create a Google Apps Script Program

This section goes through how to set up a Google Apps Script program to run after a Google Forms submission has been made, so that the responses will be recorded into the Kintone App.

Step 1

Reopen the Google form created in the previous steps and click "Script editor" from the "Other" menu.
 

Screenshot of selecting the Script editor option for the Event Invitation form created in Google Forms.

Enter a project name and a file name.
 

Screenshot of entering a Project name and File name into the Google script editor.

Step 2

In this article, we will use the following library: https://github.com/Arahabica/KintoneManager

Copy the Project Key from the link above. On Google Forms, select "Resources" and then "Libraries".
 

Screenshot of selecting a library to use with the Google script editor.

Paste the project key into the "Add a Library" field. Click "Add" to add the library, select the latest version, and save the settings.

Screenshot of adding in the project key of the library and selecting the version of the library.

Step 3

Copy the following code in to your Google Apps Script.
The strings "Email", "attend", "number_of_participants" and "names_of_participants" that are listed in the code refer to the field codes of the fields in the Kintone App. Make sure that these strings match the field code of the fields in your App form.

Update the code so that the values of the keys "subdomain", "appid" and "token" correspond to your Kintone Environment and Kintone App (navigate to the App via the browser to find the App ID as an integer inside the URL).

function getFormResponse(e) {
    'use strict';
    var itemResponses = e.response.getItemResponses();
    var records = '[';
    
    records += Utilities.formatString('{"Email": { "value": "%s" }', e.response.getRespondentEmail());

    for (var i = 0; i < itemResponses.length; i++) {
        var itemResponse = itemResponses[i];

        switch (itemResponse.getItem().getTitle()) {
            case 'Would you like to participate in this event?':
                records += Utilities.formatString(',"attend" : { "value": "%s" }',
                    itemResponse.getResponse());
                break;
            case 'The number of participants':
                records += Utilities.formatString(',"number_of_participants" : { "value": "%s" }',
                    itemResponse.getResponse());
                break;
            case 'Please enter the names of the participants':
                records += Utilities.formatString(',"names_of_participants" : { "value": "%s" }',
                    itemResponse.getResponse());
                break;
        }
    }
    records += '}]';
    Logger.log('Response JSON is "%s"', records);
    return records;
}

function sendToKintone(e) {
    'use strict';
    Logger.log('Form submitted');
    var subdomain = '{subdomain}.kintone.com'; // change URL to your kintone domain 
    var apps = {
        YOUR_APPLICATION1: { appid: 1, name: 'Kintone Connect', token: 'xxxxxxxxx' }
    };
    var manager = new KintoneManager.KintoneManager(subdomain, apps);// Initialize library
    var str = getFormResponse(e);
    str = str.replace(/\n/g, "\\n").replace(/\r/g, "\\r").replace(/\t/g, "\\t");
    var records = JSON.parse(str);// Convert to JSON
    var response = manager.create('YOUR_APPLICATION1', records); //Create a record in kintone
    // Status code 200 will return for successful requests
    var code = response.getResponseCode();
    Logger.log('Response code is "%s"', code);
}

Once finished, save your code.

Code Explanation: Sending data to Kintone

The e.response.getItemResponses() function retrieves the submitted form data:

function getFormResponse(e) {
    var itemResponses = e.response.getItemResponses();
    // ***
    // ***
}

The e.reponse.getRespondentEmail() function is used to get the email address of the form submitter, and the request data for Kintone is created:

var records = '[';
    
    records += Utilities.formatString('{"Email": { "value": "%s" }', e.response.getRespondentEmail());

    for (var i = 0; i < itemResponses.length; i++) {
        var itemResponse = itemResponses[i];

        switch (itemResponse.getItem().getTitle()) {
            case 'Would you like to participate in this event?':
                records += Utilities.formatString(',"attend" : { "value": "%s" }',
                    itemResponse.getResponse());
                break;
            case 'The number of participants':
                records += Utilities.formatString(',"number_of_participants" : { "value": "%s" }',
                    itemResponse.getResponse());
                break;
            case 'Please enter the names of the participants':
                records += Utilities.formatString(',"names_of_participants" : { "value": "%s" }',
                    itemResponse.getResponse());
                break;
        }
    }
records += '}]';

Code Explanation: Sending data to Kintone

The information of the Kintone App created above is set with the following:

function sendToKintone(e) {
    var subdomain = '{subdomain}.kintone.com'; // change URL to your kintone domain 
    var apps = {
        YOUR_APPLICATION1: { appid: 1, name: 'Kintone Connect', token: 'xxxxxxxxx' }
    };
    // ***
    // ***
}

The imported library is initialized, the request data is JSON formatted, and is then sent to Kintone:

var manager = new KintoneManagerUs(subdomain, apps);// Initialize library
var str = getFormResponse(e);
str = str.replace(/\n/g, "\\n").replace(/\r/g, "\\r").replace(/\t/g, "\\t");
var records = JSON.parse(str);// Convert to JSON
var response = manager.create('YOUR_APPLICATION1', records); //Create a record in kintone

The data post to Kintone succeeds if the response code is "200".

var code = response.getResponseCode();

Step 4

Select "Current project’s triggers" from the Edit menu and select a function to run when a form submission is made on the Google Form. Click Save when done.

Screenshot of selecting the Current project’s triggers in the Google Script editor.

Screenshot of selecting what action will trigger the code to run in the Google Script editor.

The set up for Google Apps Script is now complete.

4. Testing the integration

This section goes through how to test the newly set up integration.

Click the Send button in the upper right corner of the Google form you created, fill out the Send form and click the Send button.

Screenshot of sending out the Event invitation form to recipients via Google Forms.

Recipients will receive links to the Google Form.

Screenshot of filling out the Event invitation form.

Once the form is submitted, the response data should be added to your Kintone App.

Screenshot of a record inside a Kintone App with the details that were entered in to the Event invitation form of Google Forms.

5. Summary

Using Google form enables you to easily create forms for questionnaires and event invidations, and send them to recipients via email, as well as embedding them on your company website. By managing the responses inside Kintone, you can utilize Kintone’s features to set access controls, define business process management and easily leave notes for other team members for each response.

For more details on how to use Kintone's databases and its APIs, try out the 5-minute Kintone API tutorial from the link below. The tutorial also provides you with a free Kintone developer environment. Enjoy!

A link to start a 5-minute Kintone API Tutorial on the Kintone Developer Program website.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

The Kintone Developer Program is a program dedicated to supporting developers customizing Kintone - the teamwork transformation aPaaS that provides high-productivity features and tools for teamwork collaboration. The developer website allows you to browse API documents, learn from tutorials and sample codes, post questions, share comments with other developers and Kintone engineers, and discuss a variety of developer related topics.

Comments and Discussions

 
-- There are no messages in this forum --
Article
Posted 6 Jun 2019

Stats

861 views
2 bookmarked