Rajiv Goswami Rajiv Goswami - 4 months ago 40
Javascript Question

TypeError: Cannot read property "values" from undefined. (line 9, file "Code")



function onInstall(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Form');


var Timestamp = e.values[0];
var Load = e.values[1];
var Email = e.values[2];
var Location = e.values[3];

var template = HtmlService.createHtmlOutputFromFile("notification");
template.Location = Location;

MailApp.sendEmail(Email, "subject", "", {
HtmlBody: template.evaluate().getContent()
});

}





I have written this small program. Everytime user edit spreadsheet. It should run trigger and shoot an email to mentioned email address in column. I am not good in coding. But Please help me solving this, I have been trying from so long.

Here is link to public spreadsheet Spreadsheet



function() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetName('Form');
}

function onEdit(e) {
var Timestamp = e.values[0];
var Load = e.values[1];
var Email = e.values[2];
var Location = e.values[3];

MailApp.sendEmail(Email, "check In Call", "location");

}




Answer

There's a lot of reasons why this script isn't running. Rather then fix the errors, I wrote the snippet from scratch for you, and I'll try explaining why it's not currently working on a line by line basis.

First , here is some working code:

function onEdit(e) {
  var activateSheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(activateSheet.getSheetByName('Form'));
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = e.range.getRowIndex();//Gets the row the change was made to.  

  var timestamp = sheet.getRange(row, 1).getValues(); //Gets the email
  var load = sheet.getRange(row, 2).getValues(); //Gets the email
  var email = sheet.getRange(row, 3).getValues(); //Gets the email
  var location = sheet.getRange(row, 4).getValues(); //Gets the email

  MailApp.sendEmail(email, "Check In Call", location);

}

The fields 'Load' and 'Timestamp' don't seem to be required for what you want, but I left them in anyway, because maybe you need them.

Here's a list of things that weren't working for you:

  1. Your first function where you called the sheet didn't run at any stage, as there's no trigger, and it doesn't pass the activated sheet onto your 'onEdit' function.
  2. 'getSheetName()' is for retrieving the name of the sheet, not getting the sheet by name. The method you're actually looking for is: 'getSheetByName(name)'.
  3. e.value is only available if what's edited is a single cell, which based on your spreadsheet is unlikely.
  4. Even if e.value wasn't a single cell, you weren't using it correctly, but this isn't the subject for this question.

As a side note: I get the feeling from the syntax errors that you're not familiar with Javascript. Before trying anything with Google Apps script, I recommend you familiarize yourself with Javascript to avoid many, many wasted and frustrated hours trying to complete simple actions.

Comments