so I use apps script with sheets that have 100k rows so I want to know which methods have better performance, Normally, I use srcRange.getValues() and dstRange.setValues(dstData) to move data in sheets using apps script, however, I recently found that their performance drops significantly when trying to apply them on a range of about 20K cells, now I just want to know should I keep using them or think about using something like srcRange.copyTo(dstRange) instead if it's better performance wise.

CodePudding user response：

getValues() and setValues() are best to use when you are modifying or validating the content of your data before writing it to your Sheet. If your goal is only to copy data from one sheet to another, the best method to use is Range.copyTo(dstRange) as it does not require the script to read, process and write the large size array.

Here is a sample test I made for getValues/setValues vs copyTo:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('Sheet1');
  var sh2 = ss.getSheetByName('Sheet2');
  sh1.getRange("A1:AL2967").copyTo(sh2.getRange("A1"));
}

function myFunction2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('Sheet1');
  var sh2 = ss.getSheetByName('Sheet3');
  var data = sh1.getRange("A1:AL2967").getValues();
  sh2.getRange(1,1,data.length, data[0].length).setValues(data);
}

myFunction run time:

myFunction2 run time: