batchUpdate cell value and background color at same time in Node and Google Sheets v.4

Question

I am trying to update the background color of cells using google sheets v4 api but keep running in circles. I am able to update the cells value on each iteration but when I try changing the background color,

"cell": {
                            "userEnteredFormat": {
                                "backgroundColor": {
                                    "red": 1.0,
                                    "green": 0.4,
                                    "blue": 0.4
                                }
                            }
                        },

in the below code I keep getting the error - GaxiosError: Invalid JSON payload received. Unknown name "cell" at 'data[0]': Cannot find field.

                function updateClientFeedbackCells(auth) {
                    var sheets = google.sheets({ version: 'v4' });

                    authorize(function () {
                        var request = {
                            spreadsheetId: process.env['GOOGLE_SHEET_ID_' + envString],  // 
                            valueInputOption: 'RAW',
                            resource: {

                                "data": [
                                    {   
                                        "range": "N" + ucfSelectedRowIndex,
                                        "majorDimension": "COLUMNS",
                                        "cell": {
                                            "userEnteredFormat": {
                                                "backgroundColor": {
                                                    "red": 1.0,
                                                    "green": 0.4,
                                                    "blue": 0.4
                                                }
                                            }
                                        },

                                        "values": [
                                            [
                                                true
                                            ],
                                        ]
                                    },
                                ],
                            },
                            auth,
                        };
                        sheets.spreadsheets.values.batchUpdate(request, function (err, response) {
                            if (err) {
                                console.error(err);
                                return;
                            }
                            else {
                                console.info(response);
                                console.log("Client Feedback Sent Col Values updated");
                            };
                        });
                    });
                    function authorize(callback) {
                        //   'https://www.googleapis.com/auth/drive'
                        //   'https://www.googleapis.com/auth/drive.file'
                        //   'https://www.googleapis.com/auth/spreadsheets'
                        var auth = "https://www.googleapis.com/auth/drive";
                        if (auth == null) {
                            console.log('authentication failed');
                            return;
                        }
                        callback(auth);
                    }
                }

I've been going through the following documentation but cant seem to figure out what I'm doing incorrectly as there is so much different info. Please, is my problem with my scope or with the api type or in my syntax?

• https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells
• https://developers.google.com/sheets/api/samples/formatting
• https://developers.google.com/sheets/api/guides/batchupdate#example
• https://developers.google.com/sheets/api/guides/conditional-format

Answer 1

• You want to update the value and the background of cell by one API call with the batchUpdate method.
  • You want to update the cell of the column "N". And the row number is given by ucfSelectedRowIndex.
• You want to achieve this using googleapis of Node.js.
• You have already been able to get and put values for Spreadsheet with Sheets API.

If my understanding is correct, how about this answer?

Modification points:

• In order to update the value and the background of cell by one API call with the batchUpdate method, please use the method of spreadsheets.batchUpdate of Sheets API. This is different from the method of spreadsheets.values.batchUpdate.
• When the method of spreadsheets.batchUpdate is used, please use the gridrange as the range instead of a1Notation.

Modified script:

In this modified script, I modified the script for the API call. Before you use this script, please set the Spreadsheet ID and sheet ID. And also please set the value of ucfSelectedRowIndex.

const sheets = google.sheets({ version: "v4" });
const spreadsheetId = "###";
const sheetId = "###"; 
const ucfSelectedRowIndex = 1; // Please set more than 0.
const request = {
  requests: [
    {
      updateCells: {
        range: {
          sheetId: sheetId,
          startRowIndex: ucfSelectedRowIndex - 1,
          endRowIndex: ucfSelectedRowIndex,
          startColumnIndex: 13, // Column "N"
          endColumnIndex: 14 // Column "N"
        },
        rows: [
          {
            values: [
              {
                userEnteredFormat: {
                  backgroundColor: {
                    red: 1,
                    green: 0.4,
                    blue: 0.4
                  }
                },
                userEnteredValue: {
                  boolValue: true
                }
              }
            ]
          }
        ],
        fields: "userEnteredFormat,userEnteredValue"
      }
    }
  ]
};
sheets.spreadsheets.batchUpdate(
  { spreadsheetId: spreadsheetId, requestBody: request, auth },
  function(err, response) {
    if (err) {
      console.error(err);
      return;
    } else {
      console.info(response);
      console.log("Client Feedback Sent Col Values updated");
    }
  }
);

• When you run the script, the value and background of the cell "N1" are modified.

References:

• Method: spreadsheets.batchUpdate
• UpdateCellsRequest
• GridRange
• Method: spreadsheets.values.batchUpdate

If I misunderstood your question and this was not the result you want, I apologize.