如何在特定用户的权限下运行某些脚本？

问题描述

现在，我正在尝试相反的做法，允许用户删除SKU而不取消对工作表的保护。

我从以下内容开始，工作正常：

function deleteEachRow(){
  const ss = SpreadsheetApp.getActive();
  var SHEET = ss.getSheetByName("Ordering");
  var RANGE = SHEET.getDataRange();
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('WARNING: 
 
 Ensure the following sheets DO NOT contain data before proceeding: 
 
 Accessory INV 
 Apparel INV 
 Pending TOs 
 
 Enter New SKU:', ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() === ui.Button.OK) {
    const text = response.getResponseText();
    var rangeVals = RANGE.getValues();
  
    //Reverse the 'for' loop.
    for(var i = rangeVals.length-1; i >= 0; i--){
    if(rangeVals[i][0] === text){
      
      SHEET.deleteRow(i+1);
    };
    };
  };
};

因为我对javascript是新手，对Web应用程序也非常陌生，所以我尝试将上面的代码科学地转换成上面的URL中提供的答案。现在，脚本运行时没有错误，但未能按预期删除输入的SKU。这是我正在运行的脚本：

function deleteEachRow1(){
  const ss = SpreadsheetApp.getActive();
  var SHEET = ss.getSheetByName("Ordering");
  var RANGE = SHEET.getDataRange();
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('WARNING: 
 
 Ensure the following sheets DO NOT contain data before proceeding: 
 
 Accessory INV 
 Apparel INV 
 Pending TOs 
 
 Delete Which SKU?:', ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() === ui.Button.OK) {
    const text = response.getResponseText();

    const webAppsUrl = "WEB APP URL"; // Pleas set your Web Apps URL.

    const url = webAppsUrl + "?text=" + text;
    const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
    // ui.alert(res.getContentText()); // You can see the response value using this line.
  }
}

function doGet(e) {
  const text = e.parameter.text;
  const sheet = SpreadsheetApp.getActive().getSheetByName('Ordering');
  var rangeVals = RANGE.getValues();
  
    //Reverse the 'for' loop.
    for(var i = rangeVals.length-1; i >= 0; i--){
    if(rangeVals[i][0] === text){
      
      SHEET.deleteRow(i+1);
    };
    };
  myFunction();
  return ContentService.createTextOutput(text);
}

// This script is from https://tanaikech.github.io/2017/07/31/converting-a1notation-to-gridrange-for-google-sheets-api/
function a1notation2gridrange1(a1notation) {
  var data = a1notation.match(/(^.+)!(.+):(.+$)/);
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(data[1]);
  var range = ss.getRange(data[2] + ":" + data[3]);
  var gridRange = {
    sheetId: ss.getSheetId(),
    startRowIndex: range.getRow() - 1,
    endRowIndex: range.getRow() - 1 + range.getNumRows(),
    startColumnIndex: range.getColumn() - 1,
    endColumnIndex: range.getColumn() - 1 + range.getNumColumns(),
  };
  if (!data[2].match(/[0-9]/)) delete gridRange.startRowIndex;
  if (!data[3].match(/[0-9]/)) delete gridRange.endRowIndex;
  return gridRange;
}

// Please run this function.
function myFunction() {
  const email = "MY EMAIL"; // <--- Please set your email address.

  // Please set your sheet names and unprotected ranges you want to use.
  const obj = [ 
  { sheetName: "Ordering", unprotectedRanges: ["O5:P", "C2:E2"] },  
  { sheetName: "Accessory INV", unprotectedRanges: ["E5:H"] },  
  { sheetName: "Apparel INV", unprotectedRanges: ["E5:F"] },  
  {sheetName: "Pending TOs", unprotectedRanges: ["E6:H"] }, 
  {sheetName: "INV REF", unprotectedRanges: ["C6:C"] },
];

  // 1. Retrieve sheet IDs and protected range IDs.
  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const sheets = Sheets.Spreadsheets.get(spreadsheetId, { ranges: obj.map(({ sheetName }) => sheetName), fields: "sheets(protectedRanges(protectedRangeId),properties(sheetId))" }).sheets;
  const { protectedRangeIds, sheetIds } = sheets.reduce((o, { protectedRanges, properties: { sheetId } }) => {
    if (protectedRanges && protectedRanges.length > 0) o.protectedRangeIds.push(protectedRanges.map(({ protectedRangeId }) => protectedRangeId));
    o.sheetIds.push(sheetId);
    return o;
  }, { protectedRangeIds: [], sheetIds: [] });
  
  // 2. Convert A1Notation to Gridrange.
  const gridranges = obj.map(({ sheetName, unprotectedRanges }, i) => unprotectedRanges.map(f => a1notation2gridrange1(`${sheetName}!${f}`)));

  // 3. Create request body.
  const deleteProptectedRanges = protectedRangeIds.flatMap(e => e.map(id => ({ deleteProtectedRange: { protectedRangeId: id } })));
  const protects = sheetIds.map((sheetId, i) => ({ addProtectedRange: { protectedRange: { editors: {users: [email]}, range: { sheetId }, unprotectedRanges: gridranges[i] } } }));
  
  // 4. Request to Sheets API with the created request body.
  Sheets.Spreadsheets.batchUpdate({ requests: [...deleteProptectedRanges, ...protects] }, spreadsheetId);
}

非常感谢您的真知灼见。

推荐答案

最简单的方法可能是避免使用按钮和using a checkbox with a installable edit trigger，这也有很大的移动支持副作用。

建议的解决方案：

即使其他具有编辑访问权限的用户打开电子表格，可安装版本也会在创建触发器的用户的授权下运行。

备注：

优势：

劣势：安全性(可能的解决方法)