How to speed up moveRows script-CodePudding

I use a script how move rows in other sheet and delete them if condition match. It works but sometimes it's long. How can i speed up this please ?

function moveRows(){
  var ss=SpreadsheetApp.getActive();
  var sh0=ss.getSheetByName('Source sheet');
  var sheet = SpreadsheetApp.getActive().getSheetByName('Target');
  var lastRow = sheet.getLastRow()   1;
  var rg0=sh0.getDataRange();
  var sh1=ss.getSheetByName('Target');
  var vals=rg0.getValues();
  sheet.getRange(lastRow, 1).setValue(new Date())
  for(var i=vals.length-1;i>0;i--)
  {
    if(vals[i][0]=='OK')
    {
      sh1.appendRow(vals[i]);
      sh0.deleteRow(i 1)
    }
  }
}

I try this but it's not working. I think i can't combine sh1.getRange(sh1.getLastRow() 1, 1,vals.lenght , vals[0].length).setValues(result) with sh0.deleteRows(i 1) or the problem is for (var i=vals.lenght-1;i>0;i--) ??

function moveRows(){
  var ss=SpreadsheetApp.getActive();
  var sh0=ss.getSheetByName('Source sheet');
  var sheet = SpreadsheetApp.getActive().getSheetByName('Target');
  var lastRow = sheet.getLastRow()   1;
  var rg0=sh0.getDataRange();
  var sh1=ss.getSheetByName('Target');
  var vals=rg0.getValues();
  var v = []
  sheet.getRange(lastRow, 1).setValue(new Date())
  for(var i=vals.length-1;i>0;i--)
  {
    if(vals[i][0]=='OK'){
      v.push(vals[i])

    }
    {
      sh1.getRange(sh1.getLastRow() 1, 1, v.length; v[0].length).setValues(v)
      sh0.deleteRow(i 1)
    }
  }
}

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your 1st script in your question.

When appendRow and deleteRow are used in the loop, the process cost will become high. Ref (Author: me) In this case, how about the following flow?

Retrieve values from the source sheet.
Filtering the values with the 1st column.
Overwriting the source sheet with the filtered values.
Put the filtered values in the destination sheet.

When this flow is reflected in your script, how about the following modification?

Modified script:

function moveRows() {
  var ss = SpreadsheetApp.getActive();
  var srcSheet = ss.getSheetByName('Source sheet');
  var dstSheet = ss.getSheetByName('Target');
  var srcRange = srcSheet.getDataRange();
  var values = srcRange.getValues();
  var { src, dst } = values.reduce((o, r) => {
    o[r[0] == "OK" ? "dst" : "src"].push(r);
    return o;
  }, { src: [], dst: [[new Date(), ...Array(values[0].length - 1).fill(null)]] });
  if (dst.length == 0) return;
  srcRange.clearContent().offset(0, 0, src.length, src[0].length).setValues(src);
  dstSheet.getRange(dstSheet.getLastRow()   1, 1, dst.length, dst[0].length).setValues(dst);
}

When this script is run, the same result as your 1st script is obtained by reducing the process cost.

Note:

When Sheets API is used, the process cost for moving rows can be reduced. But, from your previous question, I'm worried that you might not be able to use Sheets API. Ref So, I proposed the above script without using Sheets API.

Reference:

reduce()