0

[repost from an erroneous earlier version]

My spreadsheet seems to be registering a change despite the fact that I'm not opening it at all. onOpen is triggered when the sheet is opened; onChange is triggered when the sheet is changed (such as receiving a form submission):

function onOpen(e) {
  var range = ss.getRange("A" + lastRow + ":A" + lastRow); 
  ss.setActiveRange(range); //automatically scroll to last row
  MailApp.sendEmail("[email address]", "OPENED", "");
}

function onChange(){
  MailApp.sendEmail("[email address]", "CHANGED", "");
  [more stuff happens here]
}

I am getting CHANGED emails at random times, including 5 AM and such. The sheet is populated by a form (actually a JotForm which integrates with the sheet). So when someone submits a form I get the CHANGED email without the OPENED email. When I open the sheet of course I get an OPENED email and an CHANGED email. But how could the sheet be changed without being opened, and without a new submission coming in either? What could be going on?

Raffi
  • 11
  • 3
  • I don't think that you get an onChange for a for submission. You do however get an onFormSubmit – Cooper Jul 24 '23 at 14:31
  • I might recommend console logging the event object from the `onChange` and reviewing the logs of one of those random executions to get some more information on this. Specifically `changeType` and `source` may be helpful in debugging. May get more help from [this answer](https://stackoverflow.com/a/68027420/13771937) – Miguel Rivera Rios Jul 24 '23 at 19:04
  • @MiguelRiveraRios can you clue me in a bit more? I'm relatively new to this. The only way I know to use console logging is when I have the code open and I run it and I see what happens. How do I log what is happening when I have neither the code nor the sheet open? – Raffi Jul 24 '23 at 23:01
  • On the left hand side of the code editor screen you should see a tab for `Executions`, it will show you the results of any times your code has previously run (whether it was initiated by you manually or by a trigger). You'll get some information at a glance, but if you click into an execution you will open the log for that execution. Normally this will not give you anything, but if you add some `console.log` lines, you can see those as well. I would recommend checking out [the event object](https://developers.google.com/apps-script/guides/triggers/events) examples to see how you might log it. – Miguel Rivera Rios Jul 25 '23 at 02:45
  • Please provide a copy of the Screenshots of the Execution log, information how's your Google Sheets was integrated with your Jotforms so the community will have better oversight over how your project works. – Babanana Jul 25 '23 at 03:53
  • Well, this is typical. The changeType it shows is... OTHER. What does that mean?? – Raffi Jul 25 '23 at 21:16
  • OTHER could mean a lot of things, based on my existing knowledge with Google Sheet, onChange or any event triggers should not detect any back end changes on your sheets or form. So I suggest using a Time Trigger instead. – Babanana Jul 26 '23 at 01:17
  • I just don't understand how any change could be happening without opening the sheet and without any forms being submitted... – Raffi Jul 31 '23 at 21:35

0 Answers0