Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api

Syntax for batchUpdate command with Google Script

I'm working with Google Script and spreadsheets but I can't seem to get what I need.

So far, I was retrieving data from an API and appending it to the end of the list. However now I am doing an optimisation to only add the new data.

This way I am:

  1. Detecting how many new rows I need to add
  2. Adding the new data at the beginning.

I am doing this by adding new rows at the beginning of the document (as many as I have new elements) and updating those new rows with the obtained data. However, I am not able to do this last part.

I can't find an example of the correct syntax of Google Sheets API v4 with Google Script and it's difficult to see where I'm going wrong.

Right now what I have is the following:

insertRowsInSheet(sheet.cases.id, newDocuments.length, data);

Where:

  1. sheet.cases.id: This is the id of the spreadsheet.
  2. newDocuments.length: This is the number of new rows I need to add.
  3. data: This is an array that has the new fields.

The method calls a function that does the following:

function insertRowsInSheet(sheetId, size, data) {
  try {
    const response = Sheets.Spreadsheets.batchUpdate({
      "requests": [
        {
          "insertDimension": {
            "range": {
              "sheetId": sheetId,
              "dimension": "ROWS",
              "startIndex": 1,
              "endIndex": size+1
            },
            "inheritFromBefore": false,
          }
        },
        {
          "updateCells": {
            rows: {
              values: [
                ["test", "test"],
                ["test2", "test2"]] 
            },
            fields: "*",
            range: {
              sheetId: sheetId,
              startRowIndex: 1,
              startColumnIndex: 1,
            }
          }
        }
      ]}, sheet.id);
    Logger.log(response);
  } catch (error) {
    Logger.log(`error: ${error}`);
  }
}

As batchUpdate only works if both methods work, I am now getting the following error:

error: GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid value at 'requests[1].update_cells.rows.values[0]' (type.googleapis.com/google.apps.sheets.v4.CellData), "test"

I know that the first part works because if I just run the first requests, it enters as many rows as I have new elements. So I go from

A B
1 test1 test1
2 test2 test2

to (assuming that I have three new elements)

A B
1 test1 test1
2 test2 test2

I know there must be some detail I'm missing, but I can't seem to translate the documentation into an example applicable to Google Script.

Thank you very much for your help.

Solution

  • In your script, I thought that it is required to modify updateCells of the request body.

    Modified script:

    spreadsheetId = "###"; // Please set Spreadsheet ID.
sheetId = "###"; // Please set Sheet ID.
data = [["test", "test"], ["test2", "test2"]]; // This is from your showing script.

const response = Sheets.Spreadsheets.batchUpdate({
  requests: [
    {
      insertDimension: {
        range: { sheetId: sheetId, startIndex: 1, endIndex: 1 + data.length, dimension: "ROWS" }
      }
    },
    {
      updateCells: {
        range: { sheetId: sheetId, startRowIndex: 1, endRowIndex: 1 + data.length },
        rows: data.map(e => ({ values: e.map(f => ({ userEnteredValue: (isNaN(f) || f == "" ? { stringValue: f } : { numberValue: f }) })) })),
        fields: "userEnteredValue"
      }
    }
  ]
}, spreadsheetId);

    Or, when Spreadsheet service (SpreadsheetApp) is used, the sample script is as follows.

    const data = [["test", "test"], ["test2", "test2"]]; // This is from your showing script.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set sheet name.
const length = data.length;
sheet.insertRowsAfter(1, length).getRange(2, 1, length, data[0].length).setValues(data);
    • In above both scripts, the rows of data length are inserted from row 2, and the value of data is put into the inserted rows.

    References: