CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
cyborg
cyborg

Reputation: 491

Google App Script to notify change on particular tab of the sheet?

I am trying to create a notification rule on a google sheet when changes are made to a specific tab of the sheet.

I found some google app script and tweaked it but I continue to get an error message as below. What can be done to fix this?

TypeError: Cannot read property 'changeType' of undefined

Code:

    function notify(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  if (sheet == 'Analytics Project' && e.changeType == 'INSERT_ROW') {
    MailApp.sendEmail('[email protected]', 'Row Added', 'A row was added to your sheet.');
  }
}

Upvotes: 0

Views: 1348

Answers (1)

Marios
Marios

Reputation: 27348

Explanation / Issue:

Your goal is to use an onChange trigger.

You need to understand the following two concepts:

  • Triggers (as the name suggests) are functions that are executed upon events. However, you are trying to manually execute this function but you are not supposed to do that because e is not defined and therefore you are getting a message that the event object e is undefined. Therefore, don't run it manually, but instead insert a new row and you will see that your script will do its job.

  • This is an installable trigger, namely you need to create an onChange installable trigger for notify.

To create an installable trigger, execute only and once the createTrigger function:

function notify(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  if (sheet == 'Analytics Project' && e.changeType == 'INSERT_ROW') {
    MailApp.sendEmail('[email protected]', 'Row Added', 'A row was added to your sheet.');
  }
}

function createTrigger(){
  var sheet = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("notify")
  .forSpreadsheet(sheet)
  .onChange()
  .create();
}

enter image description here

Upvotes: 1

Related Questions