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

Google Sheets ID
- 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 datain
function postData(e)
changePropertiesService.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
Updated 8 months ago