A customer asked if there was a way to read a Google Sheets spreadsheet from Crystal so I did some research for her. If you do a web search for “ODBC Google Sheets” you might be fooled into thinking that there are several options. However if you follow them they all point to the same ODBC driver from CDATA. This has a free trial download and costs $200 for a perpetual license or $79 per year for a subscription license.
We downloaded the trial and found that setting it up was pretty straightforward. There was only one thing that wasn’t very clear at first. Google says to register your “application” and get a security token for it. I assumed that this was only if we were coding an application wanted do authentication in the code, so we skipped that step. From Crystal we were able to connect successfully to Google, but we could not see the sheets. All we could see was a list of actions.
After a few more experiments we went back and created an application entry called Crystal in the customer’s Google account. That allowed us to generate a token and a ‘secret’ (their name for a password). Once those were entered in the local ODBC config, Crystal was able to see the tables and things worked normally.