Oh my god. It's full of code!


Merge Salesforce Package.xml Files

So I’ve recently been asked to play a larger role in doing our version control, which is not my strongest suite so it’s definitely been a learning experience. Our process at the moment involves creating a feature branch in bit bucket for each new user story. Folks do whatever work they need to do, and then create a change set. From that they generate a zip file that contains all the stuff they worked on and a package.xml file (using the super cool ORGanizer chrome plugin I just found out about). Once they get that they send it over to me and I get their changes setup as a feature branch and then request a pull to get it back into the master branch. Now I’m not sure if this is the best way to do things or not but in each new branch we need to append all the contents of the new package.xml into the existing package.xml. Much to my surprise I couldn’t find a quick clean easy way to merge two XML files. Tried a few online tools and nothing really seemed to work right, so me being me I decided to write something to do it for me. I wasn’t quite sure how to approach this, but then in an instant I realized that from my post a couples weeks ago I can convert XML into a javscript object easily. Once I do that then I can simply merge the objects in memory and build a new file. One small snag I found is that the native javacript methods for merging objects actually overwrites any properties of the same name, it doesn’t smash them together like I was hoping. So with a little bit of elbow grease I managed to write some utility methods for smashing all the data together. To use this simply throw your XML files in the packages directory and run the ‘runMerge.bat’ (this does require you to have node.js installed). It will spit out a new package.xml in the root directory that is a merge of all your package.xml files. Either way, hope this helps someone.

UPDATE (5/19): Okay after squashing a few bugs I now proudly release a version of package merge that actually like…. works (I hope). Famous last words I know.
UPDATE (5/20): Now supports automatic sorting of the package members, having XML files in sub-directories in the packages folder, forcing a package version, and merging all data into a master branch package file for continual cumulative add ons.
Download Package Merge Here!

Mass Updating Salesforce Country and State Picklist Integration Values

So it’s Friday afternoon about 4:00pm and I’m getting ready to wrap it up for the day. Just as I’m about to get up I hear the dreaded ping of my works instant messenger indicating I’ve been tagged. So of course I see whats up, it’s a coworker wondering if there is any way I might be able to help with what otherwise will be an insanely laborious chore. They needed to change the ‘integration value’ on all the states in the United States from having the full state name to just the state code (e.g. Minnesota->MN) in the State and Country Picklist. Doing this manually would take forever, and moreover it had to be done in 4 different orgs. I told him I’d see what I could do over the weekend.

So my first thought was of course see if I can do it in Apex, just find the table that contains the data make a quick script and boom done. Of course, it’s Salesforce so it’s never that easy. The state and country codes are stored in the meta data and there ins’t really a great way to modify that directly in Apex (that I know of, without using that wrapper class but I didn’t want to have to install a package and learn a whole new API for this one simple task). I fooled around with a few different ideas in Apex but after a while it just didn’t seem like it was doable. I couldn’t find any way to update the metadata even though I could fetch it. After digging around a bit I decided probably the best way was to simply download the metadata, modify it and push it back. So first I had to actually get the metadata file. At first I was stuck because AddressSettings didn’t appear in the list of meta data object in VScode (I have a package.xml builder that lets me just select whatever I want from a list and it builds the file for me) and didn’t know how to build a package.xml file that would get it. I found a handy stack overflow post that gave me the command

sfdx force:source:retrieve -m Settings:Address

Which worked to pull the data. The same post also showed the package.xml file that could be used to either pull or push that metadata (with this you don’t even need the above command. You can just pull it directly by using ‘retrieve source in manifest from org’ in VS code).

<?xml version="1.0" encoding="UTF-8"?>
<Package xmlns="http://soap.sforce.com/2006/04/metadata">

Now that I had the data the only issue really was that there wasn’t an easy way to just do a find and replace or something to update the file. The value for each state (only in the United States) had to be copied from the state code field into the integration value field. So I decided to whip up a quick nodeJS project to do it. You can download it here (it comes with the original and fixed Address.settings-meta.xml files as well if you just want to get those). It’s a pretty simple script, but it does require xml2js because parsing XML is a pain otherwise.

const fs = require('fs');
var xml2js = require('xml2js');
var parseString = xml2js.parseString;

    const data = fs.readFileSync('Address.settings-meta.xml', 'utf8')

    parseString(data, function (err, result) {

        var root = result.AddressSettings.countriesAndStates[0].countries;

        for(var i = 0; i < root.length; i++)
            var countryName = root[i].integrationValue[0];
            if(countryName == 'United States')
                console.log('Found US!');
                for(var j = 0; j < root[i].states.length; j++)
                    console.log('Changing ' + root[i].states[j].integrationValue[0] + ' to ' + root[i].states[j].isoCode[0]);
                    root[i].states[j].integrationValue[0] = root[i].states[j].isoCode[0];
        var builder = new xml2js.Builder();
        var xml = builder.buildObject(result);
        fs.writeFile("Address.settings-meta-fixed.xml", xml, function(err) {
            if(err) {
                return console.log(err);
            console.log("The file was saved!");
catch (err) 

Output of my script. Always satisfying when stuff works.

With my fixed address settings file the last step was “simply” to push it back into Salesforce. I’ll be honest, I haven’t used SFDX much, and this last step actually took longer than it should have. I couldn’t decide if I should be using force:source:deploy or force:mdapi:deploy. Seeing as I had to do this in a production org originally I thought I had to use mdapi but a new update made that no longer the case. mdapi wanted me to build a zip file or something and I got frustrated trying to figure it out. I’m just trying to push one damn file why should I need to be building manifests and making zip files and whatever?! So after some trial and error with force:source:deploy I found that it could indeed push to prod and would take just a package.xml as its input. Initially it complained about not running any test so I told it to only run local tests. That also failed because some other code in the org is throwing errors. As a work around I simply provided it a specific test to run (ChangePasswordController, which is in like every org) and that worked. The final command being

sfdx force:source:deploy -x manifest/package.xml -w 10 -l RunSpecifiedTests –runtests ChangePasswordController


Hooray it finally worked!

And viola! The fixed metadata was pushed into the org and I spared my coworker days of horrific manual data entry. I know in the end this all ended up being a fairly simply process but it did end up taking me much longer than I initially figured mostly just due to not knowing the processes involved or how to reference the data I wanted so I figured maybe this would save someone some time. Till next time.


Hey all,

So I wanted to just throw this out there, I’ve moved from Minnesota to VERY rural Montana. I traded in my 3 bedroom rambler for a studio cabin on some ranch near the Canadian border. As such my access to technology is somewhat reduced and I don’t know if I’ll be posting as much interesting stuff on this blog for a while. Odds are I’ll have some cool Salesforce stuff from time to time since I am maintaining my employment remotely but I won’t be doing as much at home hacking. If you are curious how things are going, why this happened or just like my writing style I’ve started a new blog detailing my journey. You can check it out here:

Montana Dan Blog

Anyway, I’ll still post what I can but I figured I’d should at least inform the community why I might not be around quite as much. Till next time.


Deep Clone (Round 2)

So a day or two ago I posted my first draft of a deep clone, which would allow easy cloning of an entire data hierarchy. It was a semi proof of concept thing with some limitations (it could only handle somewhat smaller data sets, and didn’t let you configure all or nothing inserts, or specify if you wanted to copy standard objects as well as custom or not). I was doing some thinking and I remembered hearing about the queueable interface, which allows for asynchronous processing and bigger governor limits. I started thinking about chaining queueable jobs together to allow for copying much larger data sets. Each invocation would get it’s own governor limits and could theoretically go on as long as it took since you can chain jobs infinitely. I had attempted to use queueable to solve this before but i made the mistake of trying to kick off multiple jobs per invocation (one for each related object type). This obviously didn’t work due to limits imposed on queueable. Once I thought of a way to only need one invocation per call (basically just rolling all the records that need to get cloned into one object and iterate over it) I figured I might have a shot at making this work. I took what I had written before, added a few options, and I think I’ve done it. An asynchronous deep clone that operates in distinct batches with all or nothing handling, and cleanup in case of error. This is some hot off the presses code, so there is likely some lingering bugs, but I was too excited not to share this. Feast your eyes!

public class deepClone implements Queueable {

    //global describe to hold object describe data for query building and relationship iteration
    public map<String, Schema.SObjectType> globalDescribeMap = Schema.getGlobalDescribe();
    //holds the data to be cloned. Keyed by object type. Contains cloneData which contains the object to clone, and some data needed for queries
    public map<string,cloneData> thisInvocationCloneMap = new map<string,cloneData>();
    //should the clone process be all or nothing?
    public boolean allOrNothing = false;
    //each iteration adds the records it creates to this property so in the event of an error we can roll it all back
    public list<id> allCreatedObjects = new list<id>();
    //only clone custom objects. Helps to avoid trying to clone system objects like chatter posts and such.
    public boolean onlyCloneCustomObjects = true;
    public static id clone(id sObjectId, boolean onlyCustomObjects, boolean allOrNothing)
        deepClone startClone= new deepClone();
        startClone.onlyCloneCustomObjects  = onlyCustomObjects;
        startClone.allOrNothing = allOrNothing;
        sObject thisObject = sObjectId.getSobjectType().newSobject(sObjectId);
        cloneData thisClone = new cloneData(new list<sObject>{thisObject}, new map<id,id>());
        map<string,cloneData> cloneStartMap = new map<string,cloneData>();
        startClone.thisInvocationCloneMap = cloneStartMap;
        return System.enqueueJob(startClone);
        return null;      
    public void execute(QueueableContext context) {
    * @description Clones an object and the entire related data hierarchy. Currently only clones custom objects, but enabling standard objects is easy. It is disabled because it increases risk of hitting governor limits
    * @param sObject objectToClone the root object be be cloned. All descended custom objects will be cloned as well
    * @return list<sobject> all of the objects that were created during the clone.
    public list<id> deepCloneBatched()
        map<string,cloneData> nextInvocationCloneMap = new map<string,cloneData>();
        //iterate over every object type in the public map
        for(string relatedObjectType : thisInvocationCloneMap.keySet())
            list<sobject> objectsToClone = thisInvocationCloneMap.get(relatedObjectType).objectsToClone;
            map<id,id> previousSourceToCloneMap = thisInvocationCloneMap.get(relatedObjectType).previousSourceToCloneMap;
            system.debug('\n\n\n--------------------  Cloning record ' + objectsToClone.size() + ' records');
            list<id> objectIds = new list<id>();
            list<sobject> clones = new list<sobject>();
            list<sObject> newClones = new list<sObject>();
            map<id,id> sourceToCloneMap = new map<id,id>();
            list<database.saveresult> cloneInsertResult;
            //if this function has been called recursively, then the previous batch of cloned records
            //have not been inserted yet, so now they must be before we can continue. Also, in that case
            //because these are already clones, we do not need to clone them again, so we can skip that part
            if(objectsToClone[0].Id == null)
                //if they don't have an id that means these records are already clones. So just insert them with no need to clone beforehand.
                cloneInsertResult = database.insert(objectsToClone,allOrNothing);

                for(sObject thisClone : clones)
                objectIds.addAll(new list<id>(previousSourceToCloneMap.keySet()));
                //get the ids of all these objects.                    
                //get the ids of all these objects.
                for(sObject thisObj :objectsToClone)
                //create a select all query to get all the data for these objects since if we only got passed a basic sObject without data 
                //then the clone will be empty
                string objectDataQuery = buildSelectAllStatment(relatedObjectType);
                //add a where condition
                objectDataQuery += ' where id in :objectIds';
                //get the details of this object
                list<sObject> objectToCloneWithData = database.query(objectDataQuery);
                for(sObject thisObj : objectToCloneWithData)
                    sObject clonedObject = thisObj.clone(false,true,false,false);
                //insert the clones
                cloneInsertResult = database.insert(clones,allOrNothing);
                for(sObject thisClone : clones)
            for(database.saveResult saveResult :  cloneInsertResult)
                else if(allOrNothing)
                    return allCreatedObjects;
            //Describes this object type so we can deduce it's child relationships
            Schema.DescribeSObjectResult objectDescribe = globalDescribeMap.get(relatedObjectType).getDescribe();
            //get this objects child relationship types
            List<Schema.ChildRelationship> childRelationships = objectDescribe.getChildRelationships();
            system.debug('\n\n\n-------------------- ' + objectDescribe.getName() + ' has ' + childRelationships.size() + ' child relationships');
            //then have to iterate over every child relationship type, and every record of that type and clone them as well. 
            for(Schema.ChildRelationship thisRelationship : childRelationships)
                Schema.DescribeSObjectResult childObjectDescribe = thisRelationship.getChildSObject().getDescribe();
                string relationshipField = thisRelationship.getField().getDescribe().getName();
                    system.debug('\n\n\n-------------------- Looking at ' + childObjectDescribe.getName() + ' which is a child object of ' + objectDescribe.getName());
                    if(!childObjectDescribe.isCreateable() || !childObjectDescribe.isQueryable())
                        system.debug('-------------------- Object is not one of the following: queryable, creatable. Skipping attempting to clone this object');
                    if(onlyCloneCustomObjects && !childObjectDescribe.isCustom())
                        system.debug('-------------------- Object is not custom and custom object only clone is on. Skipping this object.');
                    if(Limits.getQueries() >= Limits.getLimitQueries())
                        system.debug('\n\n\n-------------------- Governor limits hit. Must abort.');
                        //if we hit an error, and this is an all or nothing job, we have to delete what we created and abort
                        return allCreatedObjects;
                    //create a select all query from the child object type
                    string childDataQuery = buildSelectAllStatment(childObjectDescribe.getName());
                    //add a where condition that will only find records that are related to this record. The field which the relationship is defined is stored in the maps value
                    childDataQuery+= ' where '+relationshipField+ ' in :objectIds';
                    //get the details of this object
                    list<sObject> childObjectsWithData = database.query(childDataQuery);
                    system.debug('\n\n\n-------------------- Object queried. Found ' + childObjectsWithData.size() + ' records to clone');
                        map<id,id> childRecordSourceToClone = new map<id,id>();
                        for(sObject thisChildObject : childObjectsWithData)
                            //clone the object
                            sObject newClone = thisChildObject.clone();
                            //since the record we cloned still has the original parent id, we now need to update the clone with the id of it's cloned parent.
                            //to do that we reference the map we created above and use it to get the new cloned parent.                        
                            system.debug('\n\n\n----------- Attempting to change parent of clone....');
                            id newParentId = sourceToCloneMap.get((id) thisChildObject.get(relationshipField));
                            system.debug('Old Parent: ' + thisChildObject.get(relationshipField) + ' new parent ' + newParentId);
                            //write the new parent value into the record
                            newClone.put(thisRelationship.getField().getDescribe().getName(),newParentId );
                            //add this new clone to the list. It will be inserted once the deepClone function is called again. I know it's a little odd to not just insert them now
                            //but it save on redudent logic in the long run.
                        cloneData thisCloneData = new cloneData(newClones,childRecordSourceToClone);
                catch(exception e)
                    system.debug('\n\n\n---------------------- Error attempting to clone child records of type: ' + childObjectDescribe.getName());
        system.debug('\n\n\n-------------------- Done iterating cloneable objects.');
        system.debug('\n\n\n-------------------- Clone Map below');
        system.debug('\n\n\n-------------------- All created object ids thus far across this invocation');
        //if our map is not empty that means we have more records to clone. So queue up the next job.
            system.debug('\n\n\n-------------------- Clone map is not empty. Sending objects to be cloned to another job');
            deepClone nextIteration = new deepClone();
            nextIteration.thisInvocationCloneMap = nextInvocationCloneMap;
            nextIteration.allCreatedObjects = allCreatedObjects;
            nextIteration.onlyCloneCustomObjects  = onlyCloneCustomObjects;
            nextIteration.allOrNothing = allOrNothing;
            id  jobId = System.enqueueJob(nextIteration);       
            system.debug('\n\n\n-------------------- Next queable job scheduled. Id is: ' + jobId);  
        system.debug('\n\n\n-------------------- Cloneing Done!');
        return allCreatedObjects;
    * @description create a string which is a select statement for the given object type that will select all fields. Equivalent to Select * from objectName ins SQL
    * @param objectName the API name of the object which to build a query string for
    * @return string a string containing the SELECT keyword, all the fields on the specified object and the FROM clause to specify that object type. You may add your own where statements after.
    public string buildSelectAllStatment(string objectName){ return buildSelectAllStatment(objectName, new list<string>());}
    public string buildSelectAllStatment(string objectName, list<string> extraFields)
        // Initialize setup variables
        String query = 'SELECT ';
        String objectFields = String.Join(new list<string>(globalDescribeMap.get(objectName).getDescribe().fields.getMap().keySet()),',');
        if(extraFields != null)
            objectFields += ','+String.Join(extraFields,',');
        objectFields = objectFields.removeEnd(',');
        query += objectFields;
        // Add FROM statement
        query += ' FROM ' + objectName;
        return query;   
    public void cleanUpError()
    public class cloneData
        public list<sObject> objectsToClone = new list<sObject>();        
        public map<id,id> previousSourceToCloneMap = new map<id,id>();  
        public cloneData(list<sObject> objects, map<id,id> previousDataMap)
            this.objectsToClone = objects;
            this.previousSourceToCloneMap = previousDataMap;


It’ll clone your record, your records children, your records children’s children’s, and yes even your records children’s children’s children (you get the point)! Simply invoke the deepClone.clone() method with the id of the object to start the clone process at, whether you want to only copy custom objects, and if you want to use all or nothing processing. Deep Clone takes care of the rest automatically handling figuring out relationships, cloning, re-parenting, and generally being awesome. As always I’m happy to get feedback or suggestions! Enjoy!


Amazon Alexa is going to run/ruin my life

It was my birthday recently, just turned 28. As a gift to myself I finally decided to order an Amazon Alexa cause I’ve wanted one since I heard about it a few months ago. If you aren’t familiar it’s basically like a ‘siri’ or ‘cortana’ thing that is a stand alone personal assistant device that lives in your home. It’s always on and responds to voice commands from surprisingly far away. It can tell you the weather, check your calendar, manage your shopping list and all that kind of nifty stuff. However, it can do more, much more. Thanks to the ability to develop custom ‘skills’ (their name for apps) and out of the box If This Then That (IFTTT) integration you can quickly start making Alexa do just about anything. I’ve owned it only a day now and I’ve already taught it two new tricks.

Also, if you aren’t familiar with IFTTT it’s an online service that basically allows you to create simple rules that perform actions (hence the name, if this then that). They have the ability to integrate all kinds of different services so you no longer have to be an advanced programmer to automate much of your life. It’s a cool free service and I’d highly recommend checking it out.

You may remember a while back I did that whole write about about making an automatic door locking service software to lock and unlock my front door. I figured a good way to jump into making custom commands would be if I could to see if I could teach Alexa to do it for me upon request. Turns out it was surprisingly easy. Since I already had the web service up and running to respond to HTTP post requests, I simply needed to create an IFTTT rule to send a request when Alexa heard a specific phrase. You may recall that I had some problems with IFTTT not seeming to work for me before, but it seems to now, might have been an error on my part for all I know. Here is the rule as it stands currently.

door 1door 2

Every command issued to Alexa starts with the ‘wake word’ in this case I’ve chosen Alexa (since you can only pick between Alexa, Echo, and Amazon). Second is the command to issue so it knows what service to route the request to. For this the command is ‘trigger’ so Alexa knows to send the request to IFTTT. Then you simple include the phrase to match, and what to do. I decided to make the phrase ‘lock the door’ which when that happens will send a post request to my web server is listening with the given JSON payload. Boom done.

The next thing I wanted to do, and this is still just a very rough outline of a final idea is Chromecast integration. Ideally I’d like to be able to say ‘Alexa trigger play netflix [moviename]’ but as of right now triggers created from IFTTT for Alexa can’t really contain variables aside from just the whole command itself. So I could do ‘Alexa trigger netflix bojack horseman’ and create a specific request just for that show, but there is no way to create a generic template kind of request and pass on the details to the web service that is listening. That aside, what I do have currently is a start.

I found a command line tool that can interact with the chromecast (check this guide for Ā Command Line Chromecast), and then created a execute statment to call that from my web service. My door lock and unlock service already has logic for handling different commands so I just created a new one called ‘play’ that plays my test video.

else if(action == 'play')
	console.log('Casting Requested Thing!');
	var exec = require('child_process').exec;
	var cmd = 'castnow c:\\cast\\testVideo.mp4 --device "Upstairs Living Room"';

	exec(cmd, function(error, stdout, stderr) {

So that turned out to be pretty easy. Small caveat being that castnow is more meant to be an application that is kept open and you interact with to control the video. Since it is being invoked via a web service call it doesn’t really get to ‘interact’ with it. I suppose you might be able to do some crazy shit like keeping open a web socket and continue to pass commands to it, but that’s for another day.

The IFTTT command is basically the same as the door lock one. Just change the command to trigger it, and change the JSON payload to have the action as “play” instead of “lock” or “unlock” and the command gets triggered. I also created a corollary rule and bit of code for stopping the casting of the current video by playing another empty video file (since there isn’t an explicit stop command in the castnow software).

There you have it, with Alexa, IFTTT, and a home web server you can start to do some pretty cool customized automation stuff. I think next up is getting it to order my favorite local pizza for me šŸ˜€

URL Encode Object/Simple Object Reflection in Apex

Hey all,

Kind of a quick yet cool post for you today. Have you ever wanted to be able to iterate over the properties of a custom class/object? Maybe wanted to read out all the values, or for some other reason (such as serializing the object perhaps) wanted to be able to figure out what all properties an object contained but couldn’t find a way? We all know Apex has come a long way, but it still is lacking a few core features, reflection being one of them. Recently I had a requirement were I wanted to be able to take an object and serialize it into URL format. I didn’t want to have to have to manually type out every property of the object since it could change, and I’m lazy like that. Without reflection this seems impossible, but it’s not!

Remembering that the deserialize json method that Apex has is capable of creating an iteratable version of an object by casting it into a list, or a map suddenly itĀ becomes much more viable. Check it out.


    public static string urlEncodeObject(object objectToEncode)
        string urlEncodedString;
        String serializedObject = JSON.serialize(objectToEncode);
        Map<String,Object> deserializedObject = (Map<String,Object>) JSON.deserializeUntyped(serializedObject);
        for(String key : deserializedObject.keySet())
            urlEncodedString+= key+'='+string.valueOf(deserializedObject.get(key))+'&';
        urlEncodedString = urlEncodedString.substring(0,urlEncodedString.length()-1);
        urlEncodedString = encodingUtil.urlEncode(urlEncodedString,'utf-8');
        return urlEncodedString;

There you have it. By simply simply serializing an object, then deserializing it, we can now iterate over it. Pretty slick eh? Not perfect I know, and doesn’t work awesome for complex objects, but it’s better than nothing until Apex introduces some real reflection abilities.

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:

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.


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.


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


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 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
    global static  List<List<String>> importCSV(string url)
         List<List<String>> result = new List<List<String>>(); 
            string responseBody;
            //create http request to get import data from
            HttpRequest req = new HttpRequest();
            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.
                HTTPResponse res = http.send(req);
                responseBody = res.getBody();
                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('"')) {
                } else if (field.startsWith('"')) {
                    makeCompositeField = true;
                    compositeField = field;
                } else if (field.endsWith('"')) {
                    compositeField += ',' + field;
                    makeCompositeField = false;
                } else if (makeCompositeField) {
                    compositeField +=  ',' + field;
                } else {
        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>
        //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)
                function(result, event){
                    if (event.status) {
                {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];
                    totals[name] = 1;
            for(key in totals)
                var thisPoint = new Object();
                thisPoint.name = key;
                thisPoint.y = totals[key];
            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
            //get the data one initially so we don't have to wait for the first delay to get data
    <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>

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


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?