Google Apps Script Copy Rows and Mark Copied Too Slow-CodePudding

I have the following script that takes rows from one sheet "SheetToCopy" to another "Archive" and then marks the rows copied in another column. The problem is the script is taking way too long to run with over 10,000 rows of data. Any one have a better way to do this logging the rows?

function copyRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ss.getSheetByName('SheetToCopy');
  var tarSheet = ss.getSheetByName('Archive');
  var startRow = 0;
  var lastRow = srcSheet.getLastRow();
  var Source_Copied = 'Source_Copied';
  
  for (var i = 2; i <= lastRow; i  ) {
    var cell = srcSheet.getRange("M"   i);
    var val = cell.getValue();
    if (val !== Source_Copied) {
      
      var srcRange = srcSheet.getRange("A"   i   ":N"   i);
      
      var tarRow = tarSheet.getLastRow();
      tarSheet.insertRowAfter(tarRow);
      var tarRange = tarSheet.getRange("A"   (tarRow 1)   ":N"   (tarRow 1));
      
      srcRange.copyTo(tarRange);
      srcSheet.getRange(startRow   i, 13).setValue(Source_Copied);
      SpreadsheetApp.flush();
    }
  }
};

CodePudding user response：

Copy filtered rows

function copyRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssh = ss.getSheetByName('SheetToCopy');
  const vs = ssh.getRange(2,1,tsh.getLastRow() - 1,14).getValues().filter(r => r[12] !== "Source_Copied");
  const tsh = ss.getSheetByName('Archive');
  tsh.getRange(tsh.getLastRow()   1,1,vs.length,vs[0].length).setValues(vs);
}

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

In your script, getValue, copyTo and setValue are used in the loop. In this case, the process cost becomes high. Ref In order to reduce the process cost of your script, how about the following flow?

Retrieve values from the source sheet.
Create 2 arrays for putting to the destination sheet and putting the value of "Source_Copied".
Put the values in the destination sheet.
Put the values of "Source_Copied" in the source sheet.

By this flow, the values are retrieved by one getValues and put to the destination sheet by one setValues. And also, in order to put the value of Source_Copied to the scattered cells using RangeList. So the process cost can be reduced.

Modified script:

function copyRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ss.getSheetByName('SheetToCopy');
  var tarSheet = ss.getSheetByName('Archive');
  var Source_Copied = 'Source_Copied';
  
  // 1. Retrieve values from the source sheet.
  var srcValues = srcSheet.getRange("A2:N"   srcSheet.getLastRow()).getValues();

  // 2. Create 2 arrays for putting to the destination sheet and putting the value of "Source_Copied".
  var { dstValues, rangeList } = srcValues.reduce((o, r, i) => {
    if (r[12] != Source_Copied) {
      o.dstValues.push(r);
      o.rangeList.push("M"   (i   2));
    }
    return o;
  }, { dstValues: [], rangeList: [] });

  // 3. Put the values to the destination sheet.
  if (dstValues.length == 0) return;
  tarSheet.getRange(tarSheet.getLastRow()   1, 1, dstValues.length, dstValues[0].length).setValues(dstValues);

  // 4. Put the values of "Source_Copied" to the source sheet.
  srcSheet.getRangeList(rangeList).setValue(Source_Copied);
}

Copy filtered rows

Modified script:

References: