Google Spreadsheet Script To Insert And Delete Sheets with Protection

Question:

( by Izzz )

I'd like to share a spreadsheet, but don't want anybody who has editing permission to be able to add sheets. (Basically I want to only give them permission to edit four pages of the spreadsheet.) One solution I came up with is to add 196 additional sheets (I believe google only allows 200 sheets) and protect all 196, which by default will only leave them access to the original 4, and they'd also not be able to add any sheets. Any other easier ways?

=========

Forms wouldn't work as I want people who I'm sharing the spreadsheet with to be able to modify cells as they get tasks done. Entering data on their own sheets is also not a solution since this is at work where they restrict access to google docs unless a link to a specific doc is allowed (sigh, what problems they cause....). 

Regarding the script - I thought of doing it that way but have never created a script in google before. Do you have any more resources you can share besides the link you included? 

=========

Actually, seems like var ss = SpreadsheetApp.getActiveSpreadsheet();

 ss.insertSheet();

will add a sheet. Question is what can I add to that code to have it add 100 sheets....

Thank you

Solution:

Have a look at the following code:

///////////////////////////////////////

function insertSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  for(var i=1;i<=196;i++) ss.insertSheet(i.toString()).hideSheet();
}

function deleteSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for(var i=sheets.length-1;i>=0;i--) {
    if( sheets[i].getName() != "Sheet1" && 
        sheets[i].getName() != "Sheet2" && 
        sheets[i].getName() != "Sheet3" && 
        sheets[i].getName() != "Sheet4" ) {
          try {
            sheets[i].showSheet();
            ss.setActiveSheet(sheets[i]);
            ss.deleteActiveSheet();
          } catch(e) {};
        }
  }
  SpreadsheetApp.flush();
}

function insertSheetsWithProtection() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet,permissions;
  for(var i=1;i<=196;i++) {
    sheet = ss.insertSheet(i.toString());
    permissions = sheet.getSheetProtection();
    permissions.setProtected(true);
    sheet.setSheetProtection(permissions);
    sheet.hideSheet();
  }
}

///////////////////////////////////////

put the above code in Script editor of your Spreadsheet.


Run function insertSheets() to quickly insert 196 Sheets in your spreadsheet. It will also hide these newly created sheets.

Run function deleteSheets() to quickly delete all sheets in your spreadsheet, but you can keep the sheets that you need by mentioning the name of the sheets, in the above code I have mentioned "Sheet1", "Sheet2", "Sheet3" and "Sheet4".

Run function insertSheetsWithProtection() to quickly insert 196 Sheets in your spreadsheet with setting protection on it so that none of the collaborators will be able to delete or rename or edit this sheet. This function will also hide newly created sheets.

And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive