This feature is available on the maximum tariff.

Contents

  • How to prepare a Google Sheet
  • How to write a JSON query
  • How to write rows to specific columns
  • How to write data to specific cells
  • How to delete data from specific cells
  • How to write to the first empty cell in a row
  • How to read data from a table
  • How to find text in a table (return the first matching row)
  • How to list all matches in a table
  • How to completely delete a row (with offset)
  • How to completely delete a column (with offset)
  • How to create buttons to select a sheet in a table
  • How to work through your account
  • How to work with column letters
  • How to set variables in column names
  • How to delete data from a table
  • How to add a sheet to a table
  • Potential errors
  • Google Sheets functions (API)

In this article, we'll look at how bots can interact with Google Sheets.

To use this functionality, you need:

  • A Google Sheet with editing access enabled via link
  • A function URL
  • Query parameters

How to write a JSON query

Go to the settings of the block from which you want to send the request.

Select the POST JSON request type, enter the URL of the required function, and add the parameters to the JSON POST parameters field.

To see the response returned by your request, enter {custom_answer} in the Answer field, save the block, and run through the chain.

You can then save individual values from the response to variables in the Save Value from JSON Response field using the following syntax:

parameters_from_query -> your_variable

If the response contains nested parameters, extract them as follows.

For example, if the response is:

{
  "cell_number": {
    "row": 4,
    "col": 2
  }
}

Use:

cell_number|row -> Row
cell_number|col -> Column

Now let's look in more detail at what bots can do with Google Sheets.

How to write rows to specific columns

You can collect data from a user and write it to the first available row in the table. This is done using the mapping function.

The header must be filled in — at least one cell in the first row must contain a value.

Function URL:

https://store.mavibot.ai/function/gsheets

Query parameters:

{
  "id": "table id",
  "mapping": {
    "a": "#{variable}",
    "b": "#{yet}",
    "c": "#{yet}",
    "d": "just text"
  }
}

If you want to write data to a sheet other than the first one, add the list_name parameter:

{
  "id": "table id",
  "mapping": {
    "a": "just text",
    "b": "#{variable}"
  },
  "list_name": "Sheet name"
}

Where:

  • id — the ID of your Google Sheet. You can get it from the Google Sheets URL.
  • a, b, c, d — column names.
  • list_name — the name of the sheet, for example "Sheet 2".

Example Google Sheets URL:

https://docs.google.com/spreadsheets/d/1aUbbUaw2SRnJFAavv06Noa1EzumhyShKDm7ie6lYKc4/edit#gid=0

In this example, the table ID is:

1aUbbUaw2SRnJFAavv06Noa1EzumhyShKDm7ie6lYKc4

If the request is completed successfully, the response will look like this:

{
  "number_row": 8
}

You can save the returned row number and use it later.

If you don't want to make the table publicly editable via link, you need to pass the creds_path parameter with the path to your authorization data file.

For more information, see the Work through your account section.

Example:

{
  "id": "table id",
  "mapping": {
    "a": "#{variable}",
    "b": "#{yet}",
    "c": "#{yet}",
    "d": "just text"
  },
  "creds_path": "path to your authorization data file"
}

How to write data to specific cells

Function URL:

https://store.mavibot.ai/function/gsheets

Query parameters:

{
  "id": "table id",
  "write": {
    "a1": "#{variable}",
    "b3": "#{yet}",
    "c1": "#{yet}",
    "d20": "just text"
  }
}

Data is written to the specific cells you provide — in this example: A1, B3, C1, and D20.

Column numbering starts from 1.

If you want to write data to a sheet other than the first one, add the list_name parameter:

{
  "id": "table id",
  "write": {
    "a1": "#{variable}",
    "b3": "#{yet}",
    "c1": "#{yet}",
    "d20": "just text"
  },
  "list_name": "Sheet name"
}

If the request is completed successfully, no parameters are returned.

If you don't want to make the table publicly editable via link, pass the creds_path parameter with the path to your authorization data file.

For more information, see the Work through your account section.

Example:

{
  "id": "table id",
  "write": {
    "a1": "#{variable}",
    "b3": "#{yet}",
    "c1": "#{more}",
    "d20": "just text"
  },
  "list_name": "Sheet name",
  "creds_path": "path to your authorization data file"
}

How to delete data from specific cells

Function URL:

https://store.mavibot.ai/function/gsheets

Query parameters:

{
  "id": "table id",
  "remove": [
    "a1",
    "b3",
    "c2"
  ]
}

This removes values from the specified cells — in this example: A1, B3, and C2.

Column numbering starts from 1.

If you want to remove values from cells on a sheet other than the first one, add the list_name parameter:

{
  "id": "table id",
  "remove": [
    "a1",
    "b3",
    "c2"
  ],
  "list_name": "Sheet name"
}

If the request is completed successfully, no parameters are returned.

If you don't want to make the table publicly editable via link, pass the creds_path parameter with the path to your authorization data file.

For more information, see the Work through your account section.

Example:

{
  "id": "table id",
  "remove": [
    "a1",
    "b3",
    "c2"
  ],
  "list_name": "Sheet name",
  "creds_path": "path to your authorization data file"
}

How to write to the first empty cell in a row

You can write data to a specified row. The value will be added to the first empty cell to the right of the last filled cell.

This is done using the append_in_row function.

Function URL:

https://store.mavibot.ai/function/gsheets

Query parameters:

{
  "id": "table id",
  "append_in_row": "8",
  "value": "written value"
}

If you want to write data to a row on a sheet other than the first one, add the list_name parameter:

{
  "id": "table id",
  "append_in_row": "8",
  "value": "written value",
  "list_name": "Sheet name"
}

Where:

  • id — the ID of your Google Sheet.
  • append_in_row — the row number where the value should be written.
  • value — the value that will be written to the cell.
  • list_name — the name of the sheet.

If the request is completed successfully, the response will look like this:

{
  "number_col": 10,
  "col_name": "J3"
}

You can save the returned values and use them later.

If you don't want to make the table publicly editable via link, pass the creds_path parameter with the path to your authorization data file.

Example:

{
  "id": "table id",
  "append_in_row": "8",
  "value": "written value",
  "creds_path": "path to your authorization data file"
}

How to read data from the table

Function URL:

https://store.mavibot.ai/function/gsheets

Query parameters:

{
  "id": "table id",
  "read": {
    "a1": "a1",
    "b3": "b4",
    "c1": "c10",
    "d20": "a1"
  }
}

Use the read parameter to specify the ranges you want to retrieve.

For example:

  • "a1": "a1" returns one cell.
  • "c1": "c10" returns 10 values from column C.

The response contains the cell addresses and their values.

Column numbering starts from 1.

If you want to read values from a sheet other than the first one, add the list_name parameter:

{
  "id": "table id",
  "read": {
    "a1": "a3",
    "b3": "b6"
  },
  "list_name": "Sheet name"
}

If the request is completed successfully, the response will contain the cell names and their values.

Example:

{
  "A1": "value 1",
  "A2": "value 2",
  "A3": "value 3"
}

If you don't want to make the table publicly editable via link, pass the creds_path parameter with the path to your authorization data file.

For more information, see the Work through your account section.

Example:

{
  "id": "table id",
  "read": {
    "a1": "a3",
    "b3": "b6"
  },
  "list_name": "Sheet name",
  "creds_path": "path to your authorization data file"
}