How can I email to someone (not gmail) when I get comments in Google Spreadsheet?

Question

I made a Google Spreadsheet which has several different sheets assigned to different people. I'm trying to send a notification email to a person when he has a comment in his sheet. For example, when someone make a comment on Jake's spreadsheet, Jake will get an email saying he got a comment.

I tried to use "onEdit" to detect comment action, but onEdit cannot detect it. So, I tried to use Drive.Comments.list, but it didn't work well either.

When I tried to test Drive.Comments.list in script editor, it works fine. But, when I tried to use it via spreadsheet, that throw me an error which is "Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup." I have enabled Drive API among advanced google service and google console.

I have no idea what's wrong here.

Can anyone help me? Is there any way to detect comment action? or can anybody help me to use Drive.Comments.list properly?

Answer 1

Sorry to bear bad news, but there is no way to use the onEdit trigger to get comment data unless I am missing something in the documentation. The only methods I can find is getNote() and getNotes(): https://developers.google.com/apps-script/reference/spreadsheet/range#getnote

Those will only help you get notes from the cells and not the comments.

You could setup a timed trigger to check for notes and email those, however, like I said, this doesn't solve your problem about getting comment data.

Answer 2

To be able to use Drive.Comments.list() authenticated as your account (document owner) you should be using Installable Trigger rather than Simple Trigger wich a reserved function name onEdit() suggests.

Solution is rather simple: rename your function from "onEdit()" to something else and set it to run triggered by the document edit event.