Saturday, March 5, 2011

Connecting Spreadsheets To Nimbits

There are many ways to record historical values into Nimbits. You can either write software interfaces using the SDK, or record data manually to points using the Portal, Data Acquisition Studio, or the Android Data Logger.

The fun starts when you use all of the exiting ways to visualize your data as it changes.  The posting will help you get your data into popular spreadsheets.

The posting assumes you have logged into Nimbits and created a data point. For this tutorial, i'm using the point FProbe1 - the point i created in my blog posting showing how to feed a temperature into a data point using a Temp Probe.

You'll also want to set the point to public. Double click on the point to bring up it's properties and check off "Public."  This lets anyone see the current value of your point. There are ways to securly access your non-public points using a Secret key. But for this demo, let's keep it simple.  Since your points and data are connected to your google account, and spreadsheets like excel can't log into Google, we have to either skip the authentication process, or provide nimbits with a secret key so it knows who you are.

Making the point public really simplifies things for you, so for this demo let's do just that: (click to enlarge)

Now that I have a data point, and it's public. It's value can be accessed using a URL in this format:

You can go to that URL right now since my point is public. You'll get one little http response from the system with the current value of that point.

You'll want to replace the point name and your email address. You should be getting excited at this point realizing that if your data is available this way, there are a lot of fun ways to connect to Nimbits.

In Excel 2007 

1. Click on the cell you want your value to show up in
2. Select Data from the Ribbon and click the From Web icon in the Get External Data section
3. Enter your url like the one above and click and click Go.
4. There is no XML, the result of this service is just the value of the point, so the result page isn't pretty. It's ok, just click the yellow arrow next to your current value:

5. Click import and you're done!

6. Let's also setup excel so your values will update automatically. In my case, i have a temp probe feeding values into this point, so i want to see the changing values in excel

On the Data Ribbon, select Properties. Check off Enable Background refresh and to refresh the data every 5 minutes.

You can now use your data feed in your spreadsheet calculations and graphs. Imagine having a master spreadsheet at your office and seeing value coming in as your staff enter data on their Android based phones.

In Google Docs

1. Create a new spreadsheet in Google Docs.
2. Past this in a cell with your url:

You should see your value load. Google Docs will automatically reload your data. Enjoy!

1 comment:

  1. Tried this out and it works perfectly!

    Is there a way to essentially create a data log of your points with timestamps continuosly in google docs?

    In other words the function: current value? poiont= posts the current value in a cell in google docs.

    Is there a way to add a timestamp in another column, and then move down a row to enter the next data point and timestamp to essentially create a data log?