App Scripts Loop Copy based on Condition Optimization-CodePudding

I wrote an App Script that copies values from Sheet 2 to Sheet 1 only if the cell in column A (In Sheet 1) contains the value "Copy". This script works well, but anything over 50 rows runs pretty slow, is there a way to optimize this function to make it run faster?

function CopyValues() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet1 = ss.getSheetByName('Sheet1');
  var Sheet2 = ss.getSheetByName('Sheet2');

  //If Column A = Copy then copy the values in Sheet 2 column B to W to sheet 1
  for (var i=1; i<=1000; i = i   1)
     if (Sheet1.getRange(i,1,100,1).getValue() == 'Copy'){
     Sheet2.getRange(i,2,1,23).copyTo(Sheet1.getRange(i,2,1,23),SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    };
 };

CodePudding user response：

Try this:

function CopyValues() { 
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Sheet1');
  const sh2 = ss.getSheetByName('Sheet2');
  const vs = sh1.getRange(1,1,sh1.getLastRow(),23).getValues().filter(r => r[0] == "Copy");
  sh2.getRange(sh2.getLastRow()   1, 1,vs.length,23).setValues(vs)  
}

CodePudding user response：

I believe your goal is as follows.

Your script works fine. And, you want to reduce the process cost of your script.

In this modification, I would like to reduce the process cost of the script by Sheets API. I thought that when Sheets API is used for your situation, the process cost will be able to be reduced a little.

When Sheets API is reflected in your script, it becomes as follows.

Modified script:

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

function CopyValues2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet1 = ss.getSheetByName('Sheet1');
  var Sheet2 = ss.getSheetByName('Sheet2');
  
  // Retrieve copied row index.
  var rows = Sheet1.getRange("A1:A"   Sheet1.getLastRow()).getValues().reduce((ar, [a], i) => {
    if (a == "Copy") ar.push(i);
    return ar;
  }, []);

  // Create a request body for using the batchUpdate method of Sheets API.
  var srcSheetId = Sheet2.getSheetId();
  var dstSheetId = Sheet1.getSheetId();
  var requests = rows.map(e => ({ copyPaste: { source: { sheetId: srcSheetId, startRowIndex: e, endRowIndex: e   1, startColumnIndex: 1, endColumnIndex: 24 }, destination: { sheetId: dstSheetId, startRowIndex: e, endRowIndex: e   1, startColumnIndex: 1 }, pasteType: "PASTE_NORMAL" } }));

  // Request Sheets API using the created request body.
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

When this script is run, I thought that the same result of your showing script is obtained by reducing the process cost.

Modified script:

References: