Google表格-使用Sheet Protection运行脚本 [英] Google Sheets - Run script with Sheet Protection

问题描述

我的Google表格存在问题.一切在所有者视图上都可以正常运行，但是当我尝试将其共享给编辑器时，某些功能没有按我的计划进行.作为所有者，我使用受保护的床单和范围功能.因此，我使用了保护纸，然后使用了某些单元格以外的功能，以便编辑者可以在该范围内进行编辑.我放了两个按钮来隐藏(对于空行)并显示行.该脚本由于可以在所有者视图上工作，因此可以正常工作，但是当我将其打开到编辑器视图时，出现错误消息"Exception:您正在尝试编辑受保护的单元格或对象.如果您需要编辑，请与电子表格所有者联系以取消保护.".除某些单元格外的范围仅在F列中.我该怎么办?

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

这是示例电子表格: LINK

我希望有人能帮助我.预先谢谢你！

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

推荐答案

我相信您的目标如下.

• 在电子表格中，使用了受保护的表格和范围.
• 您要在非所有者用户运行脚本时运行脚本.
  • 在当前问题中，当用户运行脚本时，受保护范围出现错误.

  我认为解决问题的方法始终是以所有者身份运行脚本.为此，在这种情况下，我想建议使用Web Apps.在这种情况下，我认为这可能与此线程相同.但是从您的脚本来看，我认为要将线程反映到脚本中，因为方法可能会有些困难.因此，我想提出修改后的脚本作为答案.

  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.

  在此答案中，为了以Spreadsheet的所有者身份运行脚本，使用了Web Apps.

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

  首先，请从您的共享电子表格中删除包含在 doGet 中的GAS项目.

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

  2.部署Web应用.

  1. 在脚本编辑器上，通过发布"打开对话框.->部署为网络应用".
  2. 选择我" 作为将应用程序执行为:" .
     • 通过这种方式，脚本以所有者身份运行.
  • 在这种情况下，需要访问令牌才能请求Web Apps.
  1. 点击查看权限".
  2. 选择自己的帐户.
  3. 点击高级"在此应用未验证"中.
  4. 点击转到###项目名称###(不安全)"
  5. 点击允许"按钮.

• 点击确定".

3.测试此替代方法.

请点击分配给 HIDE ROWS 和 SHOW ROWS 的按钮.这样，脚本由所有者运行.这样，即使单击用户按钮，脚本的结果也与所有者运行的结果相同.

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.

• 请在启用V8的情况下使用此脚本.
• 网络应用
• 利用Google的Web Apps优势Apps脚本
• 相关问题
  • 如何使未经授权的用户保护电子表格
  • 更改工作表的所有者，无论复制者如何

  这篇关于Google表格-使用Sheet Protection运行脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！