How to call UpdateCellsRequest in Google Sheets script-CodePudding

I am trying to fix my Google Sheets script program which is timing out. I am merging a set of sheets that consist of a grid of letters that are highlighted with colours to identify shapes in the grid. I want to show a heat map and also have sheets of specific grouping of the individual shapes. I works but times out once I started adding lots of the individual shapes sheets, so I've fixed it to get everything ready and then call batchUpdate of some description. Is this possible ?. Can I batch up a series of set background colour and set bold and italic on a series of individual cells in one call within scripts ?. Below is my code, how do I call UpdateCellsRequest()

    function createBatchMerge(target_name){
//  var range=lookup[target_name].getRange('A2:S9')
  for (const target_name in merge_store){
    var sheetId=lookup[target_name].getSheetId()
    var instructions=merge_store[target_name]
    for (const cellc in instructions){
      var instruction=instructions[cellc]
//      Logger.log("CELLC=" cellc)
      var [row,col]=cellc.split(':')
//      Logger.log("ROW=" row " col=" col)
      var i={
        updateCells:{
          rows:[{
            values:[{
                "userEnteredFormat": {
                  backgroundColor:instruction.colour,
                  textFormat:{
                    italic:instruction.italic,
                    bold:instruction.bold
                  },
                  note:instruction.names.join(',')
                }
              }
            ]
          }]
        },
        start:{
          "sheetId": sheetId,
          "rowIndex": 1 row,
          "columnIndex": col
        }
      }
      jobs.push(i)
    }
  }
}

function dumpMerge(){
//  Logger.log(jobs)
  SpreadsheetApp.UpdateCellsRequest(jobs)
}

Here is my sheet. I need it to work in the associated script so I can allow collaborators to update the shapes and add new ones and then just run it. https://docs.google.com/spreadsheets/d/11bROsFF8hM9SHJtH4WewxnuZ1Z9tuB8JUFR5MM7Ga7o/edit?usp=sharing

CodePudding user response：

Issues:

SpreadsheetApp doesn't have any UpdateCellsRequest method. If you want to call UpdateCellsRequest, enable Advanced Sheets Service (see how to enable).
You are not providing a valid request body: (1) it should include a requests field, (2) it should include a fields field (see here), (3) note is not a valid field; I'm not sure what information you want to update here, but it should be added to a different field.

Solution:

Taking all this into account, a possible request body is provided below. Please note that this might not satisfy all your purposes, since it's not clear what exact information you want to update. In any case, it may work as a guide.

Code sample:

const payload = {
  requests: [
    {
      updateCells: {
        rows:[{
          values:[{
            userEnteredFormat: {
              textFormat: {
                italic: instruction.italic,
                bold: instruction.bold
              }
            }        
          }]
        }],
        start: { 
          sheetId: sheetId,
          rowIndex: 1 row,
          columnIndex: col
        },
        fields: "userEnteredFormat.textFormat" // Fields you want to update
      }
    }
  ]
}
const spreadsheetId = SpreadsheetApp.getActive().getId();
Sheets.Spreadsheets.batchUpdate(payload, spreadsheetId);