Use cases
How to easily integrate google sheets with a flow. This can solve use cases like
Trigger a flow whenever a lead is collected via a google form
Trigger a flow whenever a lead is collected via an external form.
Prerequisite
First set up a flow using the documentation given here
Demo
Let us say you set up a sample flow which looks like this
Once you are done setting up the app script (mentioned below) whenever a new row is added in google sheet you will be able to trigger a whatsapp message
Setting Up Google Apps Script
Open Your Google Sheet:
Open the Google Sheet where you want to track new rows.
Access Script Editor:
Go to
Extensions > Apps Script
in the top menu.
Create a New Script:
A new tab will open with the Apps Script editor. Here, you can write JavaScript code.
Writing the Script
Define the API Endpoint:
Set the API endpoint URL as a variable.
Write a Function to Detect New Rows:
Create a function that checks for new rows. This can be done by storing the last row number in PropertiesService and comparing it with the current last row.
Write the API Call Function:
Use
UrlFetchApp
to make the API call. You can send data from the newly added row in the API request.
Create a Trigger:
Use Apps Script's triggers to run your function automatically when a new row is added.
Setting Up a Trigger
Go to Triggers:
In the Apps Script Editor, click on the clock icon on the left to open
Triggers
.
Add a Trigger:
Click on run and give all the desired permissions
Sample script
const APP_ID = 'k076lREG11gYWO8xCM1v0'
const FLOW_ID = 'googles1heetsjourney7342'
const USERNAME = 'accc351ef14'
const PASSWORD = 'f4e5df6d1b05d18d22c03'
const PHONE_NUMBER_ROW = 'location'
const LAST_ROW_TO_CHECK = 1
function checkForNewRow() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
if (lastRow < LAST_ROW_TO_CHECK) {
return;
}
var scriptProperties = PropertiesService.getScriptProperties();
var lastCheckedRow = scriptProperties.getProperty('lastCheckedRow');
if (!lastCheckedRow || lastCheckedRow < lastRow) {
var rowData = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
callYourAPI(rowData, sheet);
}
scriptProperties.setProperty('lastCheckedRow', lastRow);
}
function sanitisePhoneNumber(phoneNumber) {
const last10digits = phoneNumber.substring(phoneNumber.length - 10);
return "+91" + last10digits;
}
function callYourAPI(rowData, sheet) {
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var dataToSend = {
"appId": APP_ID,
"medium": "whatsapp",
"flowId": FLOW_ID,
"phoneNumber": "", // Placeholder for phone number
"carryPayload": {} // Placeholder for carryPayload
};
Logger.log(headers)
// Mapping data to carryPayload
for (var i = 0; i < headers.length; i++) {
try {
if (headers[i].toLowerCase() === PHONE_NUMBER_ROW) {
dataToSend.phoneNumber = sanitisePhoneNumber(rowData[i]);
} else {
dataToSend.carryPayload[headers[i]] = rowData[i];
}
} catch (e) {
Logger.log("Error in mapping data for header '" + headers[i] + "': " + e.message);
}
}
var apiEndpoint = 'https://bikapi.bikayi.app/integrations/bikPlatformFunctions-initiateFlow';
var encodedCredentials = Utilities.base64Encode(USERNAME + ':' + PASSWORD);
var options = {
'method': 'post',
'contentType': 'application/json',
'headers': {
'Authorization': 'Basic ' + encodedCredentials
},
'payload': JSON.stringify(dataToSend)
};
// Debugging: Print the payload
Logger.log("Sending payload: " + options.payload);
var response = UrlFetchApp.fetch(apiEndpoint, options);
// Debugging: Print the response details
Logger.log("Response Code: " + response.getResponseCode());
Logger.log("Response Content: " + response.getContentText());
}
FAQs
What is the rate limit ?
What is the rate limit ?
One execution takes around 1 second to run.
Triggers total run time
90 mins / day (Free account)
6 hr / day (Workspace account)
URL Fetch calls
20,000 / day (Free account)
100,000 / day (Free account)