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
- A Google account
- A Zoho Flow account
Getting started: Creating a new project in Google Sheets
- Log in to your Google account, and access Google Sheets.
- If you need to automate an existing spreadsheet, access it. Otherwise, you can create a new spreadsheet.
- Navigate to Extensions on the menu bar, then click Apps Script.
- Provide a name for the project (optional).
- Erase the contents in the editor and paste the following function:
- function onEdit(e) {
- // Set a comment on the edited cell to indicate when it was changed.
- var SheetName = "Sheet1"
- var url ="WEBHOOK URL";
- if(SpreadsheetApp.getActiveSheet().getName() == SheetName){
- var row1 = SpreadsheetApp.getActiveSheet().getDataRange().getValues()[0];
- var index = e.range.rowStart;
- var sht = SpreadsheetApp.getActiveSheet();
- var rng = sht.getRange(index, 1, 1, 5);
- var columnName = null;
- var rangeArray = rng.getValues();
- var payload = new Object();
- payload["id"] = index;
- for(i=0;i<row1.length;i++){
- columnName = nextString(columnName);
- var column = "Column"+columnName;
- payload[column] = rangeArray[0][i];
- payload[column+"_Heading"]=row1[i];
- }
- var options = {
- "method": "post",
- "headers": {},
- "payload": payload
- };
- console.log(payload);
- var response = UrlFetchApp.fetch(url, options);
- }
- }
- function nextString(str) {
- if (! str)
- return 'A' // return 'A' if str is empty or null
- let tail = ''
- let i = str.length -1
- let char = str[i]
- // find the index of the first character from the right that is not a 'Z'
- while (char === 'Z' && i > 0) {
- i--
- char = str[i]
- tail = 'A' + tail // tail contains a string of 'A'
- }
- if (char === 'Z') // the string was made only of 'Z'
- return 'AA' + tail
- // increment the character that was not a 'Z'
- return str.slice(0, i) + String.fromCharCode(char.charCodeAt(0) + 1) + tail
- }
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.
- Log in to your Zoho Flow account, and create a new flow.
- Click Configure in the webhook trigger box.
- Copy the generated URL from the configuration window that opens, then click NEXT.
- 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.
- Also enter the correct name of the required sheet in your spreadsheet in place of Sheet1 in var SheetName = "Sheet1".
- Click the Save project button.
- Navigate to the left panel, and click Triggers.
- Click Add Trigger. In the dialog box that follows, select the Event type as On edit, then click SAVE.
- 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>.
- Click Allow in the next step.
Testing the trigger and switching the flow on
- 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.
- 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.
- Switch the flow on.
Note:
- The first row values in the sheet will be displayed as column headings in the variables.
- All the columns of the updated row will be available as variables.
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
New to Zoho Survey?
Zoho Sheet Resources
Zoho Forms Resources
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Zoho DataPrep Resources
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
New to Bigin?
Related Articles
Google Sheets
What is Google Sheets? Google Sheets lets you create and edit spreadsheets online. You can collaborate with others, define formulas, and easily create graphs and charts. How to connect your Google Sheets account to Zoho Flow Select the required ...Integrating Google Forms using Zoho Flow
Google Forms has become a popular web-form tool for collecting contacts, surveys, and quizzes. Creating a form and consolidating the responses in a spreadsheet is a straightforward process and takes only a few minutes. However, what if you need to ...Google Contacts
What is Google Contacts? Google Contacts is an online address book that lets you manage and access your contacts from anywhere. How to connect your Google Contacts account to Zoho Flow Select the required trigger or action. If you select a trigger, ...Flow loops
How to prevent infinite flow looping? Looping is a scenario in which a set of connected tasks execute repeatedly. When such loops are not regulated, they execute endlessly, creating infinite loops. A flow loop happens when a flow gets invoked ...RSS/Atom feed trigger
What is an RSS or Atom feed? An RSS or Atom feed is a structured data format used to deliver frequently updated content to users and apps. These feeds adhere to standards known as Really Simple Syndication (RSS) and Atom. RSS and Atom feeds enable ...
New to Zoho LandingPage?
Zoho LandingPage Resources