当我点击打开Google电子表格时弹出的消息框时，我试图运行"onReportOrApprovalSubmit()"函数。当我在脚本编辑器中运行该函数时，该函数运行良好。但是，当我尝试使用首次打开电子表格时弹出的消息框运行该函数时，该函数将停止运行/在该行之前被卡住:
"var approvalsSpreadsheet = SpreadsheetApp.openById(APPROVALS_SPREADSHEET_ID);"
有人知道为什么会这样吗？
var APPROVALS_SPREADSHEET_ID = "1NC6mBPRXKCA4Blbn7C9lFt9YnTZioS3_vidbVuPvZos";
var APPROVAL_FORM_URL = "https://docs.google.com/a/londonhydro.com/forms/d/1BKuKdNwsUDXyLdqy18GAcQmE_SzS7Sq_OTxVNwyCH44/viewform";
var STATE_MANAGER_EMAIL = "MANAGER_EMAIL";
var STATE_APPROVED = "APPROVED";
var STATE_DENIED = "DENIED";
var COLUMN_STATE = 6;
var COLUMN_COMMENT = 7;
function onReportOrApprovalSubmit() {
// This is the Expense Report Spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
// Also open the Approvals Spreadsheet
var ui = SpreadsheetApp.getUi(); // DEBUG
ui.alert("heelo"); // DEBUG
var approvalsSpreadsheet = SpreadsheetApp.openById(APPROVALS_SPREADSHEET_ID);
var ui = SpreadsheetApp.getUi(); // DEBUG
ui.alert('Requests sentsee'); // DEBUG
var approvalsSheet = approvalsSpreadsheet.getSheets()[0];
// Fetch all the data from the Expense Report Spreadsheet
// getRowsData was reused from Reading Spreadsheet Data using JavaScript Objects tutorial
var data = getRowsData(sheet);
// Fetch all the data from the Approvals Spreadsheet
var approvalsData = getApprovalData(approvalsSheet);
// For every expense report
for (var i = 0; i < data.length; i++) {
var row = data[i];
row.rowNumber = i + 2;
Logger.log("Row num: " + row.rowNumber);
Logger.log("row state before: " + row.state);
Logger.log("row email before: " + row.emailAddress);
if (!row.state) {
Logger.log("row state: " + row.state);
sendReportToManager(row);
sheet.getRange(row.rowNumber, COLUMN_STATE).setValue(row.state);
Logger.log("row state: " + row.state);
} else if (row.state == STATE_MANAGER_EMAIL) {
for (var j = 0; j < approvalsData.length; ++j) {
var approval = approvalsData[j];
if (row.rowNumber != approval.expenseReportId) {
Logger.log("failed expenseId: " + approval.expenseReportId + " rowNumber: " + row.rowNumber);
continue;
}
Logger.log("pass");
// Email the employee to notify the Manager's decision about the expense report.
sendApprovalResults(row, approval);
// Update the state of the report to APPROVED or DENIED
sheet.getRange(row.rowNumber, COLUMN_STATE).setValue(row.state);
break;
}
}
}
}
Row = function(){
};
function getRowsData(sheet){
var data = sheet.getDataRange().getValues();
var row = new Row();
var rows = [];
var counter = 0;
for (var i = 0; i < data.length; i++) {
row = new Row();
row.time = data[i][0];
row.emailAddress = data[i][1];
row.amount = data[i][2];
row.description = data[i][3];
row.managersEmailAddress = data[i][4];
row.state = data[i][5];
if(i != 0){
Logger.log("row state1: " + row.state);
rows[counter] = row;
counter++;
}
}
Logger.log("rowsss:");
Logger.log(rows);
Logger.log("data length: " + rows.length);
Logger.log("data display: ");
for(var k = 0; k < rows.length; k++){
Logger.log(rows[k].state);
}
return rows;
}
ApprovalData = function(){
};
function getApprovalData(sheet){
var data = sheet.getDataRange().getValues();
var row = new ApprovalData();
var rows = [];
for (var i = 0; i < data.length; i++) {
row.time = data[i][0];
row.emailAddress = data[i][1];
row.expenseReportId = data[i][2];
row.approveExpenseReport = data[i][3];
row.comments = data[i][4];
rows[i] = row;
}
return rows;
}
// Sends an email to an employee to communicate the manager's decision on a given Expense Report.
function sendApprovalResults(row, approval) {
var approvedOrRejected = (approval.approveExpenseReport == "Yes") ? "approved" : "rejected";
var message = "<HTML><BODY>"
+ "<P>" + approval.emailAddress + " has " + approvedOrRejected + " your expense report."
+ "<P>Amount: $" + row.amount
+ "<P>Description: " + row.description
+ "<P>Report Id: " + row.rowNumber
+ "<P>Manager's comment: " + (approval.comments || "")
+ "</HTML></BODY>";
MailApp.sendEmail(row.emailAddress, "Expense Report Approval Results", "", {htmlBody: message});
if (approval.approveExpenseReport == "Yes") {
row.state = STATE_APPROVED;
} else {
row.state = STATE_DENIED;
}
}
// Sends an email to a manager to request his approval of an employee expense report.
function sendReportToManager(row) {
var message = "<HTML><BODY>"
+ "<P>" + row.emailAddress + " has requested your approval for an expense report."
+ "<P>" + "Amount: $" + row.amount
+ "<P>" + "Description: " + row.description
+ "<P>" + "Report Id: " + row.rowNumber
+ '<P>Please approve or reject the expense report <A HREF="' + APPROVAL_FORM_URL + '">here</A>.'
+ "</HTML></BODY>";
MailApp.sendEmail(row.managersEmailAddress, "Expense Report Approval Request", "", {htmlBody: message});
row.state = STATE_MANAGER_EMAIL;
Logger.log("id: " + row.rowNumber);
}
function onOpen() {
var ui = SpreadsheetApp.getUi(); // Same variations.
var result = ui.alert(
'Do you want to get new requests?',
ui.ButtonSet.YES_NO);
// Process the user's response.
if (result == ui.Button.YES) {
// User clicked "Yes".
onReportOrApprovalSubmit();
ui.alert('Requests sent');
} else {
// User clicked "No" or X in the title bar.
ui.alert('No requests sent');
}
}
最佳答案
函数onOpen只能访问与其绑定(bind)的电子表格，而不能访问其他电子表格。 UI提示不会更改:显示自定义对话框不会向该函数添加任何授权。一个人无法通过launched by a simple trigger进程(例如onOpen)访问其他电子表格。
解决方案:让onOpen添加菜单项，这些菜单项将用于启动任何需要授权的功能。
function onOpen() {
var menu = [{name: "Get New Requests", functionName: "onReportOrApprovalSubmit"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu("Script", menu);
}