GoogleSheets

The GoogleSheets action allows your bot to work with Google Sheets data from the bot script. To use the tag, create a JAICP integration with Google Sheets first.

How to create an integration

Sign in to JAICP and select the necessary project.
In the toolbar, select Channels, then Create integration → Google Sheets.
Select the necessary account and grant access to jaicp.com.

On the Channels page, you will see a new integration with Google Sheets.

tip

If you want to work with Google Sheets using JavaScript code snippets rather than action tags, use the $integration built-in service instead.

Parameters

Parameter	Type	Description	Required
`operationType`	String	Action type. Possible values: • `readDataFromCells` • `writeDataToCells` • `writeDataToLine` • `deleteRowOrColumn` • `clearCellData`	Yes
`integrationId`	String	Google Sheets integration ID. You can find it on the Channels page in the Integrations section.	Yes
`spreadsheetId`	String	Spreadsheet ID. You can find it in the browser address bar by opening the necessary spreadsheet: `https://docs.google.com/spreadsheets/d/<id>/edit`	Yes
`sheetName`	String	Sheet name.	Yes
`body`	Object	Cell coordinates or values to be saved to the spreadsheet. The value format depends on the action type from the `operationType` parameter.	Yes
`okState`	String	The state the dialog will switch to if the tag script finishes without errors.	No
`errorState`	String	The state the dialog will switch to if the tag script fails: for example, if you specify invalid parameter values.	No

Action types

readDataFromCells

The readDataFromCells action allows retrieving data from cells. In the body parameter, pass an array of objects with the following properties:

cell — cell coordinates.
varName — variable name. The cell data will be stored in $session.<varName>.

caution
Use the following characters for the value of varName: Aa–Zz, _, 0–9. The first character should be a letter. JavaScript reserved words are not allowed.

body =
    [
        {
            "varName": "firstName",
            "cell": "B1"
        },
        {
            "varName": "age",
            "cell": "B3"
        }
    ]

writeDataToCells

The writeDataToCells action saves data to cells. In the body parameter, pass an array of objects with the following properties:

cell — cell coordinates.
values — an array of values to be saved into the cell.

tip
If you pass more than one value in values, the second and subsequent values will be saved in the columns following the specified cell.

body =
    [
        {
            "cell": "B1",
            "values": ["Andrew", "16501770707"]
        },
        {
            "cell": "C1",
            "values": ["{{$session.firstName}}"]
        }
    ]

caution

If you save data to a non-empty cell, its contents will be overwritten.

writeDataToLine

The writeDataToLine action saves data to the beginning of the first row that has enough empty cells. No existing spreadsheet data is overwritten: new data is added instead.

In the body parameter, pass an object with the values property. It should contain an array of values to be saved to the row, for example:

body = {"values": ["Mr.", "Andrew", "Brown", "45"]}

In the spreadsheet below, the data from the example will be added to row six, since there aren’t enough empty cells in rows four and five.

deleteRowOrColumn

The deleteRowOrColumn action deletes rows, columns, and cell ranges. When you delete a row or a column, the spreadsheet data shifts to the top or left respectively.

In the body parameter, pass an object with the values property. It should contain an array of cell ranges to be deleted:

What to delete	How to specify the range
Row	`n:n`, where `n` is the number of the row to be deleted.
Column	`A:A`, where `A` is the letter of the column to be deleted.
Several adjacent rows	`n:m`, where `n` is the first row to be deleted, and `m` is the last one.
Several adjacent columns	`A:C`, where `A` is the first column to be deleted, and `C` is the last one.

body = {"values": ["B:E", "2:8"]}

clearCellData

The clearCellData action deletes the contents of cells and cell ranges. In this case, the rows and columns within the spreadsheet do not shift.

In the body parameter, pass an object with the values property. It should contain an array of cell ranges to be cleared:

What to clear	What to specify
Cell	Cell number, such as `B7`.
Cell range	Cell range in the format `A3:C6`, where `A3` is the upper left cell, and `C6` is the lower right one.

body = {"values": ["A3:C6", "D4"]}

How to use

Retrieve data from a spreadsheet.

state: GetContact
    script:
        $client.row = "123";
    GoogleSheets:
        operationType = readDataFromCells
        integrationId = 34338a54-ii30-9ffa-i6e2-b1b94b8778ob
        spreadsheetId = 66tavVasb2b1agpFuvfnMjqD6GpUJ1Hc4ubiUHwQXh8
        sheetName = Contact list
        body =
            [
                {
                    "varName": "firstName",
                    "cell": "B{{$client.row}}"
                },
                {
                    "varName": "lastName",
                    "cell": "C{{$client.row}}"
                }
            ]
        okState = /GetContact/Verify

    state: Verify
        a: Is your name {{$session.firstName}} {{$session.lastName}}?

Delete data from a spreadsheet.

state: DeleteData
    GoogleSheets:
        operationType = deleteRowOrColumn
        integrationId = 34338a54-ii30-9ffa-i6e2-b1b94b8778ob
        spreadsheetId = 66tavVasb2b1agpFuvfnMjqD6GpUJ1Hc4ubiUHwQXh8
        sheetName = Shipping data
        body = {"values": ["1:7", "B:K"]}

Parameters​

Action types​

readDataFromCells​

writeDataToCells​

writeDataToLine​

deleteRowOrColumn​

clearCellData​

How to use​