Format cells in google sheets from API V4

Question

I'm starting to develop with the Google Sheets api after checking the documentation there are several things that I don't understand or I can't find.

This is the endpoint I am testing:

spreadsheets.values.batchUpdate

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

What I am trying to do is format cells to plain text from the API, the same thing I do from the spreadsheet by clicking on Format > Number > Plain text

How should I create the JSON from the API to apply the same format to an entire column?

This is what happens when I format the cell to Plain Text from the sheet itself:

1. This is the cell I am going to format:

2. This is what I do:

Format > Number > Plain text

3. This is the result:

This is what happens when I format the same cell to Plain Text from the google API:

1. This is the JSON that I apply:

        "requests": [
          {
            "repeatCell": {
              "range": {
                "sheetId": 3,
                "startColumnIndex": 1,
                "endColumnIndex": 2,
                "startRowIndex": 1,
                "endRowIndex": 2
              },
              "cell": {
                "userEnteredFormat": {
                  "numberFormat": {
                    "type": "TEXT"
                  }
                }
              },
              "fields": "userEnteredFormat.numberFormat"
            }
          }
        ]
      }```
   
   

2. This is the result:

My problem: How can I do from the API the same thing that I do directly from the sheet?

Answer 1

I had had the same situation as you. At that time, as a workaround, I used the following flow.

1. Retrieve values from the sheet as the formatted values.
2. Put the values on the sheet as the string values.

When this workaround is reflected in a script, it becomes as follows.

Sample script:

const spreadsheetId = "###"; // Please set the Spreadsheet ID.
const sheetName = "Sheet1"; // Please set the sheet name.
const sheets = google.sheets({ version: "v4", auth }); // Please use your authorization script.

const values = await sheets.spreadsheets.values.get({
  spreadsheetId,
  range: sheetName,
  valueRenderOption: "FORMATTED_VALUE",
});
const res = await sheets.spreadsheets.values.update({
  spreadsheetId,
  range: sheetName,
  valueInputOption: "RAW",
  resource: { values: values.data.values },
});

Result:

When this script is used, the following result is obtained.

From:

To:

Note:

• This sample script uses a sheet. If you want to use the specific range, please modify range.

• In this script, the number format is changed to Automatic. If you want to change this to Plain text, please use the following script instead of the above one.

    const spreadsheetId = "###"; // Please set the Spreadsheet ID.
    const sheetName = "Sheet1"; // Please set the sheet name.
    const sheets = google.sheets({ version: "v4", auth }); // Please use your authorization script.
    const sheetId = 0; // Please set the sheet ID of "sheetName".
  
    const values = await sheets.spreadsheets.values.get({
      spreadsheetId,
      range: sheetName,
      valueRenderOption: "FORMATTED_VALUE",
    });
    const requests = values.data.values.map((r, i) =>
      r.map((c, j) => ({
        updateCells: {
          range: {
            sheetId: sheetId,
            startRowIndex: i,
            endRowIndex: i + 1,
            startColumnIndex: j,
            endColumnIndex: j + 1,
          },
          rows: [
            {
              values: [
                {
                  userEnteredFormat: {
                    numberFormat: {
                      type: "TEXT",
                    },
                  },
                  userEnteredValue: {
                    stringValue: c,
                  },
                },
              ],
            },
          ],
          fields: "userEnteredFormat.numberFormat,userEnteredValue",
        },
      }))
    );
    const res = await sheets.spreadsheets.batchUpdate({
      spreadsheetId,
      resource: { requests },
    });
  

References:

• Method: spreadsheets.values.get
• Method: spreadsheets.values.update
• Method: spreadsheets.batchUpdate