Google Sheets - Locking Cells after value reached and one other thing

0
votes

Making a self-scheduling spreadsheet for my co-workers. The goal is for them to put their hours (12 or 6) in the cell that corresponds with the date and their name. I am trying to have it so that a date column will lockout after the sum cell for that column reaches a certain total.

I am using this (Google Script Lock Cells) as my basis but I am not having much luck.

    function myFunction() {
  function onOpen() {

  var ss = SpreadsheetApp.getActive();
  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var cell = source.getRange("B12").getValue();
  var range = ss.getRange("B2:B11");

  if (cell == 10) {

    // Protect range B2:B11 if cell 'B12' = 10
    var protection = range.protect().setDescription('Sample protected range');
    Logger.log

  } else {

    // Remove protection if cell 'B12' is anything other than 10
    var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);

    for (var i = 0; i < protections.length; i++) {
      var protection = protections[i];
      protection.remove();
    }
  }  
} 
}

I am also looking to have the sum represent shits rather than hours so if a person wrote "12" the sum column would record "1"

Any help would be amazing.

1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

You should change several things

  1. Do not nest onOpen inside myFunction - it will not work
  2. If you want the protections to be update on every update of B12 - you should use the onEdit trigger instead of onOpen
  3. It is better to replace cell == 10 through cell >= 10 to account for the possibility that the value accidentally trespasses the maximum 4.It is not enough to create a protection, to make it useful you need to remove editors from it
  4. Only the owner of the script (you) should maintain access
  5. For this, use an installable trigger instead of a simple one, to make sure that it runs always as you and not the user that opens the document

Sample (to be bound to an installable onEdit trigger):

function bindAnOnEditTiggerToMe() {

  var ss = SpreadsheetApp.getActive();
  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var cell = source.getRange("B12").getValue();
  var range = ss.getRange("B2:B11");

  if (cell >= 10) {    
    // Protect range B2:B11 if cell 'B12' = 10
    var protection = range.protect().setDescription('Sample protected range');
    var me = Session.getEffectiveUser();
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }    
  } else {    
    // Remove protection if cell 'B12' is anything other than 10
    var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);    
    for (var i = 0; i < protections.length; i++) {
      var protection = protections[i];
      protection.remove();
    }
  }  
}