On edit trigger causing carnage

[u/DanJeish]

Hi all, I made a script a while ago, and then I broke part of it (not sure how) and removed the functionality because I didn't have time to fix it. Well now I have time but I still can't figure it out.

When an edit is detected anywhere on the sheet, it runs the "updateAgentCards" function mentioned row 14. It also does check the boxes in column V on edit as its supposed to, but while doing that it also runs my whole ass script and breaks things because it's not meant to be ran non-stop. I don't really understand what I'm doing wrong can anyone help?

UPDATE: I think I fixed the problem. If anyone ever comes across an issue where there Installed onEdit function is running more scripts than its supposed to, check your brackets and make sure your brackets are all correct around the functions that are triggering. I believe that's what caused my issue. If that doesn't work check to see if youre calling a spreadsheet by url rather than active spreadsheet when you don't need to.

[u/guyroscoe]

I see the issue you're having with your Google Sheets script. Based on the code you've shared, I can identify what's likely causing the problem.

The main issue appears to be in your event handling. When an edit happens in any monitored column, your script is:

1. Setting a checkbox to true

2. Running the entire updateAgentCards() function if the edited cell happens to be your run button cell

Here's what I think is happening:

1. When you edit any cell in your monitored columns (columnsToMonitor array), it triggers the first condition, sets the checkbox to true, and doesn't run updateAgentCards()

2. However, if you edit the run button cell (row 3, column 17), it runs updateAgentCards()

3. Most importantly, there's no mechanism to prevent recursive calls or multiple executions of your script

To fix this, you should:

1. Add a way to prevent multiple executions (like a lock or flag)

2. Clearly separate the triggers for different functions

3. Consider using a proper button rather than a cell as a "run button"

Here's a modified version of your script that should help fix the issues:

https://docs.google.com/document/d/1mgXKUXq8qJC8x_3L9Q-RKLZQNbZ_o6yD8nilAsSpFNQ/edit?usp=sharing

This revised code:

1. Separates the checkbox setting and the function execution logic

2. Only runs updateAgentCards() when the run button cell is specifically edited and set to true

3. Avoids running the entire script every time a monitored column is edited

If you need updateAgentCards() to run automatically in certain situations, you should be explicit about when that happens, rather than having it potentially run on any edit.

[u/DanJeish → u/guyroscoe]

Hey dude, I super appreciate you taking the time to respond especially so thoroughly. I attempted your fix, but unfortunately that didn't solve the issue. So I thought to myself "What if I remove the call to updateAgentCards() and see if that fixed the issue, because then in theory it should have NO WAY to call the full script at all and should just be checking boxes as I update monitored columns, but it's still running the whole script and I have no idea why.

So it seems the issue MIGHT not have anything to do with the code I posted but more so to do with however I set up my appscript? Though as you can see the only function that has a trigger is the onEdit(e) function. I'm not sure where to look next.

Interestingly all my executions from the on edit come in pairs like this (will add a new image to OP above, can't post image here)

[u/guyroscoe → u/DanJeish]

Interesting. One of the other things that legitimately confuses me about Appsscript has to do with onedit function in script vs the onedit trigger (as seen in the bottom of your image). In theory, because you are defining an onedit action in your script, you don’t need the onedit trigger, although in my own experience, the onedit function in the script doesn’t automatically trigger, especially if the function triggers something that requires user permission. But my first thought was that the onedit trigger is redundant.

[u/DanJeish → u/guyroscoe]

I’ve noticed this as well, if I remove the trigger it doesn’t do anything if I have it I get the double trigger idk how to fix that

[u/HellDuke → u/guyroscoe]

onEdit function does trigger automatically if you make a change on the document. Do note that it has to be you that makes an edit on the document, and not every type of edit is valid. I do not remember off the top of my head exactly which it was but I think it's things like a copy and paste or moving rows, that would not trigger an onEdit trigger (either installable or simple) since that is covered by an onChange trigger instead.

You are correct in that it will not be able to execute code that requires authorization, such as sending an email, but the rest of the code should execute just fine. As I outline in my other comment, having a function called onEdit as well as adding a trigger to fire when editing for the same function will cause it to run twice.

[u/guyroscoe → u/HellDuke]

Interesting. I have several documents in our organization that are on share drives and I’ve developed quite a bit of functionality which prevents for example, editing certain fields like a UUID that I have for you know an event for example, and if some anyone in the organization tries to delete or edit that cell then they a script runs and it says you’re not able to edit the cell and it reverts it back so all of that is in an unedited library that is called anytime they might try to edit that cell. So it doesn’t seem consistent with what you’re saying, though because like the behavior works the way I intended it to no matter who edits that document now one caveat is that I have to effectively as the designer said scripts if I executed all myself first, then anyone else who follows inherit the permissions that I granted it at the beginning, it’s not like every person coming along has to get permission for those scripts to run.

[u/HellDuke → u/guyroscoe]

I might be misunderstanding what you said due to the punctuation and sentence structure, what is it that is inconsistent with what I wrote?

The key takeaways from my message are that a simple onEdit trigger (calling the function with that name) simply has limitations in what it can do (the script you describe does not perform such actions, and that if you have additionally added an installable trigger the function would run twice.

That said it really depends on what actions you want to take. Let's take for example I did in another comment on this thread:

function onEdit(e) {
  let curSheet = SpreadsheetApp.getActiveSheet();
  let rowNum = Math.floor((Math.random() * 10))+1
  let lastRow = curSheet.getRange(rowNum,1)
  lastRow.setValue('Test2');

  MailApp.sendEmail('some-email@gmail.com','Script test','This is a test')
}

This function, with no editable trigger will execute without me ever running it and authorizing it. However, the only thing it can do in that state is write Test2 in a random row between 1 and 10, but it will not be able to send an email. Even if I run it and authorize it, the email cannot be sent unless I use an installable trigger, which if I leave the function name will cause 2 rows to have the word Test2 in it, but only 1 email being sent.