Identify External Apps Script That Modifies Google Sheet

0
votes

There is a Google sheet with a bound Apps script. One of the functions (written by me some time ago) that applies custom formatting etc. is triggered by an installed onEdit trigger (installed by the client). This worked perfectly until now.

The spreadsheet owner (one of my clients) recently complained that when he enters any text into a cell then the cell next to it will automatically get overwritten with "FALSE", and when he edits that "FALSE" then the first cell gets overwritten with "FALSE", which makes no sense at all.

I have checked the original script, which still looks fine, it can not cause this automatic mess creation. To make sure I am not missing something, I have checked, there is only one installed trigger, and there is no other simple onEdit function in the bound script.

I have also temporarily changed the name of the function started by the installed onEdit trigger to disable the trigger. Despite the fact that now editing could not run any function in the bound script, the automatic mess was still active, and something has automatically overwritten some cells when the user is editing.

This makes me suspect that an external unbound script (could be also a script bound to another spreadsheet with a timer trigger) might be the culprit and doing all the mess.

My question is whether or not there is a way to identify any external scripts (not bound to this spreadsheet) that change the sheets?

In this case I can not provide any code or show you the spreadsheet/script because it is confidential, belongs to the client. I can not show you any code that I have tried either. Only the above explanation how I attempted to locate the source of the problem.

Additionally let me add, that in this case more than one people are messing with the sheets and scripts in the background that I don't know who they are. Therefore, there is a chance that some rogue parties are trying to deliberately sabotage my work (if for nothing else, then as a prank).

UPDATE_1

I have made a copy of the spreadsheet, then verified the installed triggers page and there are no triggers installed.

Next, to make absolutely sure that none of my scripts should cause the problem, I have deleted all my scripts. Only an empty project remained, still having the same name as originally. Saved the empty project. Reloaded the spreadsheet and tested again.

The problem still exists: when I edit the misbehaving cells, the cell next to it automatically changes to "FALSE".

I have also made sure that no ARRAYFORMULA is present in those columns.

1
google-apps-scriptgoogle-sheetstriggers
Can you share the onEdit function? Also, what are the sharing settings of the Spreadsheet? - Rafa Guillermo
As mentioned in the original post, I can not share the script. Even if I would share it, that would make no difference, since the function run by the installed onEdit trigger was disabled, and the problem still existed. The problem can not be caused by my old function serving the onEdit trigger. The sharing is set to Restricted. The spreadsheet is owned by my client, I am an editor, and there are two other editor emails addresses there (might belong to the owner anyway). - Z_Losonc
You can't see what script is editing the sheet. The best you can do is check the version history to see who edited the cell to FALSE, at least limiting the culprit down to the person who owns the script. - Rafa Guillermo

1 Answers

0
votes

The right answer to the topic question was first given here by @TheAddonDepot thanks, but for some reason he deleted it. If he re-posts the same answer here, I will accept it as the best answer and give full credit for it. Then few minutes later Rafa Guillermo repeated it as seen above.

The answer is that it is not possible to identify external apps that modify the spreadsheet, but the only chance to find a trace is to look into the version history to see which editors have modified the sheets. I take this as the correct answer as long as someone does not offer a better answer.

In mean time (as explained in the update of the original post) I have stripped down all scripts from the spreadsheet, but the problem was still present. With this setup the only remaining suspects were any addons.

It turned out that there were these two addons visible in the Add-ons menu but they were not installed:

Sheets2GCal

Magic Cell Notifications

Since I could not remove an addon that was not installed, I had to install Sheets2GCal and then removed it right away. This fixed the problem, the automatic mess has stopped.

But, to make sure the other one should not cause any problems in the future, I have installed and uninstalled that one as well.

Thanks again to the contributors.