Skip to main content

Multiple ways to update cells in a Google Spreadsheet

Updating cells using Google Spreadsheet API can be tricky.
Here some real case examples with code coming from a Spell to show you multiple ways to update a Google Spreadsheet document

Example 1: Update only one cell​

To update only one range, use PUT /v4/spreadsheets/{spreadsheetId}/values/{range}

You can define the range as a variable or directly inside the connector (as a value)

const range = `Sheet1!C1`;
const updateOneCell: Dataputvspreadsheetsspreadsheetidvaluesrange = await connectorService.config("62d9c85b6b45030961d86ba2", {
    body: {
        values: [
            ["test"]
        ]
    } as Bodyputvspreadsheetsspreadsheetidvaluesrange
});

Example 2: Update multiple cells on the same range​

To update multiple cells on the same range, use PUT /v4/spreadsheets/{spreadsheetId}/values/{range}

In this case, we'll update A1, B2, C2 and C4. So we'll use the range A1:C4 to include all the cells.

You can represent this range as an array of array, like this:

[
  [A1, B1, C1],
  [A2, B2, C2],
  [A3, B3, C3],
  [A4, B4, C4],
];

We have an array of arrays of values. To explain it:

  • We have a "global" array, representing all the values inside our selected range
  • Inside this "global" array, each array represent a row
  • Inside a "row" array, each value represent a cell

So the update using a connector is now pretty easy and will look like this:

const range = `Sheet1!A1:C4`;
const updateMultipleCells: Dataputvspreadsheetsspreadsheetidvaluesrange = await connectorService.config("62d9ca706b45030961d86d59", {
    body: {
        values: [
            ["A1 cell", ,],
            [, "B2 cell", "C2 cell"],
            [],
            [, , "C4 cell"]
        ]
    } as Bodyputvspreadsheetsspreadsheetidvaluesrange
});

If we left the value empty, that'll leave the cell untouched. If we want to clear an existing value, you can use "" as a value.

Example 3: update multiple range with multiple values in one request​

Use for that POST /v4/spreadsheets/{spreadsheetId}/values:batchUpdate

Same principle, but we will use data object with inside multiple range and multiple values

const googleSpreadsheetResult: Datapostvspreadsheetsspreadsheetidvaluesbatchupdate = await connectorService.config("62d9cd695dda770c112f6c77", {
    body: {
        "valueInputOption": "RAW",
        "data": [
            {
                "range": "Sheet1!C1",
                "values": [
                    ["test 2"]
                ]
            },
            {
                "range": "Sheet1!A1:C4",
                "values": [
                    ["A1 cell 2", ,],
                    [, "B2 cell 2", "C2 cell 2"],
                    [],
                    [, , "C4 cell 2"]
                ]
            }
        ],
    }
});