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

[Matt Lyttle]

Hi everyone,

I am relatively new to this scripting and I am now getting the error in the subject. "TypeError: Cannot call method "getRange" of null. (line 17, file "Code")"

The larger script is getting based on a form being submitted. When submitted the script find the Region submitted then cuts the Strign to find the corresponding list of email addresses from the specific Sheet and column. 

  var emaillist1 = e.namedValues['Error Region'].toString(); //Picking up the region submitted e.g. Australia:South QLD

  var emailss = emaillist1.indexOf(':'); // split the string to get the destination effected Australia:South QLD would become ':South QLD' 

  var emaillist2 = emaillist1.substring(0,emailss); // sets another string from the first character till the split sting e.g. ':South QLD' giving us 'Australia' (This seems round about but the only way I could work it out...) 

  var setup = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(emaillist2);// Each sheet for the major region is labelled by the starting region - e.g. Australia or USA

  Logger.log(emaillist1)

  Logger.log(emailss)

  Logger.log(emaillist2)

  Logger.log(setup)

  

  var emailheaders = setup.getRange(1,1,1,20).getValues()[0]; // This is suppose to get the header rows (which are the allowed entries into the google doc. e.g. Australia:South QLD) --- This is the line on the ERROR

    

  var emailcol = emailheaders.indexOf(emaillist1)+ 1; // this then searches the string to give me the column number. Which i then use to get the email addesses in that column etc. 

Details on the Logger prior to error is as below. 

[19-05-13 20:57:12:049 PDT] Australia:South QLD

[19-05-13 20:57:12:049 PDT] 9.0

[19-05-13 20:57:12:050 PDT] Australia

[19-05-13 20:57:12:050 PDT] Sheet

Nothing was changed to the code and suddenly started producing this error after working perfect for 3 months. I can't test the script easily as the e.namedvalues errors unless you submit. When I remove it and hardcode in the response as 'Australia:South QLD' I don't get the error. 

Further to confuse me though... the script works... It is sending the email perfectly to the correct recipients. I then noticed in the executions log the script is now running 2 times. and the second one is producing the error. 

Head	contactUsMailer2	Trigger	May 14, 2019, 1:57:14 PM	0.173 s	Failed
Head	contactUsMailer2	Trigger	May 14, 2019, 1:57:11 PM	7.283 s	Completed
Head	contactUsMailer2	Trigger	May 14, 2019, 1:37:59 PM	0.224 s	Failed
Head	contactUsMailer2	Trigger	May 14, 2019, 1:37:56 PM	7.985 s	Completed

So is the error due tot he fact it is running a second time and the "submitted value" is null... therefore it is erroring? If so.. why would it suddenly be trying to trigger 2 times on submit?

[googleadsscrip...@google.com]

Hi Matt,

This is more of an Apps scripts issue rather than Ads scripts. However, I would recommend double checking the range provided to the getRange method. You can find support for Apps scripts here.

Regards,

Matt

Google Ads Scripts Team

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scripts+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/684cdcb7-13eb-4ced-be56-45120be6b265%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.