Last active
August 29, 2015 14:02
-
-
Save dustinlarimer/c1af9568a5e18e2d1cf9 to your computer and use it in GitHub Desktop.
Set up an IFTTT Gmail recipe that appends messages to Google Spreadsheet. Next, paste this script into that spreadsheet's Script Editor, and set a new trigger to fire when the document is changed.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Spreadsheet Rows: | |
----------------------------- | |
A: timestamp (not used here) | |
B: from | |
C: subject | |
D: label | |
E: attachment name | |
F: attachment url | |
*/ | |
var API_KEY = "your_api_key", | |
PROJECT_ID = "your_project_id", | |
TIMEZONE_OFFSET = "-07:00", // US/Pacific PDT | |
EVENT_COLLECTION = "messages"; | |
function sendEvent(event) { | |
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(), | |
sheet, last_row, values, timestamp, payload; | |
// There are several change types to ignore | |
if (event && (event.changeType == "EDIT" || event.changeType == "OTHER")) { | |
Logger.log("Change detected..."); | |
sheet = spreadsheet.getSheets()[0]; | |
last_row = sheet.getLastRow(); | |
values = sheet.getRange(last_row, 1, 1, 6).getValues(); | |
timestamp = parseDate(values[0][0]); | |
payload = JSON.stringify({ | |
"from": values[0][1] || "", | |
"subject" : values[0][2] || "", | |
"label": values[0][3] || "", | |
"attachment": values[0][4] || "", | |
"keen": { | |
"timestamp": timestamp | |
} | |
}); | |
Logger.log("Sending event"); | |
Logger.log(payload); | |
UrlFetchApp.fetch("https://api.keen.io/3.0/projects/" + PROJECT_ID + "/events/" + EVENT_COLLECTION, { | |
"method" : "post", | |
//"muteHttpExceptions": true, -- uncomment to avoid email error reports | |
"contentType": "application/json", | |
"headers": { | |
"Authorization": API_KEY | |
}, | |
"payload" : payload | |
}); | |
// Deleting the last row triggers an error | |
if (last_row !== 1.0) { | |
Logger.log("Deleting this row"); | |
sheet.deleteRow(last_row); | |
} else { | |
Logger.log("Clearing this row"); | |
sheet.getRange(last_row, 1, 1, 6).clear(); | |
} | |
} else { | |
Logger.log("No change worth saving"); | |
} | |
} | |
// Reconstruct an ISO-8601 date string from IFTTT's format | |
function parseDate(str){ | |
var time = str.split(" "), months, timestring, parsed_hour, | |
year, month, day, hour, minute, output; | |
months = { | |
"Jan": "01", "January": "01", | |
"Feb": "02", "February": "02", | |
"Mar": "03", "March": "03", | |
"Apr": "04", "April": "04", | |
"May": "05", | |
"Jun": "06", "June": "06", | |
"Jul": "07", "July": "07", | |
"Aug": "08", "August": "08", | |
"Sep": "09", "Sept": "09", "September": "09", | |
"Oct": "10", "October": "10", | |
"Nov": "11", "November": "11", | |
"Dec": "12", "December": "12" | |
}; | |
year = time[2]; | |
month = months[time[0]]; | |
day = time[1].replace(",",""); | |
timestring = time[4].split(":"); | |
parsed_hour = (+timestring[0]).toFixed(0); | |
if (time[4].indexOf("PM") > -1 && parsed_hour < 12) { | |
hour = +parsed_hour + 12; | |
} else { | |
hour = parsed_hour; | |
} | |
minute = timestring[1].substring(0, timestring[1].length - 2); | |
output = year + "-" + month + "-" + day + "T" + hour + ":" + minute + ":00" + TIMEZONE_OFFSET; | |
//Logger.log(output); | |
return output; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment