Faster way to save checkboxes to sheet?-CodePudding

I have a sheet where I would like to apply checkboxes to a few ranges. I made a rangeList and then use the following code:

  //apply checkboxes to entire boolean rangeList
  var activeRanges = compSheet.getRangeList(rangeListArr).activate();
  activeRanges.insertCheckboxes();

But this is incredibly slow. Even with one row in the range it takes around one second to apply the checkbox to each column. I used rangeList because I thought it might work faster than applying checkboxes to individual ranges but there seems to be no difference in speed.

CodePudding user response：

Although I'm not sure about the other part of your script, when the process cost for inserting the checkboxes with RangeList is high, how about using Sheets API? When the Sheets API is used for your situation, the cost might be able to be reduced a little. When the sample range list of ['C1:C100', 'J1:J100', 'Z1:Z100'] is used, the sample script is as follows.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  const rangeListArr = ['C1:C100', 'J1:J100', 'Z1:Z100']; // Please set your range list.
  const sheetName = "Sheet1"; // Please set your sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const sheetId = sheet.getSheetId();
  const requests = sheet.getRangeList(rangeListArr).getRanges().map(r => {
    const startRowIndex = r.getRow() - 1;
    const startColumnIndex = r.getColumn() - 1;
    const endRowIndex = startRowIndex   r.getNumRows();
    const endColumnIndex = startColumnIndex   r.getNumColumns();
    return { repeatCell: { range: { sheetId, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex }, cell: { dataValidation: { condition: { type: "BOOLEAN" } } }, fields: "dataValidation" } };
  });
  if (requests.length > 0) {
    Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
  }
}

Note:

In this script, your other part of the script is not considered. So when you test this script, please test only this script.

References:

CodePudding user response：

You will divide the time by about 2 without activate()

const compSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet5')
var activeRanges = compSheet.getRangeList(rangeListArr).insertCheckboxes();

It seems to run faster than batchupdate following a few tests.