Locking cells in Google Sheets at a specific time

Question

I want to code my Google Sheets cells in a way that certain cells automatically lock at a specific time every day. I should be able to edit it, but not my collaborators.

How do I pull this off?

In the sample sheet (link here), you will see certain dates (15/7/2015-17/7/2015). I want to code it so that each date (eg A1:B6) is locked daily, 10 pm.

Answer 1

As @NightShadeQueen suggested a combination of Time-driven triggers and the protect()-method of the Class Range can be used.

1. Open the script editor in the sheet (Tools > Script editor...)
2. Create code that locks the ranges, might look something like this:

---

You'll have to modify the code if your sheet doesn't look like the Sample sheet. Especially the lockRanges()-function, which defines which ranges should be protected. Currently, it starts with cell A1, and steps down 7 rows at a time until the end of the sheet is reached.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet1'); //INSERT SHEET NAME HERE

function lockRanges() {
  //First cell to lock
  var row = 1;
  var col = 1;

  // Get last row with data in sheet
  var lastRow = sheet.getLastRow();

  //Loop until last row in sheet is passed
  while(row <= lastRow){
    //Lock current cell
    lockRange(row, col);

    // Go to row 7 steps down from current (the next date)
    row = row + 7;
  }
}



function lockRange(row, col){
  var range = sheet.getRange(row, col);

  // Create protection object. Set description, anything you like.
  var protection = range.protect().setDescription('Protected, row ' + row);

 // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
 // permission comes from a group, the script will throw an exception upon removing the group.
 var me = Session.getEffectiveUser();
 protection.addEditor(me);
 protection.removeEditors(protection.getEditors());
 if (protection.canDomainEdit()) {
   protection.setDomainEdit(false);
 }
}

3. Add the Time-driven trigger
   1. Still in the Script editor; go to Resources > Current project's triggers.
   2. Create a new trigger
   3. Choose Run = lockRanges
   4. Choose Events = Time-driven, Day timer, 10pm to 11pm (or whenever you want)

NOTE: The time may be slightly randomized (read more)

---

That's it, now the ranges will be protected at the time you chose. As with ranges you protect manually, they show up in Data > Protected sheets and ranges.

Something unclear? Ask away!