I want to design an app script for Google Sheets which protects the data upto last row when the sheet is closed.

E.g. If I am working on the sheet and data is till Row No. 100 then, when I close the sheet data upto Row 100 should be protected and locked for all users except the sheet owner.

Any help on above will be greatly appreciated.

CodePudding user response：

I believe your goal is as follows.

• You want to protect from the 1st row to the last row in the existing data range, when the sheet is closed.
• You want to achieve this using Google Apps Script.

If when a user closes the sheet means that it is to close the browser tab, unfortunately, in the current stage, there is no trigger for this situation in Google Apps Script.

From your following reply,

in that case is it possible to use the trigger of opening the sheet?

In this case, OnOpen trigger can be used. But, in your situation, I think that it is required to use the installable OnOpen trigger. Because the range is required to be protected.

And, from your following reply,

it covers the trigger to be used not the full script am looking for a script to do this

I understood that you want the sample script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please install OnOpen trigger to the function installedOnOpen. When you use this script, please set sheetNames and reopen the Spreadsheet. By this, the script is run.

function installedOnOpen(e) {
  const sheetNames = ["Sheet1"]; // Please set the sheet names you want to protect.
  const sheets = e.source.getSheets().filter(s => sheetNames.includes(s.getSheetName()));
  if (sheets.length == 0) return;
  sheets.forEach(s => {
    const p = s.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    if (p.length > 0) {
      p.forEach(pp => pp.remove());
    }
    const lastRow = s.getLastRow();
    if (lastRow != 0) {
      const newProtect = s.getRange(1, 1, lastRow, s.getMaxColumns()).protect();
      newProtect.removeEditors(newProtect.getEditors());
      if (newProtect.canDomainEdit()) newProtect.setDomainEdit(false);
    }
  });
}

• When this script is run, for the sheets of sheetNames, the 1st row to the last row in the existing data range is protected.

• If you want to run this script for all sheets in the Spreadsheet, please modify it as follows.

  • From

      const sheetNames = ["Sheet1"]; // Please set the sheet names you want to protect.
      const sheets = e.source.getSheets().filter(s => sheetNames.includes(s.getSheetName()));
    

  • To

      const sheets = e.source.getSheets();
    

• In this sample script, all columns are protected. If you want to protect only data range, please modify const newProtect = s.getRange(1, 1, lastRow, s.getMaxColumns()).protect(); to const newProtect = s.getDataRange().protect();.

Reference:

• Installable Triggers