This is the second post about our new reporting tool in Ukraine, which uses Frontline SMS and GoogleDocs to track information that our regional representatives collect in the field.
You can download the completed and documented script here.
I will now walk through the code itself and describe the purpose of each piece. Unfortunately our blog can not currently imbed inline code, so I will just post images for the time being. This is going to get into some basic computer code; just a warning. If this sort of thing is new to you, these resources may be a simpler and more basic introduction to the same sort of tool that I built (first and second). To get this tool running you will need to download a few things. Thankfully they are all free.
- Download and install the latest version of Frontline:SMS
- Make sure you have the latest version of Python installed. We will be working with the Google Data Python Library. Follow the instructions found here to get things running. You will need to have both Python and the Google Data Library.
- I recommend making sure that you can get a simple Python script to communicate with a Google Spreadsheet by following the hello world example, or by running some of the example programs that come with the Google Data Python Library.
- I also recommend making sure that you can install Frontline:SMS on your computer and get it sending and receiving text messages with your GSM modem before moving on.
Now that Frontline:SMS is up and running and you can get a Python script to connect to the Google Spreadsheet API, lets work on coding something that links the two together. This code represents just one way to accomplish this, and is by no means perfect or optimized. However, it does get the job done. Open up my script (found here) with your favorite editor and lets walk through the different portions.
# Written by Alex Pompe # Feb. 6 2012 # Feel Free to Recycle, reuse, remix this anyway you choose #for the basics import time import string #for handling the encoding issues that arise from FL's handling the SMS contents import urllib try: from xml.etree import ElementTree except ImportError: from elementtree import ElementTree # For all the Google Specific Calls import gdata.spreadsheet.service import gdata.service import atom.service import gdata.spreadsheet import atom # For bringing command line arguments into my script from optparse import OptionParser
The top section shown above is where I import all the command libraries that I will use in the script. There are a few basic ones, and then three groups that are worth mentioning. The urllib library is used to address a character encoding issue that arises from the way Frontline:SMS sends data and message contents. All the gdata and atom imports are Google specific. You are basically using these commands to ready your Python as the messanger to the GDocs API. You want to make sure that Python speaks the same language as GDocs, and thats what these libraries do. Lastly the optparse library will be used so that when FL:SMS calls the script from the command line, it can also send data along with it.
#Helpful Function Definitions
#some might never be used in my verions, but I kept them if they turn out useful to someone else
def StringToDictionary(row_data):
result = {}
for param in row_data.split():
name, value = param.split('=')
result[name] = value
return result
def PrintFeed(feed):
for i, entry in enumerate(feed.entry):
if isinstance(feed, gdata.spreadsheet.SpreadsheetsCellsFeed):
print '%s %s\n' % (entry.title.text, entry.content.text)
elif isinstance(feed, gdata.spreadsheet.SpreadsheetsListFeed):
print '%s %s %s' % (i, entry.title.text, entry.content.text)
# Print this row's value for each column (the custom dictionary is
# built from the gsx: elements in the entry.) See the description of
# gsx elements in the protocol guide.
print 'Contents:'
for key in entry.custom:
print ' %s: %s' % (key, entry.custom[key].text)
print '\n',
else:
print '%s %s\n' % (i, entry.title.text)
def ListInsertActionRow(gd_client, key, wksht_id, row_data):
entry = gd_client.InsertRow(StringToDictionary(row_data),
key, wksht_id)
if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
print 'New Row Inserted!'
def ListGetAction(gd_client, key, wksht_id):
# Get the list feed
feed = gd_client.GetListFeed(key, wksht_id)
PrintFeed(feed)
return feed
def CellsUpdateAction(gd_client, key, wksht_id, row, col, inputValue):
entry = gd_client.UpdateCell(row=row, col=col, inputValue=inputValue,
key=key, wksht_id=wksht_id)
#if isinstance(entry, gdata.spreadsheet.SpreadsheetsCell):
#print 'Cell Updated!'
def ListUpdateActionRow(gd_client, key, wksht_id, index, row_data):
feed = gd_client.GetListFeed(key, wksht_id)
entry = gd_client.UpdateRow(
feed.entry[int(index)],
StringToDictionary(row_data))
if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
print 'Row Updated!'
def ListDeleteActionRow(gd_client, key, wksht_id, index):
feed = gd_client.GetListFeed(key, wksht_id)
gd_client.DeleteRow(feed.entry[int(index)])
print 'Row Deleted!'
def FindLib(gd_client, key, wksht_id,lib_name):
query = gdata.spreadsheet.service.CellQuery()
query['min-col'] = '1'
query['max-col'] = '1'
query['min-row'] = '2'
feed = gd_client.GetCellsFeed(key, wksht_id, query=query)
for i, entry in enumerate(feed.entry):
if entry.content.text == lib_name:
return i
#print 'Could not find that library'
return -10
Shown above, these are all functions that I define and most are made use of in the main body of the script. (Some were just used for debugging.) The key thing to recognize here is that when communicating with Google Docs, their API sends back the information you request in a feed. This feed can be either rows or cells, but in general just think of it as a collection of data.
A good way to understand this is to look at the FindLib function. The purpose of this function is to return the number of the row of a library in the GDoc spreadsheet. I define my query to GDocs by only asking for the data in the first column of the spreadsheet because I know this is the only column that contains the library names. The feed that GDocs sends back to me is the list of all the library names found in that column. I loop through each one and check if it is the name of the library I am looking for. If I never find the library, I have the function return -10 to flag that a new library row must be added to the spreadsheet.
# MAIN SCRIPT STARTS HERE
#Here is all the optparse items here. Message contents stored all in one location due to Frontline encoding issues
parser = OptionParser(usage="usage: %prog [options] filename",
version="%prog 0.1") #change version number here
parser.add_option("-m", "--message",
action="store", # optional because action defaults to "store"
type="string",
dest="message",
default="-l *No Library Given*",
help="The SMS message contents")
parser.add_option("-a", "--alive",
action="store_true",
dest="real_report",
#default=False,
help="Is this a real report")
parser.add_option("--rep",
action="store", # optional because action defaults to "store"
dest="rep_name",
default="*No Rep Name Given*",
help="Name of Regional Representative")
parser.add_option("-p", "--repphone",
action="store", # optional because action defaults to "store"
#type="string",
dest="rep_phone",
default="*No Phone Number Given*",
help="Phone Number of Regional Representative")
(options, args) = parser.parse_args()
Above you can see the meat of the script. The script will be called in the command line of the computer, automatically by Frontline:SMS. When Frontline:SMS is triggered, values will be passed into the script using optparse. Looking in this section of code, you can see that several key pieces of data enter my script through the command line call. This data is marked using flags so that the script knows the difference between the text message contents and the phone number of the reporting rep. For example, the text message contents will be sent using the -m flag and will be stored in the variable message.
# DECLARE THE GOOGLE DOC YOU WANT TO SEND TO HERE email = 'YOURACCOUNTHEREt@gmail.com' password = 'YOURPASSWORDHERE' # Find this value in the url with 'key=XXX' and copy XXX below spreadsheet_key = 'FINDYOURKEYANDPUTHERE' # All spreadsheets have worksheets. I think worksheet #1 by default always # has a value of 'od6', #2 seems to change worksheet_id = 'od6' worksheet_id2 = 'od4' #AUTHENTICATION - I wouldn't touch this if I were you spr_client = gdata.spreadsheet.service.SpreadsheetsService() spr_client.email = email spr_client.password = password spr_client.ProgrammaticLogin()
This section of code is used to connect your script to your google docs account that contains the spreadsheet you want to be updating. All of this formatting is defined by the gdata libraries. You must put your google login and password into the appropriate variables. In addition you must find the key for the exact spreadsheet you will be working with. You can find this in the URL bar of your web browser while the spreadsheet is open. You then copy and paste the value found after key=. There is also values for the worksheets of the spreadsheet that are also found in the url in your browser. You then have your script login to GDocs using your name and password.
#parsing the Text message contents, removes FLSMS encoding chars, and builds a dict of updates parsed from the message
# keys are the first letter (-flag) for the update, and values are all proceeding strings until another flag
options.message = urllib.unquote_plus(options.message)
splitup = string.split(options.message,'-')
if splitup.count('') != 0: splitup.remove('')
updts = dict(zip( [x[0] for x in splitup], [x[1:].strip() for x in splitup] ))
updts = dict((k.lower(), v) for k,v in updts.iteritems())
This section of code is small, but extremely important. It is used to clean up the text message contents. Right now the text message is just one big string of characters. We need to pick out of this string all the meaningful data that will be used to update our GDoc spreadsheet.
First up, the text messages from Frontline:SMS come with a lot of extra characters (mainly + instead of spaces) that need to be removed so that we can properly parse the text message. I do this using the urllib unquote_plus command. After things are cleaned up, I look through the text message for the flag character, the dash (-). I break up the string into pieces every time I find a dash .
Text message reports can contain lots of different information about a library, and the reporter indicates this using flags. If they want to report on the Internet being down at Kyiv Central Library their text would be:
-l Kyiv Central Library -i Internet is down
The -l indicates that the strings coming next are the library name, and the -i indicates the string coming next is in regards to the Internet status. I have my script look through the message contents character by character. Whenever I find a -, I then look at the letter that comes right after it to know what kind of data this is. I then store all the words that come to be used for the update. I do this with a dictionary data structure. Before my text message contents was just a long collection of characters. Now I have it broken down into name-value pairs stored in a dictionary. Using the same example text message from above:
-l is a key with the value Kyiv Central Library.
-i is a key with the value Internet is down
#Build the update dict to send to gdocs
options2 = {}
options2['date'] = time.strftime('%m/%d/%Y') #first 4 keys automatically populated from the command line call
options2['time'] = time.strftime('%H:%M:%S')
options2['repnumber'] = urllib.unquote_plus(options.rep_phone)
options2['repname'] = urllib.unquote_plus(options.rep_name)
if ('l' or 'L') in updts: options2['library'] = updts['l'] #keys must match gdoc column headings or they will be lost
else: options2['library'] = 'Not Reported'
if ('i' or 'I') in updts: options2['internet'] = updts['i']
else: options2['internet'] = 'Not Reported'
if ('r' or 'R') in updts: options2['recommendations'] = updts['r']
else: options2['recommendations'] = 'Not Reported'
if ('s' or 'S') in updts: options2['space'] = updts['s']
else: options2['space'] = 'Not Reported'
if ('b' or 'B') in updts: options2['bars'] = updts['b']
else: options2['bars'] = 'Not Reported'
if ('c' or 'C') in updts: options2['security'] = updts['c']
else: options2['security'] = 'Not Reported'
if ('t' or 'T') in updts: options2['heat'] = updts['t']
else: options2['heat'] = 'Not Reported'
Now I need to package this data to send to Google Docs to update the spreadsheet. To do this I need to build another dictionary (in this case options2). Options2 will always contain some data, like the date, time and phone number of the report. However, not every text message will report on all the library functions. I have my script check to see if there is an update in the text message for each column in my spreadsheet. If something was not reported on, it gets a ‘Not Reported’ value attached to its respective key.
Using our same example text message, I now have the following key-value pairs ready to send to GDocs in my nice organized and clean dictionary Options2:
date is a key where the value is todays as reported by the computer
time is a key where the value is the time as reported by the computer
repnumber is a key where the value is the reporters phone number (from FL:SMS’s called ID)
repname is a key where the value is the reporters name (from FL:SMS’s contact list)
l is a key with the value Kyiv Central Library.
i is a key with the value Internet is down
r,s,b,c, & t are keys with the value Not Reported (since they weren’t included in our text message)
#Send SMS Report Entry to Worksheet 2
entry = spr_client.InsertRow(options2, spreadsheet_key, worksheet_id2)
#Now Update Worksheet 1
#find the row of the library to update, if new then it will be negative
lib_row = FindLib(spr_client, spreadsheet_key, worksheet_id,options2['library'])+2
if lib_row < 0:
#this is if it is a new library
options2['newlibrary'] = 'True'
options2['dateadded'] = time.strftime('%m/%d/%Y') + ' on ' + time.strftime('%H:%M:%S')
#print 'new library created'
entry = spr_client.InsertRow(options2, spreadsheet_key, worksheet_id)
else:
#for old libraries
#Required Reporting Information
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 13,'') #removes newlibrary TRUE flag column
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 2,time.strftime('%m/%d/%Y'))
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 3,time.strftime('%H:%M:%S'))
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 4,urllib.unquote_plus(options.rep_phone))
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 5,urllib.unquote_plus(options.rep_name))
#Optional Reporting Information
if options2['internet'] != 'Not Reported':
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 6,options2['internet'])
if options2['recommendations'] != 'Not Reported':
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 7,options2['recommendations'])
if options2['space'] != 'Not Reported':
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 8,options2['space'])
if options2['bars'] != 'Not Reported':
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 9,options2['bars'])
if options2['security'] != 'Not Reported':
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 10,options2['security'])
if options2['heat'] != 'Not Reported':
CellsUpdateAction(spr_client, spreadsheet_key, worksheet_id, lib_row, 11,options2['heat'])
I now send Options2 through the Google Docs API to my spreadsheet. First I send the whole text message to worksheet 2 which acts as a log of every individual report that comes in.
Now I go to worksheet 1 which is a list of the libraries and the status of all their reporting variables. This is what it looks like for our project in testing mode:
My script will now look for the row of the library indicated with the -l in the original text message. If it can not find the library name, then it will add it as a new row. The script will then look through all the update flags and update the corresponding columns in that row only. Our example text message would create a new row since Kyiv Central Library is not found in the spreadsheet. It would mark only the Internet column and the rest of the updates would be marked as Not Yet Reported. As you can see, FL:SMS, my script, and Google Spreadsheet can all handle different alphabets. This is very important since many of our reports here in Ukraine will be coming in cyrillic characters.
That is it for the source code. All the details of how a python script can be written to connect Frontline:SMS to a Google Docs spreadsheet. My source code is very heavily documented, so I encourage you to download it and modify it to suit your needs. It shouldn’t take much modification to change the reporting values for whatever SMS based reporting your project requires. Now we just need to setup Frontline:SMS to call this script each time a text message report is sent to your GSM USB modem. After installation you must create a new Keyword, with the response type Command Line Execution.
Your command line call must point to the python script and also it needs some flags to mark the data that will be sent on to Google Docs. Here is what my command line call looks like on a Windows test machine:
You can see that I call the script using the python command and then give it the full path to the script. After that I use flags and to send the message content and sender number into the script. This is really all you need. Everything used in this reporting system is free, except for the cost of sending the text messages. I hope that this is helpful for projects outside of ours in Ukraine. I will be looking to add functionality moving forward, feel free to comment on requests for clarification or added features that would be useful for you. In linking two great tools like Frontline:SMS and Google Docs, I think you come away with something even stronger.
