What I found

When I retrieve this sheet:
https://docs.google.com/spreadsheets/d/1amynEZWMSHat2HE6MLOn0pAjGS9-tqiq5sj5D2nrDG0/edit?usp=sharing

Containing this data:

gsheets parses this data:

>>> w.to_frame()
    Date    Foobar
0  42936  A
1  42937  B

My code:

>>> s = sheets.get('https://docs.google.com/spreadsheets/d/1lgGi_eEsLWLS8DhLCT_2Xxl-UiLAznOnzB9LBTnl8dA/')
<SpreadSheet 1lgGi...dA u'Untitled spreadsheet'>
>>> w = s.find('Sheet1')
<WorkSheet 0 u'Sheet1' (5x2)>
>>> w['A2']
42936

What I expected

I expected these date strings to be parsed as a string or a datetime object.

>>> w['A2']
2017-07-20

How can I fix it or is the gsheet parser wrong?

Traceback:

Traceback (most recent call last):
  File "C:/CODE FOLDER/Google Sheets/gsheets_grading_script.py", line 72, in <module>
    main()
  File "C:/CODE FOLDER/Google Sheets/gsheets_grading_script.py", line 11, in main
    spreadsheet = sheets.get('https://docs.google.com/spreadsheets/d/1ptJ7vHTFzsrHoL5ZsVB6D7FbZccL8K8Ob28xQ0XvYZc/edit')
  File "C:\CODE FOLDER\Google Sheets\venv\lib\site-packages\gsheets\api.py", line 107, in get
    return self[id]
  File "C:\CODE FOLDER\Google Sheets\venv\lib\site-packages\gsheets\api.py", line 88, in __getitem__
    result = models.SpreadSheet._from_response(response, self._sheets)
  File "C:\CODE FOLDER\Google Sheets\venv\lib\site-packages\gsheets\models.py", line 19, in _from_response
    values = backend.values(service, id, ranges)
  File "C:\CODE FOLDER\Google Sheets\venv\lib\site-packages\gsheets\backend.py", line 68, in values
    response = service.spreadsheets().values().batchGet(**params).execute()
  File "C:\CODE FOLDER\Google Sheets\venv\lib\site-packages\googleapiclient\_helpers.py", line 130, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "C:\CODE FOLDER\Google Sheets\venv\lib\site-packages\googleapiclient\http.py", line 851, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/**long ass link** returned "Range ('('gas007'!GAS7)) exceeds grid limits. Max rows: 1001, max columns: 27">

The link results in this:
https://i.gyazo.com/8345e5a5d26d72dad3ca10a0bf3a79d6.png

I have tried editing the scopes, and even with the so called "admin" scopes it still gives me this error.

This spreadsheet has about 20 sheets, but the sheets are 80x8 at max. The Max rows: 1001, max columns: 27 changes whenever I edit rows, so it's somehow a counter of the rows.

I even tried copying the first sheet, the "('gas007'!GAS7)" one into a seperate spreadsheet, and it parsed just fine.

I'm curious if it has anything to do with the deprecation of OAuth.

To replicate:

Try and do .get(https://docs.google.com/spreadsheets/d/1ptJ7vHTFzsrHoL5ZsVB6D7FbZccL8K8Ob28xQ0XvYZc/edit#gid=1686701513).
This fails for some reason.

Then do .get(https://docs.google.com/spreadsheets/d/1ey4cHVM0HvYZtC4UOoV1DwzZjR-YH6EkUpiHfVQoLvw/edit#gid=0) and it goes through.

The second spreadsheet is actually seriously a copy of the first one, I even used Google's built in tools. But the last one doesn't work.

Since I am developing something on AWS Lambda, I need the API to be able to access Google Sheets using service account. I saw Sheets can only access using API Key and client_json credential files... is it possible to use service account?

How does one pass on credentials to the function get?

This fails:

credentials = gsheets.get_credentials(scopes=url, secrets='credentials.json')
s = gsheets.Sheets().get(url)

With this error code:
ValueError: need credentials or developer_key

According to https://google-auth.readthedocs.io/en/latest/oauth2client-deprecation.html oauth2client should be replaced with google-auth.

Any plans to do this?

No error was thrown. 269 of 399 rows were downloaded. Code is right out of the example at https://pypi.org/project/gsheets/:

 s.sheets[0].to_csv('members.csv', encoding='utf-8', dialect='excel')

The workbook contains multiple sheets. I haven't tried deleting all the other sheets (have to go through the OAuth pain), so that could be it (although the 4 other sheets are small). There is one cross-page link from the sheet I want to another sheet.

Is there a way I can get the second half (specify a row range)?

When reading in a google sheet with "name" as a column heading into a pandas dataframe, dataframe populates the column with the name of the sheet (i.e. "Sheet1", etc) instead of the field data. Issue not occurring when using read_csv() directly from pandas.

How to read local .gsheets file?

Any way to use this API with a service account? I'd really like to start using this package as I already use plenty of the helpers here on the "official" gsheets api from google. But I haven't been able to figure out how to use the service account credentials provided by google.

Is gsheets can create multiple link in single cell like this on on google SpreadsheetApp.

const value = SpreadsheetApp.newRichTextValue()
.setText("foo no baz")
.setLinkUrl(0, 3, "https://bar.foo/")
.setLinkUrl(7, 10, "https://abc.xyz/")
.setTextStyle(7, 10, boldStyle)
.build();

Hi,

I used your gsheets for almost 2 years. Thank you.
Recently I updated pyparsing to 3.0.1 and it showed below error. Any need to upgrade your codes or it's my own issue?
Because I found below error info tips "from gsheets import Sheets".
I found it is working normally when using pyparsing 2.4.7.
Thank you in advance.

File "/Users/tim/Documents/working/py/vera.py", line 41, in
from gsheets import Sheets
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/gsheets/init.py", line 5, in
from .api import Sheets
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/gsheets/api.py", line 5, in
from . import backend
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/gsheets/backend.py", line 5, in
import apiclient
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/apiclient/init.py", line 6, in
from googleapiclient import discovery
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/googleapiclient/discovery.py", line 42, in
import httplib2
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/httplib2/init.py", line 52, in
from . import auth
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/httplib2/auth.py", line 20, in
auth_param_name = token.copy().setName("auth-param-name").addParseAction(pp.downcaseTokens)
AttributeError: module 'pyparsing' has no attribute 'downcaseTokens'

gsheets has a very nice API to read values from a spreadsheet. Are there plans to extend the API to also write values back into the spreadsheet? (or did I miss the functionality?)

any chance can add an option for cache_discovery=False in discovery build

Value in Google Spreadsheet:

https://i.gyazo.com/c437ad1a3117bea98f0f87822ee147e3.png

Value in .csv file:
https://i.gyazo.com/243623e7899ee3334d02e4f5ebee699d.png

(03. mai = 3rd of May in Norwegian)

The code:

    for i, sheet in enumerate(spreadsheet.sheets):

        # SHEET STUFF
        student_id = sheet['D2']
        print(student_id)

        sheet.to_csv('grades/' + student_id + '.csv', encoding='utf-16', dialect='excel-tab')

I'm trying to dump a work to csv, I use oauth2client.service_account to access spreadsheet obj with local json key credentials. I am able to fetch all spreadsheet but cannot dump to csv.

code below:
scope = ['https://www.googleapis.com/auth/spreadsheets', "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"] creds = ServiceAccountCredentials.from_json_keyfile_name('key.json', scope) client = gspread.authorize(creds) key_pair = client.open("test").sheet1 key_pair.to_csv('Spam.csv', encoding='utf-8', dialect='excel')

error:

key_pair.to_csv('Spam.csv', encoding='utf-8', dialect='excel')
AttributeError: 'Worksheet' object has no attribute 'to_csv'

Do I have to use url/id to fetch a worksheet to make it valid for to_csv?

Hey!
Could we please have an option to specify the valueRenderOption setting for the v4 sheets request?

Another method called formula_values() could work too where it just has valueRenderOption: 'FORMULA'.
The reason I need to access formulas is because it's the only way to fetch the image url for cells that hold image content (yup, google sheets cells can hold images now!!). The regular .values() method only returns an empty string.

Thanks for your excellent gsheets. I use it to download CSV from google drive frequently.

It will take about 4s to download one piece based on 200kb. Very slow. I am not sure if it was caused by Google API.
Could you support async? Thus, I am download multi pieces simultaneously maybe.

Donkey Kong Country 3 had the unfortunate fate of being released as the SNES was on it's way out. The Nintendo 64 had been released 2 months prior, which made many people gloss over the game without a second look. Donkey Kong Country 3 also had a (admitedly less devastating) impact on my program when trying to retrieve a certain spreadsheet.

Traceback:

Traceback (most recent call last):
  File "C:\Users\dekuk\PycharmProjects\SheetsProj\main.py", line 168, in <module>
    main()
  File "C:\Users\dekuk\PycharmProjects\SheetsProj\main.py", line 125, in main
    spreadsheet = sheet.get(spreadsheet_id)
  File "C:\Users\dekuk\PycharmProjects\SheetsProj\venv\lib\site-packages\gsheets\api.py", line 141, in get
    return self[id]
  File "C:\Users\dekuk\PycharmProjects\SheetsProj\venv\lib\site-packages\gsheets\api.py", line 122, in __getitem__
    result = models.SpreadSheet._from_response(response, self._sheets)
  File "C:\Users\dekuk\PycharmProjects\SheetsProj\venv\lib\site-packages\gsheets\models.py", line 19, in _from_response
    values = backend.values(service, id, ranges)
  File "C:\Users\dekuk\PycharmProjects\SheetsProj\venv\lib\site-packages\gsheets\backend.py", line 80, in values
    response = request.execute()
  File "C:\Users\dekuk\PycharmProjects\SheetsProj\venv\lib\site-packages\googleapiclient\_helpers.py", line 134, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "C:\Users\dekuk\PycharmProjects\SheetsProj\venv\lib\site-packages\googleapiclient\http.py", line 935, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/XXXXX=json returned "Range ('DKC3'!DKC3) exceeds grid limits. Max rows: 992, max columns: 20". Details: "Range ('DKC3'!DKC3) exceeds grid limits. Max rows: 992, max columns: 20">

Process finished with exit code 1

The googleapi link gives this:

{
  "error": {
    "code": 400,
    "message": "Range ('DKC3'!DKC3) exceeds grid limits. Max rows: 992, max columns: 20",
    "status": "INVALID_ARGUMENT"
  }
}

The problem ended up being the sheet name, which was DKC3. When the range was being interpreted, it was looking for 'DKC3'!DKC3, with the second DKC3 being a cell in the 3000th or so column, which was slightly out of range. Tested with other sheets and this seems to be consistent in behavior. The workaround was renaming the sheet to "DKC 3", and everything works fine now, but that would admittedly work less fine if the sheet was not editable by the user.

So I've just finished creating a program using gsheets and want to compile it to a .exe file using pyinstaller. Turns out the google-api-python-client package is broken and doesn't exactly work.

As referenced in this issue: googleapis/google-api-python-client#876 it could be due to importing apiclient instead of googleapiclient, but I'm not sure.