Can anyone help me with the script for locking non empty cells in a Range at specified time?

Question

I use this in a very small business. We sale the aggregates E Column and the items(DUST,6MM,10MM,20MM,40MM) and quantity F Column are listed (1 ,2 3, 4, 5,6) in drop down. So the Range E7:F300 are Unprotected, rest all the cells are protected for the users.

The users select the Item and Quantity in a day and those cells should be auto protected to that user and all other users at specific time. Say night around 10:00pm. It should protect only Non Empty cell but not the entire Range. I am new to this Script, Can anyone help me out with the script as per my requirement?

Answer 1

You want to protect cells containing content in the range E7:G150 on "sheet="CASH SALE" and range I6:L404 on sheet="HOPPER" at 10:00pm each night.

Locking cells in Google Sheets at a specific time (by @Punchlinernon on StackOverflow) provides a good basis for your solution. However your scenario is different because the range to be protected is extended each day. One result is that there would be a protected ranges created every night - over time many dozens of protected ranges would be unnecessarily/unproductively created. So the first step is to remove the protected range then protect the entire range of content. The result is a single protected range.

Functions

• lockRanges(): this is the primary function that should be assigned to the trigger. This contains the data for each of the relevant sheets and passes processing to the sub-routine locksub().

• locksub(sheetname, row, col, numCol): this is the main processing function. Data is passed from lockranges(). It relies on a single utility columnToLetter() to produce the range to be protected.

• columnToLetter(column): converts a column number to a letter. This is used to define a range.

Key Points

• var sheetname = "Transactions"; - used for the sheet name, but also used for the description of the protected range.
• var sheet = ss.getSheetByName(sheetname); - added to ensure that the changes are made to a specific sheet
• if (protections[i].getDescription() == sheetname) { - a test to check if there is already a protected range with the target description, if yes, then the range is unprotected.
• var lastRow = sheet.getLastRow(); - this returns the last row containing content and is used to define the protected range.
• if( row <= lastRow){ - a test to make sure that there is content; if no content then no action taken
• var range = sheet.getRange(row, col,numrows,numCol); - the range to be protected; variables are used for every attribute. The Range starts at row 7 of Column E and extending to the last row of content in Column F.
• var protection = range.protect().setDescription(sheetname); - protects the range, and assigns the description.

Props - @AdamL for columnToLetter

---

function lockRanges() {

  // this sheet carries the data for the ranges to be adusted
  // and passes this for execution to sub routine = logsub
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // data for CASH SALE
  var sheetname = "CASH SALE";
  //cell variables
  var row = 7; // row 7
  var col = 5; // column E
  var numCol = 3; // Column E, F and G
  locksub(sheetname, row, col, numCol);
  Logger.log("finished lockranges")


  // data for HOPPER
  var sheetname = "HOPPER";
  //cell variables
  var row = 6; //row 6
  var col = 9; // column I
  var numCol = 4; // Column I, K, K and L
  locksub(sheetname, row, col, numCol);
  Logger.log("finished lockranges")

  return;

}

function locksub(sheetname, row, col, numCol){

  // subroutine called from lockRanges
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);

  /*
  a new protected range is run for eavery time the script is run
  so, test for an existing description and delete it
  then run the rest of the script
  the result should be a single protected range
  */
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    Logger.log("DEBUG: protected range = "+protection.getRange().getA1Notation()+", name = "+protection.getDescription());
    if (protection.canEdit()) {
      if (protections[i].getDescription() == sheetname) {
        Logger.log("DEBUG: this protection is named "+sheetname)
        protections[i].remove();
      }
    }
  }

  // get the data range
  var celladdress = sheet.getRange(row, col).getA1Notation();
  var colLetter = columnToLetter(col)
  var datarange = celladdress+":"+colLetter;
  // Logger.log("DEBUG: the datarange is "+datarange)
  var Evals = ss.getRange(datarange).getValues();
  var Elast = Evals.filter(String).length;
  // Logger.log("DEBUG: Number of rows of content = "+Elast)
  var numrows = Elast;
  if( Elast>=1){
    // define the range
    var range = sheet.getRange(row, col,numrows,numCol);
    // Logger.log("DEBUG: the range is "+range.getA1Notation())


    // Lock current range
    // Create protection object. Set description, anything you like.
    var protection = range.protect().setDescription(sheetname);

    // 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);
    }
  }

  Logger.log("finished locksub")
return;
}

function columnToLetter(column){
  var temp, letter = '';
  while (column > 0){
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}