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 script

Make sure you're signed in to your Google account associated with your Grab Account 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 modify 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

//gmail before and after filter, read: https://support.google.com/mail/answer/7190
var after_date = '2017/09/25'; //this date is included in filter result
var before_date = '2017/10/25'; //this date is excluded in filter result

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 regExpRate = new RegExp("Promosi.*-.*", "g");
      var regExpRateReplace = new RegExp("Promosi.*-.*RP", "g");
      
      //only appendData if total == 0 (total RP 0 means promo code is used)
      if(regExpTotalIsZero.exec(content)){
        data += regExpDateTime.exec(content).toString().replace(regExpDateTimeReplace,'');
        data += '|'+regExpType.exec(content).toString().replace(regExpTypeReplace,'');
        data += '|'+regExpRate.exec(content).toString().replace(regExpRateReplace,'');
        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 run(){
  var ss = SpreadsheetApp.openById(SHEET_ID);
  var sheet = ss.getActiveSheet();
  //clear sheet on every run
  sheet.getDataRange().clear();

  getEmails("subject:Your GRAB E-Receipt from:no-reply@grab.com 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
}