var bodySplitRe = /(

|)/ig; // match a line break var calorieLineRe = /^s*[0-9]+ [0-9a-zA-Z]/; // match # of calories & item var calorieValueRe = /^s*[0-9]+/; // match just # of calories var rightNow = new Date().getTime(); var today = new Date(); today.setHours(0,0,0,0); // rewind to midnight today = today.getTime(); function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [{name: "Update Calories", functionName: "findCalories"}]; ss.addMenu("Dieting", menuEntries); } function formatDate(date) { return (date.getFullYear() + "/" + (date.getMonth() + 1) + "/" + date.getDate()); } function findCalories() { // pull matching email from last two calendar days; will filter more later var afterDate = new Date(rightNow - (86400000 * 2)); var messages = getMessages(afterDate); updateSpreadsheet(computeCalories(messages, '24'), computeCalories(messages, 'today'), computeCalories(messages, 'yesterday')); } function getMessages(date) { var searchString = 'subject:"@FOOD" from:me to:me after:' + formatDate(date); var threads = GmailApp.search(searchString); // get one big list of all messages from the lists of threads return threads.reduce(function (x, thread) { return x.concat(thread.getMessages());}, []); } function computeCalories(messages, which) { // sum up the total calories from each message that applies to ‘which’ return messages.reduce(function (p, c) { return p + computeMessageCalories(c, which);}, 0); } function computeMessageCalories(message, which) { var body = message.getBody(); var messageDate = message.getDate().getTime(); if (which == 'yesterday') { if (!(messageDate >= (today - 86400000) && messageDate <= today)) { return 0; } } else if (which == '24') { if ((rightNow - messageDate) > 86400000) { return 0; } } else if (which == 'today') { if (messageDate < today) { return 0; } } // split the body into lines var bodyBits = body.split(bodySplitRe); var total = 0; // for each line, look for a calorie count line, and if we find it, // extract the calories and add to the total for (var i = 0; i < bodyBits.length; i++) { if (calorieLineRe.test(bodyBits[i])) { var cal = calorieValueRe.exec(bodyBits[i]); total += parseInt(cal); } } Logger.log("returning " + total); return total; } function updateSpreadsheet(last24, today, yesterday) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; sheet.getRange("A1").setValue("Calories today"); sheet.getRange("B1").setValue(today); sheet.getRange("A2").setValue("Calories last 24hrs"); sheet.getRange("B2").setValue(last24); sheet.getRange("A3").setValue("Calories yesterday"); sheet.getRange("B3").setValue(yesterday); sheet.getRange("A4").setValue("Last Updated"); sheet.getRange("B4").setValue(new Date()); }