Getting Gmail infro using SQLite3

[Jonathan Seymour]

So I've been looking for a way to put specific Gmail information in my notification popup.  Right now I just have %NTITLE in my popup.

I've been reading up here about setting up a script to get gmail information and put it in a variable, but I can't quite get it working.  I think some of the options are outdated and not available in the latest Tasker, and since I'm a n00b I don't know if I'm using the right substitute

The linked thread talks about 'Locale Execute Plugin' but I can't find that anywhere on my tasker, so I've been using Run Shell instead.

Anyway, after reading that thread, I put together the profile that is triggered by a notification owned by Gmail.

I have a file 'mailsender' saved on my sdcard with the following script:

#! /system/bin/sh

yourmail="MYN...@gmail.com"

dir="/data/data/com.google.android.gm/databases/"

case "$1" in

name)

sqlite3 ${dir}mailstore.${yourmail}.db "select fromAddress from messages ORDER BY _id DESC LIMIT 1" | grep -o '"[^"]*"' | tr -d \";;

email)

sqlite3 ${dir}mailstore.${yourmail}.db "select fromAddress from messages ORDER BY _id DESC LIMIT 1" | grep -o \<.*\> | tr -d '<>';;

subject)

sqlite3 ${dir}mailstore.${yourmail}.db "select subject from messages ORDER BY _id DESC LIMIT 1";;

*)

sqlite3 ${dir}mailstore.${yourmail}.db "select * from messages ORDER BY _id DESC LIMIT 1" | awk '/@/{FS="|";print $4,$11}';;

esac

The task to get the subject is:

1. Run Shell - command - !@ sh /sdcard/mailsender subject > /sdcard/mailsubject        ///This successfully makes a new file once triggered, but the file is blank.

2. Wait - 2 seconds

3. Run Shell - command - Read Line mailsubject Line 1 to var %MAILSUB       ///This doesn't seem to do anything (probably because the file is blank

I was hoping at the end of this I would have a populated %MAILSUB variable that I could throw into a scene field to show the Subject line of the latest email, but something's going wrong on the way.

I have zero experience with Run Shell or any of these scripts, so hopefully there's something simple I'm missing or a different way of going around this. I'm on a rooted GS2 if that helps.

Cheers,

Jona

[MrGee]

I had the same idea, but I managed it through Tasker 

In the latest Tasker version you can search (search on shell) or you can use "Secure Settings" (which I did)

Please note the below requires root

 ReadGmail (181)

A1: Variable Clear [ Name:%GMAILMSG Pattern Matching:Off ] 
A2: Secure Settings [ Configuration:ReadGmailMsg (Root) Package:com.intangibleobject.securesettings.plugin Name:Secure Settings ] 
A3: Wait Until [ MS:500 Seconds:0 Minutes:0 Hours:0 Days:0 ] If [ %GMAILMSG Is Set ]
A4: Variable Split [ Name:%GMAILMSG Splitter:@|@ Delete Base:Off ] 

In short, I clear the variable then run SecureSettings with the following (you can copy paste but change your email address ):

sqlite3 /data/data/com.google.android.gm/databases/mailstore.<email_address>@gmail.com.db "SELECT fromAddress || '@|@' || Subject || '@|@' || snippet FROM messages ORDER BY _id DESC LIMIT 1;"

Basically this concatenates the email from + subject + snippet into one text and return the value to %GMAILMSG 

Next I wait untill %GMAILMSG has been set and split the string into multiple pieces using @|@

Now   %GMAILMSG1 has the from name + email address, %GMAILMSG2 is the subject and %GMAILMSG3 the beginning of the message

   

 

Op woensdag 17 april 2013 03:29:25 UTC+2 schreef Jonathan Seymour het volgende:

[Matt R]

Don't redirect the output to a file (the "> /sdcard/mailsubject" part).  Just have it output to %MAILSUB (put %MAILSUB in the "Store Output In" field of the Run Shell action).  Also, get rid of the "!@".

Matt

[Jonathan Seymour]

Thanks for the replies.

I've done what you suggested Matt. But my %MAILSUB variable still seems unpopulated. Perhaps I'm missing a step.

My task that is triggered by the notification is:

Run Shell: Command "sh /sdcard/mailsender subject"

Timeout (0)

Use Root (yes)

Store Output in (%MAILSUB)

All the rest is blank.

I can see the %MAILSUB in my VARS, but when I try to put it in a scene it just shows the text "%MAILSUB", not the actual subject.

Am I missing something?  

MrGee - I don't really understand your way, I don't have any Package options in my Secure Settings.  Does this require pro?

Cheers,

Jona 

[Matt R]

Download a terminal emulator app from the play store and run your command in that. It will give you better feedback on errors.

Matt

[Jonathan Seymour]

typed in sh /sdcard/mailsender subject

Got:

: not foundlsender[1]: /system/bin/sh

: not foundlsender[2]:

: not foundlsender[4]:

: unexpectedsender[6]: syntax error: 'in

Unfortunately this means nothing to me...

I just tried the other option in that previous thread of copying it to /system/xbin and making it executable, but I get "Operation not permitted"

[Matt R]

I think there's a busybox installer app in the play store. Try using that to install a full version of busybox.

Matt

[MrGee]

@Jonathan

I'm not sure, I do have the pro verson (to support the developer) but the same can be done with the "Run Shell" option in Tasker

just copy paste the command in the "command" field set the "Store Output in" field (optionally populate the "Error" field..

I've just tried on my device, exported the task and uploaded it here.

to use please make sure you replace "<email_address>" with... you guessed it your email address

next import it into tasker, then you can change the variables etc. 

[Jonathan Seymour]

Ah! Awesome. Got it working, thanks mrgee! I split it into 3 separate commands and now my scene looks great!

[Jonathan Seymour]

In case you'd like to see what you helped me create.

Cheers,

[bdanders]

That looks really good. You might be interested in this script I put together to use Gmail's imap capability to mark a message as read: https://groups.google.com/forum/?fromgroups=#!topic/tasker/oyVyM1riWic

That would make a nice third button on your popup.

[Jonathan Seymour]

Thanks,  I ended up actually turning off this popup and going back to a simple %NTITLE as going through the various sqlite scripts was taking far too long to populate the various fields for a popup.  So having the popup as soon as I got the email meant the popup was all old data for 30 seconds or so, and putting the popup after all of the scripts meant the popup only came up 30 seconds or so after the notification...  I'll try to play around an see if I can fix it to be quicker.

Anyone else have this issue, or is it just my slow phone?

[bdanders]

I find that the slowest part of running sqlite queries seems to be just the "run shell" command.  If you can compress everything down into a single command with a parseable output, it will execute pretty fast. For example, I run the following script on every gmail notification. I have two different accounts so it extracts relevant info from each of them.

info_one=`sqlite3 /data/data/com.google.android.gm/databases/mailstore.f...@gmail.com.db 'select fromAddress,_id,subject FROM messages WHERE fromAddress NOT LIKE "%firsta...@gmail.com%" ORDER BY _id DESC LIMIT 1' | sed s/\"//g`
info_two=`sqlite3 /data/data/com.google.android.gm/databases/mailstore.s...@gmail.com.db 'select fromAddress,_id,subject FROM messages WHERE fromAddress NOT LIKE "%second...@gmail.com%" ORDER BY _id DESC LIMIT 1' | sed s/\"//g`
echo $info_one":|:"$info_two | sed s/.com//g | sed s/re://g | sed s/fw://g | sed s/fwd://g

This returns the information I want in the form:
George<geo...@george.com>|1646|Subject of message from George:|:Steve<st...@steve.com>|1041|subject of message from Steve

After that it's pretty quick to parse those in tasker by separating at the ":|:" first then splitting at the pipes. This method is WAY faster than doing two run shell actions with a single sqlite call in each.

[Jonathan Seymour]

Ahh, yeah I was running 3 separate scripts. Your way is great though, I just replaced the _id with snippet to put into my popup, thanks mate!

--
You received this message because you are subscribed to a topic in the Google Groups "Tasker" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tasker/_Py7jQxIYKM/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to tasker+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Jonathan Seymour]

Sure.  Attached is the scene and task xml.

You just need to edit the task xml and replace YOURMAIL with the account name in 2 places (see command below).

Task steps:

1. Variable Clear > Name: %MAILINFO

2. Secure Settings> Run Command: 'sqlite3 /data/data/com.google.android.gm/databases/mailstore...@gmail.com.db 'select fromAddress,subject,snippet FROM messages WHERE fromAddress NOT LIKE "YOUR...@gmail.com" ORDER BY _id DESC LIMIT 1' | sed s/\"//g | sed s/.com//g | sed s/re://g | sed s/fw://g | sed s/fwd://g' 

Use Root & Wait for Proces.

Command Name: GMGET 

Output variable: %MAILINFO

3. Wait > 2 Seconds > Until %MAILINFO Is Set

4. Variable Split > %MAILINFO > Splitter |

5. Popup > Text: You've got mail! > Layout: Email Not > Timeout: Pick whatever you want, I have never.

Enjoy.

On Mon, May 13, 2013 at 5:16 AM, Kenan Aga <kena...@gmail.com> wrote:

Jonathan is it possible to put your profile, task, and scene here?

[bdanders]

There is a slight bug with one of the sed commands I gave you that you might want to correct. The "sed s/.com//g" was supposed to remove ".com" from senders, but I forgot that the period is a wildcard. You should change that command to "sed s/\\.com//g".  Sorry about that.

On Monday, May 13, 2013 6:47:31 PM UTC-4, Jonathan Seymour wrote:

Sure.  Attached is the scene and task xml.

You just need to edit the task xml and replace YOURMAIL with the account name in 2 places (see command below).

Task steps:

1. Variable Clear > Name: %MAILINFO

2. Secure Settings> Run Command: 'sqlite3 /data/data/com.google.android.gm/databases/mailstore.YOUR...@gmail.com.db 'select fromAddress,subject,snippet FROM messages WHERE fromAddress NOT LIKE "YOUR...@gmail.com" ORDER BY _id DESC LIMIT 1' | sed s/\"//g | sed s/.com//g | sed s/re://g | sed s/fw://g | sed s/fwd://g' 

Use Root & Wait for Proces.

Command Name: GMGET 

Output variable: %MAILINFO

3. Wait > 2 Seconds > Until %MAILINFO Is Set

4. Variable Split > %MAILINFO > Splitter |

5. Popup > Text: You've got mail! > Layout: Email Not > Timeout: Pick whatever you want, I have never.

Enjoy.

[Mike L]

The script that MrGee posted works for me but I was wondering, what would I need to replace to get the entire message and not just the first line or so?

Thanks

-Mike

[bdanders]

As far as I can tell, the "snippet" is the most text available from a message in the database. If you want the full text you might be able to do an imap request using sl4a, but that's a whole new level of complexity.

[bdanders]

Like so: http://mattwarren.co/2008/08/python-imaplib-and-gmail/

[Mike L]

I came across that link but avoided that route as I do not understand where the email body is after running that script, like where it stores what it found, and it does not specify that.

I found this link which states that pulling the body is possible with sqlite but I can't get it to work. Using "bodycompressed" returns something but it is just gibberish (to my eyes at least) and using "body" as outlined returns nothing.

http://a-more-common-hades.blogspot.com/2012/01/pulling-sender-and-subject-from-gmail_19.html

That python script is probably my best bet, I will play around with that

[bdanders]

I'm not sure why, but the "body" field in the database seems to be really hit-and-miss with data. Some messages are populated and others are blank.

[Mike L]

This is very frustrating. The info I need from this automated email is like 2 words after what is provided from the "snippet". I will keep messing with it. Maybe there is a way to convert "bodycompressed" back to the original body of the message? Probably not as what comes up looks like "wing dings"

[bdanders]

The "wingdings" you're seeing are what happens when you try to save binary data as text. You need to find a way to convert the compressed data into readable text. I found a link (http://stackoverflow.com/questions/15448373/how-to-dump-a-file-stored-in-a-sqlite-database-as-a-blob) that looks promising, but the xxd command doesn't on my phone. Once you can export the data to a compressed file, you (in theory...maybe) should be able to use the 'unzip' command to extract readable text. This is getting way outside my experience though. Hopefully somebody else can help out.

[bdanders]

If you decide to go the Python route, this should work for you, but I'll warn you that it probably won't be pretty. Run the following script using the "Run SL4A Script" action. Pass the appropriate values as variables to %gm_user (gmail username including '@gmail.com') and %gm_pw (gmail password). It should flash the body of the most recent message to that account as well as set the value of the variable %SL4A_Response to the body of that message.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

import imaplib
import android
droid=android.Android()
import time
import datetime

class Task():
    SET_VARIABLE = 547
    def new_task(self):
        self.action_cnt = 0
        self.extras = {'version_number': '1.0', 'task_name': 'task' + str(time.time()), 'task_priority': 9 }
    def set_var(self, varname, value):
        self.action_cnt += 1
        self.extras['action' + str(self.action_cnt)] = {'action': self.SET_VARIABLE, 'arg:1': varname, 'arg:2': value, 'arg:3': False, 'arg:4': False, 'arg:5': False}
    def run_task(self):
        taskIntent = droid.makeIntent('net.dinglisch.android.tasker.ACTION_TASK', None, None, self.extras).result
        droid.sendBroadcastIntent(taskIntent)
    def set_var_now(self, varname, value):
        self.new_task()
        self.set_var(varname, value)
        self.run_task()

try:
  gm_user = droid.getIntent().result[u'extras'][u'%gm_user']
  gm_pw = droid.getIntent().result[u'extras'][u'%gm_pw']
  mail = imaplib.IMAP4_SSL('imap.gmail.com')
  mail.login(gm_user, gm_pw)
  status, count = mail.select("inbox")
  status, data = mail.fetch(count[0], '(UID BODY[TEXT])')
  #droid.makeToast(data[0])
  if len(data[0])>0:
    droid.makeToast('Last Message:' + str(data[0]))
    t = Task()
    t.set_var_now("%SL4A_Response", 'SL4A GMail Last Message: ' + str(data[0]))
  else:
    droid.makeToast('Mail Not Found')
    t = Task()
    t.set_var_now("%SL4A_Response", 'SL4A GMail: Mail Not Found')
except Exception, e:
  t = Task()
  t.set_var_now("%SL4A_Response", 'SL4A GMail Message Error: ' + str(e))

On Wednesday, May 29, 2013 10:40:08 AM UTC-4, Mike L wrote:

[Mike L]

Haha I am already in over my head as you can probably tell. It will probably prove fruitless but I will see what I can come up with for converting my binary data to a "hexdump"

As for the script you posted, I get an error:

SL4A GMail Message Error: u'%gm_user'

That is what "%SL4A_Response" contains after running the script. I tried using information from two different gmail accounts I have linked to my phone and got the same results.

It doesn't just flash the name of the variable, so the script is doing something. Which seems like progress! 

Thank you

[bdanders]

You have to set the %gm_user to your email address (including '@gmail.com'), then set %gm_pw to your password, the do the "Run SL4A Script" action with '%gm_user,%gm_pw' in the "Pass Variable" field (no quotes).

[Mike L]

Success!

My problem was how I was passing the variables to the script, I hit enter and typed the next variable instead of separating with a comma.

It spits out a lot of code with it as well, but nothing a couple variable splits can't handle.

You are a saint, thank you very much for this

[easiuser]

I have been following this thread and hope your venture into SL4 proves fruitful.  

K-9 keeps its own database and saves quite a bit of the message in the text_content field of the messages table.  Just checked one message and it had over 2K characters.  I assume it will contain as data as you select to fetch in the K-9 Account settings.

I know some don't like K-9 but thought I would give you another option.

[Mike L]

This is for a work email which happens to be set up with gmail. 

If it comes down to it I could always forward the certain emails to a K9 compatible one though, K9 seems to be more Tasker friendly as well...

Thanks for the tip!

[Mike L]

bdanders

What can I remove from your script to stop the toast message? At first I though just drop everything around "makeToast" but those are the only places that "%SL4A_Response" is mentioned. I came across another thread of yours to mark an email as read which would tie in nicely with this profile and am wondering the same thing about that one.

Thank you for putting these out there.

[Bob Hansen]

To stop the popup messages remove red lines

[bdanders]

Delete any line that says droid.makeToast, or you could just comment the line out with '#' (you might notice there is already one in there that has been commented out.) The Tasker variable is set in the ' t.set_var_now" line.

[Mike L]

Thank you guys. This profile is almost complete now, just need a reliable way to get it to trigger...

[Michael Wood]

I've been trying to get this working too, I'm able to output the file lists in /data/data/com.google.android.gm/database. I can see mailstore...@gmail.com.db in this list. when I try and run a SQLite query on it nothing is returned. If I run the query in a terminal i get the correct response. Has anyone had this

to get the file list output I have a task setup in tasker

Secure Setting > Run Command. 

Under command I enter:-

      cd /data/data/com.google.android.gm/databases; ls;

and I use root.

Any help would be preciated.

[Matt R]

Can you post what your SQLite3 query looks like that works in a terminal but not Tasker?

Matt

[Michael Wood]

I have got a bit further now.

I have created a task in tasker.
It contains a secure service plugin that has the following command.

cd /data/data/com.google.android.gm/databases; sqlite3 mailstore...@gmail.com.db select fromAddress from messages limit 1;

I am now at least getting it to output the error to a vairiable. The error is

Sqlite3: error: Too many options: "fromAddress"

I thought limit 1 would only get the first record

[Matt R]

You need quotes:

...@gmail.com.db "select ... limit 1"; ...

Matt

[Michael Wood]

Thank you Mat, that has done then trick. Cant believe it was that simple I've tried so many combinations.again thanks for the help and speedy reply.

[Michael Wood]

Do you know how to query the table to give you the number of unread emails?

[Matt R]

Not off the top of my head.  Trying downloading a SQL table viewer/manager and see if you can figure it out.  If you can't figure it out, I can try looking at it later.

Matt

[Dave White]

A bit late, but for those searching you can query the number of unread gmail messages by label or in total. The sqlite query would look something like this. Obviously replace the 'YOUREMAILADDRESS' and 'YOUR-LABEL-NAME' with the proper account specific info.

For all unread messages:

sqlite3 /data/data/com.google.android.gm/databases/mailstore.YOUREMAILADDRESS@gmail.com.db "SELECT numUnreadConversations FROM labels WHERE name = '^all'";

For label specific:

sqlite3 /data/data/com.google.android.gm/databases/mailstore.YOUREMAILADDRESS@gmail.com.db "SELECT numUnreadConversations FROM labels WHERE name = 'YOUR-LABEL-NAME'";

[Luis Gómez Guzmán]

Is there a way to add another field that tells if the message is read or not?

[androsc...@gmail.com]

Sorry if this isn't the right place to ask this but wondering if someone here can tell me what the commands in the body mean?  Some background on the case.  The owner of the email listed as account is suspected of sending a threatening email to the listed email address under Too.  However the email account they bank received the email is from a third party email at mail.com.  Is this command something which could be utilized to forward the email from the google account > mail.com > arriving at the bank as the mail.com address?  Is my hypothesis correct or someone have a different theory.

On Tuesday, April 16, 2013 at 9:29:25 PM UTC-4, Jonathan Seymour wrote:

So I've been looking for a way to put specific Gmail information in my notification popup.  Right now I just have %NTITLE in my popup.

I've been reading up here about setting up a script to get gmail information and put it in a variable, but I can't quite get it working.  I think some of the options are outdated and not available in the latest Tasker, and since I'm a n00b I don't know if I'm using the right substitute

The linked thread talks about 'Locale Execute Plugin' but I can't find that anywhere on my tasker, so I've been using Run Shell instead.

Anyway, after reading that thread, I put together the profile that is triggered by a notification owned by Gmail.

I have a file 'mailsender' saved on my sdcard with the following script:

#! /system/bin/sh

yourmail="MYN...@gmail.com"

dir="/data/data/com.google.android.gm/databases/"

case "$1" in

name)

sqlite3 ${dir}mailstore.${yourmail}.db "select fromAddress from messages ORDER BY _id DESC LIMIT 1" | grep -o '"[^"]*"' | tr -d \";;

email)

sqlite3 ${dir}mailstore.${yourmail}.db "select fromAddress from messages ORDER BY _id DESC LIMIT 1" | grep -o \<.*\> | tr -d '<>';;

subject)

sqlite3 ${dir}mailstore.${yourmail}.db "select subject from messages ORDER BY _id DESC LIMIT 1";;

*)

sqlite3 ${dir}mailstore.${yourmail}.db "select * from messages ORDER BY _id DESC LIMIT 1" | awk '/@/{FS="|";print $4,$11}';;

esac

The task to get the subject is:

1. Run Shell - command - !@ sh /sdcard/mailsender subject > /sdcard/mailsubject        ///This successfully makes a new file once triggered, but the file is blank.

2. Wait - 2 seconds

3. Run Shell - command - Read Line mailsubject Line 1 to var %MAILSUB       ///This doesn't seem to do anything (probably because the file is blank

I was hoping at the end of this I would have a populated %MAILSUB variable that I could throw into a scene field to show the Subject line of the latest email, but something's going wrong on the way.

I have zero experience with Run Shell or any of these scripts, so hopefully there's something simple I'm missing or a different way of going around this. I'm on a rooted GS2 if that helps.

Cheers,

Jona