2

I have automatic email alerts that go into a label in my inbox (through the reply-to) and are also sent to users (through the to: part of the email).

I'm looking to add onto my script that pulls information in the body of the email. Ultimately, I want to pull the recipient of the email (in the to: part) and put that in Column B of a google sheet. Any insight will be greatly appreciated.

Script:

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

// Globals, constants
var LABEL_PENDING = "Example label/PENDING";
var LABEL_DONE = "Example label/DONE";

// processPending(sheet)
// Process any pending emails and then move them to done
function processPending_(sheet) {
  // Date format
  var d = new Date();
  var date = d.toLocaleDateString();

  // 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].getBody();

    // Processes the messages here
    orderinfo = message.split("example split");
    rowdata = orderinfo[1].split(" ");

    // Add message to sheet
    sheet.appendRow([rowdata[1]]);

    // 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);
}
tehhowch
  • 9,645
  • 4
  • 24
  • 42
David K
  • 33
  • 1
  • 5
  • How are the alerts sent? Is it a google script? – Liora Haydont May 30 '18 at 19:40
  • no, the alert is sent from an outside source and goes to certain users, but the reply-to: has my email address. this is why i can see it in my inbox. i created a label in my inbox which is where these email alerts get tagged. my current script pulls a certain section of the body of the email and adds this to columnA of the google sheet. I want to also pull the email address of the user in the to: section and put this in columnB – David K May 30 '18 at 19:54
  • Can you show your current script? – Liora Haydont May 30 '18 at 20:14
  • hi, i've added it above – David K May 30 '18 at 20:25

1 Answers1

0

You can access the recipient data with the getTo() function of the message. You could simply add a line that saves itin the column

//Gets the message body and recipent
var message = thread.getMessages()[0].getBody();
var recipient = thread.getMessages()[0].getTo();

//...

// Add message and recipient to sheet
sheet.appendRow([rowdata[1], recipient]);

More info on the function of the gmailMessage class -> https://developers.google.com/apps-script/reference/gmail/gmail-message

Liora Haydont
  • 1,252
  • 1
  • 12
  • 25
  • Got it. For example, I'll receive the following output for the email recipient: John Doe Is is possible at all to just get the email address itself? I've been trying to play around with the script & haven't had much success – David K May 31 '18 at 17:42
  • you could simply user a regex to get the text between <>. (Something like `/.*<\s+(.*)\s+>.*/` whould work, source -> https://stackoverflow.com/questions/5642315/regular-expression-to-get-a-string-between-two-strings-in-javascript) – Liora Haydont May 31 '18 at 18:38
  • For some reason, it's giving me the info of another email that's already in the Label "Example Label Done". There is one email in the "Example Label Pending" label that the script should be reading, but for some reason it's pulling information & duplicates from the "Done Label" – David K Jun 11 '18 at 19:24