View difference between Paste ID: ZT3T5LyL and 6cWdH8WT

 /**
-Ingress Portal Submission Tracker fog Google Sheets - ver 1.2 (unknown author)
+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
 
- var threads = GmailApp.search("ingress portal (Submitted|Live|Rejected|Duplicate): "+"[email protected]");
+
 **/
 
 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',
- sheet.appendRow(["Portal Name","Date Submitted","Date Resolved","Monkey Speed","Age","Link","Status"]);
+
- sheet.setColumnWidth(1, 250);
+
- sheet.setColumnWidth(2, 120);
+
  }
  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 sheetLink = '=HYPERLINK("' + link + '" ; "Link")';
+
  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")';
-     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);
+
    }
    //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);
 };