richard-to
2/4/2014 - 3:01 AM

Google App Script to parse specific emails and write to Google Sheets

Google App Script to parse specific emails and write to Google Sheets

// Modified from http://pipetree.com/qmacro/blog/2011/10/automated-email-to-task-mechanism-with-google-apps-script/

// Globals, constants
var LABEL_PENDING = "pending";
var LABEL_DONE = "done";

// processPending(sheet)
// Process any pending emails and then move them to done
function processPending_(sheet) {
  
  // Get out labels by name
  var label_pending = GmailApp.getUserLabelByName(LABEL_PENDING);
  var label_done = GmailApp.getUserLabelByName(LABEL_DONE);

  // The threads currently assigned to the 'pending' label
  var threads = label_pending.getThreads();

  // Process each one in turn, assuming there's only a single
  // message in each thread
  for (var t in threads) {
    var thread = threads[t];

    // Gets the message body
    var message = thread.getMessages()[0].getPlainBody();
    
    // TODO: Process the messages here
    
    // Add message to sheet
    sheet.appendRow([message]);
    
    // Set to 'done' by exchanging labels
    thread.removeLabel(label_pending);
    thread.addLabel(label_done);
  }
}

// main()
// Starter function; to be scheduled regularly
function main_emailDataToSpreadsheet() {
  // Get the active spreadsheet and make sure the first
  // sheet is the active one
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.setActiveSheet(ss.getSheets()[0]);

  // Process the pending emails
  processPending_(sh);
}