Wednesday, May 9, 2012

Getting CSV data into Google Spreadsheets Automatically

Today I was attempting to get CSV data from Estates' Alarm System into Google Docs as a spreadsheet. There were two ways to try and achieve this...


  1. Create an AppScript in Google that pulled a .CSV file from a web server
  2. Write a (python) script on the local machine that pushed the data into Google Spreadsheet by using the API.

The Google AppScript Way

As you know, my JavaScript ain't great, but it initially looked like it was going to work... Some code like this below and using the Array to CSV functions from here, looked promising.



function encode_utf8( s ){
  //This is the code that "I think" turns the UTF16 LE into standard stuff....
  return unescape( encodeURIComponent( s ) );
}

function get_csv() {
  var url = 'http://www-users.york.ac.uk/~admn812/alarms.csv.Active BA Alarms.csv'; // Change this to the URL of your file
  var response = UrlFetchApp.fetch(url);
  // If there's an error in the response code, maybe tell someone
  //MailApp.sendEmail("s.brown@york.ac.uk", "Error with CSV grabber:" + response.getResponseCode() , "Text of message goes here")
  Logger.log( "RESPONSE " + response.getResponseCode());
  var data = encode_utf8(response.getContentText().toString());  
  return data //as text  
}


function importFromCSV() {
  // This is the function to which you attach a trigger to run every hour  
  var rawData = get_csv(); // gets the data, makes it nice...
 
  var csvData = CSVToArray(rawData, "\t"); // turn into an array
  Logger.log("CSV ITEMS " + csvData.length);
 
  //Write data to first sheet in this spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //Logger.log(sheet);
 
  ////// From: https://developers.google.com/apps-script/articles/docslist_tutorial
 
  // I think this will write data from the 0th cell. It actually needs a line to select ALL the data and delete it,
  // in case there is less data than the previous import.
 
  for (var i = 0; i < csvData.length; i++) {
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
     //this might be where you would look at the data and maybe...
    // cell.offset(i,i+2).setBackgroundColor("green");
    //Logger.log( "i:" + i + " " + csvData[i] );
  }
}

But I got stuck. I think it's because the CSV file was UTF-16 Little Endian, and my regular expressions wouldn't work.

The Python Way

The python way is completely different in that it runs on the same computer as where the CSV file and pushes the data into a Google Spreadsheet.

I found bugs if you have funnily named header rows ( CamelCaseOnlyPerhaps ). That I solved by adding one cell at a time. It's slow but reliable....

I also found that if I used a DictReader() it broke, so I just iterated through the lines and the items ( which seemed to work ).








import time, urllib, csv, re, time
from pprint import pprint
import gdata.spreadsheet.service

email = 'your.name@york.ac.uk'
password = '********'

'''Warning. This means of connecting to the GDATA API is being deprecated soon in favour of OAuth'''
spr_client = gdata.spreadsheet.service.SpreadsheetsService()
spr_client.email = email
spr_client.password = password
spr_client.source = 'Example CSV To Spreadsheet Writing Application'
spr_client.ProgrammaticLogin( )
spreadsheet_key = '0Ajnu7JgRtB5CdDFQeGM2YVZBNXROcC1vZ0xCQ2tVX1E'

data_url = 'http://www-users.york.ac.uk/~admn812/alarms.csv.Active BA Alarms.csv'
# All spreadsheets have worksheets. I think worksheet #1 by default always have a value of 'od6'
worksheet_id = 'od6'

#### Examples from http://pseudoscripter.wordpress.com/2011/05/09/automatically-update-spreadsheets-and-graphs/ ####
def _CellsUpdateAction(row,col,inputValue,key,wksht_id):
    '''You "can" update an entire row, or rows even with a dict(array) or list of them, but I got a bizarre error when doing so, so in an attempt to find the nasty cell, do it a cell at a time'''
    entry = spr_client.UpdateCell(row=row, col=col, inputValue=inputValue,
            key=key, wksht_id=wksht_id)
    if isinstance(entry, gdata.spreadsheet.SpreadsheetsCell):
        print row,",", col, 'updated:', inputValue

def _PrintFeed(feed):
    '''Just a way to iterate through what's available'''
    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 using the gsx: elements in the entry.)
            print 'Contents:'
            for key in entry.custom:
              print '  %s: %s' % (key, entry.custom[key].text)
            print '\n',
        else:
            # THIS ONE!
            print '%s %s, %s' % (i, entry.title.text, str(entry.id.text))
            #print dir(entry)

def show_my_spreadsheets():
    print "My spreadsheets are..."
    feed = spr_client.GetSpreadsheetsFeed()
    _PrintFeed(feed)

def replace(text, look_for, replace_with=''):
    reg = look_for
    p = re.compile(reg, re.IGNORECASE | re.DOTALL)
    t = p.sub(replace_with, text)
    return t
       
def match(s, reg):
    p = re.compile(reg, re.IGNORECASE| re.DOTALL)
    results = p.findall(s)
    return results
   
def get_data(data_url=data_url):
    u = urllib.urlopen(data_url)
    data = u.read()
    data = data.decode("utf-16 LE")
    return data
   
def get_data_from_file(filepath):
    'This should work, not tested it.'
    f = open(filepath)
    data = f.read()
    f.close()
   
    data = data.decode("utf-16 LE")
    return data
   
def write_data( dict ):
    'Not used'
    entry = spr_client.InsertRow(dict, spreadsheet_key, worksheet_id)
    if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
      print "Insert row succeeded."
    else:
      print "Insert row failed."

def run():
    filepath = '/Users/tomsmith/Downloads/alarms.csv.Active BA Alarms (8).csv'   
    data = get_data() # or ... data = get_data_from_file("C:/myfolder/mycsv.csv")
    #Strip the first junky stuff off...
    data = match( data, '"Time of last Change.*')[0]
   
    # I chose to add the field headers by hand. You can do this on the fly, but
    # I found a bug if they had uppercase letters. Grr!
    fields = ["Time of last Change","Category","Technical Description","Status","Priority","Alarm Value","Alarm Message",]
   
   
    # Write header row
    for  f,field in enumerate(fields):
        _CellsUpdateAction(1,f+1,field,spreadsheet_key,worksheet_id)
       
    ## Now write the data, cell by cell
    data = data.split("\n")
    for l, line in enumerate(data):
        if l == 0:
            pass #the header line
        else:
            items = line.split("\t")
            the_dict = {}
            print "Line:", l #remember, zero-based
           
            for i, item in enumerate(items):
                #Agh, line 153 in the data doesn't have enough items
                try:
                    the_value = item.replace('"', '') #Strip quotes off the beginning/end
                   
                    #print the_value
                    header_name = fields[i].lower().replace(" ", "").replace("'", "")
                    the_dict[ header_name ] = str(the_value)
                   
                    _CellsUpdateAction(l+2,i+1,the_value,spreadsheet_key,worksheet_id)
                except Exception, err:
                    #print "\t Line", l, "only has", len(item), "items", items
                    #print err
                    pass
           
            #time.sleep(1) #Give Google a chance to catch up a bit
       
   
if __name__ == '__main__':
    #show_my_spreadsheets()
    run( )

The End Result

Is shocking really. There's no error checking and you have to put your credentials in for it to work... but it works! And it means Estates can continue connecting data from various applications into one visualisation dashboard. Hopefully more on that later.




I believe that it should be possible to grab data using AppScript ( the pull approach ) but I was beaten by unicode text formats and rudimentary JavaScript skills. This approach does require the CSV file to be available online, which is a difficulty, a complication or at best, a security challenge.













Saturday, May 5, 2012

Building an App at the University of York

A funny thing happened to me on the way to the Collaborative Tools Project this week. Someone had dropped in for a chat and I was enthusing about the creative possibilities offered by both Google App Engine and Google App Script for people "doing it for themselves" and they asked...

"But does anyone actually want to DO this?"

He had a point. To be honest, I don't know. I can't know, but I'm pretty sure I think I know that they do. And then within the week I came across someone at the University using Android tablets for data entry into spreadsheets, where data get moved between sheets based on a workflow and is all presented using lovely looking visualisations. People not only wanted to be doing this stuff - they were already doing it.

Google AppScript

So. I said I'd let you know how I was getting on with Google AppScript ( long video here if you want to get started yourself ).

The short answer is quite well. Given that I have a deep seated hatred of braces and semi-colons in programming languages and AppScript is all Javascript-based, I'm not doing bad at all.

The long answer is that I have created a Rock n Roll interface that looks like this...


The bit I am pleased about is that the top bit was created in AppScripts GUI builder and the bands were added programmatically from a spreadsheet. What's good about this is that it would mean that our designers could theme applications and programmers could do the other bits.

Another rather wonderful thing is that the app is hosted...

https://docs.google.com/a/macros/york.ac.uk/exec?service=AKfycbz2tlS0aVGB7y--qtcuEQ-uTDqRezc7pZp_ZmwjiQ

... I know it's not big or clever. But it's available for you to use now. I didn't have to ask anyone to make it available. I've made it available to "Anyone at York" but equally, it might be available to a small project group or department.

That's TWO BIG THINGS... It's there... ready to use ... AND it handles permissions. It's worth pointing out simply because think about what the alternative would have to be, to have an application available to use for University of York staff staff within the afternoon, based on a crazy idea you had in the morning. Tell me how that would happen without AppScript.

The Second Cool Thing

Is that, based on the spreadsheet data, a Google Site's pages were populated/published. If you click the links on the apps above you'll see what I mean. It ain't pretty, but it's a start. It means people could potentially comment on any of the bands I've added... or upload files or just access some of the data.

Conclusion

I know what I've made is ugly as sin, and doesn't do much but it's a very encouraging start. Having trialled Google App Engine which I think wouldn't be suitable for "citizen development"... Google AppScript definitely is ideal for small widget-like apps, or quick wizards or simple applications.

You wouldn't want to write Facebook in Google AppScript but those handy dandy applications that make your life more sensible are definitely doable. The only hard part is thinking up the idea.









Wednesday, May 2, 2012

Citizen Development 2.0

In a previous post I looked at Google App Engine as a possible means for people at the University of York to quickly create what might pukingly be called "custom solutions" for themselves.

I made an RSS aggregator ( http://uoynews.appspot.com/ ) and whilst it's OK, I must say, having to design this site to handle caching and database calls suggested to me that this wasn't an "everyman" route... Google App Engine, it seems to me is a perfectly good platform for the committed closet geek, willing and able to quickly devour the python language and some low level web development concepts ( like data modelling, caching, task queues and the like)



So, recently I've been looking at much more entry level means to creating web applications. The idea is that I should be able to create a simple form that talks to the Google Homework Sites idea, in which a class of students get a Google Site created for them, in which they enter their homework, and later, their privileges are changed to "read only" so that they can't continue working past the deadline.


So... given that the above idea works at a code level, it'd be good if I could create a form that looked something like this...

... so that you can choose ( from a list of your sites ) which site you'd like to use as a template... then select which students should get a copy of the site ( to which they will be added as a "writer" ) and then when to close the sites for editing ( the deadline ).

Introducing AppScript GUI Builder ( EXPERIMENTAL! ) 

A little known feature of Google Docs is the GUI Builder ( GUI stands for Graphical User Interface, meaning buttons and things ).

You can use GUI Builder from within a Google Spreadsheet AppScript or from with a Google Sites AppScript.





As you can see, it has text boxes and buttons and checkboxes and all the things you might need to create a simple form talks to a spreadsheet, or Google sites or other online tools.


I then discovered our SafariBooks account has an online copy of the O'Reilly book... Google Script: Enterprise Essentials. I'd been banging my head against ridiculously simple tasks in AppScript, this book has already straightened out a few kinks in my understanding.

Although I was originally embedding the GUI in a Google Site, the next step was to Publish As Service just the GUI.

Just people from the University of York can get to it here: https://sites.google.com/a/macros/york.ac.uk/exec?service=AKfycby-a5Irz-qyitYTrNpHLkdKTPBeI2zVOryPbP0YlQ


I'll let you know how I get on.






 

© 2013 Klick Dev. All rights resevered.

Back To Top