Reputation: 533
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!
Upvotes: 1
Views: 3603
Reputation: 201378
I believe your goal as follows.
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.
At first, please delete the GAS project included doGet
from your shared Spreadsheet.
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;
}
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.
Upvotes: 2