One of the nice things about Google Forms is that you can create a survey or quiz and collect the result into a Google Sheets Spreadsheet. The problem is that Google sheets are not easily consumable as a data source. In most cases, folks just export it to XLSX format to do additional work on it. If you have an ongoing survey or a set of long quizzes this requires a lot maintenance and great care when moving the Results around. A better way would be to consume the Google Sheet as an ODBC Data Source as a dynamic dataset. CDATA has such a utility which I am going to review and document. Disclaimer: My copy of the CDATA driver was donated for me to review.
This is pretty straight forward and just takes a few minutes. My suggestion is to load the 32 and 64 bit ODBC versions as well as everything else. After the install is complete it automatically opens up the CData web page to guide you through configuring an ODBC data source.
This review is going to use Microsoft Access 2010 to “Link” to the Google Sheet. The sheet contains survey results from a group of testers, all using a Google Form to enter the results of their testing efforts. The testers are distributed in multiple locations and are using different laptops including some ChromeBooks.
By “link”ing rather than “Import”ing we should never have to export the Google sheet to XLSX before every reporting session. On some days we might have 3-5 reporting sessions. The reports generated from Microsoft Access 2010 provide status, feedback, and progress for the testing team.
In order for this process to work, you are going to need a Google account with some Google Drive space. This space will contain both the Google Form, which can be accessed by most modern browsers, and a Google Sheet where we will choose to have the results saved.
Here is a sample of what the Google Form looked like. Google Forms is very powerful and has some code capabilities as well as some add-ons to make it a very useful web data collection tool.
Share the Google Form
Since the folks using this form are all over the place, we need to share a link with them that allows them to add new testing results.
An admin user who can edit the form can (Send) the form to a user via e-mail or create a link that can be shared with users or generate embed code to include on another web site. We are going to share the link with the reviewers.
We want to (1) generate a link that can be shared, (2) shorten the link, so it doesn’t look scary and (3) copy the link so we can distribute it to the testers.
A user who has the original link to the form can create a link to share with another user.
In this example, the reviewers include both internal Phoenix Workgroup and external users, so we selected the “Anyone with the link” option
The responses that are collected can optionally be put into a Google Sheet. A nice touch is that Google Forms adds the first column with a timestamp that can be used as a unique identifier. This is the Google Sheet we want to connect, via an ODBC Data Source, to Microsoft Access.
Create ODBC DSN
In this example, we are going to create a Data Source Name (DSN) using the 32 Bit ODBC driver with these steps: (1) Select System DSN, to create a connection that can be used by any user of this workstation, (2) Choose Add tocreate the new DSN, (3) Select the proper driver, in this example, it is the CDATA ODBC for Google spreadsheets and finally (4) Finish.
ClientLogin (username/password authentication) has been officially deprecated since April 20, 2012, and is now no longer available. Instead, use the OAuth 2.0 authentication standard. To authenticate to Google Spreadsheets, you will need to obtain the OAuthClientId and OAuthClientSecret by registering an app with Google Spreadsheets.
See Connecting to Google Spreadsheets to connect to Google Spreadsheets from different types of accounts: Google accounts, Google Apps accounts, and accounts using two-step verification.
OAuth 2.0 requires a bit of fiddling, but it only needs to be done once for the connection.
Open up the Google Admin web site for your Google Account and go to security. This is where we can configure the API’s
Developer Console for API’s
Now we need to go to the Developers Console for API Access to add a new project.
CDATA-ODBC is a known selection, start typing it and select the option when it displays, the project name will be displayed, in this example it is cdata-odbc-140816.
Click on the (Create), then (1) Select credentials, (2) Click on Credentials tab, (3) Click on Create Credentials button then click on the (4) OAuth Client ID.
(1) Enter a product name, in this example cdata-odbc, (2) Save your work so far.
Getting closer to creating that Client ID we need. (1) Select Other, (2) Repeat the project name (cdata-odbc), (3) Click on (Create)
Now comes a critical part, copy this stuff someplace secure because you’re going to need it in the next steps. (1) Client ID, (2) Client Secret and finally (3) (OK)
All that just to get to this point.
I would suggest that you download the JSON file with all the top-secret API stuff and save it in the same secure spot you saved the other stuff in.
The file downloaded is a standard formatted JSON file with all the top-secret goodies in it. Save it someplace secure, don’t let it out of your sight.
I use LastPass for keeping my secrets, passwords, and girlfriends’ phone numbers.
Data Source Name properties
Transfer the OAuth 2 information into the Data Source Name on the ODBC management form. The DSN should NOT have embedded spaces or special characters; this has been known to cause connection issues.
We are so close now I can taste it; we need to identify the single Google Sheet this DSN will reference.
Click on the (Test Connection) and cross your fingers. The first time you use this ODBC-DSN, a browser window will open. Why? Because the very last step is you authorizing the cdata-odbc API to talk with the CDATA ODBC driver.
We are sooooo close now. We have gotten this far, now (1) Allow cdata-odbc to manage the spreadsheets in your Google Drive.
OMG!!! It worked. I need a shot of tequila.
Microsoft Access 2010
Create Linked Table
Either open or create an Access database. (1) We need external Data; (2) it will come from an ODBC source. I wonder where that would come from?
Now we need to “Link” not “Import”. (1) Link, not Import, (2) Looks OK to me
Now we select the DSN (1). The system DSN we created is under the (Machine Data Source) tab, (2) Select the DSN we created then (3) Again, looks OK to me.
We now need to select the (Responses). (1) Is the responses (I don’t know what the item under it is) then (2) Still looks OK to me.
The next step takes a few seconds while the Google OAuth is completed and the Google Sheet is turned into a Linked Table.
View and edit Linked Table
Now by double-clicking on the linked-table Access will display the contents. The Linked Table will have a new column called ID added to it; there is a unique identifier in this column that helps make the CDATA-ODBC capable of editing that row.
Updating information on the table can be done from the table viewer
As the testers add new testing results to the Google Form they will automatically appear here, there is no need to export or copy-paste anything. If we add a row to the Linked Table inside of Access, it will automatically appear in the Google Sheet in the Google Drive.
Linked Table Structure
The CDATA ODBC Linked Tables data types for each of the columns are selected by the ODBC driver based on the format of the Google Sheet column. You may not like the data types selected.
If you attempt to change these you will be greeted with a message which translated means “Tough Luck, live with it”. There are some Access tricks to handle this but that’s another post.
Now we can use the Power of Microsoft Access to produce forms to maintain the Google Sheet or create reports. Most Forms and reports start off with a query to drive them.
This query helps to drive the Microsoft Access form used to update the information from the Google Sheet.
The Microsoft Access reporting capability provides reports from the simple list report all the way to data analytics with business charts.
“See the world as a database” and not like a spreadsheet. I am a database person; I visualize things into tables or collections, rows or documents and SQL or JSON. I’m weird like that since it seems like most other folks use spreadsheets for doing just about everything.
The ability to take a Google Spreadsheet and see it as a database is very powerful, especially considering other Google add-ons like Google Forms, etc. use Google Sheets as their data store.
CDATA has all sorts of drivers to connect to all sorts of data sources that are not typically considered a database, and offers drivers that make it less complicated to connect sources that might otherwise be difficult to connect to as a database.
CDATA isn’t free or open-source and is limited to a Windows environment, but that doesn’t restrict how useful tools like this can be in some customer solutions. One of my mentors, Dr. John Weiner, University of Buffalo, told me “Sometimes free is not the best buy, or even the only buy; be prepared to spend some cash.”
This post was an actual solution done for a customer; some of the details are masked out. The customer has decided to purchase the CDATA driver. I can see additional solutions of this type and a training class or web lecture in the future. You don’t really know something until you teach it.