How to open the latest text message in google voice

[Matt R]

I've been trying to figure this out for a while and finally have, so I thought I'd share.  To open the most recent sms received in Google Voice, use Tasker to run the following shell script (with "use root" checked):

am start -d googlevoice://conversations/$( sqlite3 /data/data/com.google.android.apps.googlevoice/databases/model.db "SELECT conversation_id FROM conversation_labels WHERE label='sms' LIMIT 1" ) -n com.google.android.apps.googlevoice/.PhoneCallListActivity

I hope this helps someone else!

Matt

[baudi]

Very nice. Thanks.

[10amla]

I just pull them from notifications (%NTITLE), since I'm not rooted.

[Matt R]

To clarify, this isn't to read what the last google voice message was, it is to open the google voice app at the conversation view of the person who sent the last message (where it would take you if you clicked on the notification).

Matt

[Matt R]

I've complicated this even more!  I can now open to the latest conversation with a particular contact.  The shell command for that is:

am start -d googlevoice://conversations/$( sqlite3 /data/data/com.google.android.apps.googlevoice/databases/model.db "SELECT conversation_labels.conversation_id FROM conversation_labels JOIN conversations ON conversations.conversation_id=conversation_labels.conversation_id WHERE hex(conversations.data) LIKE '%'||hex('%Sender')||'____2b%' AND conversation_labels.label='sms' LIMIT 1" ) -n com.google.android.apps.googlevoice/.PhoneCallListActivity

Set Tasker variable %Sender to the particular contact you want, then run the above shell command, and google voice will open directly to the latest conversation with that person.  If anyone has any comments on my sqlite query, let me know.  I've only learned to do them by searching around the internet, so it may be more complex than necessary.

Matt

[J.]

Works brilliantly! I was already parsing the notification into the sender's name and message body, so I just plugged that into your second script to replace %Sender...now my custom popup scene takes me directly to the respective GV conversation thread when I press the Reply button in my scene. Bloody well done! now if I could just figure out how to do the same for Google messenger and Facebook messenger ;-)

[Mike Lierman]

am start -d googlevoice://conversations/$( sqlite3 /data/data/com.google.android.apps.googlevoice/databases/model.db "SELECT conversation_id FROM conversation_labels WHERE label='sms' LIMIT 1" ) -n com.google.android.apps.googlevoice/.PhoneCallListActivity

All this does is open up a compose screen for me... which is usable but it does not open up the last conversation from someone... 

[Matt R]

Hmm... What's the output if you just run the command inside the parentheses from sqlite3 to LIMIT 1"? Also, did you use root?

Matt

[J.]

The first command didn't work for me either...I think there may be a syntax error in there somewhere. The second command works perfectly though...

[Matt R]

Just tried the first command again on my phone and it worked fine. You have to make sure you copy and paste it correctly (two spaces were added due to line wrapping at first when I did it).

Matt

[Mike Lierman]

When I run sqlite3 /data/data/com.google.android.apps.googlevoice/databases/model.db "SELECT conversation_id FROM conversation_labels WHERE label='sms' LIMIT 1" from terminal emulator using root, the output returned is:

"Error: no such table: conversation_lables"

[Matt R]

There appears to be a space between android and apps and your error misspelled labels. Assuming those are errors here and not in the command you ran, what's the output of the command:

sqlite3 /data/data/com.google.android.apps.googlevoice/databases/model.db .tables

Also, are you on gingerbread or ICS+?

Matt

[J.]

Just curious, what do you use to browse/connect to the Android databases.  I want to poke around and find the FB messenger and Google messenger ones and see if I can create a similar SQL command...

[TomL]

For exploration, I really like this app:

https://play.google.com/store/apps/details?id=com.speedsoftware.sqleditor

Tom

[J.]

This is strange, because I haven't changed a thing - but the second script isn't working now.  I've looked for extra spaces or syntax errors several times, but I'm not seeing anything at all. Like Mike above, it only opens up to the generic compose screen now.  Very strange indeed...

On Monday, February 11, 2013 1:08:20 AM UTC-5, Matt R wrote:

[TomL]

The sql queries that I see in the above posts do not explicitly define a sorting order, so the returning conversation ID may not be the most recent one.  My guess is that the SQL query returned a conversation ID that isn't valid because that SMS was deleted by user ages ago.

Try this query instead:

SELECT conversation_labels.conversation_id FROM conversation_labels JOIN conversations ON conversations.conversation_id=conversation_labels.conversation_id WHERE conversation_labels.label='sms' ORDER BY conversations.timestamp DESC LIMIT 1

Tom

[TomL]

Matt, same critique applies to your query.  It's missing a sorting order clause.  The fact that it has worked for you (in returning the latest message) has just been coincidence.  You need to change your query to this:

SELECT conversation_labels.conversation_id FROM conversation_labels JOIN conversations ON conversations.conversation_id=conversation_labels.conversation_id WHERE hex(conversations.data) LIKE '%'||hex('%Sender')||'____2b%' AND conversation_labels.label='sms' ORDER BY conversations.timestamp DESC LIMIT 1

Tom

[Matt R]

Tom, thanks for the input.  I've seen mention around the interwebs that SQL databases without a specific sort command will be sorted "unreliably".  I'd love to have some way to sort the table by time, but I haven't found any method to do so.  There's no column in the database with just a time stamp (I imagine it's hidden somewhere in the "data" blob column).  I tried your suggestion of conversations.timestamp and it gave an error because that isn't a valid column name.  My phone does reliably sort the results correctly already though.  I believe the issue has to do with no results returning from the query.  There's a number of reasons this could happen.  One reason could be that there aren't any recent conversations with that sender.  The GV app only seems to keep a certain amount/timeframe of conversations, and if you need older ones the app "refreshes" and downloads older stuff.  So if the conversation isn't in there yet, you won't be able to open straight to it.  The more likely issue is the %Sender does not EXACTLY match the one in the google voice message data blob (it's even case sensitive due to the conversion to hex).  I would compare the value of %Sender to what the GV app shows as the contact name.  I populate %Sender from the GV notification, and haven't had any problems.  Maybe non-English characters are problematic?

Matt

P.S. If anyone knows a way of getting around having to convert the blob to hex, I'd like to hear it.

[TomL]

My problem is I don't use sms via google voice, so my model.db data table is pretty empty. If someone would be willing to email me their model.db file, I could take a look.

Tom

[TomL]

Weird about conversations.timestamp not being a valid column, because I can run this query:

/data/data/com.google.android.apps.googlevoice/databases # sqlite3 --header mode
l.db "select c.conversation_id, c.data, c.label_flags, c.timestamp,cl.label from
conversation_labels cl join conversations c on c.conversation_id=cl.conversation_id order by c.timestamp desc limit 1"
conversation_id|data|label_flags|timestamp|label
590036ca538cd60d5bfb04f7ed6929ef24a5a5dd| |260|1351597888000|inbox
/data/data/com.google.android.apps.googlevoice/databases #

[TomL]

To get around the case sensitive matching, use the lower() function on both sides of the like clause, like this:

... where lower(hex(data)) like lower('%'||hex("My Name Here")||'___2b%') and ...

Tom