UpdateCellsRequest - Range not behaving as expected

Question

Trying to use a UpdateCellsRequest to update the formatting of a range of cells but it's only updating the first cell in the range.

let fixFormats = () => {
    return new Promise((resolve, reject) => {
        var sheets = google.sheets('v4');

        var options = {
            auth: auth,
            spreadsheetId: spreadsheetId,
            resource: {
                requests: [{
                    updateCells: {
                        range: {
                            sheetId: 0,
                            startColumnIndex: 0,
                            endColumnIndex: 2,
                        },
                        rows: [{
                            values: {
                                userEnteredFormat: {
                                    numberFormat: {
                                        type: "CURRENCY",
                                        pattern: "$#,##0.00"
                                    }
                                }
                            }
                        }],
                        fields: "userEnteredFormat.numberFormat",
                    }
                }]
            }
        }

        sheets.spreadsheets.batchUpdate(options, (err, res) => {
            if (err) {
                reject(err);
            } else {
                resolve(res);
                console.log(res);
            }
        });
    });
}

According to this post, it should be possible. I've gone through the reference docs and verified that my request is formatted properly. With special attention given to how range is defined.

Edit: More confusing behaviour from the startColumnIndex (inclusive) and endColumnIndex(Exclusive)..

While sheetId: 0, startColumnIndex: 0, endColumnIndex: 2 Only updates cell A1 for example... While sheetId: 0, startColumnIndex: 0, endColumnIndex: 3 Does the same.

BUT...if While sheetId: 0, startColumnIndex: 1, endColumnIndex: 2 Then only column B (colIndex: 1) is updated...

Is this a bug or am I missing something?

Edit: Output by request...not sure this shows the right response though.

{ spreadsheetId: '<mysheet>',
  replies: [ {} ],
  updatedSpreadsheet: 
   { spreadsheetId: '<mysheet>',
     properties: 
      { title: 'OLR',
        locale: 'en_US',
        autoRecalc: 'ON_CHANGE',
        timeZone: 'America/Los_Angeles',
        defaultFormat: [Object] },
     sheets: [ [Object], [Object], [Object], [Object], [Object], [Object] ],
     namedRanges: [ [Object] ],
     spreadsheetUrl: 'https://docs.google.com/a/company.com/spreadsheets/d/<mysheet>/edit' } }

If I JSON.stringify the response it gives formatting for every cell in the range it seems, which is a lot of cells. Still, out of 3 columns selected, the first cell in the first column is updated but the rest remain unchanged.

Answer 1

In the options payload that you pass to sheets.spreadsheets.batchUpdate(), resources.requests[].updateCells.rows[].values[]- more specifically the values field should actually be an array. You have it as a hash in your example.

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

Answer 2

Figured it out! Although I was able to get this to work by using the 'repeatCells' function instead of 'updateCells', I'm not sure why that wouldn't work because it seems to be the intended purpose of that function. I'll have to read this again but for posterity the updated code that worked:

var options = {
            auth: auth,
            spreadsheetId: spreadsheetId,
            resource: {
                requests: [{
                    repeatCell: {
                        fields: "userEnteredFormat.numberFormat",
                        range: {
                            sheetId: 1564685276,
                            startColumnIndex: 1, // inclusive
                            endColumnIndex: 3, // exclusive
                        },
                        cell: {
                            userEnteredFormat: {
                                numberFormat: {
                                    type: "CURRENCY",
                                    pattern: "$#,##0.00"
                                }
                            }
                        }
                    }
                }],
            }
        }