CODEWITHSUNDEEP
javascriptgoogle-apps-scriptsubstring
AGSTEIN
AGSTEIN

Reputation: 43

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

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...

Upvotes: 2

Views: 5796

Answers (1)

flaky
flaky

Reputation: 7404

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.

Upvotes: 5

Related Questions