GoogleSheet addin error when sheet name ends with number

KT

Hi,

I am using the Googlesheet addin to import table from a Gsheet into Dataiku. I noticed a strange error when the sheet name consistently ends with a number as showed below. In my example here, I place the same contain in sheet "H2O" and "CO2". It can read H2O without any issue. But I got an error when the sheet name is called "CO2". This issue is very consistent no matter where the Gsheet is located or what are the alphabets which come before the number on the Gsheet.

I cannot rename the sheet name and so I am looking for a solution from the code side.

Your insight is highly appreciated.

sheet name = H2O

sheet name = CO2 (same content within the same GSheet)

Alexandru

This thread seems to suggest similar issues and suggests several solutions. https://stackoverflow.com/questions/49821711/gspread-error-code-400-range-namename-exceeds-grid-limits

Since you can't rename the sheet names with something like _CO2_ then you could convert the plugin to dev, please note this will means you will lose future updates for the plugin. Make the following change by quoting tab_id on line 25 and 14. Save reload the plugin and test again.

Other solutions pointed suggest was:

Click on the "Add 1000 rows" button at the bottom of the sheet to fix the problem.

Which may be worth trying.

KT

@AlexT
Thank you so much for the reply and insight. I tried adding 1000 rows to the CO2 tab and now the tab contains 2000 rows but I still got the same error (the error does not correctly reflect the number of rows in the worksheet anymore). The screenshot below showed my attempt.

Regarding the code change, I can definitely change that but I don't have admin access to apply the changed plugin to my instance. So, I will have to import these code as library for my project... May I please have a few examples of how we can systematically call the functions to import the Gsheet then?

Thank you!

added 1000 rows to CO2 tab in the GSheet

use the plug-in to attempt importing the CO2 tabs with 2000 rows

Alexandru

Upon further testing quoting the sheet name doesn't seem to work as suggested. The underlying issue appears to be a limitation in older gspread library versions on how it handles sheet names where the name ends in a number.

Based on testing. I see the issue is no longer present in a later version of gspred 3.6 or later there were change in 3.6 that address this : https://github.com/burnash/gspread/releases/tag/v3.6.0

I'll report this to the plugin team however given the support tier for this plugin we can't make any guarantees on the timeline when we will update this in the actual plugin.

Some possible workarounds for you :

1) Convert to a custom plugin and change the requirements.txt in the plugin. A rebuild the code env.

pyOpenSSL
gspread==3.7.0
oauth2client==4.1.3
python-slugify==4.0.0

2) Create a new custom code environment instead of the managed one for the plugin and switch the plugin to use that with above requirements.

3) This would not be recommended but, and should be used as tests rather then permanent state you can also :

cd DSSDIR/code-envs/python/plugin_googlesheets_managed/bin

./pip3 install gspread --upgrade

If you want to interact with the google sheets API directly from a shared library/recipe you can reference the https://github.com/dataiku/dataiku-contrib/blob/c0f367c4dfe744394e874da61278fb188dc0b0ad/googlesheets/python-lib/googlesheets.py

Hope this helps!

jereze

Hi @KT
,

Thank you for submitting this issue.
I confirm the bug and opened an issue that you can follow on the repo of the plugin.

For now, the easiest workaround is to rename the sheet. Please note that this is not because there is a number at the end of the name but because the name looks like a cell notation (A1, A2, ... CO2, ...).

With best wishes,

Jeremy