Categories
Getting started Billing Bot settings Platform connection Text Buttons Mediafiles Other components Payment systems Growth tools Broadcasting Pre-configured components Communication with users Statistics Users FAQ Requests and integrations Artificial intelligence in a bot Notifications Variables Variables 2.0 and helpers Regular expressions Events Cases Problem solving

Google Sheets Integration

The Google Sheets component creates spreadsheets in Google Drive, manages data in them, and transfers data from the sheets to the

Functions

1. Creating a new spreadsheet with the ability to perform operations.

2. Working with existing spreadsheets.

3. Adding and updating data in the spreadsheet.

4. Searching for and retrieving data from the spreadsheet.

5. Deleting data from cells while keeping the row empty, or deleting the entire row with data.

Connecting to your Google account and the spreadsheets within it

Click the Bind Google Account button, choose your account, and grant all Google permissions to proceed.

2. Go to the Builder section, create a new screen, and add the Google Sheets component to it.

3. To select a spreadsheet from your Google Drive, open the dropdown in the Spreadsheet ID field. This list will show sheets created in Botmother through the Google Sheets component.

If you created a sheet in your Google account after adding the component, it won't appear in the dropdown list. To integrate such a sheet, open it and copy its ID from the browser’s address bar. The spreadsheet ID starts after d/ and ends before /edit.

For example, in this URL: 

https://docs.google.com/spreadsheets/d/175N9bdCOsyTbZHErxIecapimj6iDLeF4ST00vzjqu4A/edit?gid=1245975328#gid=1245975328</a>

you only need this part: 

17os3lomZVIUDyM2iF-eAXy0P9zoiAQ0QX1VPPhYE1kl

Go back to the bot editor, click the pencil icon next to the Spreadsheet ID field and paste the spreadsheet ID into this field.

Creating a new spreadsheet

1. Click the "Create new spreadsheet" button.

2. Enter a name for your spreadsheet in the "Spreadsheet title" field that appears, then click "Create".

3. You will then see a notification that the spreadsheet was successfully created.

4. Open your previously linked Google Drive. You will see the spreadsheet you just created through Botmother.

Preparing data for sending to a spreadsheet

In all fields except for Action event and the field for creating a new table, you can use variables.

For example, in the Worksheet name field, you can use either text like Sheet1 or a variable.

Fields for row numbers, column letters, etc., behave the same way — instead of entering a static row number like 1, 2, 3 or column letters, you can use variables, provided they contain the necessary number or letter as their value.

In the operations Create Spreadsheet row, Lookup Spreadsheet row, Update Spreadsheet row, and Lookup and update Spreadsheet row, you can choose how to form a row: from a variable, from a string, or from JSON.

Let’s look at each method in more detail.

From row

1. Check the From row checkbox.

2. In the Column field, enter the letter of the column where you want to insert the data.

Only uppercase English letters are allowed for column names.

3. In the Value field, enter what should appear in the specified column.

4. Click Add before entering a new value for another column.

After saving and activating the component, the values will be inserted into the next available row or the first row if the table is empty.

You can use variables in the Column and Value fields.

For example, you can collect the user's name, phone number, and preferred date using User Input or Fork, and then pass them into Google Sheets.

After the component is triggered, everything the user sent to the bot will appear in your table.

You can combine static data with data from variables. For example, in column A, the value "Website Request" is static, which means it was automatically passed along with the user's message.

From JSON

1. Check the From JSON checkbox.

2. In the Write row field, add data in this format: 

{
"A": "Website Request",
"B": "John Smith",
"C": "john.smith@example.com",
"D": "ACME Inc",
"E": "Landing page",
"F": "Urgent"
}
Use only uppercase English letters for column names.

  • here, A, B, C, D, E, F are the columns where the information will go.
  • the values next to the letters — in our case: Website Request, John Smith, john.smith@example.com, ACME Inc, Landing page, Urgent — will be placed into the corresponding columns.

Column letters A, B, C, D, E, F must be uppercase English letters. Otherwise, the data will not be added to the table.

After saving and activating the component, the information will appear in the first row of the table if it was empty. If the table already had content, the information will be written to the next available row.

You can enter not only static data into the column and value fields, but also use variables. The value of the variable will be written into the table.

For example, you can save user responses into variables using User input or Fork, and then insert these variables in the Google Sheets component, in the JSON with the object to be written to the row.

After the component is triggered, all user input will be saved in the spreadsheet.

You can also combine static and variable values.

Example: 

A – Website Request (static)
B – {{userName}}
C – {{userPhone}}

From a variable

Before sending variables to the spreadsheet, you need to define them.

1. Add a Assign Variable component and set its Name and Value.

2. Change the data type to Object from JSON.

3. Add an object in the value field: 

{"A": "Website Request", "B": "John Smith", "C": "john.smith@example.com", "D": "ACME Inc", "E": "Landing page", "F": "Urgent"}
  • Here, A, B, C, D, E, F are the column names in the table where the values will go.
  • Website Request, John Smith, john.smith@example.com, ACME Inc, Landing page, and Urgent are the values that will be added to the table.

You can use this template by replacing the sample values with your own. In that case, your values will be added to the table.

Column letters A, B, C, D, E, F must be uppercase and in English. Otherwise, the data will not be added to the table.

4. Next, add the Google Sheets component and select the spreadsheet and sheet where the data should go. Then choose the required Action event.

5. Check the box From a variable.

6. In the Variable name with the object to write the row, enter the variable you saved earlier. In our example, the variable looks like this: UsData

After saving and activating the component, the specified values will appear in the next row if the table already contains data, or in the first row if the table is empty.

You can enter not only static data into the column and value fields, but also insert variables. The value of the variable will be added to the table.

For example, you can store the user's answers in variables using User input or Fork, and then insert these variables into an object using Assign a variable.

After that, you can pass the name of the variable with the object to the Google Sheets component.

After the component is triggered, everything the user sent to the bot will appear in your table.

You can combine static data and data from variables. For example, in column A, the value Website Request is static, which means it was passed along with the user’s message automatically.

Further configuration of table operations

Create Spreadsheet row

Adds data to the specified columns in the spreadsheet, forming a new row.

1. Select an existing spreadsheet or create a new one.

2. Select a sheet for actions or leave the default sheet.

3. Select the Action event — Create Spreadsheet row.

4. Choose how the row will be sent: From row, From JSON, or From a variable, and provide the necessary data to be transferred to the spreadsheet based on the method you selected.

5. If needed, set up screens to be displayed on successful or unsuccessful completion of the operation.

6. If necessary, specify the Variable name for the result in the component settings. This variable will store the column letters — A, B, C, D, etc. — and the text sent to those columns. By default, this data goes into the variable last_spreadsheet.

7. Save the changes.

Example of spreadsheet behavior for the "Create Spreadsheet row" operation

Suppose your spreadsheet was initially completely empty:

In the component settings, you selected the first sheet, columns A to F, and specified the values to be sent.

After the component is activated, the specified values will be added to the corresponding columns in the document.

Clear Spreadsheet row

Clears the specified row while preserving the order of other rows.

1. Select an existing spreadsheet or create a new one.

2.Select a sheet to act on or leave the default one.

3. Select the Action event — Clear Spreadsheet row.

4. Enter the row number whose columns will be cleared.

5. Specify the column up to which the row will be cleared. You can enter a single uppercase English letter, such as C. You can also enter two letters like DD, BB, etc., if your table has many columns and you want to clear all of them.

6. If necessary, set up screens that will be shown on success or failure of the operation.

7. If needed, specify the Result variable name in the component settings. This variable will receive the column letters — A, B, C, D, etc., and the text passed to those columns. By default, this data goes into the variable last_spreadsheet.

8. Save your changes.

The letters for the "Clear up to column" field must be uppercase English letters only.

**Example of table behavior for the "Clear Spreadsheet row" operation**

Let's say your spreadsheet initially had two filled rows on the first sheet, as shown in the screenshot below:

The component was configured with the following settings:

  • Row: 1
  • Сlear up to column: C

This means that in the first row, the range from A to C will be cleared. After the component is activated, the document will look as follows:

Delete Spreadsheet row

Deletes a row by the specified number, shifting subsequent rows up.

1. Select an existing spreadsheet or create a new one.

2.Select a sheet for actions or leave the default sheet.

3. Choose the Action event — Delete Spreadsheet row.

4. Enter the row number to delete.

5. Optionally, set up screens that will be triggered on success or failure of the operation.

6. Optionally, specify the name of a variable for the result in the component settings. This variable will store the column letters — A, B, C, D, etc., and the text that was in those columns. By default, this data is stored in the last_spreadsheet variable.

7. Save the changes.

Example of spreadsheet behavior for the “Delete Spreadsheet row” operation

Let’s say your spreadsheet initially had three filled rows on the first sheet, as shown in the screenshot below:

If you specified Row index to delete: 2 in the component settings, the second row will be deleted, and the third row will move up to take its place after the component is activated.

Get many Spreadsheet rows

Returns up to 100 rows in JSON format from the selected range.

1. Select an existing spreadsheet or create a new one.

2. Choose a sheet to work with or leave the default one.

3. Select the Action event — Get many Spreadsheet rows.

4. Enter the desired columns range in the format A:C or A:CC if there are many columns. All letters must be uppercase and in English. The range is always inclusive — for example, if you specify A:C or A:CC, the bot will receive columns from A to C or A to CC inclusively.

5. Select the Row count to return to the bot.

6. Specify the First row number to start from.

7. Optionally, set up screens to be displayed upon successful or failed execution of the operation.

8. Optionally, set the Variable name for the result in the component settings. This variable will receive the column letters — A, B, C, D, etc. — and the text passed into these columns. By default, this data is stored in the variable last_spreadsheet.

9. Save the changes.

Example of table behavior for the “Get many Spreadsheet rows”

Suppose your table looks like the screenshot below, and you need to get data from rows 4 and 5, from columns A to D inclusive.

The component settings were configured as follows:

  • Columns: A:D
  • Row count: 2
  • First row: 4

After saving and activating the component with these settings, the bot will output information from rows 4 and 5 within the A to D column range.

You can display rows using the helper: 

stringify

If no variable was specified, you can output the data like this: 

{{stringify last_spreadsheet}}

By default, the response from the spreadsheet is stored in the last_spreadsheet variable, but you can store this response in another variable. To do this, open the component settings and enter your variable in the Variable name for result field.

If no variable was specified, you can output the data like this: 

{{stringify last_spreadsheet}}

To get the list ordered, use this template: 

{{#each last_spreadsheet.rows}}
{{#each this}}
{{#not (eq @key "row")}}
{{@key}}. {{this}}
{{/not}}
{{/each}}

{{/each}}

Lookup Spreadsheet rows

Searches and returns information from multiple rows (up to 100) based on specified values.

1. Select an existing spreadsheet or create a new one.

2. Choose the sheet for the action or leave the default one.

3. Select the Action event — Lookup Spreadsheet rows.

4. Specify the column and the value to search for. If the specified value is not found in the selected column, the bot will return null.

5. Then, specify the number of rows to retrieve from the spreadsheet. For example, if there are 50 matching rows but you specify 20, only the first 20 will be returned.

6. If needed, set up screens to be executed on success or failure of the operation.

7. Optionally, specify a Variable name for result in the component settings. This variable will contain the column letters (A, B, C, D, etc.) and the text from those columns. By default, this data is stored in the last_spreadsheet variable.

8. Save your changes.

Example of spreadsheet behavior for the “Lookup Spreadsheet rows”

Let’s say your spreadsheet contains three rows with the name *Tanya*, but you only want to retrieve the first two — the component is set to Row count: 2. The component is also configured with the following: Column: A, lookup value: Tanya.

With these settings, you will receive all the information from the rows where Tanya is present in column A.

Since the request was for 2 rows, only the first two matching rows will be returned, even though there are more matching values in the table.

ou can display rows using the helper: 

stringify

If no variable was specified, the data can be displayed like this: 

{{stringify last_spreadsheet}}

To display the list in an ordered format, use this template: 

{{#each last_spreadsheet.rows}}
{{#each this}}
{{#not (eq @key "row")}}
{{@key}}. {{this}}
{{/not}}
{{/each}}
{{/each}}

Get spreadsheet row

Returns a row in JSON format.

1. Select a table from existing ones or create a new one.

2. Select a sheet for actions or leave the default sheet.

3. Select the Action event — Get Spreadsheet row.

4. Specify the row number you want to retrieve.

5. If necessary, set the screens that will run on success and failure of the operation.

6. If needed, specify the Variable name for result in the component settings. This variable will contain the column letters — A, B, C, D, etc. — and the text passed into these columns. By default, this data is stored in the variable last_spreadsheet

7. Save your changes.

Example of table behavior for the “Get Spreadsheet row”

Suppose we need to retrieve the sixth row from the document shown in the screenshot below.

If you enter Row: 6 in the settings, the bot will return the 6th row of the document.

You can display the row using the helper: 

stringify

If no variable was specified, the data can be displayed like this: 

{{stringify last_spreadsheet}}

To get the list in an ordered format, use this template: 

{{#each last_spreadsheet.rows}}
{{#each this}}
{{#not (eq @key "row")}}
{{@key}}. {{this}}
{{/not}}
{{/each}}
{{/each}}

Lookup Spreadsheet row

Searches for a row by value in one or more columns. If the row is found, its number and data are returned.

1. Select a table from existing ones or create a new one.

2. Select a sheet for actions or leave the default sheet.

3. Select the Action event — Lookup Spreadsheet row.

4. Specify the column where the search will be performed, as well as the value to search for.

5. If necessary, activate the Search from last row checkbox.

6. If necessary, activate the Create Google Sheets Spreadsheet Row if it doesn't exist yet checkbox, then add a row, variable, or JSON object to insert.

7. Choose how the row will be sent: From row, From JSON, or From a variable, and provide the necessary data to be transferred to the spreadsheet based on the method you selected.

8. If needed, set up screens that will be triggered upon success or failure of the operation.

9. Optionally, specify the Result variable name in the component settings. This variable will store column letters — A, B, C, D, etc. — and the text passed to those columns. By default, the data is stored in the last_spreadsheet variable.

10. Save changes.

Example of spreadsheet behavior for the “Lookup Spreadsheet row” with the “Search from last row” checkbox enabled, and without creating a new row if the value is not found.

The component configured as in the example will simply find the first row from the bottom where the value "Tanya" is present in column A.

Even though the table contains another "Tanya" in column A, the bot will return only the first row found from the bottom.

The search table looks like this:

The bot will return the first row from the bottom that matches the parameters set in the component.

Displaying a row using the helper: 

stringify

If no variable was set, you can display the data like this: 

{{stringify last_spreadsheet}}

To make the list appear ordered, use this template: 

{{#each last_spreadsheet.rows}}
{{#each this}}
{{#not (eq @key "row")}}
{{@key}}. {{this}}
{{/not}}
{{/each}}
{{/each}}

Since the Create Google Sheets Spreadsheet Row if it doesn't exist yet checkbox is not enabled in the component, if the name "Tanya" wasn’t in the table, the bot would return nothing, no new row would be created, and the table would remain unchanged.

Example of table behavior for the "Lookup Spreadsheet row" with the "Create Google Sheets Spreadsheet Row if it doesn't exist yet" checkbox enabled.

If the Create Google Sheets Spreadsheet Row if it doesn't exist yet checkbox is enabled in the component settings, you must provide values to add. These values can be taken from a string, from a variable, or from JSON.

After activating the component with the settings as in the example above, a new row will be added to the table — it will be the last one. The bot didn’t find a matching value based on the specified parameters, so it created a new row using the values you provided in the settings. This same row was displayed in the bot.


Update Spreadsheet row

Edits data in an existing row by its number, preserving the order of rows and cells in the spreadsheet.

1. Select an existing spreadsheet or create a new one.

2. Select the sheet to perform actions on, or leave the default sheet.

3. Select Action event — Update Spreadsheet row.

4. Enter the row number you want to update.

5. Specify the column and the new row value.

6. Choose how the row will be sent: From row, From JSON, or From a variable, and provide the necessary data to be transferred to the spreadsheet based on the method you selected.

7. If necessary, set screens to be executed on success or failure of the operation.

8. Optionally, specify a Variable name for result in the component settings. This variable will contain the column letters — A, B, C, D, etc., and the text passed into those columns. By default, this data goes into the last_spreadsheet variable.

9. Save the changes.

Example of table behavior for the "Update Spreadsheet row".

If you set Row: 1 in the component settings and add new values for columns A, B, and C, as shown in the screenshot above, then after activating the component, the first three columns of the first row will be updated with the values you specified in the component.

For example, if before the update the first row had the following data:

Then, after activating the component with the specified settings, the first three columns in the first row will be updated. If new values had been provided for the remaining columns in the component, they would have been updated as well. As a result, the table will look like this:

Lookup and update Spreadsheet

Updates data in a row found by value, preserving the order of rows and cells in the table.

1. Select an existing spreadsheet or create a new one.

2. Select the sheet to work with or leave the default sheet.

3. Choose the Action event — Lookup and update Spreadsheet row.

4. Specify the columns and the values of the row you want to find and update.

5. Choose how the row will be sent: From row, From JSON, or From a variable, and provide the necessary data to be transferred to the spreadsheet based on the method you selected.

6. Optionally, check the box Create Google Sheets Spreadsheet Row if it doesn't exist yet. In this case, a new row will be added to the end of the table with the values you specified.

7. Optionally, check the box Search from last row. This makes the search and update start from the end of the table instead of the beginning.

8. If needed, set up screens to be executed on successful or unsuccessful operation.

9. Optionally, set the Variable name for result in the component settings. This variable will receive the column letters — A, B, C, D, etc., and the text passed to these columns. By default, this data is stored in the variable last_spreadsheet.

10. Save the changes.

Example of spreadsheet behavior for the "Lookup and update Spreadsheet row" without creating a new row if the value is not found

The component filled out as in this example will find the name Agnes in column A and update the first three columns in the found row.

Initially, the spreadsheet looks like this:

If the table had more rows matching the parameters set in the component — for example, if the name Agnes appeared in column A in another row — the bot would still update only the first matching row.

If the "Search from last row" checkbox were selected, the last matching row would be updated instead, but still only one row.

If no matching value was found — that is, if the name Agnes was not present in column A — nothing in the table would change, since the "Create Google Sheets Spreadsheet Row if it doesn't exist yet" checkbox is not selected in this example.

In our case, after the component is activated, three columns are updated in the row where Agnes is located, because three columns were specified in the component. With different settings, you can modify any number of columns in the row found by the specified value.

Example of table behavior for the “Lookup and update Spreadsheet row” the "Create Google Sheets Spreadsheet Row if it doesn't exist yet" checkbox enabled.

After activating the component with the settings as in the example above, a new row will be added to the table — it will be the last one. The bot did not find a value matching the specified parameters to update, so it created a new row with the values you provided in the settings.

To the beginning ↑