I have a Google Sheets spreadsheet on Google Drive for use by my club. I have been getting phantom blank sheets anonymously added, and yesterday I had a phantom copy of my only sheet added. All these sheets get added in the small hours like 2am.
Has anybody else had this? I can't find any mention online. I guess it's a Google bug.
Can't really help, but while browsing Arduino forums I came across discussions by people who upload their logged data to Sheets rather than to SD card, as I am doing. Evidently Google keep tweaking the rules to improve security, often breaking working IOT stuff.
Are you just accessing Sheets from a normal device, or something odd? Perhaps the phantom sheets are produced by some sort of hacking attempt? That could explain the 2am activity?
I am accessing the sheet from a laptop, but anyone or thing with the link could access the sheet from anything.
I make extensive use of google sheets, and have never seen a phantom change as such.
Is this a sheet shared with just names google account owners, or is it public so that anyone with the URL can edit?
The first thing to check is the link to the right of the help menu - this will usually say when it was last edited. If you click on that, you can then step back through all of the previous edits, and see when they were, what changed, and who made the edit. (more use when share with only names individuals)
Creating new empty sheets is something I see users do often by mistake since you only need to accidentally click on the + sight at the bottom right to add a page. Specially on spreadsheets with multiple sheets on different tabs where you may need to click in that area to select another sheet.
Duplication of a sheet is also easy using the popup menu on the tab.
One way to fix it would be a simple function to delete any sheets that are not on a list of ones that you want to keep. E.g:
// List of sheet names *not* to delete const wantedSheets = [ "OneSheet", "AnotherSheet", "Sheet1" ]
/** * Mop up any sheets that are not in the list of ones we want to keep * */
function tidyExtraSheets() { const allSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
for( let sheet of allSheets ) { let name = sheet.getName() if( wantedSheets.indexOf( name ) == -1 ) { Logger.log( "Deleting " + name ) SpreadsheetApp.getActiveSpreadsheet().deleteSheet( sheet ) } } }
The attach that to a timer to run once a day, or add it to an extra menu on the UI or similar... (you could add it to the sheets onOpen event handler, but then that would take all users through the security process to verify that they trust the script.
Not really as its a world wide thing and its always 2am somewhere. Brian
Yes indeed, I use version history to find out when spurious sheets appear, and as someone said, they can look like 2am here when changed from afar in daytime.
The sheet is accessible by anyone with the link without logging in. If sheets were added by mistake by my expected club users, the time would not be shown as 2am with no other activities.
I did not realise Google Sheets allow macros, but the extra sheets are not sufficiently frequent for me to create a macro, when I can easily delete a sheet when it appears.
I've just added code to delete a new sheet as soon as it's added.
If you go to click a tab and get the dropdown, its not that difficult to end up clicking something on the menu by accident. The destructive actions ask for confirmation, but duplicating a sheet does not.
That should stop em :-)
Very true, but how likely will an accident land on the same item twice? Actually very likely, because without logging in, you only get three items in the menu when you right-click the tab: Duplicate, Rename, and Change color. The latter two show another step to the user and would make them back off, but Duplicate just goes ahead and does it, probably without the user realising. I've just tried it, and my added code deletes it! Two problems solved in one.
You are now on the slippery slope, when you suddenly realise you can automate all kinds of stuff! :-) (DAMHIK)
I don't think that will happen. I have a website but I keep all code to a bare minimum, because the user has to wait for it to be downloaded before the site becomes useable.
Just to complete this thread for the benefit of others, here is the code I use to delete the Google sheets. I extracted the lines from a more comprehensive example I found in a discussion. Who would have known that sheet addition is called "INSERT_GRID"?
The 'for' loop deletes all sheets between no.1 and the number found. My wanted sheet is no.0
function onChange(e) { if (e.changeType == "INSERT_GRID") { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); for (var i = 1; i < sheets.length; i++) { SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheets[i]) } } }
HomeOwnersHub website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.