Prerequisites

  1. Active Google account.
  2. Spreadsheet set-up according to the guidelines mentioned below in section Setting up your Google Spreadsheet.

Terminologies

Before jumping to setting up the spreadsheet, let's get to know a few terminologies which would be helpful in setting up the integration.

📌 Last Row Data

The last row data means below the last row filled or bottom of the data present in the spreadsheet. Example in the below screenshot.

📌 Column Headers or Names

The column headers or column names are the values you have provided in row number 1. Column headers or names are highlighted in the below screenshot.

📌 Spreadsheet/Sheet VS Worksheet

The spreadsheet name is what we call the file name whereas, the worksheet name can be seen at the bottom, often called tabs or work tabs.


Setting up your Google Spreadsheet

Before getting started, you’d need to set up your spreadsheet in a certain way for the Bot to be able to work with it. Please follow the below steps or guidelines to make sure your spreadsheet is set up as expected by the Bot.

⚠️ First 2 Columns

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.

⚠️ No Blank Rows

If your spreadsheet already has some data, make sure that it doesn’t have any blank rows before the end of the sheet, as shown below. If there are any, please delete them completely from the spreadsheet.

How to delete blank rows?: Select the blank rows as shown in the above figure, click on Edit, and then click on Delete selected rows. Screenshot below.

⚠️ No Special Characters in Column Names

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'.

⚠️ No Multiple Spaces

Make sure that column names should not contain more than a single space anywhere in the spreadsheet (especially column names).


Things to remember (after setting up your spreadsheet)

⭕️ No Modifications

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.

⭕️ No Column Name Changes

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.

⭕️ No Manual Entry

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 workaround is to enter data into a different sheet and copy the entire row(s) into the actual sheet.

⭕️ Sheet Name

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.


Triggers

⚡️ New Row

Description: Used to trigger the Bot when a new row of information is added to the worksheet specified in the Bot. This trigger will work ONLY for a NEW row added at the last row data present in the spreadsheet.

For example, if your spreadsheet looks like the image below, the Bot will trigger when you add some data in row number 8, and so on. You would need to specify the spreadsheet name as well as the worksheet name in the Bot.

Data limits: Maximum of 20 rows in a single data check interval. For example, if you are on the Startup plan, the data check time is 2 minutes. A maximum of 20 rows every 2 minutes would be fetched. If there are more than 20 rows received in 2 minutes, there might be a loss of data.

Unsupported items: Please follow all the guidelines mentioned in the section 'Setting up your Google Spreadsheet'.

⚡️ New or Updated Row

Description: Used to trigger the Bot when data is added in a new row OR data is modified in an existing row. If you specify the column in the trigger condition, then the Bot will trigger ONLY IF that column is updated, and not for any other updates in the row.

For example, if I select the column 'Phone' as shown in image 1 below, the Bot would only work when there are changes made in any of the phone numbers present in the spreadsheet (shown in image 2 below).

Image 1
Image 2

Data limits: This trigger will work only for the changes made in the first 1000 rows in the spreadsheet. Once your spreadsheet has more than 1000 rows filled, then you would need to either use a new worksheet or clear data from row number 2 to row number 1000 and reuse the same sheet again for this trigger to work.

Unsupported items: Please follow all the guidelines mentioned in the section 'Setting up your Google Spreadsheet'.

⚡️ New Spreadsheet

Description: Used to trigger when a new spreadsheet is created in your Google Sheets account. This will only provide you spreadsheet name and its ID (unique and internal to Google Sheets).

Unsupported items: This trigger will not work or provide any data you put into the spreadsheet.

⚡️ New Worksheet

Description: Used to trigger when a new worksheet is created in an already existing spreadsheet in your Google account. This will only provide you worksheet name and its ID (unique and internal to Google Sheets). You'd need to specify the spreadsheet in which the Bot should look for new worksheets.

Unsupported items: This trigger will not work or provide any data you put into the worksheet or spreadsheet.


Actions

✅ Add Row

Description: This action can be used to add a new row to the specified spreadsheet. Once the spreadsheet and the worksheet are specified, the Bot will automatically populate the columns created on the worksheet as input fields. You'd need to map the fields as per your requirements.

How to map fields?

Please check out a short video on how to map the fields as per your requirements.

For example, if your spreadsheet looks like the below, the Bot will add the next piece of information at the last row data (in row number 8) as highlighted in the below image.

✅ Search Row

Description: This action can be used to search for an existing row or cell in the specified spreadsheet. You'd need to specify the name of the spreadsheet, worksheet as well as the column in which you’d like the search to be carried out with the keyword you’d like to search for.

You shall see the output fields only after you specify the spreadsheet, worksheet, and column name.

Data limits: If there are two cells matching the search keyword, the Bot will throw output containing only the first row found by it.

✅ Update Row

Description: This action can be used to modify existing data in the specified spreadsheet. Updating data works based on rows which means, you'd need to search for a row first, to update it.

How to search for a row and update it?

Please check out a short video on how to search for a row, and then update it.

✅ Delete Row

Description: This action can be used to delete a row based on the row number provided. To get the row number, you would need to search for the relevant row first.

You may follow the above video to understand how to search a row and use its row number. Once you have the row number, you can use the same in the Delete Row action.

✅ Add Worksheet

Description: This action can be used to simply add/create a fresh worksheet in the specified spreadsheet.

Data limits: You would be unable to specify any content to be added to the new worksheet.


Common problems with Google Sheets integration

❗️ My Bot is not working. I am using the New Row trigger.

This might happen because of either of the below reasons.

  1. You have not setup your spreadsheet as per the guidelines mentioned in the Setting up your Google Spreadsheet section.
  2. If you have violated any of the instructions mentioned in the Things to remember (after setting up your spreadsheet) section.

❗️ I cannot see my spreadsheet or worksheet in the drop-down menu while setting up the Bot.

This might happen if you have created the spreadsheet after starting to configure the Bot OR if you would have connected to another Google account instead of the one which has the spreadsheet.

Please re-connect the Google Sheets app, with the correct credentials and then try to set up the Bot from scratch. To reconnect Google Sheets, please follow these steps.

  1. Open the Apps tab in the app.
  2. Hover on the Google Sheets app.
  3. Click on the reconnect icon appearing on the app icon.

❗️ My Bot is not working. I am using the New or Updated Row trigger.

As of now, the ‘New or Updated row’ trigger works only for the first 1000 rows in a spreadsheet. If you add or modify existing rows that are beyond the row number 1000, the Bot will not trigger.

❗️ Error: This action would increase the number of cells in the workbook above the limit of 5000000 cells.

A single spreadsheet in Google Sheets 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.

You can handle this either by deleting the existing data or by creating a new spreadsheet and then use it in the Bot.

❗️ Error: The domain administrators have disabled Drive apps while authenticating the Google Sheets app.

This error usually occurs if the Drive Scope is not provided for the Google Sheets account. Our Triggers and actions need Drive Scope to work.

Please contact your G-Suite admin and get the required permissions to get the authentication done. You may refer to this link for more details.

Did this answer your question?