Prevent Cells From Being Edited Based on Date - Google Sheets

Question

I am looking for a way to accomplish the following:

1) We have a Google Sheet where in Row A (Columns B-...) have dates, such as 10/30/18 in B1, 10/31/18 in B2, etc. We can certainly add a time to that date as well, such as 10/31/18 14:30:00 or something like that if it makes this process easier.

2) I want the cells in that corresponding column to only be editable on the date of the cell in Row 1 of that column (for example, cells in B2:B30 would only be able to be edited on 10/30/18). If the times make it easier, I would IDEALLY like the cells to be editable from the time/date in B1 + 70 minutes. So if the date/time was 10/31/18 14:30:00, cells in that column (excluding first row) would only be able to be edited from 2:30PM (14:30:00) to 3:40pm (+70 minutes).

3) In any case, the "owner" of the sheet would need to be able do still edit cells outside of the date/time range indicated.

Please advise.

Barry Barry · Accepted Answer · 2018-11-01T16:23:28

I've tried to provide an answer based on some previously described approaches. Essentially, you create a protected range based on the date - we need to be aware that protection automatically applies to the full range, and we need to remove protection afterwards from dates in the future.

The below will be simpler, comparing only the date (and not time), locking cells with past dates. Under I mention an approach for date/time.

a = SpreadsheetApp.getActiveSpreadsheet();
  var s = a.getSheetByName('ExportPlan');
  var values = s.getRange("M1:M").getValues();
  var row, col, len, index; 

function mylockranges() {
  var myDate = TODAY(); //myDate is today

  //First range to lock
  var row = 1;
  var col = 1;


  for (row = values.length-1; row >=3 ; row--) {
    // Only unprotect rows with dates from today onwards
  if (typeof values[row][0] == 'object' && values[row][0] < myDate) {
    lockRange(row, col);

    }
  }
}

function lockRange(row, col){
  row = row+1 ; 
  var range = s.getRange(row, col, 1, 25);

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

For a date/time implementation, you can use myTime=NOW() for your date/time input. You can do addition on NOW() where adding 1 adds 1 day. Given that 70 minutes is roughly 0.0486 days, you can do something along the lines of if(NOW()< myTime +.0486) for your lock range. You can simplify this by using var allowedTime=NOW() + .0486 at the start.

Prevent Cells From Being Edited Based on Date - Google Sheets

1 Answers