Google Sheets

Connect a Google Account

To get started with Google Sheets, connect Automator to your Google account:

1. Visit Automator > Settings in /wp-admin/ on  your website.
2. In the Google tab, click Connect an account.
3. In the list of required permissions, make sure everything is checked off. Sometimes the last 2 checkboxes are unchecked by default.
4. Click Allow to let Automator access your Google Drive files and spreadsheets.

Once this is done, a success message will be displayed on your website and the Google Sheets actions will be available. Once set up, Automator can be used to add new records to a Google Sheet and/or update existing records.

Create a row in a Google Sheet

To configure the Create a row action in Google sheets:

1. Choose the Google Sheets integration in the Actions list.
2. Choose the Create a row in a Google Sheet action.
3. Make selections for the Drive, Spreadsheet and Worksheet for your account. These values determine where the rows will be added and what columns will be available.
4. After clicking the Get columns button, you will see a list of columns for your worksheet on the left and a list of values on the right.
5. For each row, populate the Value that you want to add. In most cases, you will want to choose tokens from the triggers or the system by choosing the * icon to the right of the field. (You cannot add or remove columns from the Google Sheet, you can only add rows for the existing columns. If you add or remove columns in Google Sheets itself, make sure you click Get Columns again.)
6. Save your updates and make sure the triggers, actions and recipe are all live.

Update a row in a Google Sheet

1. Choose the Google Sheets integration in the Actions list.
2. Choose the Update a row in a Google Sheet action.
3. Make selections for the Drive, Spreadsheet and Worksheet for your account.
4. After clicking the Get columns button, you will see a list of columns for your worksheet on the left and a list of values on the right.
5. In the Column search field, choose the column that you will use to identify the row to update. This correlates with the value you enter in the Match value field, which can be a token. This action works by finding a matching value in a column you specify, then updating the values in that row. In other words, suppose that column A in the worksheet has a list of row titles, and the row we want to update has a value of “Uncanny Automator” in the “Title” column. We would then make the Column search column “Title” and the value we want to match is “Uncanny Automator”. The action will find that row and be able to update other cells in that row.
6. In the Row table, review the list of columns and click the Update? switch for rows you want to update. You can update 1 or all columns, including the search column, it’s up to you. By default, no columns will be updated.
7. For every column where Update? is checked, enter a new value in the Value column. This will be the new cell value in the Google Sheet.

Tips and Troubleshooting

• You can change your connected Google account from the Automator Settings page, but you can only have one connected account at a time. Be very careful changing the account; it may break existing recipes.
• If you are connected to Google but values in the recipe drop-down lists aren’t loading, it’s likely a permissions issue. Try disconnecting and reconnecting your account and make sure ALL checkboxes are checked; sometimes the 2 at the bottom of the permissions page are not.
• Make sure each column has a header. Avoid removing headers after they’ve been included in a recipe (renaming them is fine though).
• Do not change worksheet or column names after a recipe is created and live. If you do, you should remap the worksheet in the action and pull down the column names again to make sure everything is correct.
• Worksheet and file names should start with at least 4 non-integer characters. Google Sheets has trouble with names that don’t follow this rule (e.g. TE12 and AXB123 may fail, TEIR12 and AXBC1 are fine, and certainly anything that starts with MORE than 3 letters if fine).
• You should not use quotation marks in column headings.
• New rows will always be added to the first empty row from the top. Even a comma or a space in a row would stop Automator from publishing to that row and look for the next empty row.
• If you decide to change your permissions or want to review what Uncanny Automator is allowed to do, please refer to this Google Support article: https://support.google.com/accounts/answer/3466521?hl=en