Grab promo usage report script

readme.md

GRAB promo usage report

Are you using Grab corporate promo codes (ex: KUDOS)? Want to know your promo usages without taking too much effort by manually recording your trip fares(ain't nobody got time for that)? Well you're in luck! Thanks to Google Apps Scripts you can run this script to generate your promo usage (feelsgoodman.jpg).

Limitations

This script only works for:

• Grab Receipts in Indonesian
• Gmail account

How it works

This script will filter your Grab receipt emails and parse them to specified Google sheet.

How to use the script

Make sure you're signed in to your Google account associated with your Grab Account or your work email so the script can read your Grab Receipts.

1. Open Google Drive, create a new sheet

2. In the newly created spreadsheet, open Tools -> Script Editor

3. Copy grabreceiptpromousage.gs and paste it to the Script Editor

4. Change SHEET_ID with your current spreadsheet id

5. Try to click run icon in the Script Editor

6. It will prompt you to save the project, save the project (e.g. grabscript as the project name)

7. Try to run it once more, it will prompt you to authorize the script

8. Choose the Google account associated with your Grab Account

9. Click Advanced

10. Click Go to [your project name] (unsafe)

11. Click Allow

12. Click Select function and select run in Script Editor

13. Click run icon and your spreadsheet will be filled with your receipts data

14. You can override the date filters to your liking (grab promo resets at the 25th of each month, so for usage during 25th of September through 24th of October you can set after_date = '2017/09/25' and before_date = '2017/10/25')

15. You can also run the script automatically on your preferred schedule using project trigger

16. Click the add trigger link

17. Choose the run function and time-driven event, choose the rest to your liking and save it

18. Voila! the spreadsheet will be periodically updated by that trigger

grabreceiptpromousage.gs

//inspired by https://gist.github.com/oshliaer/70e04a67f1f5fd96a708

var SHEET_ID = 'YOUR_SHEET_ID';
var CREDIT_LIMIT = 500000; //YOUR GRAB LIMIT
var WARNING_THRESHOLD = 50000; //Will send warning email if credit balance is less than this threshold
var email = Session.getEffectiveUser().getEmail();

// auto detect current period
var curDate = new Date();
// if date > cutoff date (25) after_date's month is current month, else previous month
if(curDate.getDate() > 25) {
  curDate.setMonth(curDate.getMonth())
} else {
  curDate.setMonth(curDate.getMonth() - 1)
}

// set date as cutoff date
curDate.setDate(25)

//gmail before and after filter, read: https://support.google.com/mail/answer/7190
var after_date = Utilities.formatDate(curDate, Session.getScriptTimeZone(), 'yyyy/MM/dd');
curDate.setMonth(curDate.getMonth() + 1)
var before_date = Utilities.formatDate(curDate, Session.getScriptTimeZone(), 'yyyy/MM/dd');

//uncomment these 2 lines for manual date override 
//after_date = '2017/12/15';
//before_date = '2018/01/25';
 
function getEmails(query,sheet){
  var thds = GmailApp.search(query);
  for(var i in thds){
    var msgs = thds[i].getMessages();
    for(var j in msgs){
      var data = '';
      //get da plain body
      content = msgs[j].getPlainBody();
      
      //regex is love. https://xkcd.com/208/
      //lazy matching XD might need improvement
      var regExpDateTime = new RegExp("TANGGAL.*\n.*","gi");
      var regExpDateTimeReplace = new RegExp("TANGGAL.*\n|\\+0700","gi");
      var regExpType = new RegExp("Jenis Kendaraan:.*\n.*","gm");
      var regExpTypeReplace = new RegExp("Jenis Kendaraan:.*\n","g");
      var regExpTotalIsZero = new RegExp("TOTAL\n.*RP 0", "gm");
      var regExpIsCorporateBilling = new RegExp("Detail Pembayaran:\n.*Corporate Billing", "gm");
      var regExpRate = new RegExp("Tarif Perjalanan.*", "g");
      var regExpRateReplace = new RegExp("Tarif Perjalanan.*RP", "g");
      var regExpCheckExtra = new RegExp("Biaya Tol & Lainnya", "g");
      var regExpExtraFees = new RegExp("Biaya Tol & Lainnya.*", "g");
      var regExpExtraFeesReplace = new RegExp("Biaya Tol & Lainnya.*RP", "g");
      
      var extras = 0;
      
      //only appendData if total == 0 (total RP 0 means promo code is used)
      if(regExpTotalIsZero.exec(content) || regExpIsCorporateBilling.exec(content)){
        data += regExpDateTime.exec(content).toString().replace(regExpDateTimeReplace,'');
        data += '|'+regExpType.exec(content).toString().replace(regExpTypeReplace,'');
        var rate = parseInt(regExpRate.exec(content).toString().replace(regExpRateReplace,'').trim().replace(",",""));
        
        if(regExpCheckExtra.exec(content)) {
          extras = parseInt(regExpExtraFees.exec(content).toString().replace(regExpExtraFeesReplace,'').trim().replace(",",""));
        }
        
        var sum = (+rate) + (+extras);
        data += '|'+ sum;
        appendData(data,sheet);
      }     
    }
  }
}

function appendData(data,sheet){
  var token = data.split("|");
  var lastRow = sheet.getLastRow() + 1;
  sheet.getRange(lastRow, 1).setNumberFormat("yyyy-mm-dd hh:mm:ss") ; //date formatting
  sheet.getRange(lastRow, 1).setValue(token[0]); //datetime
  sheet.getRange(lastRow, 2).setValue(token[1]); //type(GrabBike, etc.)
  sheet.getRange(lastRow, 3).setValue(token[2]); //fare
}

function sendWarningMail(sheet){
  MailApp.sendEmail(
    email,
    "",
    "Kudo Grab Promo Usage Warning",
    'Dear Myself, \n\nYour credit balance is less than your specified threshold of Rp ' + formatNum(WARNING_THRESHOLD) + 
    '\nCredit usage: Rp ' + formatNum(sheet.getRange('F5').getValues()) +
    '\nCredit balance: Rp ' + formatNum(sheet.getRange('F6').getValues()) + 
    '\n\nCheck your spreadsheet for more details: https://docs.google.com/spreadsheets/d/'+ SHEET_ID);
  
  //write sent mail flag in sheet
  sheet.getRange(10, 5).setValue("Email sent, period:");
  sheet.getRange(10, 6).setValue(curDate.getMonth());
}

function formatNum(s){
  return String(s).replace(/(.)(?=(\d{3})+$)/g,'$1.');
}

function run(){
  var ss = SpreadsheetApp.openById(SHEET_ID);
  //force set locale to en_US because grab receipt is using (,) as thousand separator
  ss.setSpreadsheetLocale('en_US');
  
  var sheet = ss.getActiveSheet();
  var sendEmailFlag = false;
  var emailFlag = sheet.getRange('E10').getValues();
  var period = sheet.getRange('F10').getValues();
  
  //check email sent flag & check period
  if(sheet.getRange('E10').getValues() == "" || sheet.getRange('F10').getValues() != curDate.getMonth()){
    sendEmailFlag = true
  }
  
  //clear sheet on every run
  sheet.getDataRange().clear();
  
  getEmails("Your GRAB E-Receipt after:"+after_date+" before:"+before_date, sheet);
  
  //preset cells
  sheet.getRange(1, 5).setValue("After Date:"); 
  sheet.getRange(1, 6).setValue(after_date); 
  sheet.getRange(2, 5).setValue("Before Date:"); 
  sheet.getRange(2, 6).setValue(before_date); 
  sheet.getRange(3, 5).setValue("Total Trip(s):"); 
  sheet.getRange(4, 5).setValue("Credit Limit:"); 
  sheet.getRange(4, 6).setNumberFormat("#,###").setValue(CREDIT_LIMIT);
  sheet.getRange(5, 5).setValue("Credit Usage:");
  sheet.getRange(6, 5).setValue("Credit Balance:");
  sheet.getRange(8, 5).setValue("Last Update:");
  sheet.getRange(8, 6).setValue(new Date().toLocaleString());
  
  //formula cells
  sheet.getRange(3, 6).setValue("=COUNT(A1:A)"); //count rows
  sheet.getRange(5, 6).setValue("=SUM(C1:C)"); //sum fare column
  sheet.getRange(6, 6).setValue("=F4-F5"); //credit balance
  
  //reapply sent mail flag
  if(!sendEmailFlag){
    sheet.getRange(10, 5).setValue(emailFlag);
    sheet.getRange(10, 6).setValue(period);
  }
  
  if(sheet.getRange('F6').getValues() < WARNING_THRESHOLD && sendEmailFlag){
    sendWarningMail(sheet);
  }
}

26/12/2017
few updates:

1. auto detect time period
2. adjustment for new receipt format

28/12/2017
update:

• new feature: send warning email to self if credit balance is less than specified threshold

28/12/2017
update:

fix NaN error on sum fare + extras, kudos to @irwandi for the fix!

29/12/2017
update:

• Force set spreadsheet locale to en_US because grab receipt is using comma (,) as thousand separator

09/01/2018
update:

• Add check for "corporate billing" payment method to accommodate new Grab for Business e-receipt