Skip to main content
All CollectionsMiscellaneous FAQs
How to integrate google sheets with a flow
How to integrate google sheets with a flow
Updated over a year ago

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

  1. Open Your Google Sheet:

    • Open the Google Sheet where you want to track new rows.

  2. Access Script Editor:

    • Go to Extensions > Apps Script in the top menu.

  3. Create a New Script:

    • A new tab will open with the Apps Script editor. Here, you can write JavaScript code.

Writing the Script

  1. Define the API Endpoint:

    • Set the API endpoint URL as a variable.

  2. 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.

  3. 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.

  4. Create a Trigger:

    • Use Apps Script's triggers to run your function automatically when a new row is added.

Setting Up a Trigger

  1. Go to Triggers:

    • In the Apps Script Editor, click on the clock icon on the left to open Triggers.

  2. Add a Trigger:

    • Click + Add Trigger in the bottom right corner.

    • Set the function to run (checkForNewRow), the deployment (Head), event source (Time-driven), and time interval (e.g., every minute).


  3. 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 ?

  • 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)

Did this answer your question?