Connecting Xcelsius to Google Spreadsheets
24 Apr
I recently was looking for a way to connect my Xcelsius model to a Google Spreadsheet and thought I would share the process and some of my discoveries.
Installing the add-on
The first thing you will need to do is download the Xcelsius Web 2.0 Components Add-On pack from Business Objects Labs. All the usual disclaimers apply when using a Labs prototype, most importantly:
This is a prototype only and NOT for use in production environments.
Once the you’ve downloaded the add-on, you’ll need to activate it in Xcelsius:
- Go to File > Manage Add-Ons…
- Click Install Add-On.
- Browse to the directory where you downloaded your add-on and select the file Web2Pack.xlx.
- Close the Add-On Manager.
- Exit and restart Xcelsius.
Now you should be all set to use the add-on.
Publishing the Google Spreadsheet
The next thing you should do is make sure your Google Spreadsheet is ready to be used by Xcelsius.
- Open the spreadsheet in your browser.
- Go to Share > Publish as a web page.
- Click Publish now.
- Select the “Automatically re-publish when changes are made” check box and copy the link URL.
Now we are ready to connect the spreadsheet to an Xcelsius model.
Creating a Google Spreadsheet connection
- In Xcelsius, go to Data > Connections…
- Click Add > Google Spreadsheet connector.
- Name your connection and set the URL to the one you copied from the Google Spreadsheet (see step 4 above).
- Bind the Spreadsheet Range and the Insert In range. The easiest thing is to bind them to the same range.
Note: Spreadsheet Range refers to the Google Spreadsheet, while Insert In refers to the spreadsheet within Xcelsius. - Under the Usage tab, select the Refresh on Load check box.
- Close the Data Manager.
Theoretically, you should now be able to bind the data from the Insert In range you selected to Xcelsius components.
Binding the data to an Xcelsius component
The following steps assume my sample data as input.
What I wish I could do in Xcelsius is add some placeholder text for my bound cells so that I can work with them more easily:
Unfortunately, as soon as you add any text to the area reserved for your Google Spreadsheet data, you get an HTTP request error at runtime. So instead, you have to make do with borders and highlight colors to make it easier on yourself.
Here is how you can use my data to create a line chart:
- Add a Line Chart to the canvas in Xcelsius.
- Bind the Chart Title to cell A1 and the Chart Subtitle to cell A7.
- Bind the data to the spreadsheet as follows:
Note that there are 3 series here, you just can’t see their names because they are bound to cells that will be loaded when the connection is refreshed. - Format the chart and dashboard to your liking.
- Save and preview.
Final notes and quirks
Overall, the add-on works quite nicely. However, there are a few quirks that I noticed.
- As mentioned above, you can’t put placeholder text into cells where the Google Spreadsheet data will be inserted. Actually, this behaves somewhat erratically – I was able to do this in one Xcelsius model, but not in another.
- Sometimes, when you save and re-open your file in Xcelsius, the Google Spreadsheet connection information disappears from the Data Manager. It’ll still work, but you won’t be able to easily edit the connection without setting it up again from scratch.
- Blank cells in columns from the spreadsheet are ignored when the data is inserted into Xcelsius. Blank rows, however, are preserved.
So, if my original data looked like this:
It will be inserted like this (note the Homer Simpson row):
- If you want to publish an Xcelsius dashboard connected to Google Spreadsheets online, you’ll run into an error like this:
If anyone figures out how to get around this, please let me know.
So there you have it: a connection to Google Spreadsheets from Xcelsius.
The next thing I want to do is create a Google Gadget that contains my Xcelsius dashboard and insert it into my Google Spreadsheet document. It’s a little circular, but here is my use case: I keep track of bills and personal expenses in a Google Spreadsheet document. I want to create a dashboard that takes in the data from my spreadsheet and provides some interactivity so I can explore where my money is going each month and how I can change my spending to get out of debt faster. Ideally, I want this dashboard right in my bills spreadsheet so I don’t have to go to two different places for my financial needs. I think this is possible to do. I just need to figure out that “security error” issue.
Natasha Lloyd is the User Experience lead for Xcelsius at SAP. 
Thank you for this blog entry. It was pointed out to me on the SAP Xcelsius forum. I am also interested in connecting to Google spreadsheets and I wasn’t aware (yet) of the issues you point out above. Reading this saved me a lot of heartache.Please let us know if you find a way to sort out the security error. Cannot connect to you on LinkedIn (no email address) but please use my email to connect when you can.
Natasha,
You figure out that error that you are receiving yet? Has anyone. I had received the same error, it was helpful to see that it wasn’t me, but I’d really like a solution.
Thanks,
Jim
@James: No, I haven’t had a chance to go back and resolve that. I will see what I can do. Send me an email (nblloyd [at] gmail [dot] com) if you want to me to notify you when I resolve it so you don’t have to keep checking back here.
This is a great blog! The Google Spreadsheet connector is a promising concept, but does not work when published to any website. I do not believe that Google has a crossdomain policy open for an Xcelsius SWF to make calls. It is unfortunate that the labs guys spent the time to get 90% of the way there for a feature that we will probably never see in the product.
Ryan — yup, you nailed it. I just downloaded http://www.google.com/crossdomain.xml — doesn’t allow any access from anywhere, so yes, the only way the connector can work is to be on the local PC…
You could use a PHP script redirector as a workaround
Hi All.
I have managed to get past this problem. Here’s what I did…
I created a Google Spreadsheet (will use the demo URL in my explanation) to track responses submitted via a Google Spreadsheet Form on my website.
I followed all the steps above and when I got to the blue error I took some time to read what it was saying. The URL Format seemed to be the key here.
I looked at the URL that Google generated for my Spreadsheet.
http://spreadsheets.google.com/pub?key=pLFYLJSMK4-8ScLNsvEWOOw=true&gid=0&output=html
I noticed that it had some extra stuff at the end when compared to what the error was showing as the required format. So I removed the following stuff “=true&gid=0&output=html”
That did the trick, I could instantly view the data inside my Xcelsius generated SWF.
Hope this helped.
Hi Ruan,
Thanks for your comment. I went back and checked, and found that I was already using the “clean” version of the URL in my model.
Have you checked that yours works when you publish your SWF to a server? I was able to get it to work locally on my machine, but not when I put it on my server.
Did anybody find a solution for the ‘blank cell’ issue? This happens with every web service within XCelsius and is rally annoying …
This error is all I get
Illegal override of SpreadConnector in com.businessobjects.xcelsius.scomponent.SpreadConnector
In Xcelsius, the preview mode does not stop initializing
The connector doesn’t seem to work. The preview keeps on initializing. I am using xcelsius 2008 sp3. Seems like this connector is not compatible with sp3 version.
Unfortunately, it appears that Google has changed something behind the scenes. I spent quite a bit of time trying to find a resolution, but the connection just doesn’t work anymore. Most likely, the plugin will need to be updated by the original developers in order for it to work again.
For those of your experiencing this problem (preview gets stuck on initializing), you should contact SAP BusinessObjects Innovation Center and ask them to update this plugin.