SHARE
TWEET

Untitled

a guest Mar 19th, 2019 56 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top