Efficiently Copy and Paste Row to Another Sheet if Cell Value = x

Question

I'm in the planning phase for a piece of code that I am implementing. I wanted to know what is the most efficient way to copy and paste a row of values to another tab if the value in column "x" equals a certain value.

Here are the methods I'm thinking of. I just want to know which is the most efficient as far as speed of execution and # of lines of code.

Filter sheet and copy results to new tab.
Loop through each row and copy them over to new tab if value in cell equals what we are looking for.
Loop through each row and push to array if value in cell equals what we are looking for. Then paste array values to new tab.

Any other methods I missed?

Example:

In the above I'd like to copy over the rows with values "xyz" in column D to a new tab.

Edit: I'm going through a minimum of 8K lines on a spreadsheet so speed of execution is key.

Does this answer your question? [Google Apps Script - Copy row from table if value in table meets condition](https://stackoverflow.com/questions/25038715/google-apps-script-copy-row-from-table-if-value-in-table-meets-condition) — Casper, Feb 06 '20 at 15:13
Alternatively, you may want to use the [query](https://support.google.com/docs/answer/3093343?hl=en) function, if you don't require the "pasted" values to be static — Casper, Feb 06 '20 at 15:14
His "high speed" solution involved a loop that pushed to an array. That's the 3rd option I provided above and he mentioned it would be faster than my second option. That helps, but when you have 8K+ rows won't a loop take too long? Wouldn't filtering and copying be faster (first option)? — DanCue, Feb 06 '20 at 15:17
I will look into the query function. Thanks for the suggestion. — DanCue, Feb 06 '20 at 15:17
The most efficient would probably be based on the "EDIT : high speed version" of the post linked by @Casper. In your case however, I would retrieve as a range only Column X. Potentially `indexOf()` would work faster than `if (... == criteria)` — ziganotschka, Feb 06 '20 at 15:19
@Casper, the pasted values need to be static and also query function looks like it can't be used in google apps script. https://stackoverflow.com/questions/17930389/can-the-google-spreadsheet-query-function-be-used-in-google-apps-script — DanCue, Feb 06 '20 at 15:21
The query function would be a non-scripting solution but as you indicate that the values need to be static, it doesn't suit your requirements. Otherwise, it would be the easiest solution by far :) I think the "high speed version" from the post I've linked should suffice though. The loop is over a text array, so I'd expect that to run quickly. — Casper, Feb 06 '20 at 15:23
@Casper Thanks for the quick responses. I will go with that solution for now but I'm hoping someone can give an explanation as to why one method is faster than the others and why my first option above would or wouldn't be efficient. Trying to think of it logically it seems like filtering and copying and pasting would be much faster than this high speed solution that is looping throw each row. Unless filtering essentially does the same thing. That could explain it. Oh well, If you have the time to write an answer officially, I'd like to give you the credit. Thanks. — DanCue, Feb 06 '20 at 15:27
@DanCue, I just had a quick look and can't find a method that'll grab JUST the values from the filtered range - `getValues()` & `getDisplayValues()` completely ignore the filter and pull all values no matter what. — ross, Feb 06 '20 at 16:10
It would do you good if you also look at the comments and the duplicate questions linked [there](https://stackoverflow.com/questions/17930389/can-the-google-spreadsheet-query-function-be-used-in-google-apps-script). — TheMaster, Feb 06 '20 at 16:21
Does this answer your question? [Fastest way to search for a row in a large Google Sheet using/in Google Apps Script](https://stackoverflow.com/questions/56737713/fastest-way-to-search-for-a-row-in-a-large-google-sheet-using-in-google-apps-scr) — TheMaster, Feb 06 '20 at 16:22

score 0 · Answer 1 · answered Feb 06 '20 at 20:33

Copy from Sheet3 and Paste in Sheet2 all the rows excluding those rows where HDR10 is Even and HDR1 is Odd and HDR6 is between 50 and 80

function runOne() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet3');
  var dsh=ss.getSheetByName('Sheet2');
  if(dsh.getLastRow()>1) {
    dsh.getRange(2,1,dsh.getLastRow()-1,dsh.getLastColumn()).clearContent();
  }
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  rg.setBackground('#ffffff');//sheet3 background white
  var vA=rg.getValues();
  var hA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];//get sheet3 headers labels
  var idx={};
  hA.forEach(function(h,i){idx[h]=i;});//relates header name to array index
  var d=0;
  for(var i=0;i-d<vA.length;i++) {
    //delete row if HDR10 is even and HDR1 is odd and HDR6 is between 50 and 80 non inclusive
    Logger.log('i:%s , d: %s, HDR10: %s, HDR1: %s, HDR6: %s',i,d,vA[i-d][idx['HDR10']],vA[i-d][idx['HDR1']],vA[i-d][idx['HDR6']]);
    if(Number(vA[i-d][idx['HDR10']])%2==0 && Number(vA[i-d][idx['HDR1']])%2==1 && Number(vA[i-d][idx['HDR6']])>50 && Number(vA[i-d][idx['HDR6']])<80) {
      vA.splice(i-d++,1);//filter out the ones we do not want
      sh.getRange(i+2,1,1,sh.getLastColumn()).setBackground('#ffff00');//highlight the copied rows
    }
  } 
  dsh.getRange(dsh.getLastRow()+1,1,vA.length,vA[0].length).setValues(vA);
}

Sheet3

Sheet2

Although copying and pasting is fast sometimes the job of extracting only the rows you want may slow down the process considerably.

Efficiently Copy and Paste Row to Another Sheet if Cell Value = x

1 Answers1