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 + 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).
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)

Google Sheets Integration

How to call the initiate flow API

Pop Ups & Other Growth Tools

Google Sheet Block