How to capture Google Forms responses into a Kintone Database

• Introduction
• 1. Preparing a Google Form
• 2. Preparing a Kintone App
• 3. Creating a Google Apps Script Program
• 4. Testing the integration
• 5. Summary

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.

   

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.

   

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.  

   

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.
 

Step 2

Enter the title and description of your form.

Step 3

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

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.

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

Step 5

Repeat the process until the event invitation form is completed.

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

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.

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

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".

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

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.
 

Enter a project name and a file name.
 

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".
 

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

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.

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.

Recipients will receive links to the Google Form.

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

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!