javascriptnode.jsgoogle-sheetsgoogle-sheets-api

Formatting cells with the Google Sheets API (v4)

I'm using the Google Sheets API (v4) to create/update spreadsheets programmatically and have run into the following issue:

As per the documentation (https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellFormat), I'm setting the number format to CURRENCY; this will correctly display the number as a numeric value with a ¥ sign at the front (Japanese locale). However, it does not seem to actually select the "Currency" option on the formats dropdown, and more importantly, does NOT reflect the specified format when downloading the spreadsheet (e.g. as an .xlsx file).

enter image description here

This is different from selecting the 'Currency' option manually (via the UI), in which case values are correctly displayed once downloaded.

enter image description here

Here's the relevant section of code:


import { google, sheets_v4 } from 'googleapis';

const sheetsApi = google.sheets({
  version: 'v4',
  auth: await this.getAuthClient(),
});

await sheetsApi.spreadsheets
  .batchUpdate({
    spreadsheetId: resource,
    requestBody: {
      requests: [
        {
          updateSpreadsheetProperties: {
            fields: 'locale',
            properties: {
              locale: 'ja',
            },
          },
        },

        ...,
        
        {
          repeatCell: {
            fields: 'userEnteredFormat.numberFormat',
            cell: {
              userEnteredFormat: {
                numberFormat: { type: 'CURRENCY' },
              },
            },
          },
        },
      ],
    },
  })
  .catch((error) => {
    console.log(error);
  });

I've also tried settings the pattern (tried few different ones), but haven't been able to actually set the cell format, despite the value being displayed as such.

Probably a simple mistake, but I've been stuck on this for a while.. any help would be greatly appreciated!

Solution

  • In that case, I thought that the property of pattern might be required to be set. So in this case, how about modifying your request of repeatCell as follows?

    Modified request:

    {
  "repeatCell": {
    "range": {,,,}, // Please set range.
    "cell": {
      "userEnteredFormat": {
        "numberFormat": {
          "type": "CURRENCY",
          "pattern": "[$¥-411]#,##0.00"  // <--- Added
        }
      }
    },
    "fields": "userEnteredFormat.numberFormat"  // or "fields": "userEnteredFormat"
  }
}

    Note:

    References: