javascript - Formatting cells with the Google Sheets API (v4) - Stack Overflow

admin管理员组

文章数量:1391951

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

As per the documentation (), 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).

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

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!

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./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).

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

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!

• javascript
• node.js
• google-sheets
• google-sheets-api

Share Improve this question Follow edited Aug 8, 2024 at 18:05 Wicket 38.8k99 gold badges8080 silver badges195195 bronze badges asked May 6, 2021 at 11:27 delafuente-ricardodelafuente-ricardo 6811 silver badge77 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 5

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:

• In my environment, when above modified request is used for the batchUpdate method, I could confirm that "Currency" was checked.

References:

• RepeatCellRequest- NumberFormat

本文标签： javascriptFormatting cells with the Google Sheets API (v4)Stack Overflow