Google Script To Pull Certain Information From Email And Put In A Sheet

Hoping to be able to automate an annoying part of my day... The emails always come formatted the same ... here's the part of the email I'm looking to pull the data from. Item#: SS1

Solution 1:

I use this gist and added a little bit to get you started. You have to setup a Time-driven Trigger to run the main_emailDataToSpreadsheet() function and have the emails labeled as 'pending'.

UPDATED:

 //Modified from https://gist.github.com/richard-to/8797504 < http://pipetree.com/qmacro/blog/2011/10/04/automated-email-to-task-mechanism-with-google-apps-script/

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();
    Logger.log(message);
    // Process the messages here
    message = message.substr(message.search("Item#:")); //Get the beginning of the important part + cut off the beginning
    Logger.log(message);
    message = message.split("\n");
    Logger.log(message[0].split(": ")[1]);

    var data = [message[0].split(": ")[1], //Item#
                message[1].split(": ")[1], //Prod. Desc.
                message[2].split(": ")[1], //Vendor
                message[3].split(": ")[1], //Ven. Item Code
                message[4].split(": ")[1] //Complaint
               ];
    // Add message to sheet
    sheet.appendRow(data);

    // Set to 'done' by exchanging labels
    thread.removeLabel(label_pending);
    thread.addLabel(label_done);
  }
}