How do I make this Google Script faster？-CodePudding

      function Backup() {
          var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
          spreadsheet.setActiveSheet(spreadsheet.getSheetByName('A'), true);
          spreadsheet.getRange('A13:H').activate();
///Copy source to target and clear it

            var target = SpreadsheetApp.openById("XXX");
            
          var source_sheet = spreadsheet.getSheetByName("A");
          var target_sheet = target.getSheetByName("B");
          var source_range = source_sheet.getActiveRange();
          var last_row = target_sheet.getLastRow();
          var values = source_range.getValues();
          target_sheet.getRange(last_row   1, 1,values.length,values[0].length).setValues(values);
          source_range.clearContent();

///How do I make this Google Script faster？

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

In this case, how about the following patterns?

Pattern 1:

In this pattern, the Spreadsheet service (SpreadsheetApp) is used.

function sample1() {
  var srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = srcSpreadsheet.getSheetByName("A");
  var srcRange = srcSheet.getRange('A13:H'   srcSheet.getLastRow())
  var srcValues = srcRange.getValues();

  var dstSpreadsheet = SpreadsheetApp.openById("XXX"); // Please set the destination Spreadsheet ID here.
  var dstSheet = dstSpreadsheet.getSheetByName("B");
  dstSheet.getRange(dstSheet.getLastRow()   1, 1, srcValues.length, srcValues[0].length).setValues(srcValues);
  srcRange.clearContent();
}

Pattern 2:

In this pattern, Sheets API is used. Before you use this script, please enable Sheets API at Advanced Google services.

function sample2() {
  var srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = srcSpreadsheet.getSheetByName("A");
  var srcRange = "'A'!A13:H"   srcSheet.getLastRow();
  var values = Sheets.Spreadsheets.Values.get(srcSpreadsheet.getId(), srcRange).values;

  var dstSpreadsheetId = "XXX"; // Please set the destination Spreadsheet ID here.
  Sheets.Spreadsheets.Values.append({ values }, dstSpreadsheetId, "B", { valueInputOption: "USER_ENTERED" });
  srcSpreadsheet.getRange(srcRange).clearContent();
}

References:

CodePudding user response：

To speed up, don't use activate() in your script. Try

  // source
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source_sheet = spreadsheet.getSheetByName("A");
  var source_range = source_sheet.getRange('A13:H');
  var values = source_range.getValues();

  // target
  var target = SpreadsheetApp.openById("XXX");
  var target_sheet = target.getSheetByName("B");
  var last_row = target_sheet.getLastRow();
  
  // copy
  target_sheet.getRange(last_row   1, 1, values.length, values[0].length).setValues(values);
  source_range.clearContent();