Quriobot Academy Quriobot Academy

2-way direct Google Sheets integration via Google App Script

Even though it is possible to integrate with the Google Sheets via Integromat or Zapier, you might still want to set up the integration directly.

The direct integration is also the most flexible way in terms of the possibilities, as you can not only work with the data from the Google Sheets but also call pretty much any other Google service.

To demonstrate the approach we’ll use such bot flow:

  • User is asked for the voucher code
  • The provided voucher code is validated via the google sheet where the codes are stored and checked whether the code exists and not yet redeemed
  • If the voucher code is valid, the user is asked for the email address
  • The voucher code is marked as redeemed by storing the timestamp and the provided email address

Here are the steps to take in order to implement such a flow:

Step 1. Prepare the google sheet with such structure:

Step 2. In the Google Sheets document,  open Tools -> Script editor, and enter such content:

/*
Copyright 2011 Martin Hawksey
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
       http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

// Usage
//  1. Enter sheet name where data is to be written below
        var SHEET_NAME = "Sheet1";

//  2. Run > setup
//
//  3. Publish > Deploy as web app
//    - enter Project Version name and click 'Save New Version'
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
//  4. Copy the 'Current web app URL' and post this in your form/script action
//
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// Validate the voucher
function doGet(e){
try {
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);

    var headerValues = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var headers = {}
    for (var index in headerValues) {
      headers[headerValues[index]] = parseInt(index) + 1
    }
    var vouchers = sheet.getRange(2, headers.Voucher, sheet.getLastRow());
    var searchResult = vouchers.createTextFinder(e.parameter.Voucher).matchEntireCell(true).findNext();

    if(searchResult)
    {
      var row = searchResult.getRow()
      var email = sheet.getRange(row, headers.Email, 1, 1);
      if (email.getValue()) {
        return ContentService
          .createTextOutput(JSON.stringify({"result":"already_used", "row": row}))
          .setMimeType(ContentService.MimeType.JSON);

      } else {
        return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": row}))
          .setMimeType(ContentService.MimeType.JSON);
      }
    }

    return ContentService
          .createTextOutput(JSON.stringify({"result":"not_found"}))
          .setMimeType(ContentService.MimeType.JSON);
} catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e.toString()}))
          .setMimeType(ContentService.MimeType.JSON);
}
}

// Mark the voucher as redeemed
function doPost(e){
// shortly after my original solution Google announced the LockService[1]
// this prevents concurrent access overwritting data
// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000);  // wait 30 seconds before conceding defeat.

try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);

    var headerValues = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var headers = {}
    for (var index in headerValues) {
      headers[headerValues[index]] = parseInt(index) + 1
    }
    var vouchers = sheet.getRange(2, headers.Voucher, sheet.getLastRow());
    var postData = JSON.parse(e.postData.contents)
    var searchResult = vouchers.createTextFinder(postData['Voucher']).matchEntireCell(true).findNext(); //Row Index - 2

    if(searchResult)
    {
      var row = searchResult.getRow()
      var email = sheet.getRange(row, headers.Email, 1, 1);
      var timestamp = sheet.getRange(row, headers.Timestamp, 1, 1);
      if (email.getValue()) {
        return ContentService
          .createTextOutput(JSON.stringify({"result":"already_used", "row": row}))
          .setMimeType(ContentService.MimeType.JSON);

      } else {
        email.setValue(postData['Email']);
        timestamp.setValue(new Date());
        return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": row}))
          .setMimeType(ContentService.MimeType.JSON);
      }
    }

    return ContentService
          .createTextOutput(JSON.stringify({"result":"not_found"}))
          .setMimeType(ContentService.MimeType.JSON);
} catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e.toString()}))
          .setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
    lock.releaseLock();
}
}

// Setup the app access to the google sheet
function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

Step 3. Save the script, providing the preferred name

Step 4. Run the setup function:

Provide your app the required access. Please note that you’ll need to allow it despite the app wasn’t verified by Google.

Step 5. Publish the app as the web app:d

Copy the web app URL for future use.

Step 5. In the Control Room, create new or edit the existing bot. Enable the developer mode:

Step 6. Add short text step, and set the custom validation for it in the step settings:

function(answer, getVariableValue, callback) {
var q = new quriobot.URL('<Google Script Web App URL>', true)
q.query = {Voucher: answer}
quriobot.ajax(
    q.toString(),
    function(responseText) {
      var response = JSON.parse(responseText)
      if (!response || response.result !== 'success') {
        return callback('The voucher is not found or already used, please try again')
      }
      callback()
    }
)
}

Where you’ll need to replace with the real web app URL which you’ve copied in the earlier tutorial step

Step 7. Add an email step.

Step 8. Add JSON webhook response action for marking the redeemed voucher.

Set the condition for the response action, to filter out responses with no email address provided.

In the URL, put the web app URL which you’ve copied in the earlier tutorial step

In the body, put such code;

{
    "Email": "{{email}}",
    "Voucher": "{{voucherCode}}"
}

Step 9. Add such bot variables

  • Name -voucherCode, Type - Step, Select a corresponding step
  • Name -email, Type - Step, Select a corresponding step

Edit this page

Add a Chart (with Chart JS library) in your bot →