google apps script TypeError: Cannot call method "substring" of undefined

Question

I have a google form that when submitted calls an "on form submit" function that includes javascript .substring(start,finish) method. The strange thing is that in my testcode it works fine but when it actually has to implement with the "on submit" code I get an error that says: TypeError: Cannot call method "substring" of undefined. (line 26, file "Code")

Here is my test code which works fine (calls a Browser.msgBox)

function test()
{

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];  //assumes that sheet containing header columns is first sheet
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var values = SpreadsheetApp.getActiveSheet().getRange(1, 1, 1, lastColumn).getValues();

  //variables with form submit data would go here but not for this testing


  var dataDump = "\n\n"
  var headerValue = values[0][5];
  var headerValue5 = headerValue.substring(0,5);

  Browser.msgBox(headerValue5);
}

This code generates an error...

function onFormSubmit(e) 
{

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];  //assumes that sheet containing header columns is first sheet
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var values = SpreadsheetApp.getActiveSheet().getRange(1, 1, 1, lastColumn).getValues();

  //define variables
  var timeStamp = e.values[0];
  var name = e.values[1];
  var email = e.values[2];
  var emailSubject = "QMG periodic wRVU responses for ";
  emailSubject = emailSubject + timeStamp;
  var dataDump = "\n\n";

  for (var i = 3; i <= e.values.length; i++)
  {
    var headerValue = values[0][i];
    var responseValue = e.values[i];
    var responseValue5 = responseValue.substring(0,5);  //error here !!!!!!
    i++;  //increment i to get detailsValue
    var detailsValue = e.values[i];
      dataDump = dataDump + headerValue;
      dataDump = dataDump + ":\n\n";
      dataDump = dataDump + responseValue;
      dataDump = dataDump + "\n";
      dataDump = dataDump + detailsValue;
      dataDump = dataDump + "\n\n*********************\n\n";

  }

  MailApp.sendEmail(email, emailSubject, dataDump);  
}

Removing line with comment "//error here !!!!!!" works fine so I know that it is reading the array data fine. Plan was to use the .substring() to determine some formatting so would like to get this working.

Thanks in advance...

answer below it right, array indexes count from 0 so the length of an array of 5 elements is 5 but its last index is 4... index 5 is undefined indeed which is not an error but has no string method that can be applied of course. — Serge insas
– Serge insas, Commented Apr 3, 2014 at 11:19

flaky · Accepted Answer · 2014-04-03 07:57:20Z

5

no pro here per se, but I think I have a hunch on what the issue is.

var array = ["aaa", "bbb", "ccc"];

for (var i = 0; i <= array.length; i++) {
    console.log(array[i]);
}

See when running the above code, I'll get a console.log at the end with undefined,
due to the i <= array.length.

Running a string method like .substring on undefined throws the error.

Change the <= to a < and you should be good to go.

answered Apr 3, 2014 at 7:57

flaky

7,4404 gold badges34 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

flaky Over a year ago

glad I could help, maybe you could check my reply as the 'correct' answer so I can get mad karma! :)

Collectives™ on Stack Overflow

google apps script TypeError: Cannot call method "substring" of undefined

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related