month 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 ?

CodePudding user response：

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.