Post Google Forms Responses into Kintone

Contents

Overview

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

Benefits of the Integration

Google Forms (External link) 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 (External link) 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 (External link) 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 (External link) 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/ (External link)), and click the [+] under Start a new form.

Screenshot: User clicks the + button under Start a new form.

Step 2

Enter a title and description for the form.

Screenshot: User enters 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.

Screenshot: User checks 'Collect email address' and un-checks all boxes under the 'Requires sign in' option.

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.

Screenshot: User enters a question in the question field.

Select Required as below if the question is mandatory.

Screenshot: User selects the 'Required' toggle switch.

Step 5

Repeat the process and add more questions.

Screenshot: User adds 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 (External link) with the following fields and settings.

Field Type Field Name Field Code
Link Email Email
Radio button Would you like to participate in this event? attend
Number The number of participants number_of_participants
Text area The names of the participants names_of_participants

The form should look like the following:

Screenshot: A Kintone App with similar fields to the questions placed in the Google Form.

Generate an API Token

Generate an API Token by following the steps in the Generating API Tokens (External link) 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.

Screenshot: User selects Script editor.

Enter a project name and a file name.

Screenshot: User enters a project name and a file name in Apps Script.

Step 2

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

Screenshot: User clicks Libraries.

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

Screenshot: User pastes the Script ID into the 'Script ID' field.

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.

Screenshot: User selects Triggers from the Edit menu.

Screenshot: User selects a function to run when an event occurs in the Google Form.

Step 5

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

Screenshot: User checks 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"
]

Screenshot: User adds the OAuth scopes.

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.

Screenshot: User fills out the form to send the created Google Form to other users.

Recipients will receive links to the Google Form.

Screenshot: The Google Form opened from the received link.

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

Screenshot: The response data from the Google Form is added to the 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