Google Tables

For better data handling, upload your data to Mavibot Tables. This will allow your bots to run much faster and avoid errors related to requests to Google Sheets.

• How to prepare a Google Sheet
• Where to find the spreadsheet ID
• How to work with column letters
• How to use variables in column names
• Possible errors
• Functions for working with Google Sheets

GENERAL INFORMATION

How to prepare a Google Sheet

Where to find the spreadsheet ID

The spreadsheet ID is located in the URL (highlighted in color, between d/ and /edit):

How to work with column letters

If you need to convert a column letter to a number, you can use the calculator function c2n. To convert a number to a column letter, use n2c.

You can also shift column letters using the addCols function. The first parameter is a string containing the column letter, and the second parameter is the offset. Use a negative offset to move to the left.

Example:

Result:

How to use variables in column names

You can use a variable to specify the address of a cell to read from, and then use the same variable to retrieve the corresponding value from the response and store it in a variable.

Possible errors

If the spreadsheet is in .XLSX format, the integration will not work. You need to convert the file to Google Sheets format.

Functions for Working with Google Sheets

["key file URL", "second key file URL"]

["key file URL", "second key file URL", {JSON key from file}, {JSON key from file}]

[{JSON key from file}]

["key file URL"]

new = sheet_create_spreadsheet('ЧЕМОДАНчик', 'amadeoartista1992@gmail.com')

{'status': True, 'message': "Google worksheet 'ЧЕМОДАНчик' is created"}

{"status":true,"list_id":1063146761}

{"status":false,"err":"Error or description"}

result = sheet_create_worksheet('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzTl', "New list")

status = get(result, 'status')

How to Delete a Worksheet in Google Sheets

To delete a worksheet, use the following function:

sheet_delete_worksheet(sheet_id, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• worksheet_name_or_id_or_index — the name or ID of the worksheet you want to delete.

Example:

deleted_sfh = sheet_delete_worksheet('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ', 'Лист14')

The function returns a response such as:

{"status":true,"result":"worksheet Лист14 is deleted"}

If an error occurs, the function returns an error description.

How to Get the Number of Worksheets in Google Sheets

To get the total number of worksheets and a list of worksheet index–name pairs, use the following function:

sheet_worksheet_indexes(sheet_id)

• sheet_id — the ID of your Google Sheets spreadsheet.

Example:

worksheet_indexes = sheet_worksheet_indexes('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ')

The function returns a response such as:

{
  "status": true,
  "worksheets_count": 10,
  "worksheet_titles": {
    "1": "Лист1",
    "2": "Лист2",
    "3": "cvtyf",
    "4": "Лист4",
    "5": "о да",
    "6": "Это новый лист",
    "7": "вль и я",
    "8": "таракан",
    "9": "чемодан",
    "10": "БАЗА ДАННЫХ"
  }
}

If an error occurs, the function returns an error description.

How to Rename a Worksheet in Google Sheets

To rename a worksheet, use the following function:

sheet_rename_worksheet(sheet_id, worksheet_name_or_index, new_tile)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• worksheet_name_or_id_or_index — the name or ID of the worksheet you want to rename.
• new_tile — the new worksheet name.

Example:

worksheet_title_change = sheet_rename_worksheet('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ', 'Лист5', 'о да')

How to Write Data Row by Row into Specific Columns

You can collect multiple pieces of data from a user and write them into the first available row of a spreadsheet.

The spreadsheet must have a header row. At least one cell in the first row must contain a value.

Use the following function:

sheet_mapping_cells(sheet_id, cell_data, worksheet_name_or_id_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell_data — a dictionary containing the data to write, for example: '{"A": "value", "D": "#{email}"}', where each key represents a column letter.
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name, index, or ID.

If the request is successful, the function returns a dictionary such as:

{"status":true,"number_row":14}

You can save the number_row value and use it in subsequent operations.

Example:

result = sheet_mapping_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '{"a":"aaaaa", "B": "#{email}"}')

status = get(result, 'status')

number_row = get(result, 'number_row')

Example with a worksheet identifier:

list_name = 'Рейтинг'

result = sheet_mapping_cells('#{gsheet_id}', '{"a":"#{client_id}", "B": "#{ball}"}', '#{list_name}')

How to Write Data to the First Empty Row

You can collect multiple pieces of data from a user and write them into the first empty row of a spreadsheet.

This function works similarly to sheet_mapping_cells, but adds new rows when the end of the spreadsheet is reached.

Note that this method does not necessarily write data to the bottom of the spreadsheet. It writes to the first empty row.

It is best suited for continuously adding data to the same columns.

Use the following function:

sheet_append_row(sheet_id, cell_data, worksheet_name_or_id_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell_data — a dictionary containing the data to write, for example: '{"A": "value", "D": "#{email}"}', where each key represents a column letter.
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name, index, or ID.

If the request is successful, the function returns a dictionary such as:

{"status":true,"number_row":14}

You can save the number_row value for future use.

Example:

result = sheet_append_row('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '{"a":"aaaaa", "B": "#{email}"}')

status = result["status"]

number_row = result["number_row"]

Example with a worksheet identifier:

list_name = 'результаты викторины'
gs_id = '12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT'

result = sheet_append_row(gs_id, '{"a":"#{client_id}", "B": "#{ball}"}', list_name)

How to Write Data to Specific Cells

Use the following function:

sheet_write_cells(sheet_id, cell_data, worksheet_name_or_id_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell_data — a dictionary containing cell addresses and values, for example: '{"A1": "value", "D4": "#{email}"}'. Each key consists of a column letter and a row number.
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name, index (starting from 1), or ID.

Example of cell_data:

'{"a1":"#{переменная}", "b3":"#{переменная}", "c1":"12545", "d20":"просто текст"}'

Data is written to the exact cells you specify. In this example, those cells are a1, b3, c1, and d20.

If the request is successful, the function returns:

{"status":true}

If an error occurs, the function returns an error description:

{"status":false,"err":"Error or description"}

Example of sheet_write_cells:

With worksheet ID 123456789:

result = sheet_write_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzTl', '{"a1":"a3", "b3": "#{email}"}', '123456789')

Without specifying a worksheet:

result = sheet_write_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzTl', '{"a1":"a3", "b3": "#{email}"}')

status = get(result, 'status')

How to Clear Specific Cells

Use the following function:

sheet_remove_cells(sheet_id, cell_list, worksheet_name_or_id_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell_list — an array of cell addresses, for example: '["A1", "D4"]', where each address contains a column letter and a row number.
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name, index (starting from 1), or ID.

If the request is successful, the function returns:

{"status":true}

If an error occurs, the function returns:

{"status":false,"err":"Error or description"}

Example:

With worksheet ID 123456789:

result = sheet_remove_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '["a1", "b3", "c2"]', '123456789')

Without specifying a worksheet:

result = sheet_remove_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '["a1", "b3", "c2"]')

status = get(result, 'status')

The function clears the values in the specified cells: a1, b3, and c2.

Column numbering starts from 1.

How to Write Data to the First Empty Cell in a Row

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

Use the following function:

sheet_append_cell_in_row(sheet_id, row, value, worksheet_name_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• row — the number of the row where the value will be written.
• value — the value to write to the cell.
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name, index (starting from 1), or ID.

If the request is successful, the function returns a response such as:

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

You can save these values for future use.

If an error occurs, the function returns a dictionary with a status of false and an error description:

{"status":false,"err":"Error value"}

Example:

Writing a value to an empty cell in the first row:

result = sheet_append_cell_in_row('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', 1, "Значение")

How to Read Data from a Spreadsheet

Use the following function:

sheet_read_cells(sheet_id, cell_data, worksheet_name_or_id_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell_data — a dictionary specifying the cell ranges to read.
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name, index (starting from 1), or ID.

Example of cell_data:

'{"a1":"a1", "a3":"b4", "c1":"c3"}'

In this example:

• "a1":"a1" returns the value of a single cell.
• "c1":"c3" returns three values from column C.

If the request is successful, the function returns a dictionary containing the status and all requested cell values:

{
  "status": true,
  "A1": "значение",
  "A3": "значение",
  "B3": "значение",
  "A4": "значение",
  "B4": "значение",
  "C1": "",
  "C2": "значение",
  "C3": "ddddddd"
}

If an error occurs, the function returns:

{"status":false,"err":"Error or description"}

Example:

With worksheet ID 123456789:

result = sheet_read_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzTl', '{"a1":"a1", "a3": "b4", "c1": "c3"}', '123456789')

Without specifying a worksheet:

result = sheet_read_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzTl', '{"a1":"a1", "a3": "b4", "c1": "c3"}')

status = get(result, 'status')

How to Clear a Range of Cells

Use the following function:

sheet_remove_range(sheet_id, del_range, worksheet_name_or_id_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• del_range — the range of cells to clear, specified in the format "A1:B2".
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name, index (starting from 1), or ID.

If the request is successful, the function returns:

{"status":true}

If an error occurs, the function returns:

{"status":false,"err":"Error or description"}

Example:

Clear cells A1, B1, A2, and B2 on the first worksheet:

result = sheet_remove_range('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', 'A1:B2')

How to Delete an Entire Row and Shift the Remaining Rows

Use the following function:

sheet_remove_row(sheet_id, row_number, worksheet_name_or_id_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• row_number — the number of the row to delete (numbering starts from 1).
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name, index (starting from 1), or ID.

If the request is successful, the function returns:

{"status":true}

If an error occurs, it returns an error description:

{"status":false,"err":"Error or description"}

Example:

Delete row 14 from the first worksheet:

result = sheet_remove_row('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '14')

How to Delete an Entire Column and Shift the Remaining Columns

Use the following function:

sheet_remove_col(sheet_id, col_number, worksheet_name_or_id_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• col_number — the number of the column to delete (numbering starts from 1).
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name, index (starting from 1), or ID.

If the request is successful, the function returns:

{"status":true}

If an error occurs, it returns:

{"status":false,"err":"Error or description"}

Example:

Delete column 6 from the first worksheet:

result = sheet_remove_col('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '6')

2. How to Search Across All Worksheets and Return Results as a List

If you need to search for values across all worksheets in a spreadsheet, use the following function:

sheet_search_in_all_sheets(workbook_id, search_value)

Parameters:

• workbook_id — the ID of your Google Sheets spreadsheet.
• search_value — the value you want to find.

This function searches for exact matches.

Example:

result = sheet_search_in_all_sheets('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ', '256')

Result:

If the request is successful, the function returns a list of dictionaries containing the search results.

Each dictionary includes the following keys:

• sheet_name — the name of the worksheet where the value was found.
• cell_id — the address of the cell where the value was found.
• search_value — the value that was searched for.

How to Search for Text in a Spreadsheet

Four search modes are available for each search function through the algorithm parameter:

1. Exact match — F
2. Keyword search — K (Latin letter)
3. Levenshtein distance — 80 (a similarity threshold from 1 to 100)
4. Regular expression — R

To select a search mode, pass the corresponding value to the function's algorithm parameter.

A detailed example is provided below using the sheet_search_in_col_return_cell function.

How to Search a Column and Return a Value from Another Column in the Same Row

A column search returns the first matching result.

Use the following function:

sheet_search_in_col_return_cell(sheet_id, query, col_number, return_col, algorithm, worksheet_name_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• query — the search query or value you want to find.
• col_number — the number of the column to search (numbering starts from 1).
• return_col — the number of the column from which to return a value.
• algorithm — the search algorithm:
  • F — exact match.
  • K — keyword search.
  • R — regular expression.
  • 1–100 — similarity percentage (see the explanation above).
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name or ID.

Usage examples

Let's examine all four search algorithms using this function.

Scenario: Search worksheet Лист1 in column 2 for a cell containing the phrase "поисковый запрос" (search query). If a matching cell is found, return the value from column 5 of the same row.

1. Exact Match — F

sheet_search_in_col_return_cell("#{sheet_id}", "поисковый запрос", 2, 5, "F", "Лист1")

If you are searching the first worksheet, the following parameters are sufficient:

sheet_search_in_col_return_cell("#{sheet_id}", "поисковый запрос", 2, 5)

Search for the phrase "Привет мир" (Hello world) in column 2 (column B) of the first worksheet.

Return the value from column 5 (column E) of the same row:

2. Keyword Search — K

The conditions are the same as in the first example.

sheet_search_in_col_return_cell("#{sheet_id}", "поисковый запрос", 2, 5, "K", "Лист1")

For example, if a column contains a cell with the text "Передан поисковый запрос в службу" (The search query was sent to the service), that cell will be found because the phrase "поисковый запрос" is part of the longer text.

Search for the keyword "сосед" (neighbor) in column 1 (column A) of the first worksheet.

Return the value from column 2 (column B) of the same row:

3. Levenshtein Distance

The conditions are the same as in the first example.

sheet_search_in_col_return_cell("#{sheet_id}", "поисковый запрос", 2, 5, "80", "Лист1")

In this example, the value 80 specifies the minimum similarity threshold.

The search is case-insensitive. The similarity value can range from 1 to 100, where 100 represents an exact match.

The function returns the best match.

Search for the phrase "Привет мир" (Hello world), but enter it with spelling mistakes as "превет мор".

The search is performed in column 2 (column B) of the first worksheet.

Return the value from column 5 (column E) of the same row.

4. Regular Expression — R

Scenario: Search worksheet Лист1 in column 1 for a cell matching the regular expression ^\d\d\d$.

This expression matches a cell containing exactly three digits.

If a matching cell is found, return the value from column 5 of the same row.

sheet_search_in_col_return_cell("#{sheet_id}", "^\d\d\d$", 1, 5, "R", "Лист1")

Note that the regular expression is passed as the second parameter instead of a regular search phrase.

If the request is successful, the function returns a dictionary containing the status and search result:

Response parameters:

• status — the search result status.
• find — the value from the selected column.
• row — the row number.
• col — the column number.
• cell — the full cell address.

If an error occurs, the function returns a status of false and an error description:

{"status":false,"err":"Error or description"}

How to Search a Column and Return the Entire Matching Row

Use the following function:

sheet_search_in_col_return_row(sheet_id, query, col_number, algorithm, worksheet_name_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• query — the search query or value you want to find.
• col_number — the number of the column to search (numbering starts from 1).
• algorithm — the search algorithm:
  • F — exact match.
  • K — keyword search.
  • R — regular expression.
  • 1–100 — similarity percentage.
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name or ID.

The function returns the entire row containing the first matching value.

Example:

result = sheet_search_in_col_return_row("#{sheet_id}", "222", '1', 'F')

This example searches for the value 222 in column 1 and returns the entire matching row.

If the request is successful, the function returns a dictionary containing the status and all cell values from the matching row.

Response parameters:

• status — the search result status.
• row_data — a dictionary containing the row data.
• row — the row number.
• col — the column number.
• cell — the full cell address.

If an error occurs, the function returns:

{"status":false,"err":"Error or description"}

How to Search a Column and Return the Entire Matching Row as an Array

Use the following function:

sheet_search_in_col_return_row_array(sheet_id, query, col_number, algorithm, worksheet_name_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• query — the search query or value you want to find.
• col_number — the number of the column to search (numbering starts from 1).
• algorithm — the search algorithm:
  • F — exact match.
  • K — keyword search.
  • R — regular expression.
  • 1–100 — similarity percentage.
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name or ID.

This function works exactly like sheet_search_in_col_return_row.

The only difference is the format of the returned search result.

If the search is successful, the function returns an array such as:

["222","ddddddd","what we are looking for","another value we are looking for","44444555"]

If the matching row contains empty cells, the corresponding array elements will contain empty values.

How to Search a Column and Return All Matching Values

To find all matching values in a column, use the following function:

sheet_search_in_col_return_cells_list(sheet_id, query, col_number, return_col, algorithm, worksheet_name_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• query — the search query or value you want to find.
• col_number — the number of the column to search (numbering starts from 1).
• return_col — specifies which column to return values from when a match is found in col_number.
• algorithm — the search algorithm:
  • F — exact match.
  • K — keyword search.
  • R — regular expression.
  • 1–100 — similarity percentage.
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name or ID.

Example:

result = sheet_search_in_col_return_cells_list("#{sheet_id}", "ффф", '1', '2', "F")

If the request is successful, the function returns a dictionary containing the status and all matching cell values.

Response parameters:

• status — the search result status.
• rows_index — an array containing the row numbers where matches were found.
• quantity — the number of matching rows.
• list — a string containing all returned values from the selected column.

If an error occurs, the function returns a status of false and an error description:

{"status":false,"err":"Error or description"}

How to Search Multiple Columns and Return All Matching Values

If you need to search multiple columns simultaneously and get a list of values from a specified column in rows where all search conditions are met, use the following function:

sheet_search_in_multiple_cols_return_list(sheet_id, columns, return_col, with_index, delimiter, algorithm, worksheet_name_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• columns — the search criteria specifying the values to find.
• return_col — the number of the column from which to return values (numbering starts from 1).
• with_index — determines how the results are numbered or indexed:
  • 0 — sequential numbering (1, 2, 3, ...).
  • 1 — the actual worksheet row index.
  • "" — a list of values, each on a new line, without indexes or numbering.
• delimiter — the separator between the index and the value.
• algorithm — the search algorithm:
  • F — exact match.
  • K — keyword search.
  • R — regular expression.
  • 1–100 — similarity percentage (see the explanation above).
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name or ID.

Example:

result = sheet_search_in_multiple_cols_return_list("#{sheet_id}", '{"1":"ффф", "4": "ффф"}', '2', '1', ' - ', "F")

status = get(result, 'status')

If the request is successful, the function returns a dictionary containing the status and matching values:

{
  "status": true,
  "rows_index": [11, 22, 15],
  "quantity": 3,
  "list": "11 - 111\n15 - 222\n22 - 111\n"
}

Response parameters:

• status — the search result status.
• rows_index — an array containing the row numbers where matches were found.
• quantity — the number of matching rows.
• list — a string containing all returned values from the selected column.

If an error occurs, the function returns a status of false and an error description:

{"status":false,"err":"Error or description"}

How to Search Multiple Columns and Return the Entire Matching Row

If you need to search multiple columns simultaneously and return the first matching row, use the following function:

sheet_search_in_multiple_cols_return_row(sheet_id, columns, algorithm, worksheet_name_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• columns — the search criteria specifying the values to find.
• algorithm — the search algorithm:
  • F — exact match.
  • K — keyword search.
  • R — regular expression.
  • 1–100 — similarity percentage (see the explanation above).
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name or ID.

Example:

result = sheet_search_in_multiple_cols_return_row("#{sheet_id}", '{"1":"ффф", "4": "ффф"}', "F")

status = get(result, 'status')

If the request is successful, the function returns a dictionary containing the status and matching row data:

{
  "status": true,
  "rows_index": [11, 22, 15],
  "quantity": 3,
  "row_data": {
    "1": "ффф",
    "2": "111",
    "3": "",
    "4": "ффф",
    "5": "sds"
  },
  "row": 11
}

Response parameters:

• status — the search result status.
• rows_index — an array containing the row numbers where matches were found.
• quantity — the number of matching rows.
• list — a string containing all values from the selected column.

If an error occurs, the function returns:

{"status":false,"err":"Error or description"}

How to Search for a Value in a Column and Then Write Data to the Spreadsheet

If you need to find a specific value in a column and then write data to the worksheet, use the following function:

sheet_search_in_col_write_cells(sheet_id, cell_data, query, col_number=None, algorithm=None, worksheet_name_or_index=None)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell_data — a dictionary containing the data to write, for example: '{"A": "value", "D": "#{email}"}', where each key represents a column letter.
• query — the search query or value you want to find.
• col_number — the number of the column to search (numbering starts from 1).
• algorithm — the search algorithm:
  • F — exact match.
  • K — keyword search.
  • R — regular expression.
  • 1–100 — similarity percentage.
• worksheet_name_or_id_or_index — an optional parameter specifying the worksheet name or ID.

Example:

result = sheet_search_in_col_write_cells(
    '1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ',
    '{"t":"Пишем пишем"}',
    'А вот и ячейка',
    2,
    "F"
)

status = get(result, 'status')

In this example:

• 'А вот и ячейка' — the value to search for, using the same search logic as sheet_search_in_col_return_cell.
• 2 — the column number where the search is performed.
• '{"t":"Пишем пишем"}' — the data to write to the spreadsheet, using the same mapping logic as sheet_mapping_cells.

If the request is successful, the function returns a dictionary such as:

{"status":true,"number_row":24}

You can save the number_row value and use it in subsequent operations.

If an error occurs, the function returns a status of false and an error description.

Arithmetic Operations in Google Sheets

The supported arithmetic operations include addition, subtraction, multiplication, and division.

Let's examine each operation in more detail.

Division

Use the following function to divide the value in a cell specified using A1 notation:

sheet_div_to_acell(sheet_id, cell, value, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell — the dividend, specified as a cell address in A1 notation (a Latin column letter followed by a row number), for example B2 or C333. If the cell is empty, its value is treated as 0 by default.
• value — the divisor (a number).
• worksheet_name_or_index — an optional parameter specifying the worksheet name or ID.

Example:

result = sheet_div_to_acell("#{sheet_id}", 'A1', 10)

This divides the value in cell A1 by 10.

The result is written back to the same cell.

Using R1C1-style cell coordinates:

sheet_div_to_cell(sheet_id, row, col, value, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• row — the row number (starting from 1).
• col — the column number (starting from 1).
• value — the divisor (a number).
• worksheet_name_or_index — an optional parameter specifying the worksheet name or ID.

If the cell specified by row and col is empty, its value is treated as 0 by default.

Example:

result = sheet_div_to_cell("#{sheet_id}", 1, 1, 10)

This divides the value in cell R1C1 (or A1) by 10.

The result is written back to the same cell.

Multiplication

Use the following function to multiply the value in a cell specified using A1 notation:

sheet_mul_to_acell(sheet_id, cell, value, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell — the first factor, specified as a cell address in A1 notation (a Latin column letter followed by a row number), for example B2 or C333. If the cell is empty, its value is treated as 0 by default.
• value — the second factor (a number).
• worksheet_name_or_index — an optional parameter specifying the worksheet name or ID.

Example:

result = sheet_mul_to_acell("#{sheet_id}", 'a1', 10)

This multiplies the value in cell A1 by 10.

The result is written back to the same cell.

Using R1C1-style cell coordinates:

sheet_mul_to_cell(sheet_id, row, col, value, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• row — the row number (starting from 1).
• col — the column number (starting from 1).
• value — the second factor (a number).
• worksheet_name_or_index — an optional parameter specifying the worksheet name or ID.

If the cell specified by row and col is empty, its value is treated as 0 by default.

Example:

result = sheet_mul_to_cell("#{sheet_id}", 1, 2, 10)

This multiplies the value in cell R1C2 (or B1) by 10.

The result is written back to the same cell.

Subtraction

Use the following function to subtract a number from the value in a cell specified using A1 notation:

sheet_sub_to_acell(sheet_id, cell, value, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell — the minuend, specified as a cell address in A1 notation (a Latin column letter followed by a row number). If the cell is empty, its value is treated as 0 by default.
• value — the subtrahend (the number to subtract).
• worksheet_name_or_index — an optional parameter specifying the worksheet name or ID.

Example:

result = sheet_sub_to_acell("#{sheet_id}", 'a1', 10)

This subtracts 10 from the value in cell A1.

Using R1C1-style cell coordinates:

sheet_sub_to_cell(sheet_id, row, col, value, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• row — the row number (starting from 1).
• col — the column number (starting from 1).
• value — the subtrahend (the number to subtract).
• worksheet_name_or_index — an optional parameter specifying the worksheet name or ID.

If the cell specified by row and col is empty, its value is treated as 0 by default.

Example:

result = sheet_sub_to_cell("#{sheet_id}", 1, 1, 10)

This subtracts 10 from the value in cell R1C1 (or A1).

Addition

Use the following function to add a number to the value in a cell specified using A1 notation:

sheet_add_to_acell(sheet_id, cell, value, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell — the first addend, specified as a cell address in A1 notation (a Latin column letter followed by a row number). If the cell is empty, its value is treated as 0 by default.
• value — the second addend (a number).
• worksheet_name_or_index — an optional parameter specifying the worksheet name or ID.

Example:

result = sheet_add_to_acell("#{sheet_id}", 'a1', 10)

This adds 10 to the value in cell A1.

Using R1C1-style cell coordinates:

sheet_add_to_cell(sheet_id, row, col, value, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• row — the row number (starting from 1).
• col — the column number (starting from 1).
• value — the second addend (a number).
• worksheet_name_or_index — an optional parameter specifying the worksheet name or ID.

If the cell specified by row and col is empty, its value is treated as 0 by default.

Example:

result = sheet_add_to_cell("#{sheet_id}", 1, 1, 10)

This adds 10 to the value in cell R1C1 (or A1).

Concatenation (Joining Strings)

Concatenation appends the string passed to the function to the existing value in a cell.

Use the following function for cells specified using A1 notation:

sheet_append_to_acell(sheet_id, cell, value, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• cell — the first string, specified by its cell address in A1 notation (a Latin column letter followed by a row number).
• value — the second string to append.
• worksheet_name_or_index — an optional parameter specifying the worksheet name or ID.

Example:

result = sheet_append_to_acell("#{sheet_id}", 'a1', 'дописать')

Result:

If the cell originally contains "Привет", the result of the concatenation will be:

"Приветдописать"

Using R1C1-style cell coordinates:

sheet_append_to_cell(sheet_id, row, col, value, worksheet_name_or_index)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• row — the row number (starting from 1).
• col — the column number (starting from 1).
• value — the string to append.
• worksheet_name_or_index — an optional parameter specifying the worksheet name or ID.

Example:

result = sheet_append_to_cell("#{sheet_id}", 1, 1, 'дописать')

Result:

If the cell originally contains "Привет", the result of the concatenation will be:

"Приветдописать"

How to Copy All Data from One Worksheet to Another

To copy all data from one worksheet to another, use the following function:

sheet_copy_list(sheet_id, sourceSheet_Name, destinationSheet_Name)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• sourceSheet_Name — the name of the source worksheet.
• destinationSheet_Name — the name of the destination worksheet.

Example:

result = sheet_copy_list(
    '1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ',
    'Лист1',
    'Лист5'
)

If the request is successful, the function returns a dictionary containing the status and an operation message:

{"status":true, "result": "spreadsheet 'Лист1' copied to 'Лист5'"}

Response parameters:

• status — the result of the copy operation.
• result — the operation message.

If an error occurs, the function returns a status of false and an error description.

How to Copy a Range of Cells from One Worksheet to Another

To copy part of the data (a range of cells) from one worksheet to another, use the following function:

sheet_copy_data_block(sheet_id, sourceSheet_Name, destinationSheet_Name, sourseSheet_data, destinationSheet_data)

Parameters:

• sheet_id — the ID of your Google Sheets spreadsheet.
• sourceSheet_Name — the name of the worksheet containing the source data.
• destinationSheet_Name — the name of the worksheet where the data will be copied.
• sourseSheet_data — a list specifying the coordinates of the source cell range.
• destinationSheet_data — a list specifying the coordinates of the destination cell range.

The sourseSheet_data and destinationSheet_data variables must be lists containing four numbers, for example:

[0, 6, 0, 6]

These numbers represent:

1. Starting row index.
2. Ending row index.
3. Starting column index.
4. Ending column index.

Row and column indexing starts from zero.

For example:

• Column A has index 0.
• Row 1 has index 0.
• Column C has index 2.
• Row 2 has index 1.

For example, the range:

[0, 1, 0, 3]

corresponds to cells A1:C1.

Note that the ending row and column indexes are exclusive, meaning they are not included in the range.

Example of a cell range:

A data block covering cells A1:F5 has the following coordinates:

[0, 5, 0, 6]

How source and destination range sizes affect copying:

If the source range (sourseSheet_data) and destination range (destinationSheet_data) have exactly the same dimensions, all data will be copied within the specified boundaries.

If the source range is larger than the destination range, all source data will still be copied, extending beyond the specified destination boundaries.

If the destination range is larger than the source range, the remaining cells may be filled with empty values if the entire source range fits. Otherwise, the remaining cells will stay empty.

Let's look at some examples.

1. The source range is larger than the destination range

2. The destination range is larger than the source range

Example:

Let's copy the data range A1:F5 from worksheet Лист1 to worksheet Лист2, shifting the copied data one row down and one column to the right.

data_ag2 = [0, 5, 0, 6]

data_ag3 = [1, 6, 1, 7]

result = sheet_copy_data_block(
    '1xeG0NcC5xaXVnXqgGY9z3uUkVlZX-vsx1xOTfK2Wnd4',
    'Лист1',
    'Лист2',
    '#{data_ag2}',
    '#{data_ag3}'
)

If the request is successful, the function returns a dictionary containing the status and an operation message:

{"status":true, "result": "spreadsheet block from 'Лист1' copied to 'Лист2'"}

Response parameters:

• status — the result of the copy operation.
• result — the operation message.

If an error occurs, the function returns a status of false and an error description.