-2

I am using the below filter function to copy data from one sheet to another, filtering out so that it only copies data if column AE = TRUE. When I use this script, it gives me the error:

TypeError: Cannot call method "getRange" of null. (line 9, file "Code")'

I am not sure what the getRange section means.

function myFunction() {
var sss = 
SpreadsheetApp.openById('1V3Tl4h43AC92K4gQtvdZ07k01QJOnfwSD1BKKTziHJU'); 
var ss = sss.getSheetByName('<20 Filter'); 
var range = ss.getRange('A:AE'); 
var data = range.getValues();

var tss = 
SpreadsheetApp.openById('1AKL81cN5QWTWSsSfuMGcFRED5QpeAK022k8VwrPl7sA'); 
var ts = tss.getSheetByName('Zones >20 Riders'); 
ts.getRange(ts.getLastRow()+1, 1, data.length, 
data[0].length).setValues(data);

var range = ts.getRange(ts.getLastRow()+1, 1, data.length, 
data[0].length)
var rawData = range.getValues()     // get value from spreadsheet 2
var data = []                       // Filtered Data will be stored in this array
for (var i = 0; i< rawData.length ; i++){
if(rawData[i][30] == "TRUE")            
{
data.push(rawData[i])
}
}
}

Or could I use this script?

function myFunction() {
var sss = SpreadsheetApp.openById('1V3Tl4h43AC92K4gQtvdZ07k01QJOnfwSD1BKKTziHJU'); //replace with source ID
var ss = sss.getSheetByName('<20 Filter'); //replace with source Sheet tab name
var range = ss.getRange('A:AE');      //assign the range you want to copy
var rawData = range.getValues()     // get value from spreadsheet 1
var data = []                       // Filtered Data will be stored in this array
for (var i = 0; i< rawData.length ; i++){
if(rawData[i][30] == "TRUE")           
{
data.push(rawData[i])
}
}
var tss = SpreadsheetApp.openById('1AKL81cN5QWTWSsSfuMGcFRED5QpeAK022k8VwrPl7sA'); //replace with destination ID
var ts = tss.getSheetByName('Zones <20 Riders'); //replace with destination Sheet tab name
ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

}

But it gives me the error:

TypeError: Cannot read property "length" from undefined. (line 15, file "Code")

Rubén
  • 34,714
  • 9
  • 70
  • 166
J.Mar
  • 1
  • 1
  • 1
  • 3

2 Answers2

1

It means that tss.getSheetByName('Zones >20 Riders'); did not return anything. You need to check that indeed a sheet was found before trying to access it.

Here's the example provided at Google Docs:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expenses");

if (sheet != null) {
    // Do your thing
}

If you are not sure what a method does, always check the documentation. For example, getRange returns the specified Range of cells in the sheet.

m.spyratos
  • 3,823
  • 2
  • 31
  • 40
-1

try changing as below

var sss = 
SpreadsheetApp.openById('1V3Tl4h43AC92K4gQtvdZ07k01QJOnfwSD1BKKTziHJU'); 
var ss = sss.getSheetByName('<20 Filter'); 
var sheet = ss.getSheets()[0];
var range = sheet.getRange('A:AE'); 
var data = range.getValues();

note: getSheets returns the sheet this range belongs to.

Rubén
  • 34,714
  • 9
  • 70
  • 166
lpradhap
  • 623
  • 6
  • 17
  • Thanks for your help! Unforuntately this didn't work. Could I try using this script? – J.Mar Sep 25 '17 at 00:15
  • TypeError: Cannot find function getSheets in object Sheet. (line 5, file "Code") – J.Mar Sep 25 '17 at 00:19
  • can you console.log(ss) after var ss = sss.getSheetByName('<20 Filter'); – lpradhap Sep 25 '17 at 00:22
  • That still gives me the same error if I put var ss = sss.getSheetByName('<20 Filter'); console.log(ss) or var ss = sss.getSheetByName('<20 Filter'); console.log(ss) – J.Mar Sep 25 '17 at 00:23
  • nope, cant be. since your trying to console log "var ss = sss.getSheetByName('<20 Filter'); console.log(ss)" even before your calling the getSheets. then something should have printed, null or something mate. – lpradhap Sep 25 '17 at 00:26
  • Yeah it still just gives me this error :( TypeError: Cannot find function getSheets in object Sheet. (line 5, file "Code") – J.Mar Sep 25 '17 at 00:34
  • Any other suggestions on a filter script? How about the other one I posted 'Could I use this script?...'? – J.Mar Sep 25 '17 at 00:35