How to Make Specific Cells Publicly Editable in Google Sheets

file-sharinggoogle sheetsgoogle-drive-sharingsharing

I want to publish a tool using Google Sheets that should be as easy to use as possible, so I would not like the user to have to create a copy (requiring a Google account), then editing cells in that, instead I want them to edit two cells in my file directly.

Now the problem: The best I've found so far is protecting an entire sheet except for those two cells, which at least prevents adding new rows etc., but not creating new sheets in that file. Alternatively, I could share ranges with certain mail addresses, but that would require manual action every time someone wants to use my tool (and additional steps again). I was also unable to to find any advances sharing options other than nothing/view/comment/edit and some unrelated options, like disabling printing or re-sharing.

So how do I make everyone able to edit only a few cells, nothing else?

Best Answer

According to Rubén's comment, there is no way to do this. Google Sheets files can only be completely uneditable or they allow creating new sheets.

This in my opinion a bug or just an oversight and I've reported it using the "feedback" menu, in case that even goes anywhere.

Code

function setUsers() {  
  var date = new Date(), day = date.getDay(), hour = date.getHours();
  var monday = 1, friday = 5;
  if((day == monday && hour == 8) || (day == friday && hour == 14)) {
    var sProp = PropertiesService.getScriptProperties();
    if(!sProp.getProperty('day')) { 
      sProp.deleteAllProperties();
      sProp.setProperty('day', day);
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      switch(day) {
        case monday:
          ss.addEditors(ss.getViewers());
          break;
        case friday:
          var editors = ss.getEditors(); 
          for(var i = 0, iLen=editors.length; i < iLen; i++) {
            var user = editors[i];
            ss.removeEditor(user);
            ss.addViewer(user);
          }
          break;
      }      
    }    
  }
  return;
}

Trigger

enter image description here

Explained

The trigger is only fired when it's a Monday or a Friday and only on those specific times. After that it will check whether it hasn't been triggered once before, by looking up in the scripts memory. If nothing is found for that day, then delete all other entries and create a new one. This way, if the script is triggered again, it will find an entry and skip to return. The switch statement makes it possible to switch between the days and the different actions to make. On Mondays, the existing viewers need to be added as editors. This can be done via a batch operation (at once). On Fridays the editors need to be removed first in order to add them as viewers.

Notes

I've tested it against two of my own accounts and it worked. As far as I know, no messages are being send about the user being added as viewer or as an editor (which is the case when you do that manually). In the editor mode, the viewer mode is being enforced automatically. This isn't the case when the user is in the viewer mode. There the user needs to refresh the window (F5).

If you the set trigger on a 5 minutes interval, then in theory the time frame can be missed by 5 minutes. If you choose to pick the 1 minute interval, then this will be minimized to 1 minute.

Another option would be to create a trigger each and every time. This way the set time can be programmatically triggered (even thought the triggers are bound to be executed give or take 15 minutes, see reference²).

Add the code under Tools > Script editor and press the bug button to authenticate the script. Furthermore, create a trigger by selecting Resources > Current project's triggers.

Setback

There's one huge setback to this solution however. Everyone, added as an editor, will be able to see and change the code and there's no way to avoid that, see reference¹.

Reference

Google Sheets – Automatically Fill Missing Values

I added this formula to your spreadsheet

=ArrayFormula(if(row(B2:B) <= max(if(not(isblank(B2:B)), row(B2:B))),vlookup(row(B2:B),filter({row(B2:B),B2:B},len(B2:B)),2),))

It seems to deliver what you asked, except for the interpolation part. Enter this formula in row 2, no need to drag down. I hope this helps ?