Guest User

Untitled

a guest
Mar 19th, 2019
64
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. //Goes through the budgets listed and checks if the current spend is over the percentage limit, if so, the tracker will send an email to the respective account managers
  2. function trackBudget() {
  3.  
  4. var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GDS Version");
  5. var values = sheet.getDataRange().getValues();
  6. var headers = values.shift(); //plucks the value from the first row
  7. var count = 0; //ensures the loop only sends one email
  8. var quota = MailApp.getRemainingDailyQuota();
  9. var percentageLimit = values[0][16]; //Q2
  10. var channel;
  11. var budget;
  12. var spendToDate;
  13. var checker = values[1][16];//Q3 //ensures the function only sends one email per day
  14.  
  15. if(checker == "ON") {
  16. for (var i=0; i<values.length; i++) {
  17. channel = values[i][0];
  18. budget = values[i][1]; //B column
  19. spendToDate = values[i][2]; //C column
  20. //var ui = SpreadsheetApp.getUi();
  21. // Check totals sales
  22. if (budget < spendToDate){
  23. if(count==0){
  24. sendEmail(channel,percentageLimit);
  25. //sendEmail(budget,spendToDate);
  26. //ui.alert('budget > spendToDate'+ spendToDate + ' '+ channel+ ' '+stdDev);
  27. count= 1;
  28. sheet.getRange("Q3").setValue("OFF");
  29. }
  30. }
  31. }
  32. }
  33. }
  34. // Useless for now
  35. function getSheet(name) {
  36. return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  37. }
  38. // Resets trigger at midnight.
  39. function resetEmailTrigger() {
  40.  
  41. var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GDS Version");
  42. var values = sheet.getDataRange().getValues();
  43. var headers = values.shift(); //plucks the value from the first row
  44. var checker = values[1][16];
  45.  
  46. if (checker == "OFF") {
  47. sheet.getRange("Q3").setValue("ON");
  48. }
  49. }
  50.  
  51. //Sends the email if the conditions are met
  52. function sendEmail(channel, percentageLimit){
  53. // Fetch the email address
  54. var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange("B2:B4");
  55. var nameRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange("A2");
  56. var unfiltered = emailRange.getValues();
  57. var emailAddress = unfiltered.filter(String);
  58. //clears the nulls from the array
  59. var link = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  60. for(var i=0; i<emailAddress.length; i++) {
  61. var message = "Hey, \n\n" + channel +" is nearing the limit of the budget. It's "+ percentageLimit + "% away from the budget. \n\n"+ "Click the link to be redirected to the tracker to review. You can also change the limit in the tracker. " + link;
  62. var subject = "Account Budget Nearing The Limit";
  63. MailApp.sendEmail(emailAddress[i], subject, message);
  64. }
  65. }
RAW Paste Data