Skip to content

Instantly share code, notes, and snippets.

@dustinlarimer
Last active August 29, 2015 14:02
Show Gist options
  • Save dustinlarimer/c1af9568a5e18e2d1cf9 to your computer and use it in GitHub Desktop.
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.
/*
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