Format cells in google sheets from API V4-CodePudding

I'm starting to develop with the

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?

CodePudding user response：

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 },
  });

Sample script:

Result:

From:

To:

Note:

References: