Solved

Help with Google Sheets Excel Add-In - Gap of knowledge

  • 7 August 2023
  • 2 replies
  • 67 views
J
Badge

I know I’m missing some fundamental context of query building - but I just want to cobble something together and could use a bit of assistance.

I’d like to use the Google Sheets Excel Add-In, or even the ODBC Driver for Google Sheets, to pull some data down from a live aggregate that exists in one of our team’s Google Sheets - and automatically push it periodically into an Excel sheet. It’d be the entire contents of one sheet, read-only (mono-directional), placed into a named table in an Excel sheet - easy peasy. Or so I thought.

I initially installed the ODBC driver, connected it successfully, but it only gives me a “This data source contains no visible tables” message. And since there aren’t really any options or configs, I bailed on that and installed the Add-In.

With the add-in, however, I can only seem to get it to list my account’s folders, workbooks, and sheets.  I can’t actually get it to read the contents of a particular sheet. That is, only those three ‘tables’ are available - do I really need to build table and/or create a schema just to bring the entire contents down?

Anyone able to point me in the right direction to doing this, or give some steps?  :D

icon

Best answer by Brooks E 8 August 2023, 20:44

View original

2 replies

B
Userlevel 1
Badge

In terms of connecting to a Spreadsheet stored in a Shared or Team Drive in Google, you will need to make sure that you first have set SupportsAllDrives to True, to enable the ability to query all spreadsheets across your drive. 

By default this is set to false, so if you are missing access to the desired spreadsheets, that is a likely reason why. You can find more on this property below: 

https://cdn.cdata.com/help/RLJ/xls/RSBGoogleSheets_p_SupportsAllDrives.htm

Past that, once you are connected you should be able to see the list of Sheets and Spreadsheets as Tables, using the naming convention Spreadsheet_Sheet. 

While they do show up as a list, you should be able to easily select from the given list in the CData Query editor, highlight the desired Sheet, and then click OK to decide on the columns to include and any modifications to make: 

You can see more about the details of querying data with the Add-In in our documentation below: 

https://cdn.cdata.com/help/RLJ/xls/pg_lsget.htm

If the issue you were seeing was that only the Folders, Sheets, and Spreadsheets Views were available, those are included by default, and would indicate either a lack of access to items in other Drivers causing a lack of results, which would be fixed with SupportsAllDrives, or another underlying issue in the connection. 

Should you have an overflow of Tables after enabling SupportsAllDrives, then you can narrow down what to include using the Spreadsheet, FolderName, and FolderId properties to specify what items are of interest in the connection. 

J
Badge

Well, that worked most excellently! Seems like SupportsAllDrives should just be enabled by default. But hey, it’s all working now. Thanks so much E!

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings