CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Vlack10
Vlack10

Reputation: 1

How do I make sure that multiple Google Sheets files in a folder are protected in one go?

I have a working code that allows me to protect multiple Google Sheets files located inside a folder and to add editors.

But, I have to run the code multiple times in accordance to the number of files in the folder. For example: If I have seven files in the folder, I have to run it seven times.

How do I make sure the files are protected with one run?

This is my humble attempt of putting together the code:

function AddEditorsProtection(){
var myFolder = DriveApp.getFoldersByName('Folder Name').next(); //Add Folder name
var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet"); 
while (spreadSheets.hasNext()) // Loops through all Spread Sheets inside the folder.

var sheet = spreadSheets.next();
var name = sheet.getName();

var spreadsheet = SpreadsheetApp.openById(sheet.getId());
var firstSheet = spreadsheet.getSheets()[0];
var secondSheet = spreadsheet.getSheets()[1];
var thirdSheet = spreadsheet.getSheets()[2];

spreadsheet.setActiveSheet(firstSheet);
var protection = firstSheet.protect().setDescription('Description 1');
protection.addEditors(['Editor 1 Email', 'Editor 2 Email']); // Insert list of editors.

spreadsheet.setActiveSheet(secondSheet);
var protection = secondSheet.protect().setDescription('Description 2');
protection.addEditors(['Editor 1 Email', 'Editor 2 Email']); // Insert list of editors.

spreadsheet.setActiveSheet(thirdSheet);
var protection = thirdSheet.protect().setDescription('Description 3');
protection.removeEditors(['Editor 1 Email', 'Editor 2 Email']); // Insert list of editors.
};

I followed this helpful post to get to where I am: Using scripts across multiple files without creating a new script in every file

Upvotes: 0

Views: 131

Answers (1)

ARANYA MUKHERJEE
ARANYA MUKHERJEE

Reputation: 41

There isn't a built-in feature to protect or edit the protection of several sheets at once but Google Apps Script could be used to automated this task or an add-on created by yourself or by someone else.

Reference

Use Add-ons & Apps Script

Extending Google Sheets

However, if you're only worried about accidental edits, you can use:

protection.setWarningOnly(true);

This applies to all users, including the owner. Unfortunately, there is no way to set the warning text to something more helpful and the user can choose to ignore it.

Here's a script that may achieve what you want:

function protectAllSheets(){
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    let protection = sheet.protect();
    protection.setWarningOnly(true);
}

Upvotes: 1

Related Questions