aiguofer/gspread-pandas

A package to easily open an instance of a Google spreadsheet and interact with worksheets through Pandas DataFrames.

gspread-pandas's Issues

Documentation - Getting Started - bug in sample code

Code from:
https://gspread-pandas.readthedocs.io/en/latest/getting_started.html
spread.update_cells('A1', 'A1', ['Created by:', spread.email])

Should be:
spread.update_cells('A1', 'B1', ['Created by:', spread.email])

ConfigException from removing openid from scope

This is related to the v2 branch.

So by removing openid from the oauth scope, the following config exception occurs if you try to add a new user, BUT have an existing google secret saved in .config, from a project where openid is manually set as a scope. (I think this was auto-configured for during setup?)

from gspread_pandas import Spread
s = Spread("gspread_test")

# prompts me to go to google oauth page and tells me to paste code in application

ConfigException: Scope has changed from "https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/userinfo.email" to "https://www.googleapis.com/auth/drive openid https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/userinfo.email".

I was able to fix this by either

1. adding openid back to default_scopes
2. creating a new project using the pypi instructions
3. deleting openid as a scope in the original project

Just wanted to call this out - might be helpful to add a message to change either the default scope or modify the project.

Add ability to set dtypes or converters

Currently, when we pull data with df_to_sheet, every column gets set with dtype 'object'. It'd be nice to have a way to provide column dtypes or converters for each column similar to pd.read_csv.

See: https://stackoverflow.com/questions/47507426/pandas-dataframe-filter-by-dateform-that-doesnt-have-dtype

Add function that lists all spreadsheets in a Google Drive folder

Ideally this feature would make it possible for a user to point to a folder within Drive and the output would be a list of Google Sheets inside the folder.

Function cells not returning value

I have a few functions in my Google Sheet but when reading it, it returns #N/A

The function I am using is:
=INDEX(GOOGLEFINANCE("CURRENCY:USDAOA", "close", today()-1), 2, 2)

I tried SUM and it works fine.

README Example URL broken (is now stats.idre.ucla.edu)

I submitted a pull request with the fix.

Error code 429. Quota exceeded for quota group 'ReadGroup' and limit 'USER-100s' of service

Is there a plan to manage the excess of requests to the api? I need to do a massive reading of spreadsheets and I need to implement a backoff to manage the delay of requests to the api. I do not see in the documentation that has been solved previously, any idea?

Instruction to generate OAuth is outdated

At this line:
https://github.com/aiguofer/gspread-pandas/blob/master/README.rst?plain=1#L107

If we try to create an OAuth key right now at GCP, there is no option "Other" for application type.
Although It may be Web App or Desktop?

Thanks

df_to_sheet breaks when dtype="category"

In the df_to_sheet function you use a fill_na which throws an error. If there is a dtype of category you could first add the fill value as a category.

So before this:
df_list = df.fillna('').values.tolist()
You could do:
df[categorical_cols] = df[categorical_cols].cat.add_categories([''])

Gspread 5.0 compatibility

There have been various changes in the gspread library in the past year or so, including some breaking import changes in v5. We need to investigate what changes affect us and update gspread-pandas to match.

We'll need a major version release with the changes.

Add functionality in df_to_sheet to append data to an existing sheet

Hi Diego! Currently in gspread_pandas.spread.Spread().df_to_sheet() you are limited to either wiping the sheet or overwriting existing rows using the replace parameter. How feasible would it be to add an option to append to an existing sheet instead of just overwriting when replace = False? Based on some googling, I think the answer probably lies in values_append within gspread.

If you can get me pointed in the right direction I'll see if I can put together a PR.

EOFError: EOF when reading a line (after "Enter verification code:")

I have the appropriate google_secret.json in the appropriate (.config) directory. When I attempt to run these commands, I get some sort of EOFError:

>>> from gspread_pandas import Spread
>>> spread = Spread('[email protected]', 'my_google_sheet')

The result is (where I never actually have the opportunity to Enter my verification code):

Go to the following link in your browser:

    https://MY_URL

Enter verification code: 
EOFError: EOF when reading a line
---------------------------------------------------------------------------
EOFError                                  Traceback (most recent call last)
<ipython-input-225-7ca7f1c76127> in <module>()
----> 1 spread = Spread('[email protected]', 'foresight_search_terms')
/usr/local/lib/python3.6/site-packages/gspread_pandas/client.py in __init__(self, user, spread, sheet, config)
     75         self._config = config or get_config()
     76         self._creds_file = path.join(self._config['creds_dir'], user)
---> 77         self._login()
     78         self.email = self._get_email()
     79 
/usr/local/lib/python3.6/site-packages/gspread_pandas/client.py in _login(self)
    130             creds = Storage(self._creds_file).locked_get()
    131         else:
--> 132             creds = self._authorize()
    133 
    134         self.client = gspread.authorize(creds)
/usr/local/lib/python3.6/site-packages/gspread_pandas/client.py in _authorize(self)
    122         args = argparser.parse_args(args=['--noauth_local_webserver'])
    123 
--> 124         return run_flow(flow, storage, args)
    125 
    126     def _login(self):
/usr/local/lib/python3.6/site-packages/oauth2client/util.py in positional_wrapper(*args, **kwargs)
    138                 else:  # IGNORE
    139                     pass
--> 140             return wrapped(*args, **kwargs)
    141         return positional_wrapper
    142 
/usr/local/lib/python3.6/site-packages/oauth2client/tools.py in run_flow(flow, storage, flags, http)
    226             sys.exit('Try running with --noauth_local_webserver.')
    227     else:
--> 228         code = input('Enter verification code: ').strip()
    229 
    230     try:
EOFError: EOF when reading a line

documentation vs implementation of Spread.__init__

While trying to minimize API usage, I realized that the implementation and the documentation of Spread.__init__ are inconsistent:

Whereas the documentation states that by default, no sheet is opened, the actual implementation uses 0 as default value, which opens the first sheet.

While I would prefer the documented behavior, I guess, backwards-compatibility concerns will argue against me...

Thanks for creating gspread-pandas, Diego!

Float numbers are exported as strings to google sheets

MacOS High Sierra
gspread-pandas==1.0.3
pandas==0.22.0
jupyter==1.0.0
jupyter-client==5.2.3
jupyter-console==5.2.0
jupyter-core==4.4.0

The result of exporting a data frame to Google Sheets:
spread.df_to_sheet(tracked, index=False, sheet='Dashboard', start='A3', replace=True)

Let me know if you need anything else!

Run gspread-pandas on a remote server and authenticate at the user browser using OAuth

Is it possible to run the gspread-pandas on a remote-server (like aws ec2) and allow the user to authenticate using their local browser (google chrome on their machines)?

I have a use case where I'd create ephemeral VMs/Containers and let users SSH into it and thei use their own google credentials (which demands OAuth).
I tried this but when the gspread-pandas prompts the link to login to google and the user open it on their local browser (localhost), they can fill the google info but at the last screen it can't redirect back to the server, and the terminal which is running the python script keeps waiting the login (which already broke).

I may need to open a ssh-tunnel between local host and the remote server so the python script on remote can listen to the 8182 of the local browser?

Thanks

Functions missing in docs

Hi,
I've noticed that on the docs page some functions ('including sheet_to_df()' and 'df_to_sheet()') are missing now.

ServiceAccount does not work correctly

If you have a service account and you give access to it via the email. In this case, the file, when queried by drive looks like this:

{'id': 'some id here', 'name': 'some spreadheet name here'}]

This breaks here: https://github.com/aiguofer/gspread-pandas/blob/master/gspread_pandas/client.py#L256-L265

Since there is no parent, it raises an error, as it somehow expects said key to be there.

I hacked around that like this and it works:

    def _list_spreadsheet_files(self, q):
        files = self._query_drive(q)

        for f in files:
            try:
                parent = next(
                    d for d in self._dirs + [self._root] if d["id"] in f.get("parents", {})
                )
                f["path"] = parent.get("path", "/")
            except StopIteration:
                f["path"] = "/"

        return remove_keys_from_list(files, ["parents"])

I'm not really, really sure what I'm doing here, hence I didn't make a PR. Happy to do so, if you think this would work.

Specifying only one of `freeze_index` or `freeze_headers` if `df_to_sheet()` fails

import gspread_pandas as gp
import pandas as pd
df = pd.DataFrame([{'a': 1, 'b': 2}])
spread = gp.client.Spread(...)

# OK
spread.df_to_sheet(df, sheet='Sheet1')
# OK
spread.df_to_sheet(df, sheet='Sheet2', freeze_index=True, freeze_headers=True)

# FAILS
spread.df_to_sheet(df, sheet='Sheet3', freeze_index=True)
# FAILS
spread.df_to_sheet(df, sheet='Sheet4', freeze_headers=True)

Output:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "</Users/vshih/.virtualenvs/salesforce_asset/lib/python3.4/site-packages/decorator.py:decorator-gen-15>", line 2, in df_to_sheet
  File "/Users/vshih/.virtualenvs/salesforce_asset/lib/python3.4/site-packages/gspread_pandas/client.py", line 243, in _ensure_auth
    return func(self, *args, **kwargs)
  File "/Users/vshih/.virtualenvs/salesforce_asset/lib/python3.4/site-packages/gspread_pandas/client.py", line 632, in df_to_sheet
    None if not freeze_index else index_size)
  File "</Users/vshih/.virtualenvs/salesforce_asset/lib/python3.4/site-packages/decorator.py:decorator-gen-16>", line 2, in freeze
  File "/Users/vshih/.virtualenvs/salesforce_asset/lib/python3.4/site-packages/gspread_pandas/client.py", line 243, in _ensure_auth
    return func(self, *args, **kwargs)
  File "/Users/vshih/.virtualenvs/salesforce_asset/lib/python3.4/site-packages/gspread_pandas/client.py", line 675, in freeze
    'requests': create_frozen_request(self.sheet.id, rows, cols)
  File "/Users/vshih/.virtualenvs/salesforce_asset/lib/python3.4/site-packages/gspread_pandas/util.py", line 123, in create_frozen_request
    if rows >= 0:
TypeError: unorderable types: NoneType() >= int()

Adding formulas is now broken

I have some columns that generate formulas for Google sheets. Since the new version, these are now broken since data is now sent with value_input_option='USER_ENTERED'. I had to do this to work around non-JSON serializable items, allowing me to stringify them first.

The two ways to fix this are:

• Create a better way to serialize objects, or stringify only when needed (seems complicated, error prone, and hard to handle dates)
• Leave as is and add an option to pass specific columns as formulas (seems like the better option, but requires a little more effort on the user's part)

parse_sheet_headers breaks when spreadsheet is empty

HEre's how I fixed it (line 42 in util.py)

elif header_rows == 1:

changed to

elif header_rows == 1 and len(headers)>0: col_names = pd.Index(headers[0])

Error in line 147 conf.py?

in conf.py line 147 :

• cfg_file = conf_dir / user is returning an error which is preventing conf.get_creds() from completing.

• Error: "gspread_pandas/conf.py", line 147, in get_creds, creds_file = creds_dir / user, gspread_pandas.exceptions.ConfigException: unsupported operand type(s) for /: 'str' and 'str'

I am trying to get the example code to run in repl.it and need to change the locations of the .config directories. Here is the code I have inserted.

configuration = conf.get_config(conf_dir='gspread_pandas/', file_name='google_secret.json')
conf.get_creds(config=configuration, creds_dir='creds/')

move_file fails to find a folder

Steps to reproduce:

1. I have file, which belongs to google service account and the folder from my account the service account has access to.
2. I call client.move_file and get Exception: Folder does not exist
3. I've looked into code and found out that the client.move_file method looks for folder id and then moves file in it.
4. I can get the folder it by calling client.find_folders. If there was a way to pass the id to client.move_file it would solve the issue.

client.create_folder('test')
client.refresh_directories()
# i see the folder here
client.directories 

# this fails
client.move_file('file_id', 'test') 

TypeError: data type "category" not understood

Environment info

Raspberry Pi 1 Model B
Operating System: Raspbian Jessie (based on Debian 8)
Architecture: armhf
Python version: 3.4.2
gspread version: 0.15.1
pandas version: 0.14.1

Steps undertaken

As per example on https://github.com/aiguofer/gspread-pandas#example

1. Set up and downloaded credentials as per guidance;
2. Replaced "http://www.ats.ucla.edu/stat/data/binary.csv" with "https://stats.idre.ucla.edu/stat/data/binary.csv" as file path has changed;
3. Replaced 'example_user', 'Example Spreadsheet', & sheet='New Test Sheet' with values specific to my project;
4. Run script:
   4a) Authenticated with link & provided activation code when prompted
   4b) Error occurs

Traceback

Traceback (most recent call last):
File "gspreadPandas.py", line 21, in
spread.df_to_sheet(df, index=False, sheet='Sheet1', start='A2', replace=True)
File "", line 2, in df_to_sheet
File "/usr/local/lib/python3.4/dist-packages/gspread_pandas/client.py", line 117, in _ensure_auth
return func(self, *args, **kwargs)
File "/usr/local/lib/python3.4/dist-packages/gspread_pandas/client.py", line 576, in df_to_sheet
df = fillna(df, fill_value)
File "/usr/local/lib/python3.4/dist-packages/gspread_pandas/util.py", line 114, in fillna
for col in df.dtypes[df.dtypes == 'category'].index:
File "/usr/lib/python3/dist-packages/pandas/core/ops.py", line 572, in wrapper
res = na_op(values, other)
File "/usr/lib/python3/dist-packages/pandas/core/ops.py", line 535, in na_op
result = lib.scalar_compare(x, y, op)
File "lib.pyx", line 657, in pandas.lib.scalar_compare (pandas/lib.c:12114)
TypeError: data type "category" not understood

Additional info 1

This script successfully works on the following environment:
Operating System: Windows 7 Pro x64
Architecture: AMD64
Python version: 3.4.0
gspread version: 0.15.1
pandas version: 0.20.3

I have attempted to install pandas 0.20.3 on the Raspberry Pi however whilst most required packages install succsessfully, python3-pandas-lib is not available form armhf therefore this updated version of pandas cannot be fully installed.

Upgrading to Stretch (Debian 9) may potentially fix this, however I am unable to do so due to current configuration limitations.

Failure when `range` tries to fetch too many cells

Will probably need to batch up how many cells to fetch at a time.

How to Auth w/ OAuth 2.0 for Client-side Web Applications

I searched for such an example but could not find one. So asking here.
I'm trying to create a web app using gspread-pandas where people can authenticate with their google accounts.
I've already got my user's access token and other information for the session (using flask_dance.contrib.google)

session['google_oauth_token'] = {'access_token': 'bar', 'expires_at': 1625445840.363251, 'expires_in': 3598.476306, 'id_token': 'foo', 'scope': ['openid', 'https://www.googleapis.com/auth/userinfo.email', 'https://www.googleapis.com/auth/drive.file'], 'token_type': 'Bearer'}

and I have created my OAuth 2.0 Client (Web Application) on Google Console:

config = {"web":{"client_id":"bla",
"project_id":"foo",
"auth_uri":"https://accounts.google.com/o/oauth2/auth",
"token_uri":"https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
"client_secret":"barbar",
"redirect_uris":["https://mysite.com/login/google/authorized"]}}

I'm converting the session info to a google.oauth2.credentials.Credentials object like:

from google.oauth2.credentials import Credentials
session['credentials'] = Credentials(session['google_oauth_token']['access_token'],
        id_token=session['google_oauth_token']['id_token'],
        client_id=config['web']['client_id'],
        client_secret=config['web']['client_secret'],
        scopes =  ['https://www.googleapis.com/auth/userinfo.email',
                        'openid', 'https://www.googleapis.com/auth/drive.file'])

and trying this:

spread = Spread('trying', creds=session['credentials'],config=config, create_sheet=True)

I got this error:

 File "/srv/w/venv/lib64/python3.6/site-packages/gspread_pandas/spread.py", line 131, in __init__
   self.client = Client(user, config, scope, creds)
 File "/srv/w/venv/lib64/python3.6/site-packages/gspread_pandas/client.py", line 101, in __init__
   self._root = self._drive_request(file_id="root", params={"fields": "name,id"})
 File "/srv/w/venv/lib64/python3.6/site-packages/gspread_pandas/client.py", line 202, in _drive_request
   res = self.request(method, url, params=params, json=data)
 File "/srv/w/venv/lib64/python3.6/site-packages/gspread_pandas/util.py", line 313, in request
   raise error
 File "/srv/w/venv/lib64/python3.6/site-packages/gspread_pandas/util.py", line 300, in request
   return ClientV4.request(client, *args, **kwargs)
 File "/srv/w/venv/lib64/python3.6/site-packages/gspread/client.py", line 76, in request
   raise APIError(response)

gspread.exceptions.APIError: {'errors': [{'domain': 'global', 'reason': 'notFound', 'message': 'File not found: 0AAZoBnp_Ax6Uk9PVA.', 'locationType': 'parameter', 'location': 'fileId'}], 'code': 404, 'message': 'File not found: 0AAZoBnp_Ax6Uk9PVA.'}

Any guidance/suggestions on how to authenticate w/ OAuth 2.0 for Client-side Web Applications?
Thank you.

Issue in frozen column count

Hello Diego,
I noticed one pretty minor issue while fetching frozen column count that resulted in error in sheet with frozen column for latest version.

frozenColCount should be replaced by frozenColumnCount in

gspread-pandas/gspread_pandas/spread.py

Line 592 in d04e57d

frozen_cols = self._sheet_metadata["properties"]["gridProperties"].get(

.

Adding the packages to AWS Lambda layers

Hello,
So I tried to set-up the package in a layer in AWS Lambda to use it later in a function.

Unfortunately, I can't seem to make the env variable work or set it properly so it can read the google_secret.json

To prepare the lambda package I:

1. Created an EC2 instance
2. Created a local directory - build/python/lib/python3.7/site-packages
3. Installed all the packages I use in that directory, including gspread_pandas
4. Zipped the python directory
5. Uploaded it to S3 and then used it as a layer

The process worked for all the other packages except for this one, as I didn't have the config pointing to the google_secret.json

As a fix I tried to:

1. Change this path in the config file
   _default_dir = ( Path(environ.get("$XDG_CONFIG_HOME", Path(environ.get("HOME")) / ".config")) / "gspread_pandas" )
   to a path I created in the python directory (that was zipped) where I stored the google_secret.json "python/config/gspread_pandas/"
2. Create a file google_secret.json in the lambda function, create a environment variable "GSPREAD_PANDAS_CONFIG_DIR" and set it the file's path

None of the above worked, unfortunately.

In both cases, I get the following error
{ "errorMessage": "expected str, bytes or os.PathLike object, not NoneType", "errorType": "TypeError", "stackTrace": [ " File \"/var/lang/lib/python3.7/imp.py\", line 234, in load_module\n return load_source(name, filename, file)\n", " File \"/var/lang/lib/python3.7/imp.py\", line 171, in load_source\n module = _load(spec)\n", " File \"<frozen importlib._bootstrap>\", line 696, in _load\n", " File \"<frozen importlib._bootstrap>\", line 677, in _load_unlocked\n", " File \"<frozen importlib._bootstrap_external>\", line 728, in exec_module\n", " File \"<frozen importlib._bootstrap>\", line 219, in _call_with_frames_removed\n", " File \"/var/task/lambda_function.py\", line 8, in <module>\n import gspread_pandas\n", " File \"/opt/python/lib/python3.7/site-packages/gspread_pandas/__init__.py\", line 5, in <module>\n from .client import Client\n", " File \"/opt/python/lib/python3.7/site-packages/gspread_pandas/client.py\", line 15, in <module>\n from gspread_pandas.conf import default_scope, get_creds\n", " File \"/opt/python/lib/python3.7/site-packages/gspread_pandas/conf.py\", line 25, in <module>\n Path(environ.get(\"$XDG_CONFIG_HOME\", Path(environ.get(\"HOME\")) / \".config\"))\n", " File \"/var/lang/lib/python3.7/pathlib.py\", line 1022, in __new__\n self = cls._from_parts(args, init=False)\n", " File \"/var/lang/lib/python3.7/pathlib.py\", line 669, in _from_parts\n drv, root, parts = self._parse_args(args)\n", " File \"/var/lang/lib/python3.7/pathlib.py\", line 653, in _parse_args\n a = os.fspath(a)\n" ] }

I have to mention that I don't have much experience with coding, so I apologize in advance for the poor explanation.

Any help would be greatly appreciated!

How to work with colab (finds only subset of all sheets)

Hi,
I'm trying to figure out how to pass the creds to Spread.

I'm in Colab, so it's a bit different than the standard user.
To use gspread (not the pandas version) I authenticate like so:

from google.colab import auth
auth.authenticate_user()
import gspread
from oauth2client.client import GoogleCredentials
gc = gspread.authorize(GoogleCredentials.get_application_default())

But using gspread_pandas.spread.Spread('sheetname',creds=?) I'm lost as to what to supply here.

https://aiguofer.github.io/gspread-pandas/ == 404 Not found

Add option to create filters on sheet

Google Sheets V4 API provides ability to add filters to a worksheet. It'd be nice to have a flag in df_to_sheet to create a filter on the newly uploaded spreadsheet.

See: https://sites.google.com/site/scriptsexamples/learn-by-example/google-sheets-api/filters

Relevant code section:

  var filterSettings = {
    "range": {
      "sheetId": sheetId,
      "startRowIndex": 0,
      "endRowIndex": lastRow,
      "startColumnIndex": 0,
      "endColumnIndex": lastColumn
    }
  };
  var requests = [{
    "setBasicFilter": {
      "filter": filterSettings
    }
  }];
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);

Incorrect behavior when reading merged cells

When reading in merged cells, only the first cell in the merged range gets the correct value. This can be especially frustrating when using multi-level headings since they won't import correctly into the DataFrame.

Digging into this, it looks like it's an issue with the older Sheets APIs. This seems to have been fixed in v4, but gspread uses v3. I might look into adding a workaround using v4 directly in gspread_pandas.

Improve method for changing config location

It'd be nice to have a better way to change the default location for the config within a project or app. This should also be better documented.

See https://stackoverflow.com/questions/47384121/while-working-with-gspread-pandas-module-i-want-to-change-default-dir-of-the-mo

Make `user` parameter optional

I originally developed this with a multi-user environment in mind and didn't think too much about it before open sourcing it. Now that I'm not using it in the same way, I've noticed this is a bit annoying. It'd be great if this was optional and there was just a 'default' user where credentials got stored. However, this would likely end up being a breaking change.

I'm thinking doing a minor version release that deprecates the user_or_creds param and adds a separate user and creds optional params. I could then do a major version release to actually remove it a few months later.

What do others think?

gspread-pandas in Google Colaboratory

gspread- pandas does not appear to work in Google Colaboratory.
It seems that there are issues with authentication - which we have been able to work around - as well as accessing spreadsheets once authentication is working.

First column label missing sometimes

I noticed on a data push that the first column header was missing. All I remember was I had passed index=False. Need to investigate.

Ordering sheets within Spread object

I've gotten a lot of utility from this package, but I couldn't find that there was a method for reordering or reindexing spreadsheets within a Spread object. I have tried assigning back the Spread.sheets in a new order to the Spread.sheets attribute, but get a can't set attribute error. Would this be a new feature request, or am I missing something?

Thank you

Fix for RESOURCE_EXHAUSTED broken

Thanks for your work in this library, it's very handy.

I'm using it for the first time and hitting the same RESOURCE_EXHAUSTED error mentioned in issue #25.

I believe this is due to a change in the error message sent by the Google API from "USER-100s" to ''Read requests per user per 100 seconds".

I've submitted a PR that fixes the issue for me and passes the tests: #41

Google dropping support for OAuth out-of-band (oob) flow

I've been trying to get gspread-pandas to work with user credentials instead of the service account route, and noticed this hard-coded line which I think is impacting how I can use user credentials.

gspread-pandas/gspread_pandas/conf.py

Line 156 in 5c37c6b

config, scope, redirect_uri="urn:ietf:wg:oauth:2.0:oob"

According to google, this flow will not be supported anymore:
https://developers.googleblog.com/2022/02/making-oauth-flows-safer.html#disallowed-oob

latest doc on readthedocs.io is empty

https://gspread-pandas.readthedocs.io/en/latest/gspread_pandas.html is empty.
but stable version works https://gspread-pandas.readthedocs.io/en/stable/gspread_pandas.html.

Look into shared Drives

There's some differences between files in a regular drive and those in shared drives according to the API.

It seems like the current implementation might run into some problems with ^. I don't have shared drives so I haven't tested any of this, nor do I currently have time. I'm creating this issue to remember to look into it when possible, or if someone else can.

Can't clear sheets with frozen or protected ranges

If a sheet has a frozen row or column, clear_sheet won't work. Might need to use API v4 to get information about this and only clear up to the protected/frozen range.

Gspread new versions split models module

Gspread new version 5.0.0 split models.py module and breaks the import of it. Reference here

This issue should be fixed with this PR

Error gspread_pandas.client.Spread

line 91, in init
self._config = config or get_config()

/conf.py", line 33, in get_config
cfg = cfg[cfg.keys()[0]]

TypeError: 'dict_keys' object does not support indexing

Is python 3 not supported?

Error adding permissions to the user

fails on any argument to add_permissions method

Add ability to set ValueInputOption in df_to_sheet

Hey Diego,

I recently ran into an issue where I needed to export dataframe containing a Version Number column. I noticed that my string value "6.10" was being interpreted as a float and getting converted to "6.1" in the google sheet.

After messing around with Spread, I noticed that it was coming from this line from Spread.update_cells from gspread_pandas.client

self._retry_func(partial(self.sheet.update_cells, cells, "USER_ENTERED"))

changing the value_input_option parameter from "USER_ENTERED" to "RAW" fixed this issue.

Would it be possible to change that method to take a kwarg instead?

I was also thinking it might be helpful to add the ability to map the input to certain columns and default the rest to USER_ENTERED.

I can also take a stab at it on a personal fork if that'd help - thanks!

Cannot Find Spreadsheet

Hi,

I was using v1.2.2 and my script was connecting, reading and writing to my Google sheet as expected. After upgrading to v2.1.2 I am receiving

Traceback (most recent call last):
File "C:/MyWork/Python/Backup/RPMSheetWriter/main.py", line 64, in
config=global_list.spread_config)
File "C:\MyWork\Python\Backup\RPMSheetWriter\venv\lib\site-packages\gspread_pandas\spread.py", line 130, in init
self.open(spread, sheet, create_sheet, create_spread)
File "C:\MyWork\Python\Backup\RPMSheetWriter\venv\lib\site-packages\gspread_pandas\spread.py", line 199, in open
self.open_spread(spread, create_spread)
File "C:\MyWork\Python\Backup\RPMSheetWriter\venv\lib\site-packages\gspread_pandas\spread.py", line 249, in open_spread
raise new_error
gspread.exceptions.SpreadsheetNotFound: Spreadsheet not found

When I use the client.list_spreadsheet_files() it does list my sheet, but if I try to read or write to it, I get the exception.

Any help will be appreciated.

The service is currently unavailable error

Gspread-pandas version 2.1.2 show error on trying to read the spreadsheet

APIError: {
  "error": {
    "code": 503,
    "message": "The service is currently unavailable.",
    "status": "UNAVAILABLE"
  }
}

Add ability to set ValueRenderOption in sheet_to_df

First of all, I love this project!
Just came across it and am very happy to find the ability to set ValueInputOption in df_to_sheet (#26). Analogously, it'd be great to be able to set ValueRenderOption in sheet_to_df.
The ability to get the unformatted values or see which formulas are entered into each cell would be very useful.

Implementation notes:
There are two ways to support this - either allowing to set ValueRenderOption for all values (would require burnash/gspread#648 to be merged), or allowing column-wise configuration, like how ValueInputOption is supported today. To remain consistent, I suppose that the latter is preferred. It also does not require any upstream PRs to be merged.

Unlike ValueInputOption, there are two non-default values, so to remain consistent with the raw_column_names parameter (based on the RAW non-default value to ValueInputOption), we should add unformatted_value_column_names and formula_column_names (based on the UNFORMATTED_VALUE and FORMULA non-default values to ValueRenderOption).

Fetching the unformatted and formula columns can then be done using https://gspread.readthedocs.io/en/latest/api.html#gspread.models.Worksheet.col_values, similar to how raw_cells are updated, but with the notable difference that one request per column is required.

gspread-pandas changes font to 24pt bold

MacOS High Sierra
gspread-pandas==1.0.3
pandas==0.22.0
jupyter==1.0.0
jupyter-client==5.2.3
jupyter-console==5.2.0
jupyter-core==4.4.0

The result of exporting a data frame to Google Sheets:
spread.df_to_sheet(tracked, index=False, sheet='Dashboard', start='A3', replace=True)

Unknown config file format

Hi, 'Unknown config file format' error while parsing the google config file.

ConfigException Traceback (most recent call last)
in ()
----> 1 Spread('y','w')

~/anaconda3/lib/python3.7/site-packages/gspread_pandas/client.py in init(self, user_creds_or_client, spread, sheet, config, create_spread, create_sheet, scope)
282 self.client = user_creds_or_client
283 elif isinstance(user_creds_or_client, (basestring, OAuth2Credentials)):
--> 284 self.client = Client(user_creds_or_client, config, scope)
285 else:
286 raise TypeError(

~/anaconda3/lib/python3.7/site-packages/gspread_pandas/client.py in init(self, user_or_creds, config, scope)
72 #: (list) - Feeds included for the OAuth2 scope
73 self.scope = scope
---> 74 self._login(user_or_creds, config)
75
76 def _login(self, user_or_creds, config):

~/anaconda3/lib/python3.7/site-packages/gspread_pandas/client.py in _login(self, user_or_creds, config)
78 creds = user_or_creds
79 elif isinstance(user_or_creds, basestring):
---> 80 creds = get_creds(user_or_creds, config, self.scope)
81 else:
82 raise TypeError(

~/anaconda3/lib/python3.7/site-packages/gspread_pandas/conf.py in get_creds(user, config, scope)
148 return run_flow(flow, storage, args)
149
--> 150 raise ConfigException("Unknown config file format")

ConfigException: Unknown config file format