prasanthmj · January 29, 2024 17:50 · wfresch · Jan 22, 2020 · xMQ2RwURb · Jan 27, 2020
diff --git a/messages.html b/messages.html
 <!DOCTYPE html>
 <html>
  <head>
    <base target="_top">
    <title>Message Display Test</title>
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  </head>
  <body style="padding:3em;">
  <h1>Messages</h1>
    <ul>
    <? for(var m=0;m<messages.length;m++){ ?>
    <li><?= messages[m].getSubject() ?></li>
    <p><?= messages[m].getPlainBody() ?></p>
    <? } ?>
    </ul>
  </body>
 </html>
diff --git a/parse-extract-from-gmail.js b/parse-extract-from-gmail.js


 function getRelevantMessages()
 {
  var threads = GmailApp.search("newer_than:1d AND from:citicorp.com AND subject:Transaction confirmation AND -label:payment_processing_done",0,100);
  var messages=[];
  threads.forEach(function(thread)
                  {
                    messages.push(thread.getMessages()[0]);
                  });
  return messages;
 }

 function parseMessageData(messages)
 {
  var records=[];
  if(!messages)
  {
    //messages is undefined or null or just empty
    return records;
  }
  for(var m=0;m<messages.length;m++)
  {
    var text = messages[m].getPlainBody();
    
    var matches = text.match(/Rs\.\s+([\d\,\.]+)\s+(?:spent on card)\s+(\d+)\s+(?:on)\s+([\d\-A-Z]+)\s+(?:at)\s+([\w\s]+)\./);
    
    if(!matches || matches.length < 5)
    {
      //No matches; couldn't parse continue with the next message
      continue;
    }
    var rec = {};
    rec.amount = matches[1];
    rec.card = matches[2];
    rec.date= matches[3];
    rec.merchant = matches[4];
    
    //cleanup data
    rec.amount = parseFloat(rec.amount.replace(/,/g, ''));
    
    records.push(rec);
  }
  return records;
 }

 function getMessagesDisplay()
 {
  var templ = HtmlService.createTemplateFromFile('messages');
  templ.messages = getRelevantMessages();
  return templ.evaluate();  
 }

 function getParsedDataDisplay()
 {
  var templ = HtmlService.createTemplateFromFile('parsed');
  templ.records = parseMessageData(getRelevantMessages());
  return templ.evaluate();
 }

 function saveDataToSheet(records)
 {
  var spreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1ql-S5TjH5KoekRvRqiwuyequyxhgas/edit#gid=0");
  var sheet = spreadsheet.getSheetByName("Sheet1");
  for(var r=0;r<records.length;r++)
  {
    sheet.appendRow([records[r].date,records[r].card, records[r].merchant, records[r].amount ] );
  }
  
 }

 function processTransactionEmails()
 {
  var messages = getRelevantMessages();
  var records = parseMessageData(messages);
  saveDataToSheet(records);
  labelMessagesAsDone(messages);
  return true;
 }

 function labelMessagesAsDone(messages)
 {
  var label = 'payment_processing_done';
  var label_obj = GmailApp.getUserLabelByName(label);
  if(!label_obj)
  {
    label_obj = GmailApp.createLabel(label);
  }
  
  for(var m =0; m < messages.length; m++ )
  {
     label_obj.addToThread(messages[m].getThread() );  
  }
  
 }

 function doGet()
 {
  return getParsedDataDisplay();

  //return getMessagesDisplay();
 }
diff --git a/parsed.html b/parsed.html
 <!DOCTYPE html>
 <html>
  <head>
    <base target="_top">
    <title>Data parsed from emails</title>
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  </head>
  <body style="padding:3em;">
    <h2>Parsed data from Messages</h2>
    <table style="width:420px">
    <thead>
    <tr>
    <th>Date</th><th>Merchant</th><th>Card</th><th>Amount</th>
    </tr>
    </thead>
    <tbody>
    <? for(var m=0;m<records.length;m++){ ?>
    <tr>
    <td><?= records[m].date ?></td>
    <td><?= records[m].merchant ?></td>
    <td><?= records[m].card ?></td>
    <td><?= records[m].amount ?></td>
    </tr>
    <? }?>
    </tbody>
    </table>
    <div class="block">
    <button id="save_to_sheet" class="blue">Save Data to Sheet</button>
    <span id="saving_label" style="visibility:hidden">Saving ...</span>
    </div>
    <script>
    document.querySelector("#save_to_sheet").addEventListener("click", 
    function(e)
    {
       var self=this;
       self.style.visibility = 'hidden';
       var saving_label = document.querySelector("#saving_label");
       saving_label.style.visibility = 'visible';
       google.script.run.withSuccessHandler(function()
       {
         alert("Saved expense data");
         self.style.visibility = 'visible';
         saving_label.style.visibility = 'hidden';
         
       }).withFailureHandler(function()
       {
         alert("an error occured while saving");
         self.style.visibility = 'visible';
         saving_label.style.visibility = 'hidden';
       }).processTransactionEmails();
       
    });
    </script>
  </body>
 </html>
	<!DOCTYPE html>
	<html>
	<head>
	<base target="_top">
	<title>Message Display Test</title>
	<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
	</head>
	<body style="padding:3em;">
	<h1>Messages</h1>
	<ul>
	<? for(var m=0;m<messages.length;m++){ ?>
	<li><?= messages[m].getSubject() ?></li>
	<p><?= messages[m].getPlainBody() ?></p>
	<? } ?>
	</ul>
	</body>
	</html>


	function getRelevantMessages()
	{
	var threads = GmailApp.search("newer_than:1d AND from:citicorp.com AND subject:Transaction confirmation AND -label:payment_processing_done",0,100);
	var messages=[];
	threads.forEach(function(thread)
	{
	messages.push(thread.getMessages()[0]);
	});
	return messages;
	}

	function parseMessageData(messages)
	{
	var records=[];
	if(!messages)
	{
	//messages is undefined or null or just empty
	return records;
	}
	for(var m=0;m<messages.length;m++)
	{
	var text = messages[m].getPlainBody();

	var matches = text.match(/Rs\.\s+([\d\,\.]+)\s+(?:spent on card)\s+(\d+)\s+(?:on)\s+([\d\-A-Z]+)\s+(?:at)\s+([\w\s]+)\./);

	if(!matches \|\| matches.length < 5)
	{
	//No matches; couldn't parse continue with the next message
	continue;
	}
	var rec = {};
	rec.amount = matches[1];
	rec.card = matches[2];
	rec.date= matches[3];
	rec.merchant = matches[4];

	//cleanup data
	rec.amount = parseFloat(rec.amount.replace(/,/g, ''));

	records.push(rec);
	}
	return records;
	}

	function getMessagesDisplay()
	{
	var templ = HtmlService.createTemplateFromFile('messages');
	templ.messages = getRelevantMessages();
	return templ.evaluate();
	}

	function getParsedDataDisplay()
	{
	var templ = HtmlService.createTemplateFromFile('parsed');
	templ.records = parseMessageData(getRelevantMessages());
	return templ.evaluate();
	}

	function saveDataToSheet(records)
	{
	var spreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1ql-S5TjH5KoekRvRqiwuyequyxhgas/edit#gid=0");
	var sheet = spreadsheet.getSheetByName("Sheet1");
	for(var r=0;r<records.length;r++)
	{
	sheet.appendRow([records[r].date,records[r].card, records[r].merchant, records[r].amount ] );
	}

	}

	function processTransactionEmails()
	{
	var messages = getRelevantMessages();
	var records = parseMessageData(messages);
	saveDataToSheet(records);
	labelMessagesAsDone(messages);
	return true;
	}

	function labelMessagesAsDone(messages)
	{
	var label = 'payment_processing_done';
	var label_obj = GmailApp.getUserLabelByName(label);
	if(!label_obj)
	{
	label_obj = GmailApp.createLabel(label);
	}

	for(var m =0; m < messages.length; m++ )
	{
	label_obj.addToThread(messages[m].getThread() );
	}

	}

	function doGet()
	{
	return getParsedDataDisplay();

	//return getMessagesDisplay();
	}