Google Apps Script Copy Range If Condition Met

Question

I have two spreadsheets, source and destination. In source I have data sheet and in destination I have archive sheet. In data sheet J column contains percentages. I am trying to workout a script which automatically copies the range (A:I) of the rows that has greater than 10% in the J cell to append in the archive sheet.

I'm stuck at this point:

function CopyRange() {
  var sourcespread = SpreadsheetApp.openById('aaa'); //replace with source ID
  var sourcesheet = sourcespread.getSheetByName('bbb'); //replace with source Sheet tab name
  var destspread = SpreadsheetApp.openById('ccc'); //replace with destination ID
  var destsheet = destspread.getSheetByName('ddd'); //replace with destination Sheet tab name
  var testrange = sourcesheet.getRange('J:J');
  var testvalue = (testrange.setNumberFormat("0.00").getValues());
  var data = [];
  var j =[];
  //Condition to check in J:J, if true, copy the same row to data array 
  for (i=0;i<testvalue.length;i++) {
    if (testvalue[i] >= 10) {
    data.push.apply(data,sourcesheet.getRange(i+1,1,1,3).getValues());
  //Copy matched ROW numbers to j
    j.push(i);
}  
}
  //Copy data array to destination sheet
  destsheet.getRange(destsheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);
}

I'm getting error:

TypeError: Cannot read property 'length' of undefined (line 19, file "cp3")

then `1.8>10` will evaluate to **false**. This is why you are getting the error. — Marios, Sep 28 '20 at 16:15
@Marios that's the first row... there are many rows that are above 10 — Nix, Sep 28 '20 at 16:19
true.. Is this the source sheet ? (`bbb`) or the destination sheet (`ddd`) ? — Marios, Sep 28 '20 at 16:24

score 3 · Accepted Answer · answered Sep 28 '20 at 12:36

Issue:

if (testvalue[i] >= 10)

This condition is never satisfied, therefore data is a 1D array (=[]) and not a 2D array. Therefore,
- data=[]
- data[0] =undefined(has no value in index 0)
- data[0].length => Throws:

TypeError: Cannot read property 'length' of undefined (line 19, file "cp3")

The reason the condition is never satisfied is because

In data sheet J column contains percentages

The value of 10% is 0.1(10/100) and NOT 10.

Solution:

Use 0.1 instead of 10
In addition, As the previous answer mentions , testValue is a 2D array. Although implicit conversion to number will take place, it is preferable to use proper index:
```
if (testvalue[i][0] >= 0.1)
```

To read:

Marios · Answer 2 · 2020-09-28T16:21:57.093

1

Issue:

testvalue is a 2D array of values, therefore in the if condition you are comparing a row with a value, instead of a value vs value. Therefore, you should flat the array: var testvalue = testrange.setNumberFormat("0.00").getValues().flat();

Solution:

function CopyRange() {
  var sourcespread = SpreadsheetApp.openById('aaa'); //replace with source ID
  var sourcesheet = sourcespread.getSheetByName('bbb'); //replace with source Sheet tab name
  var destspread = SpreadsheetApp.openById('ccc'); //replace with destination ID
  var destsheet = destspread.getSheetByName('ddd'); //replace with destination Sheet tab name
  var testrange = sourcesheet.getRange('J:J');
  var testvalue = testrange.getValues().flat();
  var data = [];
  var j =[];
  //Condition to check in J:J, if true, copy the same row to data array 
  for (i=0;i<testvalue.length;i++) {
    if (testvalue[i] >= 10) { 
    data.push.apply(data,sh.getRange(i+1,1,1,3).getValues());  
  //Copy matched ROW numbers to j
    j.push(i);
}  
}
  //Copy data array to destination sheet
if(data.length!=0){destsheet.getRange(destsheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);}
}

edited Sep 28 '20 at 16:21

answered Sep 28 '20 at 12:18

Marios

26,333
8
32
52

I was the one who [wrote the original script](https://stackoverflow.com/a/48691791). Over time, I've seen various copy pastes on this site itself. All without even attempting to remove my signature comment: `//Copy matched ROW numbers to j`. So, I know `push.apply` works. It was one of my first attempts to `flat`(Those days We didn't have `flat` :)). – TheMaster Sep 28 '20 at 13:23
1

@TheMaster yes I noticed my mistake. Sorry for that :) I voted up your answer :) – Marios Sep 28 '20 at 13:24
@TheMaster please don't take it offensive... Thank you for the script, I tried multiple threads to find a way... but didn't see that, I'll credit the original post from next time... if I plainly ask what I want, ppl say I didn't even try... I'm trying to learn by example and also by the hard way. Hope you'd understand – Nix Sep 28 '20 at 14:57
@Nix It's ok. But if I may ask, where did you find the script? – TheMaster Sep 28 '20 at 15:02
@TheMaster https://stackoverflow.com/questions/56261647/google-apps-script-copy-rows-from-one-sheet-containing-todays-date-into-anoth – Nix Sep 28 '20 at 15:04
@Marios I changed it accordingly but the error comes... J:J contained formula and I made the change (*100) so it comes as desired but still I'm getting the same error – Nix Sep 28 '20 at 15:30
@Nix could you please share a screenshot of J column? It would really help us identify the issue. – Marios Sep 28 '20 at 15:41

Google Apps Script Copy Range If Condition Met

2 Answers2

Issue:

Solution:

To read:

Issue:

Solution:

Linked