How to group data into tables in Google Sheets App Script using Batch Operating technique with Array-CodePudding

I have hundreds of rows which will be grouped into different tables. I have successfully achieved that by using basic "for loop" by alternately reading data and writing results into the cells. Unfortunately, that only works for the small number of rows in Google Sheets. When there are hundreds of rows, the looping stops in the middle of the process and the rest of the codes that come after the looping do not run anymore.

As described in google app script documentation, the solution is to perform Batch Operating by using arrays so that the time for data reading and writing can be reduced significantly with the help of setValues(Array), setFontWeights(Array),setHorizontalAlignments(Array) functions.

I am also wondering how to set the borders using this technique, as there is no setBorders(Array) function.

I provide the link below to the Google Sheet file in which you can find the sample of the simplified data and the results that I expect.

BATCH OPERATING - GROUPING DATA INTO TABLES - Google Sheets File

CodePudding user response：

If you want to quickly set borders, enable sheet api service and adapt the following script (this is an example)

function setBorderCells() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sh = ss.getActiveSheet()
  bordersUpdating(ss.getId(), sh.getSheetId(), 2, 6, 7, 8)
}
function bordersUpdating(id, gid, startRow, endRow, startColumn, endColumn) {
  const resource = {
    "requests": [
      {
        "updateBorders": {
          "range": {
            "sheetId": gid,
            "startRowIndex":  startRow - 1,
            "endRowIndex":  endRow,
            "startColumnIndex":  startColumn - 1,
            "endColumnIndex":  endColumn
          },
          "top": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
          "bottom": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
          "left": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
          "right": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
          "innerHorizontal": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
          "innerVertical": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
        }
      }
    ]
  }
  Sheets.Spreadsheets.batchUpdate(resource, id);
}

CodePudding user response：

You can simply apply setBorder(and other functions) to a range:

SpreadsheetApp.getActive().getSheetByName("Log")
    .getRange(1,1,5,5)
    .setBorder(true,true,true,true,true,true,
        "green",SpreadsheetApp.BorderStyle.DASHED)