感謝這里的另一個用戶，我能夠允許其他用戶在不取消保護的情況下向作業表添加新的 SKU。在這里看到的原始帖子：將編輯者限制為特定范圍腳本
現在，我試圖做相反的事情，允許用戶在不取消保護作業表的情況下洗掉 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: \r\n \r\n Ensure the following sheets DO NOT contain data before proceeding: \r\n \r\n Accessory INV \r\n Apparel INV \r\n Pending TOs \r\n \r\n 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: \r\n \r\n Ensure the following sheets DO NOT contain data before proceeding: \r\n \r\n Accessory INV \r\n Apparel INV \r\n Pending TOs \r\n \r\n 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);
}
任何見解都非常感謝。
uj5u.com熱心網友回復：
可能最簡單的方法是避免使用按鈕并使用帶有可安裝編輯觸發器的復選框，這也有很大的移動支持副作用。
建議的解決方案：
- 使用復選框
- 將它連接到一個可安裝的編輯觸發器，它以安裝觸發器的用戶身份運行。因此，如果所有者安裝觸發器，無論誰編輯作業表，觸發器都會以所有者身份運行，從而可以訪問包括受保護范圍在內的特權資源。
可安裝版本在創建觸發器的用戶的授權下運行，即使另一個具有編輯權限的用戶打開電子表格也是如此。
筆記：
優勢：
代碼簡單性和可維護性。不需要 webapp 或任何復雜的設定。缺點：安全
如果代碼系結到作業表，作業表的編輯者可以直接訪問作業表的腳本。因此，任何有惡意的編輯器都可以修改代碼。如果具有可安裝觸發器的功能具有gmail權限，則任何編輯器都可以記錄所有者的所有電子郵件。因此，需要特別注意請求的權限。請注意，您的網路應用程式設定已經是這種情況。任何編輯器都可以修改doGet以訪問受保護的資料。如果 webapp 在一個單獨的獨立腳本中，這不是問題。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/384483.html
標籤：javascript 谷歌应用程序脚本 谷歌表格 保护