LOCK column range at specific Date (month wise)

user_19442543 0
Viewed 83

LINK TO THE SAMPLE FILE HERE I have a spreadsheet which contains multiple tabs in it on which data is filled by multiple users at a time which go direct to my master-sheet through IMPORT-range Function but this create a problem for me as a user can change Data or value at any time which further will reflect in master same which mean i have no control over the data as user can edit the values or the data according to the need. i want to lock the value for the columns at every 15th of the EACH SAMPLE DATA IMAGE HEREmonth AS you can see in the Image which shown each Tab have month wise column which is to be filled by the user Any help would be appreciated any suggestions to it ?

google-apps-script google-sheets
1 Answers

Issue:

  • To do this, you'll have to use Google Apps Script.
  • A range cannot be protected for all users. At least the user executing user will still be able to edit this (this is not a feature of the solution proposed below; it's how Sheets work).

Solution:

  • Create a time-driven trigger that will fire the 15th of each month. This can be done manually or programmatically (by executing the function installTrigger below once, using onMonthDay). The triggered function (protectCurrentMonthColumn in the sample below) should do the following.
  • Get the month index and year of the current date (see Date).
  • Get a list of sheets to protect (retrieve all sheets via Spreadsheet.getSheets() and filter out the ones to ignore) and iterate through them.
  • For each sheet, get the column index of the header that contains current month date. You can compare monthIndex and year for that, and use findIndex to get the index.
  • Using the columnIndex, get the corresponding Range and protect it.

Code sample:

function protectCurrentMonthColumn() {
  const SHEETS_TO_IGNORE = ["NDRX", "Verified NDRx"]; // Change according to your preferences
  const now = new Date();
  const monthIndex = now.getMonth(); // Current month
  const year = now.getFullYear(); // Current year
  const ss = SpreadsheetApp.getActive();
  const sheetsToProtect = ss.getSheets().filter(s => !SHEETS_TO_IGNORE.includes(s.getSheetName())); // Filter out ignored sheets
  sheetsToProtect.forEach(s => { // Iterate through sheets to protect
    const headers = s.getRange("1:1").getValues()[0];
    const columnIndex = headers.findIndex(header => { // Get index of the column to protect (header is current month and year)
      return typeof header.getMonth === 'function' && header.getMonth() === monthIndex && header.getFullYear() === year;
    });
    if (columnIndex > -1) { // If header is not found, don't protect anything
      const rangeToProtect = s.getRange(1,columnIndex+1,s.getLastRow()); // Column to protect
      const protection = rangeToProtect.protect();
      var me = Session.getEffectiveUser();
      protection.addEditor(me);
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
  });
}

function installTrigger() {
  ScriptApp.newTrigger("protectCurrentMonthColumn")
  .timeBased()
  .onMonthDay(15)
  .create();
}

Note:

  • You have to execute installTrigger once for this to work.
user_106120110
Related