SpreadsheetApp.openById not going through in Google Spreadsheet

Question

I am trying to run the function "onReportOrApprovalSubmit()" when I click on the message box that pops up when I open my google spreadsheet. The function runs perfectly fine when I run it in the script editor. However, when I try to run it using the message box that pops up when I first open the spreadsheet, the function stops running/gets stuck just before the line:

 "var approvalsSpreadsheet = SpreadsheetApp.openById(APPROVALS_SPREADSHEET_ID);"

Does anyone have any idea why this is happening?

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

Answer 1

The function onOpen can only access the spreadsheet it is bound to, not others. The UI prompt doesn't change that: showing a custom dialog doesn't add any authorization to the function. One cannot access other spreadsheets from a process that is launched by a simple trigger, such as onOpen.

Solution: let onOpen add menu items that will be used to launch any functions requiring authorization.

function onOpen() {
  var menu = [{name: "Get New Requests", functionName: "onReportOrApprovalSubmit"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Script", menu);
}