CODEWITHSUNDEEP
python-3.xoauth-2.0google-sheets
Sam Lilienfeld
Sam Lilienfeld

Reputation: 515

Writing Pandas DataFrames to Google sheets: no such file or directory .oauth/drive.json

I've been trying to find a way to read and write data between Pandas and Google sheets for a while now. I found the library df2gspread which seems perfect for the job. Been spending a while now trying to get it to work.

As instructed, I used the Google API console to create my client secrets file and saved it as ~/.gdrive_private. Now, I'm trying to download the contents of a Google spreadsheet as follows:

workbook = [local filepath to workbook in Google Drive folder]
df = g2d.download(workbook, 'Sheet1', col_names = True, row_names = True)

When I run this, it is successfully opening a browser window asking to give my app access to my Google sheets. However, when I click allow, an iPython error is coming up: 

FileNotFoundError: [Errno 2] No such file or directory: '/Users/samlilienfeld/.oauth/drive.json'

What is this file supposed to contain? I've tried creating the folder and including my client secrets again there as drive.json, but this does not work.

Upvotes: 4

Views: 2480

Answers (3)

smcrl
smcrl

Reputation: 11

Here the two functioning ways as of 2019:

1.DateFrame data to Google sheet:

#Import libraries 
import pandas as pd
import warnings
warnings.filterwarnings("ignore")

# Connection to googlesheet
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# From dataframe to google sheet
from df2gspread import df2gspread as d2g

# Configure the connection 
scope = ['https://spreadsheets.google.com/feeds']

# Add the JSON file you downloaded from Google Cloud to your working directory
# the JSON file in this case is called 'service_account_gs.json' you can rename as you wish
credentials =ServiceAccountCredentials.from_json_keyfile_name('service_account_gs.json',
                                                           scope
                                                          )
# Authorise your Notebook with credentials just provided above
gc = gspread.authorize(credentials)

# The spreadsheet ID, you see it in the URL path of your google sheet
spreadsheet_key = '1yr6LwGQzdNnaonn....'

# Create the dataframe within your notebook
df = pd.DataFrame({'number': [1,2,3],'letter': ['a','b','c']})


# Set the sheet name you want to upload data to and the start cell where the upload data begins 
wks_name = 'Sheet1'
cell_of_start_df = 'A1'

# upload the dataframe
d2g.upload(df,
       spreadsheet_key,
       wks_name,
       credentials=credentials,
       col_names=True,
       row_names=False,
       start_cell = cell_of_start_df,
       clean=False)
print ('Successfully updated')

2.Google sheet to DataFrame

from df2gspread import gspread2df as g2d
df = g2d.download(gfile='1yr6LwGQzdNnaonn....',
              credentials=credentials,
              col_names=True, 
              row_names=False)
df

Upvotes: 1

Vik
Vik

Reputation: 11

I did a work around for the time being by passing a pre-authenticated credential file to the g2d call.

I made a gist here (for Python2x but should work for 3x) to save the credential file by passing the secret file (basically ~/.gdrive_private) and the resulting authenticated credential filename to save.

Use the above gist in an standalone script with appropriate filenames and run it from a terminal console. A browser window will open to perform the OAuth authentication via Google, and should give you a token which you can copy paste into the terminal prompt. Here's a quick example:

from gdrive_creds import create_creds


# Copy Paste whatever shows up in the browser in the console.
create_creds('./.gdrive_private', './authenticated_creds')

You can then use the file to authenticate for df2gspread calls. Once you create the cred file using the gist method, try something like this to get access to your GDrive:

from oauth2client.file import Storage
from df2gspread import gspread2df as g2d


# Read the cred file
creds = Storage('./authenticated_creds').get()

# Pass it to g2df (Trimmed for brevity)
workbook = [local filepath to workbook in Google Drive folder]
df = g2d.download(workbook, 'Sheet1', col_names = True, credentials=creds)

df.head()

This worked for me.

Upvotes: 1

Maybelinot
Maybelinot

Reputation: 17

It seems like this issue was because /User/***/.oauth folder wasn't created automatically by oauth2client package (e.g. issue). One of possible solutions is to create this folder manually or you can update df2gspread, issue should be fixed in last version.

Upvotes: 0

Related Questions