Portal Submission Tracker

/**
Ingress Portal Submission Tracker fog Google Sheets - ver 1.2 (unknown author)

Installation:
- start new google docs spreadsheet
- Tools -> Script editor
- paste the whole code, save and close the editor and spreadsheet
- open spreadsheet again
- next to Help menu you will see "Ingress Portals" menu
- Ingress Portals -> Update shreadsheet

Embrace the Enlightenment!
**/

function ingressSubmissionsTracker() {
 var threads = GmailApp.search("ingress portal (Submitted|Live|Rejected|Duplicate): "+"[email protected]");
 var sheet   = SpreadsheetApp.getActiveSheet();
 var rowBackgrounds = {
   'Live':'lightgreen',
   'Rejected':'orange',
   'Submitted':'white',
   'Duplicate':'magenta',
 }
 var rownum  = 1;
 sheet.clear();
 sheet.appendRow(["Portal Name","Date Submitted","Date Resolved","Monkey Speed","Age","Link","Status"]);
 sheet.setColumnWidth(1, 250);
 sheet.setColumnWidth(2, 120);
 sheet.setColumnWidth(3, 120);
 sheet.setFrozenRows(1);
 var portalStatus = {};
 var resolveDate = {};
 for (var i = 0; i < threads.length; i++) {
   var header = threads[i].getFirstMessageSubject();
   var date = threads[i].getLastMessageDate();
   var link = threads[i].getPermalink();
   var portalState = header.split(":")[0].split(" ")[2];
   var portalName = header.split(": ")[1];
   var portalKey = portalName.toUpperCase();
   var sheetLink = '=HYPERLINK("' + link + '" ; "Link")';
   if (!(portalKey in portalStatus)){
     portalStatus[portalKey] = portalState;
   }
   if (!(portalKey in resolveDate)){
     resolveDate[portalKey] = "";
   }
   if (portalState != "Submitted"){
     portalStatus[portalKey] = portalState;
     resolveDate[portalKey] = date;
   }
   // populate spreadsheet only when we hit Submitted message
   if (portalState == 'Submitted') {
     rownum++;
     sheet.appendRow([portalName, date, resolveDate[portalKey],
                     "=IF(NOT(ISBLANK(C"+rownum+"));INT(C"+rownum+"-B"+rownum+");)",
                     "=INT(NOW()-B"+rownum+")",
                     sheetLink, portalStatus[portalKey]]);
     var lastRow = sheet.getRange("A"+rownum+":G"+rownum);
     lastRow.setBackground(rowBackgrounds[portalStatus[portalKey]]);
   }
 }
}

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the ingressSubmissionsTracker() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Update spreadsheet",
    functionName : "ingressSubmissionsTracker"
  }];
  spreadsheet.addMenu("Ingress Portals", entries);
};