Skip to content

Instantly share code, notes, and snippets.

@richard-to

richard-to/gist:8797504

Created February 4, 2014 03:01
Show Gist options
  • Star 12 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save richard-to/8797504 to your computer and use it in GitHub Desktop.
Save richard-to/8797504 to your computer and use it in GitHub Desktop.
Download ZIP
Google App Script to parse specific emails and write to Google Sheets
Raw
gistfile1.js
// 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);
}
@bpugh22
Copy link

bpugh22 commented Feb 27, 2022

Your script works very well. Thank you!

How can I add From, date, and subject line to your script, please?

@richard-to
Copy link
Author

Hi @bpugh22. It's been a long time since I've looked at this script.

But looking at the docs (https://developers.google.com/apps-script/reference/gmail/gmail-message), it seems you can probably do something like this (add after line 24 in the loop):

var from = thread.getMessages()[0].getFrom();
var date = thread.getMessages()[0].getDate();
var subject = thread.getMessages()[0].getSubject();

I haven't tested, but hope it helps.

@bpugh22
Copy link

bpugh22 commented Feb 28, 2022 via email

@richard-to
Copy link
Author

richard-to commented Feb 28, 2022 •
edited

Oh yeah, you'll also need to add the new info to the function that adds the rows to spreadsheet.

The current code reads:

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

So you'll need to update that line to this:

// Add message to sheet
sheet.appendRow([message, from, date, subject]);

@bpugh22
Copy link

bpugh22 commented Feb 28, 2022 via email

@bpugh22
Copy link

bpugh22 commented Feb 28, 2022 via email

@azkhalidtruth
Copy link

Hi Richard,

can you help me to parse bookings email I receive. I want to extract few information like bookings date, time, location and contact.

@richard-to
Copy link
Author

Hi azkhalidtruth,

I'm not sure if I can help you in this case. I can make guess on how it can work though.

I'm assuming the content you want is inside the email message.

Parsing the message can be tricky. It depends on how consistent the content is. If there's a consistent pattern, you can try to use regular expressions. Apps Script is similar to javascript I think.

Here is a StackOverflow example on how to use Regular Expressions: https://stackoverflow.com/questions/17573598/google-apps-script-regular-expression-to-get-the-last-name-of-a-person

var message = thread.getMessages()[0].getPlainBody();

// TODO: Process the messages here using regular expressions to extract bookings date, time, location, and contact.

// You can add the extracted data to spreadsheet by appending to the row --something like this
sheet.appendRow([message]);
sheet.appendRow([booking_date]);
sheet.appendRow([time]);
sheet.appendRow([location]);
sheet.appendRow([contact]);

You can also try asking ChatGPT to write the code to parse the email. Not sure if it will work or not. But worth trying.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment