3

I have set up a simple spreadsheet with cells containing a drop down list of Yes and No.

I want to lock these cells based on date, so that all the people who I am sharing the sheet with will not be able to edit them after the passing of a certain date.

Can this be done?

(If I can set up the same condition, but based on a certain time on that date will be great. But that's not totally necessary.)

1

I've created a script that does what you ask for. But since you can't invoke "Undo" with script it starts with copying your data to a separate sheet (called "scriptSheet"), which you have to create. You can hide it after that. This copying happens every time you open your spreadsheet.

When you edit a cell in a specified range the script checks if a date and time specified by you has passed, if it has it informs the user and copies the value from the scriptSheet. If the date hasn't passed the value is instead copied into the scriptSheet.

So here's the script:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Locked Sheet");        //Edit to be the name of your sheet
  var scriptSheet = ss.getSheetByName("scriptSheet");
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  scriptSheet.clear();
  sheet.getRange(1, 1, lastRow, lastCol).copyTo(scriptSheet.getRange(1, 1));
}

function onEdit(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getActiveRange();
  var currRow = range.getRow();
  var currCol = range.getColumn();

  if(sheet.getName() == "Locked Sheet" && currCol == 1){ //Edit to be the name of your sheet and column with dropdowns
    var lockDateRange = sheet.getRange(2, 6);            //Edit to be the address of the cell with your date or set to string, '3/16/2013 17:00:00' for example
    var scriptSheet = ss.getSheetByName("scriptSheet");
    var now = new Date();
    var lockDate = new Date(lockDateRange.getValue());
    if (now > lockDate){
      scriptSheet.getRange(currRow, currCol).copyTo(sheet.getRange(currRow, currCol));
      Browser.msgBox("You do not have permission to edit this cell");
    }else{
      sheet.getRange(currRow, currCol).copyTo(scriptSheet.getRange(currRow, currCol));
    }
  }
}

Right now the date is retrieved from a cell (F2) as stated in the comment on row 19 you can write the date in the script instead. The cell reference just makes it easier to change the date.

  • I'm trying to do something similar. I have a sheet where user can select from a drop down menu In column A2:A, where they think a part should be sent. If the part is getting sent to a specific local(VMI Local or VMI Proxy), I want to lock the next two columns, B2:C, where the users would enter the stocking quantities, so they can not enter a stocking level. Would it be possible to update this script to meet by these needs? – George Boutselis Jan 8 at 17:54
0

Nowadays there is the Google Sheets have a "protection" feature that could be applied to sheets and ranges.

To apply it automatically you could use Google Apps Script and a time-driven installable trigger.

Reference

Not the answer you're looking for? Browse other questions tagged or ask your own question.