Google Sheets

Nov 2018: Update for Users seeing "Google API sent 500 (internal error)"

We have recently noticed that a few users are getting an error message Google API sent 500 (internal error) with:

  • Trigger: New Row
  • Action: Add a Row

We are investigating this on priority and are trying to resolve this as early as possible. 

In the meantime, we would recommend trying to use the following alternative trigger and action and reach out to us in case you encounter any further difficulties:

  • Trigger: New Row (old)
  • Action: Add a Row (v4)

Setting up your Google Spreadsheet

Before using a Google Spreadsheet to Add Rows into a sheet, make sure you have the required Column Names filled in the first row, AND some dummy data in the 2nd row as well, as shown here.

How to Find and Update an existing Row

Let's say you have a sheet with Columns like Name, Email, and Phone number. And assume you have Name, Email columns filled-in but Phone numbers are not.

Assume you have a Website Form which takes a person's email and phone number. Let's say you want to now go to the sheet and add the phone number data corresponding to the email entered.  

Let's see how this can be done.

The ‘Update Row’ action can be used for this. Once you select the Sheet and Worksheet, the Columns will be visible as Input fields.

The tricky part is in specifying which row to update – Row ID. In most cases, this data is not available. 

In such cases, you will need to use the ‘Search’ option for Row Id. This will add a ‘Search Row’ action (image below) before the Update Row action.

The idea here is to search for the Row containing the email entered, get the row ID, and use it in the next Action 'Update Row'. 

So, in the 'Search Row' action, you can specify the Column to search for, the value to search for (the email coming from the Form).

This will fetch the matching Row data into the output fields including the Row ID.  Now in the 'Update Row' action, provide this Row ID and also the data of All the columns. So you are essentially reusing the same data (coming from Search Action) for Name and Email, and add the New data (phone number) coming from the Form. 

New Row Trigger

Limitation of New Row Trigger

  • The first two rows should always have some data before setting up the Bot.
  • Avoid empty rows in your sheet. The trigger will only fire when you add a new row immediately after the currently filled rows. I.e if you have a sheet with Rows 1,2,3,4, 9,10 filled in, then adding data in Row 5 will trigger the Bot, but adding data to row 8 or 11 will not.
  • Avoid Deleting or Modifying existing data in the sheet. If you need to it, Disable the Bot first, make necessary changes, then Edit your bot to check if the output fields are still valid and enable it again when you are done.
  • Changing column names will cause problems. If you change the names, you will need to Edit the Bot and check all your input/output field mappings again and re-enable the Bot.
  • Manual data entry into the sheet may sometimes cause issues. The Bot may pickup data before you finish typing the data into the row and hence not function properly. A work-around is to enter data into a different sheet and copy the entire row(s) into the actual sheet.

Working with Formulas

When using the 'Add Row' action, if one of your Columns needs to be a Formula, you will need to add the Formula in the Bot itself (and not in the Sheet). Adding a Formula in the Sheet and dragging it will cause the Bot to treat all those rows as filled rows and it will start adding new rows below the dragged formula. 

For example, you can add a SUM Formula to the Sheet Column from the Bot like this

Saving Phone Numbers

When you add phone numbers to a google sheet with the 'Add Row' action, there may be an issue sometimes when the number starts with a plus ( + ).  As a work around, you will need to edit the Bot and add a single quote ( ' ) at the beginning of the phone number field.