title: Google form expense approval report
body: <p>I am working at the expanse report  in Google form but in my workflow 
  i got the email that send to the manager directly but when manager approve or reject the request i didn't get the email that have to send to the employee or requester directly but i need to run the function onReportOrApprovalSubmit() manually.
  i think there is a problem maybe in trigger ...advice me please </p>
  
  
  
  <pre class="lang-js prettyprint-override"><code>var APPROVALS_SPREADSHEET_ID = "";
  var APPROVAL_FORM_URL = ;
  var STATE_MANAGER_EMAIL = "MANAGER_EMAIL";
  var STATE_APPROVED = "APPROVED";
  var STATE_DENIED = "DENIED";
  var COLUMN_STATE = 5;
  var COLUMN_COMMENT = 7;
  
  // Main tutorial function:
  // For each row (expense report):
  //   - if it's new, email the report to a manager for approval
  //   - if it has recently been accepted or denied by a manager, email the results to the employee
  //   - otherwise (expense reports that have already been fully processed or old expense reports 
  //     that still have not been approved or rejected), do nothing
  // Ideally, this function would be run every time the Approvals Spreadsheet or the Expense Report
  // Spreadsheet are updated (via a Form submission) or regularly (once a day).
  function onReportOrApprovalSubmit() {
    // This is the Expense Report Spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
  
    // Also open the Approvals Spreadsheet
    var approvalsSpreadsheet = SpreadsheetApp.openById(APPROVALS_SPREADSHEET_ID);
    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 = getRowsData(approvalsSheet);
  
    // For every expense report
    for (var i = 0; i &lt; data.length; ++i) {
      var row = data[i];
      row.rowNumber = i + 2;
      if (!row.state) {
        // This is a new Expense Report.
        // Email the manager to request his approval.
        sendReportToManager(row);
        // Update the state of the report to avoid email sending multiple emails
        // to managers about the same report.
        sheet.getRange(row.rowNumber, COLUMN_STATE).setValue(row.state);
      } else if (row.state == STATE_MANAGER_EMAIL) {
        // This expense report has already been submitted to a manager for approval.
        // Check if the manager has accepted or rejected the report in the Approval Spreadsheet.
        for (var j = 0; j &lt; approvalsData.length; ++j) {
          var approval = approvalsData[j];
          if (row.rowNumber != approval.expenseReportId) {
            continue;
          }
          // Email the employee to notify the Manager's decision about the  report.
          sendApprovalResults(row, approval);
          // Update the state of the report to APPROVED or DENIED
          sheet.getRange(row.rowNumber, COLUMN_STATE).setValue(row.state);
          break;
        }
      }
    }
  }
  
  // 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 = "&lt;HTML&gt;&lt;BODY&gt;"
      + "&lt;P&gt;" + "PCS Manager" + " has " + approvedOrRejected + " your change report."
   + "&lt;P&gt;" + "Report Id: " + row.rowNumber
      + "&lt;P&gt;" + "Requester Name: " + row.name
      + "&lt;P&gt;" + "Change Request Type: " + row.changeRequestType
      + "&lt;P&gt;" + "Description: " + row.description
      + "&lt;P&gt;" + "Justifications: " + row.justifications
      + "&lt;P&gt;" + "Manager Approval Statues :" + (approval.approveExpenseReport || "")
  
      + "&lt;P&gt;Base Points: " + (approval.basePoints || "")
      + "&lt;P&gt;Cost($): " + (approval.cost || "")
      + "&lt;P&gt;Manager's comment: " + (approval.comments || "")
      + "&lt;/HTML&gt;&lt;/BODY&gt;";
    MailApp.sendEmail(row.emailAddress, "Change 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 = "&lt;HTML&gt;&lt;BODY&gt;"
      + "&lt;P&gt;" + row.emailAddress + " has requested your approval for a change report."
      + "&lt;P&gt;" + "Report Id: " + row.rowNumber
      + "&lt;P&gt;" + "Requester Name: " + row.name
      + "&lt;P&gt;" + "Change Request Type: " + row.changeRequestType
      + "&lt;P&gt;" + "Description: " + row.description
      + "&lt;P&gt;" + "Justifications: " + row.justifications
  
      + '&lt;P&gt;Please approve or reject the change report &lt;A HREF="' + APPROVAL_FORM_URL + '"&gt;here&lt;/A&gt;.'
      + "&lt;/HTML&gt;&lt;/BODY&gt;";
    MailApp.sendEmail("p@gmail.com", "Change Report Approval Request", "", {htmlBody: message});
    row.state = STATE_MANAGER_EMAIL;
  }
  
  
  
  
  
  /////////////////////////////////////////////////////////////////////////////////
  // Code reused from Reading Spreadsheet Data using JavaScript Objects tutorial //
  /////////////////////////////////////////////////////////////////////////////////
  
  // getRowsData iterates row by row in the input range and returns an array of objects.
  // Each object contains all the data for a given row, indexed by its normalized column name.
  // Arguments:
  //   - sheet: the sheet object that contains the data to be processed
  //   - range: the exact range of cells where the data is stored
  //       This argument is optional and it defaults to all the cells except those in the first row
  //       or all the cells below columnHeadersRowIndex (if defined).
  //   - columnHeadersRowIndex: specifies the row number where the column names are stored.
  //       This argument is optional and it defaults to the row immediately above range; 
  // Returns an Array of objects.
  function getRowsData(sheet, range, columnHeadersRowIndex) {
    var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
    var dataRange = range || 
      sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
    var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
    var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
    var headers = headersRange.getValues()[0];
    return getObjects(dataRange.getValues(), normalizeHeaders(headers));
  }
  
  // For every row of data in data, generates an object that contains the data. Names of
  // object fields are defined in keys.
  // Arguments:
  //   - data: JavaScript 2d array
  //   - keys: Array of Strings that define the property names for the objects to create
  function getObjects(data, keys) {
    var objects = [];
    for (var i = 0; i &lt; data.length; ++i) {
      var object = {};
      var hasData = false;
      for (var j = 0; j &lt; data[i].length; ++j) {
        var cellData = data[i][j];
        if (isCellEmpty(cellData)) {
          continue;
        }
        object[keys[j]] = cellData;
        hasData = true;
      }
      if (hasData) {
        objects.push(object);
      }
    }
    return objects;
  }
  
  // Returns an Array of normalized Strings. 
  // Empty Strings are returned for all Strings that could not be successfully normalized.
  // Arguments:
  //   - headers: Array of Strings to normalize
  function normalizeHeaders(headers) {
    var keys = [];
    for (var i = 0; i &lt; headers.length; ++i) {
      keys.push(normalizeHeader(headers[i]));
    }
    return keys;
  }
  
  // Normalizes a string, by removing all alphanumeric characters and using mixed case
  // to separate words. The output will always start with a lower case letter.
  // This function is designed to produce JavaScript object property names.
  // Arguments:
  //   - header: string to normalize
  // Examples:
  //   "First Name" -&gt; "firstName"
  //   "Market Cap (millions) -&gt; "marketCapMillions
  //   "1 number at the beginning is ignored" -&gt; "numberAtTheBeginningIsIgnored"
  function normalizeHeader(header) {
    var key = "";
    var upperCase = false;
    for (var i = 0; i &lt; header.length; ++i) {
      var letter = header[i];
      if (letter == " " &amp;&amp; key.length &gt; 0) {
        upperCase = true;
        continue;
      }
      if (!isAlnum(letter)) {
        continue;
      }
      if (key.length == 0 &amp;&amp; isDigit(letter)) {
        continue; // first character must be a letter
      }
      if (upperCase) {
        upperCase = false;
        key += letter.toUpperCase();
      } else {
        key += letter.toLowerCase();
      }
    }
    return key;
  }
  
  // Returns true if the cell where cellData was read from is empty.
  // Arguments:
  //   - cellData: string
  function isCellEmpty(cellData) {
    return typeof(cellData) == "string" &amp;&amp; cellData == "";
  }
  
  // Returns true if the character char is alphabetical, false otherwise.
  function isAlnum(char) {
    return char &gt;= 'A' &amp;&amp; char &lt;= 'Z' ||
      char &gt;= 'a' &amp;&amp; char &lt;= 'z' ||
      isDigit(char);
  }
  
  // Returns true if the character char is a digit, false otherwise.
  function isDigit(char) {
    return char &gt;= '0' &amp;&amp; char &lt;= '9';
  }
  </code></pre>