Accessing google sheet and update it via Python

Question

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,

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

I think it is just anonnymized. But yes, please check the path and the filename if this is really correct. At least the exception says like you have given a wrong path. — Sasku
– Sasku, Commented Nov 22, 2019 at 9:12
@OferSadan docs.google.com/spreadsheets/d/1ZsssThFQ9BU*****AQ7zFSCJpcBOfyTU7z8WFOSoZzQ/edit#gid=0 This is the path i have given and i can access it Or Is there any other thing have to do? — Sandeep
– Sandeep, Commented Nov 22, 2019 at 9:14
@Sasku Path and file name is correct i have cross checked it multiple times and above mentioned is the path i have given, but no luck. ANy other suggetsions or am i doing anything wrong? — Sandeep
– Sandeep, Commented Nov 22, 2019 at 9:15

Ofer Sadan · Accepted Answer · 2019-11-22 09:25:59Z

3

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

edited Nov 22, 2019 at 9:25

answered Nov 22, 2019 at 9:19

Ofer Sadan

12k6 gold badges42 silver badges66 bronze badges

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

Comments

Grabes · Accepted Answer · 2021-08-27 18:54:47Z

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)

Collectives™ on Stack Overflow

Accessing google sheet and update it via Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related