OnChangeTrigger does not run and have an error message in the GAS trigger panel

I don't understand why my trigger does not run correctly.

I wrote this script to automatically log all changes performed in this spreadsheet. These logs would be written in the sheet "Management Sheet" in the range "D4:H". The fields which must be filled in this range will be: [1--> user email ,2--> new Date(),"--> changeType,4--> sheetName,5--> range of modification)]

function setUpTrigger(){
  ScriptApp.newTrigger('loggerModifFunction')
  .forSpreadsheet('ss Id')
  .onChange()
  .create();
}

function loggerModifFunction(e) {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Management Sheet').getRange(e.range.getLastRow(), 5).setValue([user,new Date(),changeType,sheetName,range]);
}

Google script window shows the following error:

"TypeError: Cannot read property 'lastRow'of undefined".

Solution 1:

Try this

function loggerModifFunction(e) {
  Browser.msgBox(JSON.stringify(e))
}

this will show you what information is available!

{"authMode":"FULL","changeType":"EDIT","source":{},"triggerUid":"123456789012345","user":{"email":"[email protected]","nickname":"XXXX"}}

In any case you can fetch e.range neither user or changeType

You can fetch for instance e.user.email, or e.changeType

Code #1

function setUpTrigger() {
  ScriptApp.newTrigger('loggerModifFunction')
    .forSpreadsheet('ssID1')
    .onChange()
    .create();
}

function loggerModifFunction(e) {
  var ss = SpreadsheetApp.openById('ssID2')
  var sh = ss.getSheetByName('Management Sheet')
  sh.appendRow([e.user.nickname, new Date(), e.changeType, e.source.getActiveSheet().getName(), e.source.getActiveRange().getA1Notation()])
}

where

• ssID1 : the ID of the spreadsheet I want to survey
• ssID2 : the ID of the spreadsheet I want to collect and write the changes (the code is built in this spreadsheet)

Code #2

If you want a specific location and exclude some sheets

function loggerModifFunction(e) {
  var ss = SpreadsheetApp.openById('1HlRT30uvpPD7GNrv2aRZydO1a3zc0kQOulGBd_0mc3k')
  var sh = ss.getSheetByName('Management Sheet')
  var sheetName = e.source.getActiveSheet().getName()
  var excl = ['Sheet1','Sheet2'];//excluded sheets
  if (excl.indexOf(sheetName) == -1){
    sh.getRange(sh.getLastRow()+1,4,1,5).setValues([[e.user.nickname, new Date(), e.changeType, sheetName, e.source.getActiveRange().getA1Notation()]])}
}

Nota

If your are not in the same organization, due to permission policy you will not be able to recover user (except yourself)