Ingress Portal Submission Tracker - ver 1.5

/**
Ingress Portal Submission Tracker fog Google Sheets - ver 1.5 (unknown author)
based on http://pastebin.com/6cWdH8WT
v1.3: add portal number counter collumn; changed search filter to be inclusive of legacy e-mails from [email protected] and [email protected]
v1.4: add link to Intel map for NIA emails which include the link
v1.5: adjust search query to better detect relevant e-mails

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("subject:(ingress portal (Submitted|Live|Rejected|Duplicate)) "+"@google.com");
 var sheet   = SpreadsheetApp.getActiveSheet();
 var ss      = SpreadsheetApp.getActiveSpreadsheet()
 ss.toast("Scanning mailbox for Niantic messages. Please wait..", "Scan Started", -1);
 var rowBackgrounds = {
   'Live':'lightgreen',
   'Rejected':'orange',
   'Submitted':'white',
   'Duplicate':'magenta',
 }
 var rownum  = 1;
 sheet.clear();
 sheet.appendRow(["Thread ID","Portal Name","Date Submitted","Date Resolved","Monkey Speed","Age","Email Link","Status","Location"]);
 sheet.setColumnWidth(1, 90);
 sheet.setColumnWidth(2, 250);
 sheet.setColumnWidth(3, 120);
 sheet.setColumnWidth(4, 120);
 sheet.setFrozenRows(1);
 var portalStatus = {};
 var portalLink = {};
 var resolveDate = {};
 for (var i = 0; i < threads.length; i++) {
   var header = threads[i].getFirstMessageSubject();
   Logger.log("header = " + header);
   var date = threads[i].getLastMessageDate();
   Logger.log("date = " + date);
   var link = threads[i].getPermalink();
   Logger.log("link = " + link);
   var portalState = header.split(":")[0].split(" ")[2];
   Logger.log("portalState = " + portalState);
   var portalName = header.split(": ")[1];
   Logger.log("portalName = " + portalName);
   var portalKey = portalName.toUpperCase();
   var sheetLink = '=HYPERLINK("' + link + '" ; "Link")';
   if (!(portalKey in portalStatus)){
     portalStatus[portalKey] = portalState;
   }
   if (!(portalKey in resolveDate)){
     resolveDate[portalKey] = "";
   }
   //If portal Live, try to get Intel coordinates
   Logger.log(portalName + "; portalState = " + portalState);
   if (portalState == 'Live'){
     if (!(portalKey in portalLink)){
       //get messages properly and enumerate
       //var NIAmessages = threads[i].getMessages();
       //threads[x].getMessages()[0].getFrom();
       //blah blah blah .. or just make a dummy assumption about targetting the first message in a thread (Gmail doesn't seem to thread these properly)
       var NIAmsg = threads[i].getMessages()[0].getBody();
       //extract URL
       portalLink[portalKey] = '=HYPERLINK(' + getLink(NIAmsg)+ '; "Intel Link")';
       //portalLink[portalKey] = '=HYPERLINK("' + 'http://ingress.com/intel' + '" ; "Intel Link")';
       //Logger.log("portalKey link = " + portalLink[portalKey]);
     }
   }
   if (portalState != "Submitted"){
     portalStatus[portalKey] = portalState;
     resolveDate[portalKey] = date;
   }
   // populate spreadsheet only when we hit Submitted message
   if (portalState == 'Submitted') {
     rownum++;
     sheet.appendRow([i,portalName, date, resolveDate[portalKey],
                     "=IF(NOT(ISBLANK(D"+rownum+"));INT(D"+rownum+"-C"+rownum+");)",
                     "=INT(NOW()-C"+rownum+")",
                     sheetLink, portalStatus[portalKey],portalLink[portalKey]]);
     var lastRow = sheet.getRange("B"+rownum+":I"+rownum);
     lastRow.setBackground(rowBackgrounds[portalStatus[portalKey]]);
   }
 }
 ss.toast("Processed " + rownum + " portals.", "Scanning Done", 10);
}


 function getLink(str) {
   //check if href is found then do it.
   // if
   //or just do it the dummy way...
   var link = str.split('<a href=')[1].split('target="_blank">')[0];
   return link;
 }

/**
 * 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);
};