Skip to content

Instantly share code, notes, and snippets.

@richard-to
Created February 4, 2014 03:01
Show Gist options
  • 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.
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);
}
@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