MrAurelien
MrAurelien

Reputation: 358

Shared spreadsheet by link & unprotected range

Here is my problem. I am generating some sheets in a shared Spreadsheet document. It's shared by link. The idea is to lockdown everyone except the Owner of the spreadsheet. All ranges to be locked down has been locked by the following code : range.protect().setDescription("Locked");

All other ranges (that can be modified by anyone) are left without calling protect() on them. So I used the examples at Protection class and I come with this code that I ran in debug mode as the owner :

let rangeProtect = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (let i = 0; i < rangeProtect.length; i++) {
    let protection = rangeProtect[i];
    if (protection.canEdit() && (protection.getDescription() == "Locked")) {
        let editors = ss.getEditors();
        for (let j = 0; j < editors.length; j++) {
            ss.removeEditor(editors[j]);
        }
        // Add only owner
        ss.addEditor(ownerEmail);
    }
}

Unfortunately this is not working. Every person who use the link can still edit everything. But if I modify by hand each protected range with the name "locked" to 'owner only' it works...

So can you tell me what I am doing wrong ?

I tried also this approach:

let protection = sheet.protect().setDescription('Locked');
let unprotected = sheet.getRange("B1:C10");
protection.setUnprotectedRanges([unprotected]);

The problem stay the same :

  1. If I setup the shared link to "View" i cannot modify anything despite the setUnprotectedRanges() call. Everything is locked.
  2. If I setup the shared link to "Edit", I can see that the sheet is locked correctly with exception of that range (as seen when opening the "ranges and sheets protected" from Data menu) but still all person using the link can modify anything in the sheet.

I am stuggling with this problem for days now and I'm running out of idea.

I really tried a LOT of things. So if you know how to do this properly could you give a piece of working code/shared document sample to accomplish this ? Let's say :

  1. Create two ranges with "Locked" description (ex: First Row & First Column)
  2. Set the correct protection on "Locked" ranges (Edition by Owner only)
  3. Anybody with the link could modify all but locked ranges
  4. Using a shared document by link

Any help would be greatly appreciated ! Really !

Note: This code is written in Typescript because I'm using CLASP that convert everything into Google Apps Script's code.

Upvotes: 0

Views: 51

Answers (0)

Related Questions