CODEWITHSUNDEEP
javascriptgoogle-sheetsspreadsheetgoogle-docs
Dromnes
Dromnes

Reputation: 302

'Private' cells

I wondered if there is any way to "give" a cell to someone in Google Spreadsheet? I have a spreadsheet which anybody can write what they want, and anybody can edit what they want. I want to add a function that makes it possible for only the "author" of a cell can edit it.

E.g. John Doe writes "I want a banana" in cell "B5". And Jane Doe writes "I want an apple" in cell "B6". John does not want to give an apple to Jane, so he edits "B6" and changes the text to "I do not want an apple.

I want to prevent John to edit Janes cell. I want to make sure that only John can edit his cell, and Jane can edit her cell. And I want it to happen automatically.

Does anybody know how i can do this?

Upvotes: 0

Views: 52

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

The manual way

You can protect single ranges by going on

right click > Protect Range > Set Permissions > Restrict who can edit this range > Custom

and enter the email address that is allowed to modify that cell.

The admin bulk way

You can also do it in a function if you want to go through a bunch of names and email addresses:

var ranges = [["A1", "[email protected]"],
              ["A2", "[email protected]"]];

function setEditRights(){
  for (var i = 0; i < ranges.length; i++){
    protect(ranges[i][0], ranges[i][1]);
  }
}

function protect(range, emailAddress){ 
  SpreadsheetApp.getActiveSpreadsheet()
                .getActiveSheet()
                .getRange(range)
                .protect()
                .addEditor(emailAddress); 
}

The Self service way

Another option is to allow users to reserve cells for themselves you could do that like this:

function setAccess(){
  SpreadsheetApp.getActiveSpreadsheet()
                .getActiveSheet()
                .getActiveRange()
                .protect()
                .addEditor(Session.getActiveUser().getEmail()); 
}

with a function adding a menu

function onOpen(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Reserve a Range')
      .addItem('Reserve current Range', 'setAccess')
      .addToUi();
}

This will allow users to select a cell or range and lock it for access for only themselves and you as the owner. It is however basically just a more conventient shortcut of the first method.

Upvotes: 2

Related Questions