I'm trying to set the background of a single specific cell in column C via the NodeJS Google Sheets library. However, I am receiving this error

errors: [
    {
      message: `Invalid value at 'requests[0].update_cells.range' (type.googleapis.com/google.apps.sheets.v4.GridRange), "'Question of the day'!C2"`,
      reason: 'invalid'
    }
  ]

This is my method call

      sheets.spreadsheets.batchUpdate({
        auth: this._token,
        spreadsheetId: this._spreadsheet,
        resource: {
          requests: [{
            updateCells: {
              range: '\'Question of the day\'!C'   (index   1),
              fields: 'userEnteredFormat',
              rows: [{
                values: [{
                  userEnteredFormat: {
                    backgroundColor: {
                      red: 1,
                      green: 0,
                      blue: 0
                    }
                  }
                }]
              }]
            }
          }]
        }
      })

The auth is correct as well as the spreadsheet. I tested the range the error throws in Google Sheets and it's a valid and working range.

I'm not sure why this is happening

CodePudding user response：

In your script, the UpdateCellsRequest of batchUpdate method is used. In this case, the range is the gridRange which is not A1Notation. I think that this is the reason of your issue.

Modified script:

In this case, please use the sheet ID of the sheet of "Question of the day" to gridRange.

const gridRange = {
  "sheetId": 0, // Please set the sheet ID of the sheet of "Question of the day".
  "startRowIndex": index,
  "endRowIndex": index   1,
  "startColumnIndex": 2, // This means the column "C"
  "endColumnIndex": 3 // This means the column "C"
};
sheets.spreadsheets.batchUpdate({
  auth: this._token,
  spreadsheetId: this._spreadsheet,
  resource: {
    requests: [{
      updateCells: {
        range: gridRange,
        fields: 'userEnteredFormat',
        rows: [{
          values: [{
            userEnteredFormat: {
              backgroundColor: {
                red: 1,
                green: 0,
                blue: 0
              }
            }
          }]
        }]
      }
    }]
  }
})

References:

• UpdateCellsRequest
• GridRange