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.

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

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

  7.   var index = e.range.rowStart;
  8.   
  9.   var sht = SpreadsheetApp.getActiveSheet();
  10.   var rng = sht.getRange(index, 1, 1, 5);

  11.   var columnName = null;

  12.   var rangeArray = rng.getValues();
  13.   var payload = new Object();

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

  30. function nextString(str) {
  31.     if (! str)
  32.         return 'A'  // return 'A' if str is empty or null

  33.     let tail = ''
  34.     let i = str.length -1
  35.     let char = str[i]
  36.     // find the index of the first character from the right that is not a 'Z'
  37.     while (char === 'Z' && i > 0) {
  38.         i--
  39.         char = str[i]
  40.         tail = 'A' + tail   // tail contains a string of 'A'
  41.     }
  42.     if (char === 'Z')   // the string was made only of 'Z'
  43.         return 'AA' + tail
  44.     // increment the character that was not a 'Z'
  45.     return str.slice(0, i) + String.fromCharCode(char.charCodeAt(0) + 1) + tail
  46. }
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.


    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 CRM Training
      Redefine the way you work
      with Zoho Workplace

        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 CRM Training

          Create, share, and deliver

          beautiful slides from anywhere.

          Get Started Now


            Get started with Zoho Sign

            in a few quick steps!

            Download Help Guide





                      Still can't find what you're looking for?

                      Write to us: support@zohoforms.com


                            




                            

                        Zoho Marketing Automation

                          Zoho Sheet Reources

                           




                              Zoho Forms Resources


                                Secure your business
                                communication with Zoho Mail


                                Mail on the move with
                                Zoho Mail mobile application

                                  Stay on top of your schedule
                                  at all times


                                  Carry your calendar with you
                                  Anytime, anywhere




                                        Zoho Sign Resources

                                          Sign, Paperless!

                                          Sign and send business documents on the go!

                                          Get Started Now


                                              Zoho SalesIQ Resources



                                                  Zoho TeamInbox Resources



                                                          Zoho DataPrep Resources



                                                            Zoho DataPrep Demo

                                                            Get a personalized demo or POC

                                                            REGISTER NOW


                                                              Design. Discuss. Deliver.

                                                              Create visually engaging stories with Zoho Show.

                                                              Get Started Now











                                                                                    • 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 ...
                                                                                    • Webhook trigger

                                                                                      What is a webhook? A webhook is a user-defined URL callback. It is a mechanism that enables apps to receive data updates in real time through a URL call. Webhooks can be incredibly helpful when you're working with data updates that need to be ...
                                                                                    Wherever you are is as good as
                                                                                    your workplace

                                                                                      Resources

                                                                                      Videos

                                                                                      Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                                                      eBooks

                                                                                      Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                                                      Webinars

                                                                                      Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                                                      CRM Tips

                                                                                      Make the most of Zoho CRM with these useful tips.



                                                                                        Zoho Show Resources