Google Spreadsheet Query in Redash v5 - SOLVED


#1

Hi,

I recently upgraded a self hosted instance of Redash from v2 to v5. Seemed to get it all working, but have just noticed that Google Spreadsheets datasource is missing.

This was previously working in v2, but now the option to create a Google Spreadsheet datasource is gone. The queries using Google Spreadsheets from the previous version are still there, but won’t update and result in error " Error running query: ‘NoneType’ object has no attribute ‘annotate_query’"

I have tried updating google-api-python-client (now v1.7.4), oauth2client (now v4.1.3) and gspread (now v3.0.1), then restarted Redash, as suggested if having issues with data source availability.

  • Redash Version: 5.0.2+b5485
  • Browser/OS: Chrome

#2

How did you restart Redash?


#3

Thanks for the quick reply!

Tried both “sudo supervisorctl restart all” and complete server reboot.


#4

Try installing gspread version 0.6.2 (this is the version we tested with), as v3 wasn’t tested yet and might have backward incompatible changes.


#5

Uninstalled gspread, installed version 0.6.2, then restarted all. Unfortunately no luck, same results.


#6

You can open Python shell and run the following:

    import gspread
    from gspread.httpsession import HTTPSession
    from oauth2client.service_account import ServiceAccountCredentials

See if any one of them fail – if it does, it will explain your issue.


#7

Great thanks so much Arik!

When I ran:

from oauth2client.service_account import ServiceAccountCredentials

received the following error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/oauth2client/service_account.py", line 26, in <module>
    from oauth2client import crypt
  File "/usr/local/lib/python2.7/dist-packages/oauth2client/crypt.py", line 23, in <module>
    from oauth2client import _pure_python_crypt
  File "/usr/local/lib/python2.7/dist-packages/oauth2client/_pure_python_crypt.py", line 24, in <module>
    from pyasn1_modules.rfc2459 import Certificate
  File "/usr/local/lib/python2.7/dist-packages/pyasn1_modules/rfc2459.py", line 20, in <module>
    from pyasn1.type import opentype
ImportError: cannot import name opentype

So I upgraded google-auth-oauthlib & pyasn1-modules, restarted, and Google Spreadsheets are now there!

Unfortunately I’m still having an issue with some of the associated queries and there isn’t anything obvious to me that is wrong. For a given spreadsheet, some sheets return results without an issue, but others come back with the error “Error running query: **‘NoneType’ object is not iterable**”.

It seems that if a sheet has any text in the columns (excluding headings), then it will fail, but if the data is either numeric or a date, it works. For an example of an extremely simple summary that fails, see below.

Category Counter
Outstanding 13
In Progress 4
Overdue 2

#8

Don’t worry Arik eventually worked it out myself. Needed to update python-dateutil.

Thanks so much for your assistance and all of your work on this software. It is awesome!!


#9

Super. Thank you for the update!