Payload Syntax for batchUpdate

Question

I am using the googleapis module in a Node application. I was using version 21 but have just updated to version 52 due to a security vulnerability in the older version.

There are several breaking changes. I have overcome most except for formatting a date/time string. Is the following payload correct for formatting a date/time value in cell A11?

const formatDate = (next) => {
  sheets.spreadsheets.batchUpdate({
    auth: authClient,
    spreadsheetId: sheetId,
    requestBody: {
      requests: [{
        "repeatCell": {
          range: { sheetId: 0, startRowIndex: 10, endRowIndex: 11, startColumnIndex: 0, endColumnIndex: 1},
          cell: { userEnteredFormat: { numberFormat: { "type": "DATE_TIME", "pattern": "ddd yyyy-mm-dd hh:mm" } } },
          fields: "userEnteredFormat.numberFormat"
          }
        }]
    }
  }, (err, response) => {
      // ...
      next();
     }
  );
}

No errors were returned with the above payload, but the formatting is not taking place. Is the key requestBody? Previously I was using resource.

I used async to perform authentication before formatting the date:

const authClient = new google.auth.JWT(client_email, null, private_key, SCOPES, null);
const sheetId = "1vgiEnV8fU_MrnIy31fbPAzhHz.......";

function authenticate(next) {
  authClient.authorize((err) => {
     next(err);
  }
}
const tasks = [ authenticate, insertRow, formatdate ];
require("async").series(tasks);

Code for insertRow is not included here, but that works without problem.

Answer 1

I think that your script is correct. The cell format of "A11" is modified with the request body. And in this case, the request body can be used for both requestBody and resource.

But please confirm the following points, again.

• sheetId of spreadsheetId: sheetId, is required to be the Spreadsheet ID.

• In this case, when =now() is put to the cell "A11" and run the script, you can see the modified cell format.

• By the following modification, you can check the returned values from Sheets API.

    sheets.spreadsheets.batchUpdate(
      {
        auth: authClient,
        spreadsheetId: "spreadsheetId",  // <--- Please check this.
        requestBody: {
          requests: [
            {
              repeatCell: {
                range: {
                  sheetId: 0,
                  startRowIndex: 10,
                  endRowIndex: 11,
                  startColumnIndex: 0,
                  endColumnIndex: 1,
                },
                cell: {
                  userEnteredFormat: {
                    numberFormat: {
                      type: "DATE_TIME",
                      pattern: "ddd yyyy-mm-dd hh:mm",
                    },
                  },
                },
                fields: "userEnteredFormat.numberFormat",
              },
            },
          ],
        },
      },
      (err, res) => {
        if (err) {
          console.log(err);
          return;
        }
        console.log(res.data);
      }
    );
  

Note:

• In my environment, I tested your script with googleapis@52.1.0, and I could confirm the script worked.
• This modified script supposes that your authorization process for using Sheets API has already been done.

References:

• googleapis for Node.js
• Method: spreadsheets.batchUpdate
• RepeatCellRequest

Added:

Sample script for testing:

const client_email = "###";  // Please set here.
const private_key = "###";  // Please set here.
const spreadsheetId = "###";  // Please set here.

const { google } = require("googleapis");
let jwtClient = new google.auth.JWT(
  client_email,
  null,
  private_key,
  ["https://www.googleapis.com/auth/spreadsheets"]
);
jwtClient.authorize((err) => {
  if (err) console.log(err);
});
const sheets = google.sheets({ version: "v4", auth: jwtClient });
sheets.spreadsheets.batchUpdate(
  {
    spreadsheetId: spreadsheetId,
    requestBody: {
      requests: [
        {
          repeatCell: {
            range: {
              sheetId: 0,
              startRowIndex: 10,
              endRowIndex: 11,
              startColumnIndex: 0,
              endColumnIndex: 1,
            },
            cell: {
              userEnteredFormat: {
                numberFormat: {
                  type: "DATE_TIME",
                  pattern: "ddd yyyy-mm-dd hh:mm",
                },
              },
            },
            fields: "userEnteredFormat.numberFormat",
          },
        },
      ],
    },
  },
  (err, res) => {
    if (err) {
      console.log(err);
      return;
    }
    console.log(res.data);
  }
);