Format cells in google sheets from API V4

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:

This is the cell I am going to format:

This is what I do:

Format > Number > Plain text

This is the result:

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

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"
         }
       }
     ]
   }```

This is the result:

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

Contents hide

From:

To:

Note:

References:

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Retrieve values from the sheet as the formatted values.
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:

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating