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 ment "//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...

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 ment "//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...

• javascript
• google-apps-script
• substring

Share Improve this question Follow asked Apr 3, 2014 at 7:22 AGSTEINAGSTEIN 4322 silver badges44 bronze badges 1

• 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 Commented Apr 3, 2014 at 11:19

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 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.