Trigger a flow when a row is updated in your Google Sheets spreadsheet

How to trigger a flow when a row is updated in your Google Sheets spreadsheet

Google Sheets is an online spreadsheet application that's popular amongst both individuals and businesses. You can easily integrate Google Sheets with hundreds of applications using Zoho Flow. In addition to the variety of standard triggers and actions that are available, you can employ a simple trick to get more out of your Google Sheets integrations—trigger your flows whenever the contents of a row in a spreadsheet is modified.

You can use this technique to create flows that can notify you, update data in other apps, and more. In this guide, we'll go through how to set up this trigger in Zoho Flow. 

What you will need

  1. A Google account
  2. A Zoho Flow account

Getting started: Creating a new project in Google Sheets

  1. Log in to your Google account, and access Google Sheets.
  2. If you need to automate an existing spreadsheet, access it. Otherwise, you can create a new spreadsheet.
  3. Navigate to Extensions on the menu bar, then click Apps Script.



  4. Provide a name for the project (optional).
  5. Erase the contents in the editor and paste the following function:
  1. function onEdit(e) {
  2.   // Set a comment on the edited cell to indicate when it was changed.

  4. var SheetName = "Sheet1"
  5.   var url ="WEBHOOK URL";

  7. if(SpreadsheetApp.getActiveSheet().getName() == SheetName){
  8.   var row1 = SpreadsheetApp.getActiveSheet().getDataRange().getValues()[0];

  10.   var index = e.range.rowStart;
  11.   
  12.   var sht = SpreadsheetApp.getActiveSheet();
  13.   var rng = sht.getRange(index, 1, 1, 5);

  15.   var columnName = null;

  17.   var rangeArray = rng.getValues();
  18.   var payload = new Object();

  20.   payload["id"] = index;
  21. for(i=0;i<row1.length;i++){
  22. columnName = nextString(columnName);
  23. var column = "Column"+columnName;
  24.   payload[column] = rangeArray[0][i];
  25.   payload[column+"_Heading"]=row1[i];
  26. }
  27. var options = {
  28. "method": "post",
  29. "headers": {},
  30. "payload": payload
  31. };
  32. console.log(payload);
  33. var response = UrlFetchApp.fetch(url, options);
  34. }
  35. }

  37. function nextString(str) {
  38.     if (! str)
  39.         return 'A'  // return 'A' if str is empty or null

  41.     let tail = ''
  42.     let i = str.length -1
  43.     let char = str[i]
  44.     // find the index of the first character from the right that is not a 'Z'
  45.     while (char === 'Z' && i > 0) {
  46.         i--
  47.         char = str[i]
  48.         tail = 'A' + tail   // tail contains a string of 'A'
  49.     }
  50.     if (char === 'Z')   // the string was made only of 'Z'
  51.         return 'AA' + tail
  52.     // increment the character that was not a 'Z'
  53.     return str.slice(0, i) + String.fromCharCode(char.charCodeAt(0) + 1) + tail
  54. }
You will have to replace 'WEBHOOK URL' in the above function with the URL generated while creating a new flow (with webhook trigger) in Zoho Flow. To do so:

Creating a new flow: Using Zoho Flow's webhook trigger

A trigger is an event that starts a workflow. There are different types of triggers available in Zoho Flow. The webhook trigger generates a unique URL, which can be configured in third party applications (like Google Sheets in this scenario) to instantly transmit data to Zoho Flow upon an event. 
  1. Log in to your Zoho Flow account, and create a new flow.
  2. Click Configure in the webhook trigger box.



  3. Copy the generated URL from the configuration window that opens, then click NEXT.
  4. Return to Google Apps Script tab and replace WEBHOOK URL in the function with the copied URL within the quotes. Do not remove the quotes.



  5. Also enter the correct name of the required sheet in your spreadsheet in place of Sheet1 in var SheetName = "Sheet1". 
  6. Click the Save project button.



  7. Navigate to the left panel, and click Triggers.



  8. Click Add Trigger. In the dialog box that follows, select the Event type as On edit, then click SAVE.



  9. You will be asked to select your Google account. Since you are inserting a code, it will ask you to proceed only if you trust the developer. Do not worry. Click Advanced, then click Go to <your project name>.
  10. Click Allow in the next step.

Testing the trigger and switching the flow on

  1. Return to Zoho Flow and click TEST. You can try changing the data in your spreadsheet to test if it is working perfectly. Click DONE.



  2. Connect actions or decisions to the webhook trigger in the flow builder. The values of the modified row will be available as variables in the Insert Variable panel.
  3. Switch the flow on.

Note: 
  1. The first row values in the sheet will be displayed as column headings in the variables.
  2. All the columns of the updated row will be available as variables.