CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user1774015
user1774015

Reputation: 31

Using the myImportRange custom spreadsheet function - TypeError: Cannot call method "split" of undefined

I am trying to use ahab's custom Google Apps Script, myImportRange. I am not familiar with writing custom scripts but I followed instructions in an existing post, and copied and pasted the following into the script editor:

// to be used in the spreadsheet like ImportRange, 
// i.e. like this: =myImportRange( "key" ; "sheet!range" ; GoogleClock() )
// the third parameter - GoogleClock() - triggers an automatic update every minute.
// updated 2011-07-17 (ahab): better regex to strip sheetname of *outer* single quotes
function myImportRange(key ,sheetrange) { 
    var shra = sheetrange.split("!") ;
    if (shra.length==1) shra[1]=shra[0], shra[0]="";  

    var sheetstring = shra[0].replace( /^'(.*)'$/g , "$1") // was: replace( /'/g , "") ; updated 2011-07-17 (ahab)

    var rangestring = shra[1] 

    var source = SpreadsheetApp.openById( key )    

    if ( sheetstring.length==0 ) sheet = source.getSheets()[0] ;
    else sheet = source.getSheetByName( sheetstring ) ;

    return  sheet.getRange( rangestring ).getValues(); 
}

I am now getting the following error:

TypeError: Cannot call method "split" of undefined. (line 6)

I'm sorry if this is a repeat question, I have found many topics related to this subject, unfortunately my knowledge if this kind of thing is so basic, I can't work out how other answers relate to my script.

I'd be very grateful for any advice!

Upvotes: 3

Views: 1324

Answers (1)

Mogsdad
Mogsdad

Reputation: 45750

Since the error isn't clear enough, adding a type check will improve the feedback to the user if they don't provide the range as a string. 

// to be used in the spreadsheet like ImportRange, 
// i.e. like this: =myImportRange( "key" ; "sheet!range" ; GoogleClock() )
// the third parameter - GoogleClock() - triggers an automatic update every minute.
// updated 2011-07-17 (ahab): better regex to strip sheetname of *outer* single quotes
function myImportRange(key ,sheetrange) {
    if (typeof(sheetrange) != "string" throw new Error("range must be a string");

    var shra = sheetrange.split("!") ;
    if (shra.length==1) shra[1]=shra[0], shra[0]="";  

    var sheetstring = shra[0].replace( /^'(.*)'$/g , "$1") // was: replace( /'/g , "") ; updated 2011-07-17 (ahab)

    var rangestring = shra[1] 

    var source = SpreadsheetApp.openById( key )    

    if ( sheetstring.length==0 ) sheet = source.getSheets()[0] ;
    else sheet = source.getSheetByName( sheetstring ) ;

    return  sheet.getRange( rangestring ).getValues(); 
}

Upvotes: 1

Related Questions