Accesso Google Sheet con API Python

È possibile accedere ai dati presenti in un Google Sheet (sia in lettura che in scrittura) utilizzando la libreria ufficiale in Python. Questo apre tutto un mondo in termini di automatizzazione dei processi: ad esempio, posso recuperare delle informazioni da un GSheet e usarle per aggiornare un tool di project management quale Redis; al tempo stesso posso reperire informazioni in qualunque modo e scriverle su un GSheet.

Vediamo un pò come si può fare.

Accesso

Il primo passo è configurare il proprio account Google per consentire l'accesso alle API. Sostanzialmente, quello che si deve fare è creare un nuovo progetto Google Cloud (un modo in cui Google possa identificare le nostre richieste), e abilitare su questo progetto le API GSheet.

Consiglio una lettura di questa guida, in cui sono spiegati tutti i passaggi.

Il risultato finale di questo percorso è l'ottenimento di un file credentials.json che contiene le informazioni necessarie alla libreria per accedere al nostro account (saranno comunque necessarie le credenziali, che verranno inserite in un secondo tempo).

Installazione librerie

Per l'accesso avremo bisogno di un pò di librerie python, che possiamo installare con le consuete modalità.

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

Autenticazione

Il primo passo da fare è chiedere l'autenticazione. L'autenticazione è effettuata con il proprio account Google. Durante la richiesta viene mostrata la consueta schermata di Google in cui si chiede di consentire l' uso del proprio account. Le informazioni scambiate sono poi salvate in un file locale token.json, in modo tale da restare disponibili per le successive esecuzioni senza richiedere ulteriori autorizzazioni.

import os
from google.oauth2.credentials import Credentials
from google.auth.transport.requests import Request
from google_auth_oauthlib.flow import InstalledAppFlow

creds = None
token_path = 'token.json', 
credentials_path = 'credentials.json'
scopes = ['https://www.googleapis.com/auth/spreadsheets'] #spreadsheets.readonly

# The file token.json stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.

if os.path.exists(token_path):
    creds = Credentials.from_authorized_user_file(token_path, scopes)

# If there are no (valid) credentials available, let the user log in.

if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file(
            credentials_path, 
            scopes
        )
        creds = flow.run_local_server(port=0)

    # Save the credentials for the next run
    with open(token_path, 'w') as token:
        token.write(creds.to_json())
        
return creds

Si noti che in testa al codice abbiamo definito il percorso dei file sopracitati e l'array scopes, che contiene la lista dei permessi che stiamo richiedendo (nell'esempio è il permesso ad accedere in lettura/scrittura ai gsheet, possiamo usare l'alternativa spreadsheets.readonly per accedere in sola lettura e non rischiare modifiche indesiderate).

Inizializzazione servizio

A partire dall'oggetto autorizzativo costruito prima possiamo ora costruire l'oggetto che rappresenta il servizio, in questo caso il servizio "spreadsheets".

from googleapiclient.discovery import build

service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()

Lettura dati

A questo punto abbiamo tutto quello che ci serve per leggere il contenuto di un gsheet.

SPREADSHEET_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
SPREADSHEET_NAME = 'xxxxxxxxxxxxxxxxxxx'
RANGE = f'{SPREADSHEET_NAME}!A1:AQ'

result = sheet.values().get(
    spreadsheetId=SPREADSHEET_ID,
    range=RANGE
).execute()

values = result.get('values', [])

I parametri da fornire sono lo spreadsheet id (l'id unico dello sheet, visibile nell'url, come da immagine sotto), ...

...il nome della pagina all'interno della spreadsheet (case sensitive), ...

...e il range da leggere, nel formato nome_foglio!prima_cella:ultima_cella.

A questo punto nella variabile values è presente un array di array, che presenta i risultati letti dal gsheet organizzati per righe.

Se usiamo pandas possiamo comodamente convertire questi valori in un dataframe, usando la prima riga per determinare il nome delle colonne.

gsheet = pd.DataFrame(
    values[1:],
    columns=values[0]
)

Nota: la dimensione degli array dipende dai dati effettivamente presenti nel foglio, quindi bisogna prestare attenzione quando si leggono fogli parzialmente vuoti perché pandas potrebbe arrabbiarsi (tipo: ci sono 20 colonne ma gli array letti successivamente hanno solo 18 valori perché le ultime due colonne sono vuote). In questi casi bisogna bonificare gli array letti, ad esempio aggiungendo elementi vuoti fino a quando ciascuna riga ha lo stesso numero di elementi della riga di testata.

Lettura altre informazioni

Oltre al puro contenuto delle celle è possibile leggere informazioni aggiuntive, come ad esempio colori, formattazioni o un eventuale link associato alla cella stessa. Per fare questo si effettua un recupero dei dati aggiungendo il parametro "includeGridData".

response = sheet.get(
  spreadsheetId=SPREADSHEET_ID, 
  ranges=[f'{SPREADSHEET_NAME}!E4:I500'], 
  includeGridData=True
).execute()

A seguito di questo, le informazioni aggiuntive si possono trovare "scavando" nel nuovo oggetto restituito, di struttura non particolarmente user friendly :-). Nel mio caso, per recuperare i link dalla prima colonna del range, ho usato questo codice:

for rowdata in response['sheets'][0]['data'][0]['rowData']:
    first_cell = rowdata['values'][0]
    link = first_cell['hyperlink'] if 'hyperlink' in first_cell else ''

Update

Per modificare/scrivere valori nelle celle si effettua un update batch, quindi un'unica operazione di update composta da un certo numero di singoli aggiornamenti. Questi aggiornamenti specificano un range di celle, e un array di valori, uno per ciascuna cella da aggiornare. Alla fine l'update è eseguito chiamando la funzione "batchUpdate" sull'oggetto "values()" dello sheet.

updates = []

updates.append({
  'range': f'{SHEET_NAME}!A1:C1',
  'values': [[
    "prima cella",
    "seconda cella",
    "terza cella"
  ]]
})

# Batch update per valori
sheet.values().batchUpdate(
  spreadsheetId=SPREADSHEET_ID,
  body = {
    'value_input_option': 'USER_ENTERED',
    'data': updates
  }
).execute()

Esiste anche la possibilità di modificare altre informazioni relative alla pagina a parte i semplici valori, quali ad esempio i colori delle celle (utili per evidenziare valori particolari). Si procede in maniera simile, accodando una serie di update ad un'unica chiamata batch, ma con sintassi leggermente diverse.

Per esempio, per modificare il colore di un range di celle, si usa questo codice. Si noti che bisogna passare un range sia per le righe che per le colonne; occhio che il range parte da zero (la riga numero zero corrisponde alla prima riga nel gsheet, quindi quella con scritto 1) e l'estremo superiore è escluso. Si noti anche che nella sezione "values" dobbiamo passare esattamente un elemento per ciascuna delle celle da aggiornare.

requests = []

# Esempi di colori celle
WHITE = {"red": 1, "green": 1, "blue": 1}
RED = {"red": 1}
GREY = {"red": 0.8, "green": 0.8, "blue": 0.8}
LIGHTYELLOW = {"red": 1, "green": 0.95, "blue": 0.8}

requests.append({
  "updateCells": {
    #range 0-indexed, con estremo superiore escluso
    "range": {
      "sheetId": SHEET_ID,
      "startRowIndex": index-1,
      "endRowIndex": index,
      "startColumnIndex": 0,
      "endColumnIndex": 3
    },
    "rows": [
      {
      "values": [
          {"userEnteredFormat": {"backgroundColor": RED}},
          {"userEnteredFormat": {"backgroundColor": GREY}},
        ]
      }
    ],
    "fields": "userEnteredFormat.backgroundColor"
  }
})

sheet.batchUpdate(
  spreadsheetId=SPREADSHEET_ID, 
  body={
    "requests": requests
  }
).execute()

Voilà! Alla prossima!