2

I have some data in an Excel sheet, I need to programmatically be able to insert it into a Google Sheet via a short python script. I'm using pygsheets and pandas (if I need it). I have the auth stuff set up and I'm able to access the sheet I want to insert data into; it's just the actual insertion using pygsheets I'm having issues with. Here's some code:

from google.oauth2 import service_account
import pygsheets
import pandas as pd
import json

# set settings file
settings_file = open("/Users/user/Desktop/settings.json", "r").read()
settings = json.loads(settings_file)
creds = settings['oauth_creds']
credentials = service_account.Credentials.from_service_account_info(creds)
scoped_credentials = credentials.with_scopes(
    ['https://www.googleapis.com/auth/spreadsheets']
)

# set dataframe as excel sheet (saved on Desktop)
new_data = pd.read_excel('/Users/user/Desktop/test-data.xlsx', None)

# instantiate pygsheets with credentials
gc = pygsheets.authorize(custom_credentials=scoped_credentials)

# open spreadsheet with URL
sheet_url = settings['sheet_url']
sh = gc.open_by_url(sheet_url)

# activate correct sheet
sheet_name = settings['data_sheet']
wks = sh.worksheet_by_title(sheet_name)

# empty active sheet
wks.clear()

# add dataframe to active sheet
wks.set_dataframe(new_data,(1,1))

When I run the script, I get an error on the last line: AttributeError: 'OrderedDict' object has no attribute 'replace'. Any idea how to properly send data to the Sheet? Also, if it's easier, I can do this with a CSV; I also do not have to use pandas, if it's easier without it. Any help is appreciated!

impostorsyndrome
  • 103
  • 1
  • 10
  • I've temporarily solved my issue by pulling the data from a CSV: `with open('/Users/user/Desktop/test-data.csv', 'rU') as f: reader = csv.reader(f, delimiter=',', quotechar='"') new_data = list(reader)` and then using `wks.append_table(values=new_data)`. – impostorsyndrome May 16 '19 at 21:40

1 Answers1

0

Generate credential file using Google sheets and name it as creds.json:

  1. Enable API Access for a Project if you haven’t done it yet.
  2. Go to “APIs & Services > Credentials” and choose “Create credentials > Service account key”.
  3. Fill out the form
  4. Click “Create key”
  5. Select “JSON” and click “Create”

then try:

import pygsheets
def upload_to_gsheet(df, sheet_number):
    gc = pygsheets.authorize(service_file=/path/to/creds.json)
    sh = gc.open_by_key(spreadsheet_id)
    wks = sh[sheet_number]
    wks.set_dataframe(df, (1, 1))
Kriti Pawar
  • 832
  • 7
  • 15