Google Sheets Lock Entire Column Except One Column Based on Values-CodePudding

I have this spreadsheet below, and I'm looking to lock the entire column in sheets except for the column that matches today's date (11/10/2022). And then the next day it will lock the column E and 11/11/2022 column(F) will be unlocked and so on. And I'd like to specify users who can edit those locked columns.

Here's my sheet.

CodePudding user response：

Unprotect Todays Column:

function unprotectTodaysColumn() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Sheet0');
  let protection = sh1.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  if (protection) { protection.remove(); }
  const ds = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yy");
  const col = sh1.getRange(1, 1, 1, sh1.getLastColumn()).getDisplayValues().flat().indexOf(ds)   1;
  const rg = sh1.getRange(1, col, sh1.getLastRow());
  let p = sh1.protect();
  p.setUnprotectedRanges([rg])
}

Protection

CodePudding user response：

Try:

function unprotectToday() {
  // Protect whole sheet, then remove all other users from the list of editors.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); //Change sheet name
  var protection = sheet.protect();

  //Gets the date today and finds its column in the sheet to unprotect
  var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yy");
  var col = sheet.createTextFinder(today).findNext().getColumn();
  var unprotect = sheet.getRange(1,col, sheet.getLastRow());
  protection.setUnprotectedRanges([unprotect]);
  
  // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
  // permission comes from a group, the script throws an exception upon removing the group.
  var me = Session.getEffectiveUser();
  protection.removeEditors(protection.getEditors());
  protection.addEditors([me,"[email protected]","[email protected]"]); //Add users who can edit
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

Explanation:

What this does is it protects the whole sheet. It then gets the current date and finds its column in the sheet to unprotect. On the last part of the script you can also add users to allow to edit the protected range.

More details here: Class Protection