Oh my god. It's full of code!

Using google forms and sheets as a data source for graphs

Hey all,

Long time no post! I’ve been on vacation and in general just being kind of lazy, but today I’ve got a simple fun project for us. You see, my girlfriend is always right, well almost always. Very rarely I’ll remember something correctly, but in general she’s always correct (and not in the ‘haha men are so dumb, women know everything’ way, actually legit she remembers way more stuff than me). This phenomenon has gotten so pervasive that I just for kicks wanted to create a live chart running in the house display how often either of us was right about stuff (I know I’ll regret this eventually).  So for my mini project I had a few goals

1) Have a live chart that updates automatically on a TV in my house (we have an extra TV that we generally just use a media center/music streaming box via a chomecast)

2) Make an easy interface to add new data to the chart

3) Make the chart slick looking

4) Keep it simple. This is basically a hobby project so I don’t want to go too nuts.

Before we get started, you can see the demo here:
http://xerointeractive-developer-edition.na9.force.com/partyForce/RightChart

Please close it when you are done though, my dev org only gets so many HTTP requests per day (note to self, add some kind of global request caching or something).

I was able to complete this project in about an hour and a half and meet all my goals. So now I’ll show you how.

Right off the bat I had a general idea of how I would do this (though the approach did morph a bit). From a previous project I knew it was possible that store and retrieve data in a google spreadsheet. You can get the raw CSV data by using a special URL, and them import that via an http request from an Apex controller. I figured this was easier than setting up a salesforce object, creating a custom interface for adding data, and hell it’s cool to be able to utilize google forms data for something.

form

My basic form for collecting data

From there it’s just a matter of passing the data to a chart system, and making it poll the sheet occasionally. So anyway, first off we are going to need a google form to collect our data. Head to google docs, and create a new spreadsheet. Use the forms menu to create a new form for your page. In my case, it’s just a simple single question multiple choice (with an other option). Each time the form is submitted it puts the name, and a timestamp into a sheet called ‘Form Responses 1’. This data format works pretty well. I played around with trying to create another sheet that used queryIf to sum all the times various names appeared in the sheet, but that approach had a limiting factor of only working for names I pre-coded it for. It wasn’t dynamic enough. So I decided to just let google collect the data, and I’d handle the summing and formatting in my code.

sheet1

Your form should be gathering data in a way that looks something like this

To actually get the data in a usable form for programming, we need a raw csv version of it. Thankfully google will provide this for you (though they aren’t exactly forthcoming with it). As of this writting, so get the raw CSV of your sheet, go to file and hit publish. Just publish the one sheet. You should be given a shareable url with a long unique looking id string. Take that and put it into this URL format

https://docs.google.com/spreadsheets/d/key/export?format=csv&id=key

Just replace the word key with your documents unique ID. You should be able to put that URL in your browser and it should automatically attempt to download your spreadsheet in CSV format. If so, you are in good shape. If not, make sure you published it, and it’s shared and all that good stuff. Once you have that working we can move to the next step.

publish

Publish your form results sheet and make note of that unique ID, you’ll need it!

So now that the data exists and is accessible we need to GET it. I decided because it’s the easiest publishing platform I know I’d just use Salesforce sites. So that means Apex is going to be my back end. So I’ll need an Apex call to fetch the CSV data from the google sheet, and some code to parse that CSV into some kind of logical structure. Again thankfully from past projects, I had just such a a class.

//gets CSV data from a given URL and parses it into a list of lists
global class RightChartController 
{

    public String getDataSourceUrl() {
        return 'Your google document url here';
    }

   

    //gets CSV data from a given source
    @remoteAction
    global static  List<List<String>> importCSV(string url)
    {
         List<List<String>> result = new List<List<String>>(); 
        try
        {
            string responseBody;
            
            //create http request to get import data from
            HttpRequest req = new HttpRequest();
            req.setEndpoint(url);
            req.setMethod('GET');         
            Http http = new Http();
            
            //if this is not a test actually send the http request. if it is a test, hard code the returned results.
            if(!Test.isRunningTest())
            {
                HTTPResponse res = http.send(req);
                responseBody = res.getBody();
            }
            else
            {
                responseBody = 'Name,Count\ntammy,10\njoe,5\nFrank,0';
            }
            
            //the data should come back in in CSV format, so hand it off the the parsing function which will make a list of a list of strings (each list is one row, each item within that sub list is one column)
            result = RightChartController.parseCSV (responseBody,true);
        }
        catch(exception e)
        {
            system.debug('\n\n\n\n----------------------------- Error importing chart data. ' + e.getMessage() + ' on line ' + e.getLineNumber());
        }
        return result;
    }
    
    //parses a csv file. REturns a list of lists. Each main list is a row, and the list contained is all the columns.
    public static List<List<String>> parseCSV(String contents,Boolean skipHeaders)
    {
        List<List<String>> allFields = new List<List<String>>();
    
        // replace instances where a double quote begins a field containing a comma
        // in this case you get a double quote followed by a doubled double quote
        // do this for beginning and end of a field
        contents = contents.replaceAll(',"""',',"DBLQT').replaceall('""",','DBLQT",');
        // now replace all remaining double quotes - we do this so that we can reconstruct
        // fields with commas inside assuming they begin and end with a double quote
        contents = contents.replaceAll('""','DBLQT');
        // we are not attempting to handle fields with a newline inside of them
        // so, split on newline to get the spreadsheet rows
        List<String> lines = new List<String>();
        try {
            lines = contents.split('\n');
        } catch (System.ListException e) {
            System.debug('Limits exceeded?' + e.getMessage());
        }
        Integer num = 0;
        for(String line : lines) {
            // check for blank CSV lines (only commas)
            if (line.replaceAll(',','').trim().length() == 0) break;
            
            List<String> fields = line.split(',');  
            List<String> cleanFields = new List<String>();
            String compositeField;
            Boolean makeCompositeField = false;
            for(String field : fields) {
                if (field.startsWith('"') && field.endsWith('"')) {
                    cleanFields.add(field.replaceAll('DBLQT','"'));
                } else if (field.startsWith('"')) {
                    makeCompositeField = true;
                    compositeField = field;
                } else if (field.endsWith('"')) {
                    compositeField += ',' + field;
                    cleanFields.add(compositeField.replaceAll('DBLQT','"'));
                    makeCompositeField = false;
                } else if (makeCompositeField) {
                    compositeField +=  ',' + field;
                } else {
                    cleanFields.add(field.replaceAll('DBLQT','"'));
                }
            }
            
            allFields.add(cleanFields);
        }
        if (skipHeaders) allFields.remove(0);
        return allFields;       
    }
}

So now we’ve got the back end code that is required to both get the data and parse it (Don’t forget to add a remote site exception in your Salesforce security controls for docs.google.com!). Now we just need an interface to use that data and display it in a nifty chart. Using highcharts this is pretty easy. Mine ended up looking something like this (You don’t have to tell me the code is kind of sloppy, this was just a quick throw together project).

<apex:page controller="RightChartController" sidebar="false" showHeader="false" standardStylesheets="false">
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
    <script src="https://code.highcharts.com/highcharts.js"></script>
    <script src="https://code.highcharts.com/highcharts-3d.js"></script>
    <script>
        //load the document source  locally incase we want to let the user change it or something later
        var docSource = '{!dataSourceUrl}';
        var chart;
        
        //fetches the data from the google sheet
        function getData(docSource,callback)
        {
           Visualforce.remoting.Manager.invokeAction(
                '{!$RemoteAction.RightChartController.importCSV}', 
                docSource,
                function(result, event){
                    if (event.status) {
                        callback(result);
                    }
                }, 
                {escape: true}
            );   
     
        }
        
        //massages the data from being an array of arrays (one line per form entry) into an array of objects with totals
        //should probably be refactored to make it more efficient, but whatever.
        function translateDataToHighChartFormat(csvData)
        {
            var chartData = new Array();
            var totals = new Object();
            
            for(var i = 0; i < csvData.length; i++)
            {
                var timestamp = csvData[i][0];
                var name = csvData[i][1];
                 
                if(totals.hasOwnProperty(name))
                {
                    totals[name]++;
                }
                else
                {
                    totals[name] = 1;
                }
            }
            
            for(key in totals)
            {
                var thisPoint = new Object();
                thisPoint.name = key;
                thisPoint.y = totals[key];
                chartData.push(thisPoint);
            }
            
            return chartData;
        }
        
        //create the chart on document load
        $(function () 
        {
            chart = new Highcharts.Chart({
                chart: {
                    type: 'pie',
                    options3d: {
                        enabled: true,
                        alpha: 45,
                        beta: 0,
                    },
                    renderTo: 'container'
                },
                title: {
                    text: 'Told You So'
                },                
                plotOptions: {
                    pie: {
                        depth: 25
                    }
                },
                series: [{
                    data: []
                }]
            });
            
            //set interval timer to poll the document every 10 seconds
            setInterval(function(){
                getData(docSource,function(result){
                    chart.series[0].setData(translateDataToHighChartFormat(result));
                    
                });
            },10000);
            
            //get the data one initially so we don't have to wait for the first delay to get data
            getData(docSource,function(result){
                chart.series[0].setData(translateDataToHighChartFormat(result));
                $('#Loading').hide();
            });
        });    
    </script>
    <div id="container" style="height: 400px"></div>
    <div id="Loading" style="text-align:center; font-weight:bold; font-size: 24px">Loading Chart Data Please Wait</div>
</apex:page>

If everything has gone smoothly, you should end up with something that looks like this

chart

With our page alive, it’s a simple matter to add it to a Salesforce site. Anyone can view it, and anyone you give the form link to will be able to add data to it. As data is added the chart will automatically redraw itself every 10 seconds with the new data set. Then it was just a simple matter of having the chart open on some computer and using the chrometab app for chrome to send it to my chromecast. Now we can be reminded of how stupid I am all the time….. what have I done?

One response

  1. Haha, cool! My girlfriend was also amused by this. 😉

    September 24, 2014 at 12:23 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s