Google Spreadsheets Data API - Read

[Jared_B]

Overview

This is an example of using the Google Spreadsheets Data API in LabVIEW.

Description 

This is the first example of what may be several examples using the Google Spreadsheets Data API in LabVIEW.

The first step towards understanding this application would be to read the associated documentation with the API that Google provides. The VIs try to mimic the standard LabVIEW programming paradigm.

The VIs:

1. Initialize:
   • Authenticates user to a hosted Google Doc account.
2. Get Books:
   • Queries the Google Doc account for the amount of Workbooks
   • Returns the list of Workbooks by name
3. Get Sheets:
   • Queries the chosen Workbook for the amount of Worksheets
   • Returns the list of Worksheets by name
4. Read Sheets:
   • Queries the chosen Worksheet for the amount of non-Null cell values
   • Initializes a 2D Array in LabVIEW based on the Rows/Columns of the chosen Worksheet
   • Populates the individual Cell Values in the 2D Array in a Row/Column fashion

Requirements

• LabVIEW 2012 (or compatible)
• Google Data API v2.2.0.0 (or compatible)

Steps to Implement or Execute Code

1. Install the .NET Google Data API referenced below.
2. Enter Username and Password on the Front Panel
3. Enter the name of the Spreadsheet, and the name of the Workbook you'd like to read.
4. Run the VI.

Additional Information or References

VI Snippet

This was built using the .NET version of the Google Data API v2.2.0.0 found here.

 **This document has been updated to meet the current required format for the NI Code Exchange.**

[huz]

Hello,

This is excellent. I have a quick question. Instead of read, I want to write data to a google doc (Spread Sheet). Do you have any idea how difficult that would be implement?

Regards

Huz

[Jared_B]

Hey Huz,

I've currently got a working VI that will allow you to modify a cell.  But I'm still working on the ability to create both new cells, as well as new files/tables/spreadsheets.  I don't spend too much time working on this, but it should not be too difficult.  The modify is the easiest to implement (building off of the currently posted VIs).  As far as creation of new items (even cells) I haven't explored that area yet.  I'm hoping to continue working on these off-and-on, and get some more goodies posted.

Regards,

Jared Boothe

[huz]

Jared,

What I am really trying to do is use Google Doc Spreadsheet as a data repository for database/collaboration tool. I plan to setup an online spreadsheet template in google docs, where the first row is just headers. Then from LabVIEW vi, i want to dump data into the google spreadsheet in the next row down, everytime a button is pressed. This way, if I have the vi running in multiple locations, I can store the data all in one location (file).  I know this is possible in Excel for local files, but I dunno if I can do something like this in for google docs?

Huz

[Jared_B]

I'm pretty sure you'd be able to do this.  What you'd have your static VI do would be to use the VIs I created above (modified) to read the first row of a sheet.  Then you'd figure out how to create a new row (and individual cells, if necessary) based on the API.  I haven't done this aspect yet, but I don't think it'd be any more difficult than the original 'read' VIs that I created.

I'd have a look at this:  http://code.google.com/apis/spreadsheets/data/2.0/developers_guide_dotnet.html#CreatingRows

Essentially, try to mimic what they are doing in that .NET code, in LabVIEW.  That should be a good starting point.  If you look at the 'Read' stuff, you can see how I translated that into LabVIEW .NET calls.

Good luck!  If you get it up and running nicely, feel free to post it as an add-on to the stuff I've added.  I'd love to have the community really help this take off, as a Google Docs API for LabVIEW would be awesome, in my opinion.

Regards,

Jared Boothe

[AndyVessey]

Jared,

Many thanks for this great and future thinking cloud vi - just what I was looking for. I too like yourself and Huz would like to develop this further to include write capability. Anyone who has developed a write function please post a link/comment.

This GoogleDocRead.vi works at home but not at work as a web proxy server exists. I know the cedentials for the server. This problem occurs at Google API level and probably has nothing to do with LabVIEW.

start>all programs>Google Data API SDK>samples>spreadsheets.

Enter my username and password and the API crashes with Unhandled Exception error (attached)

I think this holds the clue: http://code.google.com/p/google-gdata/wiki/WebProxySetup

Regards

Andy

[CU Boulder]

Thank you for creating this Jared_B, Unfortunately though I enter all of the information (including username, password, Book Name and Sheet Name) and nothing gets returned when I run the VI.  (I attached a screenshot of the Probe reading at the bottom)

Another VI that returns a list of your google docs can be found at https://decibel.ni.com/content/docs/DOC-15430 .  It is then supposed to also tell you the values within those Spreadsheets.  Unfortunately I get this error whenever I try to use it (see below).  It is the most helpful VI I have found so far (with the list of google docs), does anyone know how to make it work all the way though?

I simply am looking for a working VI to read the values out of a Google Spreadsheet! Please help! Thanks!

[AndyVessey]

Hi CU,

First things first... do the Google samples work for you located here: windows- start>all programs>Google Data API SDK>samples>spreadsheets?

These are stand alone apps by Google and do not involve LabVIEW.

If these don'r work, LabVIEW won't either - these must work before proceding with LabVIEW.

Hope this helps

Andy

[CU Boulder]

Hey Andy,

Thanks for the reply... I tried the Google Data API SDK example and it seems to work fine.  Still can't seem to figure out what the problem is.  For now I think I'm just going to have labview run a windows macro that downloads the spreadsheet for use in Labview.

Thank you,

Logan

[jasonlinker]

Has anybody figured out an easy way to upload data into a google spreadsheet?

[dpnsw]

Hi Jasonlinker,

The current issue is that everything in this thread is way out of date. It may work if your docuemnt is completely read write accessable to the world but that isn't an option for me.

I had some success with accessing spreadsheets until google made a mod in the OAuth 2.0 libraries. I was working on it late last year but haven't had time to get back to it.

The easiest way I had to access the spreadsheet was to create an App Script within Google Drive and then call that script from the GoogleScriptAPI. I was able to read and write cells (really basic) but then google updated something in the OAuth libs and my authentication broke. Haven't gotten around to trying a fix that.

The ultimate goal is to get a number of libraries done, SSH, Google Drive and Denso Robotics to publish through the LabVIEW maker hub site. Unfortunately I have a problem project that is chewing up all my time 😞

The OAuth setup is a little involved as you have to set up the "Google Developer's console" to create a CLientID that you can use to log in. It has to have scope assinged against it and then I spent time messing around int he OAuth playground that Google set up to try and get it working.

If you do want to do battle with this I might be able to strip out the sensitive bits of what I have and send it to you or post it here.

Let me know if you would like to play around with it but be warned it is extremely messy as I was basically testing lots of things to try and get it to work.

Oh and there is a backing Visual Studio project that creates the .dll's you need and I think I added a couple of methods in there so I could use the google examples. I have already compiled this and created the dll's required. If Google have updated their source code then it may have fixed some of the things I had a problem with and I could quite quickly update the dll's. Once again let me know if you (or anyone else is interested)

The goal is certainly to get LabVIEW to Google API's working via OAuth 2.0 but if you wait for me it might be 6 months away. Check LabVIEW maker hub occasionally if you can wait.

All the best

David

[jmor2000]

Here is a link to a project linking labview with google.drive API with OAuth 2.0. I have had success with this example, and been able to migrate the method for use with google sheets api.

https://github.com/ni/labview-gdrive

All credit goes to: david-lvtn and maggie-otoole

 

[Student_LV]

Hi!

Unfortuantely, I Entered all the information (username, password, book and sheet names ) but when i run the VI suggested by Jared_B, I get nothing in return and it always crashes with " exception error", 

I need to read data from google spreadsheets in labview using google spreadsheet data API 

Does anyone know how to make it work ?

Please help! Thanks!

[jmor2000]

Full labVIEW library for google drive and google sheets (see the 4th comment)

https://lavag.org/topic/20522-labview-google-drive-and-sheets-server/