Read google spreadsheet in pandas dataframe

Question

Not very experience with API authentication but I cannot figure out how to read a Google Sheet share with me from Python.

I've tried:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name('/Users/alexiseggermont/Downloads/Accountable-7b29dac08324.json', scope)

gc = gspread.authorize(credentials)

wks = gc.open("mysheet").sheet1

This gives me ImportError: cannot import name 'opentype' on line 2.

I then tried:

import oauth2client.client, oauth2client.file, oauth2client.tools
import gspread

flow = oauth2client.client.OAuth2WebServerFlow(client_id, client_secret, 'https://spreadsheets.google.com/feeds')
storage = oauth2client.file.Storage('credentials.dat')
credentials = storage.get()
if credentials is None or credentials.invalid:
    import argparse
    flags = argparse.ArgumentParser(parents=[oauth2client.tools.argparser]).parse_args([])
    credentials = oauth2client.tools.run_flow(flow, storage, flags)

gc = gspread.authorize(credentials)

# when this cell is run, your browser will take you to a Google authorization page.
# this authorization is complete, the credentials will be cached in a file named credentials.dat

This opens a window asking if I want to give my app access to my sheets, and I click yes. But then sheet = gc.open("mysheet").sheet1 gives me a permission error:

APIError: {
 "error": {
  "errors": [
   {
    "domain": "global",
    "reason": "insufficientPermissions",
    "message": "Insufficient Permission"
   }
  ],
  "code": 403,
  "message": "Insufficient Permission"
 }
}

The only suggestion I find to solve this error is to change the 'scope' variable, but there is no scope variable used in that code, so I am confused.

halfer · Accepted Answer · 2021-11-06 01:06:44Z

1

You can add several scopes using spaces. So can you try the following modification?

From :

flow = oauth2client.client.OAuth2WebServerFlow(client_id, client_secret, 'https://spreadsheets.google.com/feeds')

To :

flow = oauth2client.client.OAuth2WebServerFlow(client_id, client_secret, 'https://spreadsheets.google.com/feeds https://www.googleapis.com/auth/drive')

Note :

Before you run the modified script, please remove credentials.dat. By this, credentials.dat is created using new scopes.
In my environment, I confirmed that when the scopes is only https://spreadsheets.google.com/feeds, the same error occurs. When the scopes are https://spreadsheets.google.com/feeds https://www.googleapis.com/auth/drive, no error occurs.

edited Nov 6, 2021 at 1:06

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered May 12, 2018 at 1:26

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Luke Barker · Accepted Answer · 2018-06-27 23:20:32Z

there is also Gspread-Pandas library itself, where the docs have a simple intro to the Oauth part, and in a simpler way: https://github.com/aiguofer/gspread-pandas

Before using, you will need to download Google client credentials for your app.

Client Credentials To allow a script to use Google Drive API we need to authenticate our self towards Google. To do so, we need to create a project, describing the tool and generate credentials. Please use your web browser and go to Google console and :

Choose Create Project in popup menu on the top. A dialog box appears, so give your project a name and click on Create button. On the left-side menu click on API Manager. A table of available APIs is shown. Switch Drive API and click on Enable API button. Do the same for Sheets API. Other APIs might be switched off, for our purpose. On the left-side menu click on Credentials. In section OAuth consent screen select your email address and give your product a name. Then click on Save button. In section Credentials click on Add credentials and switch OAuth 2.0 client ID. A dialog box Create Cliend ID appears. Select Application type item as Other. Click on Create button. Click on Download JSON icon on the right side of created OAuth 2.0 client IDs and store the downloaded file on your file system. Please be aware, the file contains your private credentials, so take care of the file in the same way you care of your private SSH key; i.e. move downloaded JSON to ~/.config/gspread_pandas/google_secret.json (or you can configure the directory and file name by directly calling gspread_pandas.conf.get_config Thanks to similar project df2gspread for this great description of how to get the client credentials.

User Credentials Once you have your client credentials, you can have multiple user credentials stored in the same machine. This can be useful when you have a shared server (for example with a Jupyter notebook server) with multiple people that may want to use the library. The first parameter to Spread must be the key identifying a user's credentials. The first time this is called for a specific key, you will have to authenticate through a text based OAuth prompt; this makes it possible to run on a headless server through ssh or through a Jupyter notebook. After this, the credentials for that user will be stored (by default in ~/.config/gspread_pandas/creds or you can manually set it in GSPREAD_PANDAS_CONFIG_DIR env var) and the tokens will berefreshed automatically any time the tool is used.

Users will only be able to interact with Spreadsheets that they have access to.

Handling Authentication In the backend, the library is leveraging Google's oauth2client <http://oauth2client.readthedocs.io/en/latest/__ to handle authentication. It conveniently stores everything as described above so that you don't have to worry about boiler plate code to handle auth. However, if you need to customize how you handle authentication you can do so in a few different ways. You can change the directory where everything is stored using the GSPREAD_PANDAS_CONFIG_DIR env var. You can also generate your own oauth2client.client.OAuth2Credentials and pass them in when instanciating a Client or Spread object. For other ways to customize authentication, see gspread_pandas.conf.get_config and gspread_pandas.conf.get_creds

Collectives™ on Stack Overflow

Read google spreadsheet in pandas dataframe

2 Answers 2

From :

To :

Note :

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

From :

To :

Note :

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related