Integrations

Google Sheets Integrations

To collect information and store it without a CRM system or something similar, you can use direct integration with Google Sheets.

What you need:

An active Google account with access to Google Sheets
Script for Google sheets (provided below)
Service Call in Chatbot Builder (to make the request)

What to do:

Create a new Google Sheet
Change the Google Sheet privacy settings to Public
Define the Columns according to the data fields you plan to store such as "Phone" and "Input"
Copy the Google Sheet ID from the URL in the browser as per the example below
Add the script attached below by clicking on Extensions → Apps Script

Copy and paste the following code inside in Script editor

📘
Note
In order for this to work:
Remove myFunction and paste the code
var SHEET_NAME = "Sheet1"; should have the same name as the sheet where you will be saving your data
in function postData(e) change PropertiesService.getScriptProperties().setProperty('key', '1dJ-PncB6DkrP4U1GCkQ1Q97tQwiGx-RT3Rk_RY6LT8M');
where you will set your ID value for Google Sheets.

var SHEET_NAME = "Sheet1";      

var SCRIPT_PROP = PropertiesService.getScriptProperties(); 

function doPost(e){
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);
  var data = postData(e);
  lock.releaseLock();
  return data;
}

function postData(e){
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    PropertiesService.getScriptProperties().setProperty('key', '1dJ-PncB6DkrP4U1GCkQ1Q97tQwiGx-RT3Rk_RY6LT8M');
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    
    ///parsing parameter e for content of request
    var request = JSON.stringify({"result":e});  
    var request = JSON.parse(request);
    
    var x = request['result'];
    x = x['postData'];
    x= x['contents'];
    var requestData = JSON.parse(x);
    
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){            // special case if you include a 'Timestamp' column
        var date = new Date();
        row.push( date.getMinutes());
      } 
      else { 
        row.push(requestData[headers[i]]);       // else use header name to get data
      }
    }
    
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
    .createTextOutput(JSON.stringify({"result":"success", "row": nextRow, "message" : requestData}))
                     .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
                         .createTextOutput(JSON.stringify({"result":"error", "error": e, "docum" : doc}))
                         .setMimeType(ContentService.MimeType.JSON);
  }
}

Now you are able to publish your script

Click New Deployment as a Web app
Set access to Anyone
Run the script and Allow all access to everyone

After you have completed the previous steps, the script is deployed as a web application and you will get a web app URL, which will be used as an endpoint in API requests.
Copy the URL and open Chatbot.
In the chatbot use Service Call and POST and use variables we collected through the Flow and update them inside Google Sheets

📘
Use the Raw option and not Form to share data with Google Sheets

🚧
For Google Sheets request limitation, visit: https://developers.google.com/apps-script/guides/services/quotas

Google Sheets Integrations

📘Note

📘

🚧

📘
Note