Created
March 20, 2016 10:43
-
-
Save rishigb/61904cd6772e02b1decc to your computer and use it in GitHub Desktop.
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 submitted ="SUBMITTED"; | |
| var approved ="APPROVE"; | |
| var rejected ="DECLINE"; | |
| var APPROVAL_FORM_URL ='https://docs.google.com/a/helpchat.in/forms/d/1rofjwJbNZq_hMfQaBLWYk2RnhA24p7nCzXR1CTISVa8/viewform' ; | |
| var APPROVALS_SPREADSHEET_ID='1yvnH_ddE3d_MNOUHbrk535N0N6LJhpD4CGzQmc-l-vA'; | |
| function mainFunction() { | |
| var sheet = SpreadsheetApp.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 statusCol = sheet.getLastColumn(); | |
| // Fetch the range of cells , with the increase in number of columns, increase 12 too. | |
| var dataRange = sheet.getRange(startRow, 1, numRows-1, sheet.getLastColumn())//getRange(row, column/starting column of the range, numRows, numColumns/number of columns to return) this part is working. | |
| var data = dataRange.getValues(); | |
| /* Open the second sheet here and check for the reportID and status here */ | |
| var approvalSpreadsheet = SpreadsheetApp.openById(APPROVALS_SPREADSHEET_ID); | |
| var astartRow = 2; | |
| var aSpreadsheet = approvalSpreadsheet.getActiveSheet(); | |
| var anumRow = approvalSpreadsheet.getLastRow(); | |
| var astatusColumn = approvalSpreadsheet.getLastColumn(); | |
| var adataRange = aSpreadsheet.getRange(2,1,anumRow-1,approvalSpreadsheet.getLastColumn()); | |
| var adata = adataRange.getValues(); | |
| for (i in data) { | |
| var row = data[i]; | |
| var empId = row[2]; // 3rd column | |
| var empName = row[3]; | |
| var empEmail=row[statusCol-6]; | |
| var managerEmail=row[statusCol-5]; | |
| var statusOfRes = row[statusCol-2]; | |
| var vendor1 =row[4];//E | |
| var amount1 = row[10];//k | |
| var vendor2 =row[12];//M | |
| var amount2 = row[18];//S | |
| var vendor3 =row[20];//U | |
| var amount3 = row[26];//AA | |
| var vendor4 =row[28];//AC | |
| var amount4 = row[34];//AI | |
| var vendor5 =row[36];//AK | |
| var amount5 = row[42];//AQ | |
| var reportId= startRow++ ;//Row Number form the sheet | |
| var comments = row[statusCol-1]; | |
| //var vendorAndamountInfo = (vendor1+":"+amount1+vendor2+":"+amount2+vendor3+":"+amount3+vendor4+":"+amount4+vendor5+":"+amount5 ); | |
| //Logger.log(comments); | |
| //Logger.log(reportId); | |
| //Update the status on the main sheet from here | |
| for (j in adata){ | |
| //When both the report ID in main sheet and the approval form back end are the same, update the status or comment. | |
| if (reportId == adata[j][1]){ | |
| sheet.getRange(adata[j][1], statusCol-1).setValue(adata[j][2]); | |
| sheet.getRange(adata[j][1], statusCol).setValue(adata[j][3]); | |
| } | |
| } | |
| /*********************Send emails here. Check the statuCol from the Main sheet and send emails on the basis of that. */ | |
| if (row[statusCol-2] != submitted) { | |
| if (row[statusCol-2] == approved){ | |
| sendResponseEmployee (empEmail,reportId,row[statusCol-2],comments); | |
| Logger.log(row[statusCol-2]); | |
| } | |
| if (row[statusCol-2] ==rejected) { //statusCol -2 is the status column, it can also be written as 46 | |
| sendResponseEmployee(empEmail,reportId,row[statusCol-2],comments); | |
| Logger.log("rejected"); | |
| Logger.log(row[statusCol-2]); | |
| } | |
| if(row[statusCol-2] ==""){ | |
| sendReportManager(empName,reportId,managerEmail,vendor1,amount1,vendor2,amount2,vendor3,amount3,vendor4,amount4,vendor5,amount5); | |
| sheet.getRange(reportId, statusCol-1).setValue("SUBMITTED"); | |
| Logger.log(row[statusCol-2]); | |
| } | |
| } | |
| else { | |
| Logger.log("Report already submitted, doing no action"); | |
| } | |
| } | |
| } | |
| /************************ Function to send emails */ | |
| function sendReportManager(_empName,_reportId,_managerEmail,_vendor1,_amount1,_vendor2,_amount2,_vendor3,_amount3,_vendor4,_amount4,_vendor5,_amount5) { //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>" + "Report Id: " + _reportId | |
| + "<p>" + "Amount: " + _amount1 | |
| + "<p>" + "Vendor: " + _vendor1 | |
| + "<p>" + "Amount: " + _amount2 | |
| + "<p>" + "Vendor: " + _vendor2 | |
| + "<p>" + "Amount: " + _amount3 | |
| + "<p>" + "Vendor: " + _vendor3 | |
| + "<p>" + "Amount: " + _amount4 | |
| + "<p>" + "Vendor: " + _vendor4 | |
| + "<p>" + "Amount: " + _amount5 | |
| + "<p>" + "Vendor: " + _vendor5 | |
| + '<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}); | |
| } | |
| function sendResponseEmployee(_empEmail,_reportId,_status,_comments) { //Create a function with variable names, passing the inputs in above code. | |
| var message = "<html><body>" | |
| + "<p> Status of your request is " +_status + "</p>" | |
| + "<p>" + "Report Id: " + _reportId | |
| +"<p>"+"Comments: "+ _comments | |
| + "</body></html>"; | |
| MailApp.sendEmail(_empEmail, "Expense Report Approval Result", "", {htmlBody: message}); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment