Page MenuHomePhabricator (Chris)
Paste P8

Google Sheets Javascript IPST by Anonymous V1.5 Uploaded Dec 17th, 2014
ActivePublic
Actions

Authored by chrisw on Apr 24 2020, 10:47 AM.
Tags
  • Unknown Object (Project)
Referenced Files
F2354: raw.txt
Apr 24 2020, 10:47 AM
Subscribers
None
/**
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 super-ops@google.com and ingress-support@google.com
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);
};

Event Timeline

chrisw created this paste.Apr 24 2020, 10:47 AM
chrisw created this object in space S2 Public space.
chrisw created this object with visibility "Public (No Login Required)".
chrisw created this object with edit policy "Administrators".
chrisw updated the paste's language from autodetect to js.

Google Sheets Javascript IPST by Anonymous V1.5 Uploaded Dec 17th, 2014

chrisw added a comment.EditedAug 19 2024, 10:28 AM

original:

/**
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): "+"ingress-support@google.com");
 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);
};