Overview

This article introduces how to post Google Forms responses into Kintone Apps using Google Apps Script.

Benefits of the Integration

Google Forms allows end users to easily create and send questionnaires and event invitations. There are many advantages of capturing the form responses into Kintone's databases over spreadsheets.

1. Access controls
   Kintone allows end users to set granular access controls to the data in its Apps. For example, records that have a "Keep Private" answer for one of their Google Forms questions can be made private. The permissions can be set so that only the HR department can view the submitted data.
2. Business Process Management
   Workflows can be set for each record in the Kintone App. This makes the actions after the form submission much clearer. For example, after receiving a Google Forms submission into Kintone, the record can be assigned to a Design team member to work on creating creatives related to the submission data.
3. Communication
   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 submission, an Events team member can write a comment in the record. They can reach out to a Sales team member who may have some connection with the submitter of the form.

Prepare Google Forms

Step 1

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

Step 2

Enter a title and description for the form.

Step 3

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

Step 4

Click the Add Question icon and select a question type such as Multiple choice. Enter a question in the question field, as well as the response options.

Select Required as below if the question is mandatory.

Step 5

Repeat the process and add more questions.

Google Forms will automatically save every change that is made.

Prepare a Kintone App

Create an App

Log in to Kintone, and create an App with the following fields and settings.

The form should look like the following:

Generate an API Token

Generate an API Token by following the steps in the Generating API Tokens article in the Kintone help site. Apply the Add records permissions to the API Token. Save the settings, and apply the changes to the App.

Prepare a Google Apps Script Program

Step 1

Reopen the Google Form, open the menu on the top right, and select Script editor.

Enter a project name and a file name.

Step 2

The following library is used in this tutorial: https://github.com/Arahabica/KintoneManager
Copy the Script ID from the link above.

Paste the Script ID into the Add a Library field. Click Add to add the library, select the latest version, and save the settings.

Step 3

Type the following code in to the Google Apps Script editor.
The strings Email, attend, number_of_participants, and names_of_participants that are listed in the code are the field codes of the fields in the Kintone App. Match the values of subdomain, appid, and token to values that correspond to the Kintone App.
Make sure to also change the subdomain, appid, and token to values that correspond to the Kintone App.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

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);
}

Save the code when done.

Step 4

Select Triggers from the Edit menu and select a function to run when an event occurs of the Google Form. Click Save when done.

Step 5

In the Project Settings, check the check box for the Show 'appsscript.json' manifest file in editor option.

Return to the editor, and add the below OAuth scopes in appsscript.json.

1
2
3
4
5

"oauthScopes": [
  "https://www.googleapis.com/auth/forms.currentonly",
  "https://www.googleapis.com/auth/forms",
  "https://www.googleapis.com/auth/script.external_request"
]

Test the integration

Click the Send button in the upper right corner of the created Google form. Fill out the options to send the form to other users.

Recipients will receive links to the Google Form.

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

Code Explanation

Getting data from the Google form

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

1
2
3
4
5

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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 += '}]';

Sending data to Kintone

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

1
2
3
4
5
6
7
8

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:

1
2
3
4
5

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 submission to Kintone succeeds if the response code is 200.

1

var code = response.getResponseCode();

Reference

• Google Apps Script | Authorization Scopes