Google Sheets - Run script with Sheet Protection

Question

I'm having an issue with my google sheet. Everything works fine on the owner view, but when I tried to share it to an editor, some function didn't go as I planned. As an owner, I use the protected sheets and ranges function. So, I used the protect sheet, then the except certain cells function so that editors can edit within that range. I put a two buttons to hide (for the rows that is empty) and show rows. The script is working fine since it worked on the owner view, but when I opened it to the editor view, there's an error that says "Exception: You are trying to edit a protected cell or object. Please contact the spreadsheet owner to remove protection if you need to edit.". The range within the except certain cells is in the F column only. What should I do with this?

I'm using this code to hide the emplty rows.

var startRow = 9;
var colToCheck = 2; // Column B

function shouldHideRow(ss, rowIndex, rowValue) {
  if (rowValue != '') return false;
  if (ss.getRange(startRow + rowIndex, colToCheck, 1, 1).isPartOfMerge()) return false;
  if (ss.getRange(startRow + rowIndex + 1, colToCheck, 1, 1).isPartOfMerge()) return false;
  return true;
}

function HideRows() {
  
  var ss = SpreadsheetApp.getActiveSheet();
  var numRows = ss.getLastRow();
  var elements = ss.getRange(startRow, colToCheck, numRows).getValues();
  for (var i=0; i<(numRows - startRow); i++) {
    if (shouldHideRow(ss, i, elements[i][0])) {
      ss.hideRows(startRow + i);
    }
  }
  // Hide the rest of the rows
  var totalNumRows = ss.getMaxRows();
  if (totalNumRows > numRows)
    ss.hideRows(numRows+1, totalNumRows - numRows);
}

Here's the sample spreadsheet: LINK

I hope that someone can help me. Thank you in advance!

Answer 1

I believe your goal as follows.

• In your Spreadsheet, the protected sheet and ranges are used.
• You want to run your script when the users who are not the owner run the script.
  • In your current issue, when the user runs the script, an error occurs by the protected range.

Modification points:

I thought that the method for resolving your issue is always to run the script as the owner. For this, in this case, I would like to propose to use Web Apps. In this case, I thought that this might be the same situation of this thread. But from your script, I thought that to reflect the thread to your script as the methodology might be a bit difficult. So I would like to propose the modified script as an answer.

In this answer, in order to run the script as the owner of Spreadsheet, the Web Apps is used.

Usage:

At first, please delete the GAS project included doGet from your shared Spreadsheet.

1. Prepare script.

Please copy and paste the following script to the script editor and save it.

function doGet(e) {
  this[e.parameter.run](e.parameter.sheetName || null);
  return ContentService.createTextOutput();
}

function HideRows() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const url = ScriptApp.getService().getUrl();
  UrlFetchApp.fetch(url + "?run=script_HideRows&sheetName=" + activeSheet.getSheetName(), {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}});

// DriveApp.getFiles()  // This is used for automatically detecting the scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for the access token.
}

function showRows() {
  const url = ScriptApp.getService().getUrl();
  UrlFetchApp.fetch(url + "?run=script_showRows", {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}});
}

var startRow = 6;
var colToCheck = 2; // Column L

// This script is the same with your "HideRows".
function script_HideRows(sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var numRows = ss.getLastRow();
  var elements = ss.getRange(startRow, colToCheck, numRows).getValues();
 
  for (var i=0; i<(numRows - startRow); i++) {
    if (shouldHideRow(ss, i, elements[i][0])) {
      ss.hideRows(startRow + i);
    }
  }
  // Hide the rest of the rows
  var totalNumRows = ss.getMaxRows();
  if (totalNumRows > numRows)
    ss.hideRows(numRows+1, totalNumRows - numRows);
};

// This script is the same with your "showRows".
function script_showRows() {
  // set up spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
//  var ss = SpreadsheetApp.getActiveSpreadsheet(),
  var sheets = ss.getSheets();

  for(var i = 0, iLen = sheets.length; i < iLen; i++) {
    // get sheet
    var sh = sheets[i];

    // unhide columns
    var rCols = sh.getRange("1:1");
    sh.unhideColumn(rCols);

    // unhide rows
    var rRows = sh.getRange("A:A");
    sh.unhideRow(rRows);
  }
};

function shouldHideRow(ss, rowIndex, rowValue) {
  if (rowValue != '') return false;
  if (ss.getRange(startRow + rowIndex, colToCheck, 1, 1).isPartOfMerge()) return false;
  if (ss.getRange(startRow + rowIndex + 1, colToCheck, 1, 1).isPartOfMerge()) return false;
  return true;
}

2. Deploy Web Apps.

1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
2. Select "Me" for "Execute the app as:".
   • By this, the script is run as the owner.
3. Select "Anyone" for "Who has access to the app:".
   • In this case, the access token is required to request to Web Apps.
4. Click "Deploy" button as new "Project version".
5. Automatically open a dialog box of "Authorization required".
   1. Click "Review Permissions".
   2. Select own account.
   3. Click "Advanced" at "This app isn't verified".
   4. Click "Go to ### project name ###(unsafe)"
   5. Click "Allow" button.
6. Click "OK".

3. Test this workaround.

Please click the buttons assigned with HIDE ROWS and SHOW ROWS. By this, the script is run by the owner. By this, even when the user is clicked the button, the result of script is the same with that run by the owner.

Note:

• Please use this script with enabling V8.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script
• Related questions
  • How to enable not authorized users to protect the spreadsheet
  • Changing Owner of the Sheet irrespective of the duplicator