Page MenuHomePhabricator (Chris)
Files F2354

raw.txt
No One
Actions

Authored By
chrisw
Apr 24 2020, 10:47 AM
Size
4 KB
Referenced Files
None
Subscribers
None

raw.txt
View Options

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

File Metadata

Mime Type
text/plain; charset=utf-8
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
1919
Default Alt Text
raw.txt (4 KB)

Event Timeline