How to update google sheets with google api using python?

Question

from __future__ import print_function
import pandas as pd
from datetime import date
from apiclient.discovery import build
from httplib2 import Http
from oauth2client import file, client, tools
import gspread
import df2gspread

SPREADSHEET_ID = 'spreadsheet id'
RANGE_NAME = "A1:S3"



def get_google_sheet(spreadsheet_id, range_name):
# Retrieve sheet data using OAuth credentials and Google Python API.

global scopes
global creds
global store
global flow
global service
global gsheet

scopes = 'https://www.googleapis.com/auth/spreadsheets'
# Setup the Sheets API
store = file.Storage('credentials.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('client_secret.json', 
scopes)
    creds = tools.run_flow(flow, store)
service = build('sheets', 'v4', http=creds.authorize(Http()))

# Call the Sheets API
gsheet = 
service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, 
range=range_name).execute()

return gsheet

def gsheet2df(gsheet):
# Converts Google sheet data to a Pandas DataFrame.

header = gsheet.get('values', [])[0]   # Assumes first line is header!
values = gsheet.get('values', [])[1:]  # Everything else is data.
#header = values[0]
if not values:
    print('No data found.')
else:
    df = pd.DataFrame.from_records(values)
    df.columns = header

    return df

global df

def Export_Data_To_Sheets():


    response_date = service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID,
        valueInputOption='RAW',
        range=RANGE_NAME,
        body=dict(
            majorDimension='ROWS',
            values=df.T.reset_index().T.values.tolist())
    ).execute()
    print('Sheet successfully Updated')



gsheet = get_google_sheet(SPREADSHEET_ID, RANGE_NAME)
df = gsheet2df(gsheet)

email_list = df
names = email_list['email']

for name in names:
name = 'test'
Export_Data_To_Sheets()

When I try to update/append a cell or cells in google sheets I get this error: Traceback (most recent call last): File "/Users/adaniels/PycharmProjects/EmailAutomation/venv/AutoEmailScript/FranEmails.py", line 167, in Export_Data_To_Sheets() File "/Users/adaniels/PycharmProjects/EmailAutomation/venv/AutoEmailScript/FranEmails.py", line 66, in Export_Data_To_Sheets response_date = service.spreadsheets().values().update( File "/Users/adaniels/PycharmProjects/EmailAutomation/venv/lib/python3.8/site-packages/googleapiclient/_helpers.py", line 130, in positional_wrapper return wrapped(*args, **kwargs) File "/Users/adaniels/PycharmProjects/EmailAutomation/venv/lib/python3.8/site-packages/googleapiclient/http.py", line 840, in execute raise HttpError(resp, content, uri=self.uri) googleapiclient.errors.HttpError: https://sheets.googleapis.com/v4/spreadsheets/13R_wIcWrX9Xa6ZURnLtuHK5czRfTKVakk6nOWkTjRqA/values/A1%3AS3?valueInputOption=RAW&alt=json returned "Request had insufficient authentication scopes.">

Process finished with exit code 1

email_list is a DataFrame so you cannot call the spreadsheets method like you can on service — It_is_Chris
– It_is_Chris, Commented Apr 9, 2020 at 19:09
originally I had service there, but it said service is not defined, do I have to call my other function in Export_Data_To_Sheets():? — YellowJacket
– YellowJacket, Commented Apr 9, 2020 at 19:46
Hello @YellowJacket, if you try to update a cell, since email_list is a DataFrame just like @Chris has mentioned, why don't you try to call the service but in the gsheet2df directly? Cheers! — ale13
– ale13, Commented Apr 10, 2020 at 11:28
When I call the service in gsheet2df I don't get any errors, but nothing happens. Is it possible to update a single cell at the time in the for loop? — YellowJacket
– YellowJacket, Commented Apr 10, 2020 at 15:56
Hello @YellowJacket, can you log the response of the request? Which code are you receiving? Moreover, what do you mean by "Is it possible to update a single cell at the time in the for loop"? Cheers! — ale13
– ale13, Commented Apr 16, 2020 at 10:15

ale13 · Accepted Answer · 2020-04-22 08:55:35Z

0

The "Request had insufficient authentication scopes." error is due to the fact that the needed scopes were not found.

In your case, you should declare your scope/s like this:

scopes = ['https://www.googleapis.com/auth/spreadsheets']

If you added any new scopes or modified the existing ones, you should delete the already existing credentials.json file since this file contains the authentication for the previous scopes, hence the above error.

Reference

Sheets API Python Quickstart

answered Apr 22, 2020 at 8:55

ale13

6,1423 gold badges13 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

How to update google sheets with google api using python?

1 Answer 1

Reference

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Reference

Comments

Your Answer

Sign up or log in

Post as a guest

Related