CODEWITHSUNDEEP
pythonpython-3.x
Sandeep
Sandeep

Reputation: 721

Accessing google sheet and update it via Python

I'm using the below code to update the google sheet i have with the data from a PostgreSQL table. The table refresh frequently and i need to update the Google Sheet with the latest data of the table.

I'm new to Google API and went through goggle posts and did all he steps like sharing the google sheet with the client_email, But it is not working.

There are 3 columns as shown below,

enter image description here

The column header are in 3rd row and i need to update the values from 4th row onwards.

Below is the current code,

import psycopg2
import gspread

from oauth2client.service_account import ServiceAccountCredentials
import pprint
#Create scope
scope =  ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']

cnx_psql = psycopg2.connect(host="xxx.xxx.xxx.xx", database="postgres", user="postgres",
                         password="**********", port="5432")
psql_cursor = cnx_psql.cursor()

meta_query = '''select * from dl.quantity;'''
psql_cursor.execute(meta_query)
results = psql_cursor.fetchall()
cell_values = (results)

creds = ServiceAccountCredentials.from_json_keyfile_name('/Users/User_123/Documents/GS/gsheet_key.json',scope)
client = gspread.authorize(creds)

sheet = client.open('https://docs.google.com/spreadsheets/d/***************').sheet1
pp = pprint.PrettyPrinter()

result = sheet.get_all_record()

for i, val in enumerate(cell_values):  
    cell_list[i].value = val  
    sheet.update_cells(cell_list) 

psql_cursor.close()

cnx_psql.close()

Getting the below error,

Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/gspread/client.py", line 123, in open self.list_spreadsheet_files() File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/gspread/utils.py", line 37, in finditem return next((item for item in seq if func(item))) StopIteration

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "/Users/User_123/Documents/Googlesheet_update.py", line 30, in sheet = client.open('https://docs.google.com/spreadsheets/d/********************').sheet1 File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/gspread/client.py", line 131, in open raise SpreadsheetNotFound gspread.exceptions.SpreadsheetNotFound

Upvotes: 1

Views: 1529

Answers (2)

Grabes
Grabes

Reputation: 21

This Snippet of Code will allow you to connect, from there you can look at the documentation to complete the rest of your actions!

from oauth2client.service_account import ServiceAccountCredentials
import gspread

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

#Generate a json file by using service account auth in google developer console
'''
Link: https://console.developers.google.com/
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” and “Done”.
5) Press “Manage service accounts” above Service Accounts.
6) Press on ⋮ near recently created service account and select “Manage keys” and then click on “ADD KEY > Create new key”.
7) Select JSON key type and press “Create”.
8) Go to the google sheet and share the sheet with the email from service accounts.
'''
creds = ServiceAccountCredentials.from_json_keyfile_name('mod.json', scope)
client = gspread.authorize(creds)

sheet = client.open_by_url("#Paste yout google sheet url here").sheet1

data = sheet.get_all_records()

sheet.update_cell(1, 1, "You made it") #Write this message in first row and first column

print(data)

Upvotes: 1

Ofer Sadan
Ofer Sadan

Reputation: 11942

Your code and comments suggests that you are trying to open the spreadsheet using the full URL, but you're using the open function that only works with titles.

From the docs:

You can open a spreadsheet by its title as it appears in Google Docs:

sh = gc.open('My poor gym results')

If you want to be specific, use a key (which can be extracted from the spreadsheet’s url):

sht1 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')

Or, if you feel really lazy to extract that key, paste the entire spreadsheet’s url

sht2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')

In your case the last example is the way to go, so use client.open_by_url instead of client.open

Upvotes: 3

Related Questions