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
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
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
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
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 ale13 · Accepted Answer · 2020-04-22T08:55:35

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

How to update google sheets with google api using python?

1 Answers

Reference