Warm tip: This article is reproduced from serverfault.com, please click

Google Script having a conflict with scripts

发布于 2020-12-01 01:33:33

I'm new to this scripting thing. So, I just want to ask if why am I receiving this kind of error? So the thing is that when I first open the spreadsheet, everything looks fine. The buttons with the lock or unlock work both user and owner. But then after I clicked the hide or show button, this error shows when I clicked the lock or unlock button on the user side. The buttons are located on one sheet while the scripts are in one project file.

Error

Here's the code for the hide and show rows:

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 = 11;
var colToCheck = 2;

// This script is the same with your "HideRows".
function script_HideRows() {
  var sheetNames = ["MTB_Q1", "MTB_Q2", "MTB_Q3", "MTB_Q4", "SUMMARY OF QUARTERLY GRADES"];  // Please set the sheet names here. In this case, 4 sheets are used.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach(sheet => {
    var sheetName = sheet.getSheetName();
    if (sheetNames.includes(sheetName)) {
      if (sheetName == "SUMMARY OF QUARTERLY GRADES") {  // When the sheet is "SUMMARY", the start row is changed.
        startRow = 12;
      }
      var numRows = sheet.getLastRow();
      var elements = sheet.getRange(startRow, colToCheck, numRows).getValues();
     
      for (var i=0; i < elements.length; i++) {
        if (shouldHideRow(sheet, i, elements[i][0])) {
          sheet.hideRows(startRow + i);
        }
      }
      // Hide the rest of the rows
      var totalNumRows = sheet.getMaxRows();
      if (totalNumRows > numRows)
        sheet.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 == "" || rowValue == '#REF!') return true; // <--- Added
  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;
}

And below is the code for the lock and unlock:

// These are used for the buttons of "LOCK" and "UNLOCK" at the row 10 in your image.
const lock_row10 = () => Lock("MTB_Q1");
const unlock_row10 = () => Unlock("MTB_Q1");

// These are used for the buttons of "LOCK" and "UNLOCK" at the row 11 in your image.
const lock_row11 = () => Lock("MTB_Q2");
const unlock_row11 = () => Unlock("MTB_Q2");

// These are used for the buttons of "LOCK" and "UNLOCK" at the row 12 in your image.
const lock_row12 = () => Lock("MTB_Q3");
const unlock_row12 = () => Unlock("MTB_Q3");

// These are used for the buttons of "LOCK" and "UNLOCK" at the row 13 in your image.
const lock_row13 = () => Lock("MTB_Q4");
const unlock_row13 = () => Unlock("MTB_Q4");

// IMPORTANT: If you have more buttons, please add the functions like above.


function Unlock(sheetName1) {
  
   var me = Session.getEffectiveUser();
   var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName1);
   var protection = sheet1.protect().setDescription('Sample protected range');
   var unprotected = protection.getUnprotectedRanges();
   unprotected.push(sheet1.getRange('F9:O52'));
   unprotected.push(sheet1.getRange('S9:AB52'));
   protection.setUnprotectedRanges(unprotected);
   protection.addEditor(me);
   protection.removeEditors(protection.getEditors());
   if (protection.canDomainEdit()) {
     protection.setDomainEdit(false);
   }
}

function Lock(sheetName1) {
  
  var confirm = Browser.msgBox('Confirmation','Are you sure you want to lock this sheet?',Browser.Buttons.YES_NO);
  if(confirm=='yes'){
    
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName1);
  var protection = sheet1.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  if (protection && protection.canEdit() && sheet1.protect().setDescription('Sample protected range')) {
    protection.remove();
  }
    LockSheet(sheet1);
    Browser.msgBox("The sheet is locked!")
  }
}

function LockSheet(sheet1) {
  var protection = sheet1.protect().setDescription('Sample protected sheet');
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

UPDATE

I think the problem is not related to the hide and show button.

Questioner
cjvdg
Viewed
11
ziganotschka 2020-12-02 17:54:23

To avoid authorization issue I suggest you a workaround: Use a WebApp

  • Create one WebApp for locking and one Webapp for unlocking the ranges
  • Execute both WebApps and the spreadsheet owner with the option Who has access to the app: set to Anyone even anonymous
  • To run the WebApp on button click, write the function:
function urlFetch(){
  UrlFetchApp.fetch("URL_OF_YOUR_WEPAPP");
}
  • Assign this function to your button
  • Important: When you implement your functions into the WebApp, you cannot use getActive() and getActiveSheet() anymore

Sample WebApp content:

function doGet(){  
  var response = Unlock();
}
function Unlock() {
  var ss = SpreadsheetApp.openById("YOUR SPREADSHEET ID");
  var sheet = ss.getSheetByName("Sheet1");
  var protection = sheet.protect().setDescription('Sample protected range');
  var unprotected = protection.getUnprotectedRanges();
  unprotected.push(sheet.getRange('F9:O52'));
  protection.setUnprotectedRanges(unprotected);
  protection.addEditors(ss.getEditors())
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}