Pulling sender and subject from Gmail

This came up on the Tasker group today. Someone wanted a way to get the sender and/or subject from Gmail to have Tasker keep beeping until they read an email from an important person. Or something like that.

The Android Gmail client doesn't have an API to allow you to get any information about a new email. But we're root and we don't care about that API business.

In the directory /data/data/com.google.android.gm/databases is a file named mailstore.YOURNAME@gmail.com.db. Breaking out the trusty sqlite3, we find that the sender and subject is contained in the 4th and 11th fields of the messages table and we can extract that by dumping the table...

sqlite3 mailstore.YOURNAME@gmail.com.db "select * from messages"

...and filtering it with awk.

awk '/@/{FS="|";print $4,$11}' | tail -n1

The last line seems to be the most recent email. For some reason I can't get awk to grab the last line on my phone, so I had to use tail.

That is what I posted on the Tasker group but that was a test and it needs tweaked. It is essentially grepping all the lines containing "@" then printing the 4th and 11th fields. I tried that first because you can get some gibberish in those messages, and I wanted to grab the lines with email addresses and that was a quick way, but the receiver's (your) email address is on the same line as the information we want so it would be a lot safer to grab lines containing your email address instead of the general "@" symbol.

awk '/YOURNAME@gmail.com/{FS="|";print $4,$11}' | tail -n1

The end result is something like "John" <john_doe@isp.net> Re: blah blah

Instead of posting a huge long line that makes my blog look funny, I'll put it all together like this:

#! /system/bin/sh
email=YOURNAME@gmail.com
file=/data/data/com.google.android.gm/databases/mailstore.${email}.db        
sqlite3 ${file} "select * from messages" | awk '/@/{FS="|";print $4,$11}' | tail -n1

I didn't actually test it, but Tasker can listen for notifications which belong to any apps including Gmail. You can have a profile that watches for Gmail notifications, then executes that script and sends the output to a file, then reads the line from the file into a variable, and acts on it accordingly. You could have it read the sender's name to you, remind you to read the email if it's someone important, or whatever.