Created
March 20, 2016 02:34
-
-
Save rishigb/8e13785a90cc2a1c8a5f to your computer and use it in GitHub Desktop.
sheetScript
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| var APPROVALS_SPREADSHEET_ID ='1GqnBcrjUnG3XZFj3a4Kc8j_7aPKSMlqE70fmrTTdliM'; | |
| var APPROVAL_FORM_URL ='https://docs.google.com/forms/d/1kdpRJIxFLBrJyumCNOIx00x0Xp23ar1m0pXQ_qxFDRU/viewform?usp=send_form'; | |
| var STATE_APPROVED = "Approved"; | |
| var STATE_DENIED = "Denied"; | |
| var submitted ="SUBMITTED"; | |
| function sendEmails() { | |
| var sheet = SpreadsheetApp.getActiveSheet(); | |
| var approvalsSpreadsheet = SpreadsheetApp.openById(APPROVALS_SPREADSHEET_ID); | |
| var sheetApproval = approvalsSpreadsheet.getActiveSheet(); | |
| var startRow = 2; // First row of data to process | |
| var numRows = sheet.getLastRow(); // Number of rows to process - This has to be the maximum number of rows. this part is working | |
| var startRowApproval =2; | |
| var numRowsApproval = sheetApproval.getLastRow(); | |
| // Fetch the range of cells , with the increase in number of columns, increase 12 too. | |
| var dataRange = sheet.getRange(startRow, 1, numRows-1, 12)//getRange(row, column/starting column of the range, numRows, numColumns/number of columns to return) this part is working. | |
| var data = dataRange.getValues(); | |
| var dataRangeApproval = sheetApproval.getRange(startRowApproval,1,numRowsApproval-1,2); | |
| var dataApproval =dataRangeApproval.getValues(); | |
| for (i in data) { | |
| var row = data[i];//data[i][0] is going to give the value of the cell. | |
| var empId = row[0]; // First column | |
| var empName = row[2]; // Second column | |
| var managerEmail=row[3];// | |
| var vendor =row[5];// | |
| var amount = row[10];// | |
| var formId= row[0];//Timestamp as of now | |
| var empEmail =row[15]; | |
| //Check for form submission |Need to write code to update the flag on submission | |
| Logger.log(row[2]); | |
| if (row[11]!=submitted){ | |
| //sendReportToManager(empName,amount,vendor,formId,managerEmail); | |
| Logger.log("sending email to manager"); | |
| } | |
| /* | |
| //else check for rejection status from the other sheet and call for approved/decline email. | |
| for (j in dataApproval){ | |
| var row_approval = dataApproval[j]; | |
| var status = row_approval[1]; | |
| if (status == STATE_APPROVED){ | |
| Logger.log("sendApprovalStatus called"); | |
| } | |
| } | |
| */ | |
| } | |
| } | |
| function sendReportToManager(_empName,_amount,_vendor,_formId,_managerEmail) { //Create a function with variable names, passing the inputs in above code. | |
| var message = "<html><body>" | |
| + "<p>" + _empName + " has requested your approval for an expense report." | |
| + "<p>" + "Amount: " + _amount | |
| + "<p>" + "Vendor: " + _vendor | |
| + "<p>" + "Report Id: " + _formId | |
| + '<p>Please approve or reject the expense report <a href="' + APPROVAL_FORM_URL + '">here</a>.' | |
| + "</body></html>"; | |
| MailApp.sendEmail(_managerEmail, "Expense Report Approval Request", "", {htmlBody: message}); | |
| } | |
| // Sends an email to an employee to communicate the manager's decision on a given Expense Report. | |
| function sendApprovalResults(r, 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 || "") | |
| + "</body></html>"; | |
| MailApp.sendEmail(row.emailAddress, "Expense Report Approval Results", "", {htmlBody: message}); | |
| if (approval.approveExpenseReport == "Yes") { | |
| row.state = STATE_APPROVED; | |
| } else { | |
| row.state = STATE_DENIED; | |
| } | |
| } | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment