我有一個復雜的任務串列電子表格，用于跟蹤已完成的任務。由于設定的原因，我需要保護一些公式和范圍，以避免作業表的用戶意外洗掉它們。問題是一些腳本需要在作業表的受保護部分上運行。在所有者帳戶下一切正常。當其他用戶使用作業表時，他們會收到受保護范圍的腳本錯誤。在網上搜索后，我設定了以下內容：
function doGet(e) {
this[e.parameter.run](e.parameter.sheetName || null);
return ContentService.createTextOutput();
}
function atEdit() {
const activeSheet = SpreadsheetApp.getActiveSheet();
const url = ScriptApp.getService().getUrl();
UrlFetchApp.fetch(url "?run=script_atEdit&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 readdTask() {
const activeSheet = SpreadsheetApp.getActiveSheet();
const url = ScriptApp.getService().getUrl();
UrlFetchApp.fetch(url "?run=script_readdTask&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.
}
基本上，在腳本作為 Web 應用程式發布后，即使其他用戶從共享表運行它，它也會在所有者帳戶下運行。
但是，我的 onEdit 腳本中出現以下錯誤
（請注意，由于 onEdit 無法訪問 Auth 呼叫，因此將 onEdit 重命名為 atEdit）。
TypeError：無法在 doGet(sysW.1:2:24) 處讀取 script_atEdit(sysW.1:57:34) 處未定義的屬性“activeSheet”
這是我的 atEdit 腳本：
function script_atEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet(); // → gets the spreadsheet we will use to get individual sheets from
var activeSheet = e.source.getActiveSheet(); // → gets the information and the active sheet
var globalsSheet = ss.getSheetByName("Admin"); // → gets the sheet where we store our global values, GAS handles globals a bit differently therfore declaring a dynamic global is inconvenient
if (activeSheet.getName() == 'Tasks') { // → specifies on what sheet within the worksheet the action below will happen
var aCell = e.source.getActiveCell(), col = aCell.getColumn(); // → gets the active cell and column the user clicks on and puts the info into aCell variable
if (col == 2) {
var dateCell = aCell.offset(0,1); // → offset, specifies what row and colum hold / will hold the date (- is up, is down) and number of columns away (- is left, is right)
var aRow = aCell.getRow();
if (aCell.getValue() === true) {
var newDate = new Date(); // → gets the current timestamp; the current date and the current time the user checked the cell
var taskID = activeSheet.getRange(aRow,7).getValue();
var taskDate = activeSheet.getRange(aRow,5).getValue();
globalsSheet.getRange(1, 2).setValue(taskID) // → sets the global value in the Admin sheet so that it becomes accessible to other functions, IE. other functions copy the value from Admin sheet
globalsSheet.getRange(2, 2).setValue(taskDate)
var everyNth = activeSheet.getRange(aRow,8).getValue();
globalsSheet.getRange(3, 2).setValue(everyNth)
dateCell.setValue(newDate); // → puts the above information into the actual cell the user checked
activeSheet.getRange(aRow,1,1,6).setBackground("#87868c"); // → changes the background color of the row in which the user checked the cell
script_readdTask() } // → calls readdTask function which will repeat the task and set its date to whatever repeat frequency it is set in the setTasks sheet
else {
dateCell.setValue(""); // → clears the date when the cell is unchecked
activeSheet.getRange(aRow,1,1,6).setBackground("#ffffff"); }}} // → restores the color of the row to what it was, white, if the cell is unchecked
var sheetName = activeSheet.getRange(1,15).getValue();
var nameCheck1 = activeSheet.getRange(1,1).getValue();
var nameCheck2 = activeSheet.getRange(1,6).getValue();
var currentSheet = "" nameCheck1 nameCheck2
if (sheetName == currentSheet){
var bCell = e.source.getActiveCell()
if (bCell.getValue() === true){
bCell.setBackground("green")
} else if (bCell.getValue() === false){
bCell.setBackground("#f3f3f3");
}
}
}
uj5u.com熱心網友回復：
我認為您的問題是由于雖然script_atEdit(e)被呼叫 from doGet(e)，但您試圖script_atEdit(e)用作 OnEdit 觸發器呼叫的函式。
在您的情況下，從您的回復中，我了解到這script_atEdit(e)是由doGet(e)call from運行的atEdit()。atEdit()由 OnEdit 觸發器運行。
在這種情況下，請script_atEdit(e)進行如下修改。在您的腳本中，eofscript_atEdit(e)是作業表名稱。
從：
var activeSheet = e.source.getActiveSheet();
到：
var activeSheet = SpreadsheetApp.getActive().getSheetByName(e) || SpreadsheetApp.getActiveSheet();
并且，
從：
var aCell = e.source.getActiveCell(), col = aCell.getColumn();
到：
var aCell = activeSheet.getActiveCell(), col = aCell.getColumn();
并且，
從：
var bCell = e.source.getActiveCell()
到：
var bCell = activeSheet.getActiveCell();
筆記：
- When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
- You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/443751.html