e.source and e.range missing/unavailable

Question

I have a basic installable trigger I am trying to use to send an email invite based on when a Sheet is edited (a specific checkbox is selected in my case) however when I try and access the range or source object from my event object I get [object Object] and undefined respectively. However I know the event object is working thanks to being able get the oldValue, value, triggerUid, and user.

function onEditCheck(e) {

  var cells = e.range.getA1Notation();
  var name = e.source.getActiveSheet().getName()

  console.log('cells: ' + cells);
  console.log('sheet name: ' + name);
  console.log('id: ' + e.triggerUid);
  console.log('edit: ' + e.value);

  if(e.oldValue === 'false' && e.value === 'TRUE') {
    sendEmail(e.user.getEmail(), e.range);
  } 
}

The only other reference to this issue I could find was this question here however I have had no luck with the permissions "solution" they found.

Any help or insight would be appreciated.

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

This is because necessary authorization wasn't granted to your script.

Add this somewhere in your code:

//SpreadsheetApp.getActive()

This will trigger the oAuth flow and request the permission to access Spreadsheets, which was missing.

This was indeed the issue however adding this code did not fix it itself I had to delete then recreate my trigger in order for the correct authorization to be grant. This fix may work for others but i believe mine was different do the enterprise google account i was using. – mfort11 Dec 06 '18 at 19:09

score 0 · Answer 2 · answered Dec 06 '18 at 04:50

This worked for me:

function createTrigger(){
  var tr=ScriptApp.newTrigger('onEditCheck').forSpreadsheet('My SSID').onEdit().create();
}

function onEditCheck(e) {
  var cell = e.range.getA1Notation();
  var name = e.range.getSheet().getName();
  var data=Utilities.formatString('<br />Cell Edited: %s<br />Name of Sheet: %s<br />TriggerId: %s<br />Value: %s<br />Old Value: %s<br />',cell,name,e.triggerUid,e.value,e.oldValue);
  var userInterface=HtmlService.createHtmlOutput(data);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Testing')
}

By the way it's really handy to go to the trigger editor after creating the trigger and set notifications to immediate. That way you'll get notifications to failures pretty fast from Google Server.

e.source and e.range missing/unavailable

2 Answers2

Related: