Google脚本与脚本有冲突

本文介绍了Google脚本与脚本有冲突的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我是这个脚本新手.所以，我只想问为什么我会收到这种错误?事实是，当我第一次打开电子表格时，一切看起来都很好.带有 lock 或 unlock 的按钮可同时用于用户和所有者.但是然后，当我单击按钮时，当我单击用户侧的锁定或解锁按钮时，会显示此错误.这些按钮位于一页上，而脚本位于一个项目文件中.

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.

以下是 hide 和 show 行的代码:

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;
}

下面是 lock 和 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);
  }
}

更新

我认为问题与 hide 和 show 按钮无关.

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

为避免授权问题，我建议您采用一种变通方法:使用 Web应用程序

创建一个用于锁定的WebApp和一个用于解锁范围的Webapp
使用选项谁有权访问该应用程序:设置为任何人甚至匿名
要在单击按钮时运行WebApp，请编写函数:

function urlFetch(){
  UrlFetchApp.fetch("URL_OF_YOUR_WEPAPP");
}

将此功能分配给您的按钮
重要提示:在WebApp中实现功能时，不能再使用 getActive()和 getActiveSheet()

示例WebApp内容:

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);
  }
}

这篇关于Google脚本与脚本有冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！