Apps Script - How to copy data containing checkboxes?

Question

I'm updating large quantities of data using a script. I have to use arrays since .getValue and .setValue is very inefficient. My problem is that the data I am handling is containing checkboxes but when I use an array to copy and paste the data, the checkboxes are turned into TRUE and FALSE strings.

Is there any way to keep the checkbox object when using arrays to copy data ? Using .insertCheckboxes on individual cells is also very slow (and I cannot use .getRange().insertCheckboxes since the cells that contain checkboxes are not always adjacent).

Answer 1

If the source data is in a spreadsheet range, use Range.copyTo() with the appropriate CopyPasteType.

If you cannot use Range.copyTo() for some reason, you can copy values and validations with something like this:

function copyFromSheet1ToSheet2() {
  const ss = SpreadsheetApp.getActive();
  const source = ss.getRange('Sheet1!A1:D10');
  const target = ss.getRange('Sheet2!A1');
  copyValuesAndValidations(source, target);
}


function copyValuesAndValidations(sourceRange, targetRange) {
  targetRange = targetRange.offset(0, 0, sourceRange.getHeight(), sourceRange.getWidth());
  targetRange.setValues(sourceRange.getValues());
  targetRange.setDataValidations(sourceRange.getDataValidations());  
}