Google Script - Cannot read property "range" of undefined (line 30, file "Code")

3.3k views Asked by At

So, I'm trying to use the script to identify changes in Row 60 and if it identifies certain changes, it will automatically email the client to update him/her on the status of their order.

However, line 30 of the Code is throwing up an error that I can't figure out any help would be appreciated:

function onEdit(e) {
  // Your sheet params
  var sheetName = "Form responses 1";
  var dateModifiedColumnIndex = 64;
  var dateModifiedColumnLetter = 'BL';

  var range = e.range; // range just edited
  var sheet = range.getSheet();
  if (sheet.getName() !== sheetName) {
    return;
  }

  // If the column isn't our modified date column
  if (range.getColumn() != dateModifiedColumnIndex) { 
    var row = range.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy, hh:mm:ss");
    var dateModifiedRange = sheet.getRange(dateModifiedColumnLetter + row.toString());
    dateModifiedRange.setValue(time);
  };
 }

function triggerOnEdit(e) 
{
  sendEmail(e);
}

function checkStatus(e)
{
  var range = e.range; //This is the line that throws up the error. //
  if (range.getColumn() <=60 && range.getColumn() >=60)
  {
    var edited_row = range.getRow();
    var status = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form responses 1").getRange(edited_row,60).getValue();
    if (status == "PENDING" || status == "APPROVED" || status == "REJECTED")
    {
      return edited_row;
    }
  }
  return 0;
}

function sendEmail(e)
{
  var approved_row = checkStatus(e);
  if (approved_row <=0)
  {
    return;
  }
  sendEmailByRow(approved_row);
}

function sendEmailByRow(row)
{
  var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form responses 1").getRange(row, 1, row, 63).getValues();
  var row_values = values[0];
  var candidate_email = composeCandidateEmail(row_values);
  MailApp.sendEmail(candidate_email.email,candidate_email.subject,candidate_email.message);
}

function composeCandidateEmail(row_values)
{
  var name = row_values [4];
  var email = row_values [5];
  var comment = row_values [63];
  var status = row_values [60];
  var link = row_values [61];
  var subject = "Your Purchase Order is " +status;
  var message = "Hello " +name+ ",\n \n Your Purchase Order is " +status+ 
                ". \n \n The following comments were made: /n" +comment+ "\n \n You can find your original Purchase Order here: /n /n" +link;
                return({message:message, subject:subject, email:email});
}

Image of spreadsheet, pay attention to the Column headers as some columns are hidden:

Image of Spreadsheet

Thanks for any help on this B

1

There are 1 answers

2
Cooper On BEST ANSWER

Try this:

You need to create and installable trigger for it. Of course, you can not test it by running it from the code editor like any other function because most other functions don't require event objects. If you wish to provide an image of the spreadsheet and a csv of the data (which can be posted into a code section of your question), then I'd be willing to try to get it running for you. Note: I don't wish to have a link to a spreadsheet because I no longer following links to spreadsheets. If you must know why then read my profile.

function onMyEdit(e) {
  var sh=e.range.getSheet();
  if (sh.getName() != 'Form responses 1')return;
  if (e.range.columnStart != 64) { 
    var time=Utilities.formatDate(new Date(), "GMT-08:00", "MM/dd/yy, HH:mm:ss");
    sh.getRange(e.range.rowStart,64).setValue(time);
  }
  if(e.range.columnStart == 60) {
    if(e.value=="PENDING" || e.value=="APPROVED" || e.value=="REJECTED") {
      var vs=getSheetByName("Form responses 1").getRange(e.range.rowStart,1,1,63).getValues()[0];
      var subject=Utilities.formatString('Your Purchase Order is %s', vs[60]);
      var message=Utilities.formatString('Hello %s,\n\n Your Purchase Order is %s. \n\n The following comments were made:\n%s\n\n You can find your original Purcase Order her: \n\n %s',vs[4],vs[5],vs[63],vs[61]);
      MailApp.sendEmail(vs[5],subject,message);
    }
  }
}