Google Apps Script - Copy row from table if value in table meets condition

Question

I would like to copy entire row(s) into another sheet if A(i) equals the content of a cell (see criteria).

Example:

My criteria (which is located in ws2.A4) = "good"

Copy row 7 into ws1.A5 as ws1.A7 = "good"

Copy row 8 into ws1.A6 as ws1.A8 = "good"

But not the other rows.

(Note: I am trying to adapt this vba code into GAS https://stackoverflow.com/a/12185026/457557)

Here is where I blocked now :

function copy_row_s_if_cellAi_equal_X() {

var ws1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("base");
var ws2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ok");
var Alast = ws1.getLastRow()
var criteria = ws2.getRange(4 ,1).Value
var target = ws2.getRange(5 ,1)

for (var i = 3; i < Alast; i++ ) {
   if (ws1.getRange(i ,1) == criteria) { 

      ws1.getRange(i ,1).copyTo(target, {contentsOnly:true}); // copy/paste content only

  }
 }

}

Serge insas · Accepted Answer · 2014-07-30T15:15:54.093

3

something like that does what you want... not sure I understood exactly what you wanted though...

function copy_row_s_if_cellAi_equal_X() {

  var ws1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("base");
  var ws2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ok");
  var Alast = ws1.getLastRow();
  var criteria = ws2.getRange(4 ,1).getValue();

  for (var i = 3; i < Alast; i++ ) {
    Logger.log(ws1.getRange(i ,1).getValue()+ ' ==? ' + criteria);
    if (ws1.getRange(i ,1).getValue() == criteria) { 

      ws1.getRange(i ,1,1,ws1.getLastColumn()).copyTo(ws2.getRange(i ,1,1,ws1.getLastColumn()), {contentsOnly:true}); // copy/paste content only

    }
  }
}

EDIT : high speed version

function copy_row_s_if_cellAi_equal_X() {  
  var ws1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("base");
  var ws2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ok");
  var Alast = ws1.getLastRow();
  var criteria = ws2.getRange(4 ,1).getValue();
  var dataws1 = ws1.getRange(3,1,Alast,ws1.getLastColumn()).getValues();
  var outData = [];
  for (var i in dataws1) {
    if (dataws1[i][0] == criteria) { 
      outData.push(dataws1[i])
    }
  }
  ws2.getRange(ws2.getLastRow(),1,outData.length,outData[0].length).setValues(outData);
}

edited Jul 30 '14 at 15:15

answered Jul 30 '14 at 14:30

Serge insas

45,904
7
105
131

Thanks Serge. I have adapted your code to my needs : ws1.getRange(i ,1,1,ws1.getLastColumn()).copyTo(ws2.getRange(**ws2.getLastRow()+1**,1,1,ws1.getLastColumn()), {contentsOnly:true}); // copy/paste content only – miodf Jul 30 '14 at 14:53
Alas I have thousands of rows, so the full process takes too much time. It checks if each cells Ai equals the cell of my criteria one by one. Any way to speed the process ? In my case the cells Ai are already ranked by Column A. – miodf Jul 30 '14 at 14:57
I don't know if this would speed the process but : maybe could it try to find first the criteria "good" in col A ? Then I know that there are no more than 30 rows with the same Ai value. So once it finds the first correct row(i), try again for the next 30 rows. Thanks in advance ;) – miodf Jul 30 '14 at 15:00
I used your code to start with but is is indeed very unefficient... I'll update with a version that will handle "big data" using arrays. give me a few minutes... – Serge insas Jul 30 '14 at 15:02
Hi Serge, wow ! Many thanks. This is so great ! ;) I 've just adapted your code to fit my needs here : ws2.getRange(ws2.getLastRow() **+1** ,1,outData.length,outData[0].length).setValues(outData); Thank you again. ;) – miodf Jul 30 '14 at 15:51
Ooops...I forgot the +1 indeed :-) it was overlapping the last row ...sorry about that,I was a bit in a rush :-)- thx for accepting. – Serge insas Jul 30 '14 at 17:27
Serge, you have no need to be "sorry" ! You have helped me so much ! ;) Many thanks! ;) By the way, you also helped me in the past few months (e.g. : http://stackoverflow.com/questions/22841016/how-to-list-all-files-or-only-certains-type-of-files-inside-a-main-folder-and?lq=1) By luck do you know if google forecast to change one day their handling of folder contents like in a pc (windows explorer) and not only adding labels to files (the idea being that when a user has too many files in Drive but only a few in a folder, currently he can't find duplicates in it) ? Thanks ;) – miodf Jul 30 '14 at 18:20
I doubt they change anything in that matter...but you can actually get all files in a single folder, no matter how much files you have in the other folders... the folder object has a getfilesmethod. – Serge insas Jul 30 '14 at 18:47
Hello, In cases that there are no rows to be copied I have an error "TypeError: Cannot read property "length" from undefined" I have tired to add this: if (outData !== null) { ws2.getRange(ws2.getLastRow() +1 ,1,outData.length,outData[0].length).setValues(outData); } Alas unsuccessfully. I also tried '' instead of null. ;( Thanks in advance ;) – miodf Dec 29 '14 at 19:36
Hello, I have found a way to do that. ;) I use "if (outData.length !== 0) {". sorry for disturbing. See ya. ;) – miodf Dec 30 '14 at 10:45

Google Apps Script - Copy row from table if value in table meets condition

1 Answers1

EDIT : high speed version

Linked