How to disable editing cells after a particular time in Google Spreadsheet?

Question

Use Case - I have shared a Google Spreadsheet amongst a dozen friends and we are entering our prediction for matches. The catch is to enter it before the game starts. Using Spreadsheet as everyone can see everyone's prediction.

Problem - Is there an AddOn or any feature which allows to disable editing a few cells after a particular time? Say post midnight A[7]-M[7] cells cannot be edited.

Answer 1

You can set a script to run at a specific time:

In the script editor, create a function:

function protectRangeAtMidnight() {
  //My code will go here
}

In the script editor, click on the RESOURCES menu, and choose, CURRENT PROJECTS TRIGGERS.

Add a trigger for a specific date and time.

The problem is, what is the code going to do? If you protect the range, but the people you are sharing the spreadsheet with have edit authority, they can just unprotected the range. If you change their permissions to VIEW only, then you'd have to change it back at some point for the next game. That would work as long as there can be a time period where no one else can edit the sheet.

You can remove a user from the editor list:

Remove Editor

function protectRangeAtMidnight() {
  SpreadsheetApp.openById('The SS ID').removeEditor(emailAddress);

}

You can also set file sharing permissions through DriveApp:

setSharing(accessType, permissionType)

Answer 2

I am actually doing exactly the same thing and came across the same problem. The solution I came up with does not lock the cells but uses data validation. Some of the solutions suggested online did not seem to take into account that you need to lock a row of results which have a date associated with it.

This is the layout I am using for my predictions: Google sheet cropped image example

The cells in blue then have the following data validation (criteria is custom formula, reject input):

=if(isnumber(C1),and(now()<$A1,C1>=0,C1-int(C1)=0))

It checks that what is entered in C1 is a number. If it is, it then checks the following:

1. If the current date and time is before 'kick off'.
2. If the number is greater than or equal to zero.
3. That it is a whole number.

If so, it allows the cell to be changed. If the match has kicked-off, the cell cannot be altered and a red triangle will appear in the cell (because the data validation will be violated as now() will be after the date in question) but it stops the cells from being changed once the game has kicked off.

If you couple the above with locking the entire sheet (apart from the blue cells) it should allow users to make predictions prior to kick-off.

If it is necessary for cells to be altered after the game has begun you can modify the date in column A to then make the update before changing the date back.

Hope this helps!

Answer 3

What works is to:

Lock the cells or columns at a specific time, then remove the protection at another time: using a daily trigger (or even manually)

Modifications needed before running the functions:

• Sheets names (In my example it's Sheet1, Sheet2)

• Range to protect (in my example it's A:D) (editors won't be able to edit the specified range)

function Lock() {

  var tabs = ['Sheet1', 'Sheet2'];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  for (var i = 0; i < tabs.length; i++) {
    var spreadsheet = ss.getSheetByName(tabs[i]);

    var protection = spreadsheet.getRange('A:D').protect();

    protection.setDescription('Protected')
    protection.removeEditors(protection.getEditors());

  }
};

And to remove the protection you'll use the following script: Editors get "back" their permission to edit the specified range:

function Unlock() {

  var ss = SpreadsheetApp.getActive();
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    if (protections[i].getDescription() == 'Protected') {
      protections[i].remove();
    }
  }
};