Can you update the style of a cell without overwriting the value with Google Spreadsheets API?

user_1712944 0
Viewed 120

I am using the Google Spreadsheets API to chance the backgroundColor of a cell.

By using batchUpdate and userEnteredFormat this works well. The following code is a simplified version that will make the backgroundColor of a cell red.

const request = {
  spreadsheetId: 'xxx',
  resource: {
    [{
      updateCells: {
        range: {
          ...
        },
        fields: '*',
        rows: [
          {
            values: [
              {
                userEnteredFormat: {
                  backgroundColor: { red: 1, green: 0, blue: 0 },
                },
              },
            ],
          },
        ],
      },
    }],
  },
};

const result = await sheets.spreadsheets.batchUpdate(request);

The problem is that this will also overwrite the cell value.

I would have expected the value is only overwritten when setting the userEnteredValue, but it always overwrites it.

How can I update the style of a cell without overwriting its value?

google-sheets-api google-sheets
1 Answers

I think that your request body has 2 modification points as follows.

Modified script:

const request = {
  spreadsheetId: spreadsheetId,
  resource: {
    requests: [  // <--- Modified
      {
        updateCells: {
          range: {
            ...
          },
          fields: "userEnteredFormat.backgroundColor",  // <--- Modified
          rows: [
            {
              values: [
                {
                  userEnteredFormat: {
                    backgroundColor: { red: 1, green: 0, blue: 0 },
                  },
                },
              ],
            },
          ],
        },
      },
    ],
  },
};
  • requests: is added.
  • About fields, fields: '*' was modified to fields: "userEnteredFormat.backgroundColor". By this, only the background color is updated. I thought that this is the reason of your issue.

Reference:

user_71086530
Related