Google Sheets – Run script with Sheet Protection

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:

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top