0

I wrote a script that sends an email immediately when a cell is updated in the GOOGLE SEETS sheet with the contents of the cell When I run it, it throws an error and it says:

TypeError: Cannot read properties of undefined (reading 'source') onEdit @ קוד.gs:3

I have a GOOGLE SEETS sheet that receives data from GOOGLE FORMS. I want as soon as the sheet is updated to receive an email with the received data. This is what I wrote:

`

function onEdit(e) {
  // מחזירה את הגיליון הפעיל
  var sheet = e.source.getActiveSheet();
  // מחזירה את התא הפעיל
  var cell = e.source.getActiveCell();
  // מחזירה את השורה שבה נמצא התא הפעיל
  var row = cell.getRow();
  // מחזירה טווח של תאים מתוך הגיליון הפעיל, מתחיל מהשורה הראשונה והעמודה הראשונה ומכיל את השורה המעודכנת
  var range = sheet.getRange(row, 1, 1, sheet.getLastColumn());
  // מחזירה מערך של הערכים שבתאים המצויים בטווח שנבחר
  var values = range.getValues();
  // מאחדת את כל האיברים שבמערך לתוך מחרוזת אחת, בעזרת המפריד ","
  var message = values.join(", ");
  // שולחת מייל לכתובת EXAMPLE@gmail.com עם נושא "עדכון בגיליון" וגוף ההודעה הוא השורה המעודכנת
  MailApp.sendEmail("EXAMPLE@gmail.com", "עדכון בגיליון", message);
}

`

This is the error I get:

TypeError: Cannot read properties of undefined (reading 'source') onEdit @ קוד.gs:3

[It is possible that the language is a bit sloppy, this is because it is a translation from Hebrew to English, using Google Translate]

Rubén
  • 34,714
  • 9
  • 70
  • 166
ZLALIT 123
  • 11
  • 1
    I thought that the reason for your issue of `This is the error I get: TypeError: Cannot read properties of undefined (reading 'source') onEdit` is due to that you might directly run the function `onEdit` by the script editor. In this case, such an error occurs. Please be careful about this. – Tanaike Dec 21 '22 at 12:50
  • 1
    From `I wrote a script that sends an email immediately when a cell is updated in the GOOGLE SEETS sheet with the contents of the cell`, it seems that you want to run by the OnEdit trigger. In your script, it is required to use the event object by giving when the trigger is fired. But, in this case, `MailApp.sendEmail` cannot be used with the simple trigger. So, please rename the function name and install it as the installable trigger of OnEdit, and edit the cell. By this, the script is run. – Tanaike Dec 21 '22 at 12:50

1 Answers1

0

You can't do that because sendEmail requires authorization, which onEdit won't ever ask for.

https://developers.google.com/apps-script/guides/triggers/

Edit: Since I can't comment on other people answers, I'm editing my own: Tanaike's suggestion works, but mind your usage since theres a limit. Apart from that, your code works fine when running it as an installable trigger.

You can have up to 20 Triggers/user/script, each of them has a maximum run time of 6 min/execution plus a maximum total run time of 90 min/day. As you're running it on every single edit, keep in mind you're likely to hit that last quota limitation.

Joinville SEINFRA-UP
  • 34
  • 5
  • About `Tanaike's suggestion works, but mind your usage since theres a limit.`, can I ask you about the detail of it? I think that my English skill is poor. So, I'm worried whether my comments have modification points. I apologize for my poor English skill. – Tanaike Dec 21 '22 at 13:38
  • Trigger run time limit and number of triggers/user/script. Your english is fine, I don't think your comment needs any modification. Just thought I might put that there, since we don't know the scope of his project, and the 90min/day limit of run time is not really that hard to hit on such a trigger. – Joinville SEINFRA-UP Dec 21 '22 at 13:45
  • Thank you for replying. About `Trigger run time limit and number of triggers/user/script.`, in my comment, I proposed the installable trigger. By this, the expilation time is 6 minutes for Gmail account. How about this? About `number of triggers/user/script`, how about adding it to your question? – Tanaike Dec 21 '22 at 13:47
  • I mean "Triggers total runtime", on https://developers.google.com/apps-script/guides/services/quotas. If hes on a free gmail account, thats 90min/day, and even that simple script would hit that mark considering its running on every single cell edit. – Joinville SEINFRA-UP Dec 21 '22 at 13:56
  • Thank you for replying. I understood it. In that case, how about including the information in your answer? – Tanaike Dec 21 '22 at 14:04
  • 1
    Okay, just included it. – Joinville SEINFRA-UP Dec 21 '22 at 14:26