Connecting Google Sheets to Automate.io
There are two methods to connect Google Sheets to Automate.io.
Go to APPS in your dashboard. Click the blue ‘+’ button on the top-right. You will see a ‘Add a new app’ pop-up.
Search for Google Sheets in the search box. Select Google Sheets. You will see a window that prompts you to validate the app. Enter your Google credentials.
Click Allow, and then click Save. Now, Google Sheets is successfully connected to Automate.io.
While creating a Bot, you can directly connect Google Sheets either as a Trigger or an Action app. Refer to 'Connecting the Trigger app' or 'Connecting the Action app' in How to create a Bot.
If you are already logged in to your Google account on your browser, you will be asked to allow access to Automate.io.
Supported Triggers and Actions
A Trigger is the event that starts the Bot. So it’s always the first event in the Bot. An Action is the event a Bot performs. It occurs as a response to the Trigger in your Bot.
Automate.io supports the following Triggers and Actions for Google Sheets.
- New Row: Triggers when a new row is added in a worksheet
- New or Updated Row: Triggers when a new Row is added or an existing row is updated
- New Spreadsheet: Triggers when a Spreadsheet is created
- New Worksheet: Triggers when a Worksheet is created
- Add Row : Adds a new row to a given Worksheet
- Add a Worksheet: Adds a new Worksheet to a given Spreadsheet
- Search Rows: Searches a row based on column value
- Update a Row: Update a row in a given Worksheet based on row ID (the Row ID can be found using the ‘Search Rows’ Action)
- Delete a Row: Delete a Row from the specified Worksheet based on the row ID (the Row ID can be found using the ‘Search Rows’ Action)
Working with Google Sheets
If you are using any of the Triggers or the ‘Search Rows’ Action, ‘View Only’ access to the spreadsheet is sufficient. For the rest of the Actions, you must have full Edit permissions.
Setting up your Google Spreadsheet
Before using Automate.io to add rows into your Google Spreadsheet, you need to set it up in a certain way. You must fill the required column names in the first row and some dummy data in the second row, like in the picture shown below.
Note: If your column names are in bold text, Automate.io adds the new rows in bold text.
If your spreadsheet already has some data, make sure that it doesn’t have completely blank rows before the end of the sheet, as shown below. Your Bot will trigger only when new rows are added at the bottom of your sheet.
Make sure that the column names in your sheet do not contain a dot or a period. In other words, do not give the column name as 'First.name'.
And the column name should not contain more than a single space at one place.
Important things to remember while working with Google Sheets
- Do not delete or modify existing data manually in the sheet. If you need to do it, turn your Bot OFF, make the necessary changes, go to edit your Bot to check if the output fields are still valid and then turn it back ON when you are done.
- Changing column names will cause problems. If you change the column names, you will need to edit the Bot, check all your input/output field mappings again and then re-enable the Bot.
- Manual data entry into the sheet may sometimes cause issues. The Bot may pick up 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.
- Do not rename or re-sort the sheet while your Bot is running. If you need to rename your sheet, turn your Bot OFF, make the necessary changes and then turn it back ON.
Finding and Updating an existing row
Let's say you have a spreadsheet with columns First name, Last name, Email, and Phone with only the First name, Last name, and Email columns filled in.
And you have a website form that takes a person's email and phone. Now you want to go to that sheet and add the phone number data corresponding to the email entered.
Let's see how this can be done.
Use the ‘Update a row’ Action for this. Once you select the Spreadsheet and Worksheet, the columns in the sheet will be visible under Input fields.
The tricky part is in specifying which row to update—here is where we must select the ID of the row to be updated. But in most cases, this data is not available. Then you will need to use the ‘Search’ option for Row ID.
Click the Search button. This will add a ‘Search Rows’ action 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, select the Spreadsheet and the Worksheet. Then specify the Column, and under ‘Search For’ select the value (this should be the email coming from the website form in this example) to search for.
Remember that this value should match exactly with the data that is in the spreadsheet cell. So, in this case if the email in the Email column in your spreadsheet is firstname.lastname@example.org the data in the Email field in your website form should also be email@example.com. 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 (if it wasn’t automatically filled when you added the ‘Search Row’ Action) and also the data of all the columns.
So you are essentially reusing the same data (coming from Search Action), and adding new data (phone number) coming from the website form.
Working with Formulas
When using the 'Add Row' Action, if one of your columns needs to be a formula, you should add the formula in the Bot itself (and not in the Sheet). Adding the formula in the Sheet and dragging it will cause the Bot to treat all those rows as filled rows and it will add new rows below the dragged formula.
For example, you can add a SUM Formula to the Sheet Column from the Bot as shown below.
Saving Phone Numbers
When you add phone numbers to a spreadsheet with the 'Add Row' Action, there may be an issue sometimes when the number starts with a plus ( + ). As a workaround, you will need to edit the Bot and add a single quote ( ' ) at the beginning of the phone number field as shown below.
1. The ‘New or Updated row’ Trigger works only for the first 1000 rows on a Google spreadsheet.
2. 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 soon 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)
3. Error Message: This action would increase the number of cells in the workbook above the limit of 5000000 cells.
A single Google spreadsheet has a limit of 5,000,000 cells across all of its worksheets. So, you will see this error message when your spreadsheet cell limit exceeds that number.