Prevent User from adding sheets - google sheets

code here
docs.google.com/document/d/1h...
Photo by cottonbro: www.pexels.com/photo/woman-ho...

[@nourhanebadlis3444]

جزاك الله بألف خير

[@encikmohdrazifibrahim6243]

thank you for sharing

[@jayasri-ft8040]

Super idea, thankyou

[@askfarouk]

Thanks for the positive comment

[@lucianopessanha7437]

Thanks.

[@askfarouk]

Welcome

[@user-kn2rb4cs6v]

I like the idea. But mobile devices don't run SCRIPTS. I have written scripts for years. Unfortunately you can only do this on Desktop computer. And it doesn't stop users from creating sheets. You have to manually delete them; delete them by opening the sheet on a Desktop computer.
:{

[@ashaharika9565]

How to reverse it.
Kindly help

[@lucianopessanha7437]

how do I make hidden tabs remain hidden (hide.sheet)? In my test it deletes the hidden tabs and if I declare them it activates them so as not to delete them.

[@askfarouk]

Hi
Not supposed to be deleted, but let me check

[@arianeg6004]

@@askfarouk Hi, thanks for your idea. Have you perhaps already resolved for those deleted hidden tabs?

[@Account-gd6qu]

This is also my concern ty

[@Accountingpapers]

مرحبا استاذ احمد، كيف يمكن ان نجعل من الشيتات المخفية / مخفية لدى المستخدم ولايمكن له الاطلاع عليها، ايضا كيف يمكن منع المستخدم من اضافة شيت جديد للملف ...شكرا.

[@aymanabuelmaged7230]

A very cool idea, as usual, excellence and creativity, but what if I have more than one sheets?

[@askfarouk]

Thanks 🙏 Mr.Ayman

[@askfarouk]

Same idea make i start from the number of sheets

[@aymanabuelmaged7230]

@@askfarouk appreciate

[@thaycuongtin8479]

you can change the value of i in the for loop. docs.google.com/document/d/1kEUE1XOnwk03jbgBsVPAjJDa2xbikZeLN4hhgwfAdzA/edit?usp=sharing

[@flukturr]

Error
TypeError: Cannot read properties of null (reading 'activate')
onOpen @ Delete sheets.gs:12 :( @@thaycuongtin8479

[@thaycuongtin8479]

It doesn't work on mobile phones, as Iphone. Thank you for sharing!

[@askfarouk]

I can't confirm as i am not apple friendly user

[@Desmog]

// Deletes any tab named "SheetN" where N is a number
function onOpen() {
var newSheetName = /^Sheet[\d]+$/
var ssdoc = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ssdoc.getSheets();
// is the change made by the owner ?
if (Session.getActiveUser().getEmail() == ssdoc.getOwner().getEmail()) {
return;
}
// if not the owner, delete all unauthorised sheets
for (var i = 0; i < sheets.length; i++) {
if (newSheetName.test(sheets[i].getName())) {
ssdoc.deleteSheet(sheets[i])
}
}
}
This will automatically delete any new sheet that was not created by the owner

[@kamaljeetsingh8919]

the code did not work, I have just copy and paste the code without any modification

[@Desmog]

@@kamaljeetsingh8919 did you run the script?
If yes, open the sheet on a PC and see the magic. ( it's not deleting on mobile phones for whatever reason)

[@user-kn2rb4cs6v]

@@Desmog Android and "mobile devices" don't run javascript. It really stinks! But you can only do this on a Desktop computer.

[@Pizzacaviar]

@@Desmog Hey Desmog I have been trying to run this script for the past months and I cannot get it to work. Trying to do this as I manage the schedule of a non-profit but my work is keeping me busy and I don't know much about code. I am thinking this might be due to the fact that my Google Spreadhseet is in French hence newly created sheets are named "feuille XXX" where x is a number. I tried changing 'sheet' by 'feuille' in the script but that does not work. I recently changed the language of the Googlesheet doc to English but still no luck. Any idea what's up ? Thanks !

[@Desmog]

@Pizzacaviar
For your language, please use the updated version below:
function onOpen() {
var newSheetName = /^Feuille[\d]+$/;
var ssdoc = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ssdoc.getSheets();
// If the change is made by the owner, exit the function
if (Session.getActiveUser().getEmail() == ssdoc.getOwner().getEmail()) {
return;
}
// If not the owner, delete all unauthorised sheets
for (var i = 0; i < sheets.length; i++) {
if (newSheetName.test(sheets[i].getName())) {
ssdoc.deleteSheet(sheets[i]);
}
}
}
How to Set Up the Trigger (in French):
1. Ouvrez le document Google Sheets.
2. Allez dans `Extensions > Apps Script`.
3. Copiez et collez le script dans l'éditeur de script.
4. Enregistrez le script.
5. Allez dans `Éditer > Déclencheurs du projet actuel`.
6. Cliquez sur `+ Ajouter un déclencheur`.
7. Sélectionnez `onOpen` dans la liste déroulante des fonctions à exécuter.
8. Choisissez `À partir du tableur` pour la source de l'événement.
9. Sélectionnez `À l'ouverture` pour le type d'événement.
10. Enregistrez le déclencheur.