Updating background colour using batchUpdate in Google Apps Script API

Question

I am trying to use Google Apps Scripts batchUpdate to update the styling of a range of cells.

I have put together the very simple example to hopefully enable me to get started, however I am getting the following error message back from this.

GoogleJsonResponseException: API call to sheets.spreadsheets.values.batchUpdate failed with error: Invalid JSON payload received. Unknown name "requests": Cannot find field. at updateGoogleSheet(fullSheet/fullSheet:316)

My code to try and do the update is as follows

    var data = {
        requests: [{
            updateCell: {
                range: 'Sheet3!A3',
                cell: {
                    userEnteredFormat: {
                        backgroundColor: {
                            red: 1
                        }
                    }
                },
                fields: 'userEnteredFormat(backgroundColor)'
            }
        }]
    };

    Sheets.Spreadsheets.Values.batchUpdate(data, spreadsheetId);

It would be a lot cleaner to get and set formatting for ranges rather than bath updating cells, why are you choosing to do it this way? — J. G.
It’s because I need to set a lot of different formats at once over a variety of ranges so thought it would be better if I could do it in a batch so I don’t hit concurrency limits which I was before doing individual ranges. Unless you know of a different way which won’t hit the limits of google? — user3284707
I think you should try it with RangeLists, concurrency won't apply because you aren't going through the advanced api. — J. G.

J. G. J. G. · Accepted Answer · 2019-04-30T20:19:40

It sounds like what you really want is to declare a rangelist and apply formatting to those in bulk.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C','D4']);
rangeList.setBackground('red');

sheet.getRangeList(['B3','F6').setFontFamily("Roboto");

Updating background colour using batchUpdate in Google Apps Script API

1 Answers