# Google Sheets API gebruiken in Python

[[TOC]]

Spreadsheets zijn de lijm van veel bedrijfsprocessen: rapportages, voorraadlijsten, dashboards en ad-hoc-databases. Met de Sheets API in Python lees en schrijf je die data programmatisch. Je vult rapporten met verse cijfers, exporteert query-resultaten of gebruikt een sheet als simpele backend. Het is een van de meest dankbare API's om mee te beginnen.

In dit artikel bouw je lezen, schrijven en opmaken stap voor stap op.

## De service opzetten

Voordat je waarden leest of schrijft, bouw je een service-object met geldige credentials. Voor server-naar-server-automatisering gebruik je doorgaans een service-account; voor scripts namens een gebruiker een OAuth-flow.

```python
from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds = Credentials.from_service_account_file('key.json', scopes=SCOPES)
service = build('sheets', 'v4', credentials=creds)
SHEET_ID = 'jouw-spreadsheet-id'
```

:::tip title="Deel de sheet met je service-account"
Een service-account heeft een eigen e-mailadres (eindigt op `.iam.gserviceaccount.com`). Deel de spreadsheet met dat adres, anders krijg je een 403-fout, ook al klopt je code. Voor alleen-lezen toegang volstaat de scope `spreadsheets.readonly`.
:::

## Waarden lezen

Je leest een bereik in A1-notatie, bijvoorbeeld `Blad1!A1:D10`.

```python
resultaat = service.spreadsheets().values().get(
    spreadsheetId=SHEET_ID, range='Blad1!A1:D10'
).execute()
rijen = resultaat.get('values', [])
```

:::info title="Lege cellen worden weggelaten"
Het resultaat is een lijst van lijsten: elke binnenste lijst is een rij. Lege cellen aan het einde van een rij worden weggelaten, dus controleer de lengte van elke rij voordat je een kolom opvraagt om een `IndexError` te voorkomen.
:::

## Waarden schrijven

Met `update` overschrijf je een bereik. De `valueInputOption` bepaalt of Google je invoer interpreteert.

```python
body = {'values': [['Naam', 'Omzet'], ['Jansen BV', 15000]]}
service.spreadsheets().values().update(
    spreadsheetId=SHEET_ID, range='Blad1!A1',
    valueInputOption='USER_ENTERED', body=body
).execute()
```

:::tip title="USER_ENTERED versus RAW"
Gebruik `valueInputOption='USER_ENTERED'` als je wilt dat Google formules en datums interpreteert zoals een gebruiker die zou typen. Kies `RAW` als je de waarden letterlijk wilt opslaan zonder interpretatie, bijvoorbeeld voor tekst die toevallig op een formule of datum lijkt.
:::

## Rijen toevoegen

Voor het loggen van data wil je vaak onderaan toevoegen zonder te overschrijven. Dat doet `append`.

```python
service.spreadsheets().values().append(
    spreadsheetId=SHEET_ID, range='Blad1!A1',
    valueInputOption='USER_ENTERED',
    insertDataOption='INSERT_ROWS',
    body={'values': [['Nieuwe rij', 42]]}
).execute()
```

Met `insertDataOption='INSERT_ROWS'` schuift Google bestaande rijen op in plaats van ze te overschrijven. De API zoekt vanaf het opgegeven bereik naar de eerstvolgende lege rij onder je bestaande tabel.

## Een rapportage-pijplijn

Een veelvoorkomend patroon is een sheet die elke nacht ververst wordt met data uit een bron. De stappen daarvoor zien er zo uit.

:::howto title="Nachtelijke ververs-pijplijn"
1. Haal verse data op uit je database of API.
2. Wis het oude databereik met `values().clear()`.
3. Schrijf de nieuwe data met `values().update()`.
4. Pas eventueel opmaak toe met `batchUpdate()`.
5. Plan dit dagelijks via een geplande taak (cron) of een Cloud Function met Cloud Scheduler.
:::

## Opmaak en structuur

Voor meer dan platte waarden gebruik je `batchUpdate` met een lijst van request-objecten. Daarmee voeg je bladen toe, bevries je rijen, kleur je cellen of voeg je voorwaardelijke opmaak toe.

:::howto title="Opmaak toepassen met batchUpdate"
1. Bouw een lijst van request-objecten op, elk met een bewerking.
2. Verpak ze in een body met de sleutel `requests`.
3. Roep `spreadsheets().batchUpdate()` aan met `spreadsheetId` en de body.
4. Alle bewerkingen worden atomair in een keer toegepast: faalt er een, dan faalt het geheel.
:::

:::warn title="Werk in batches, niet per cel"
Honderden losse update-calls lopen snel tegen de rate limits aan en zijn traag. De Sheets API hanteert quota per minuut per gebruiker en per project. Met `batchUpdate` of een enkele `update` over een groot bereik bespaar je quota en tijd aanzienlijk. Bouw daarnaast een retry met exponentiele backoff in voor 429- en 503-antwoorden.
:::

## Veelgestelde vragen

:::faq
### Hoe vind ik de spreadsheet-ID?
Het is het lange deel in de URL tussen `/d/` en `/edit` wanneer je de sheet in de browser opent.

### Kan ik meerdere bereiken in een keer lezen?
Ja, gebruik `values().batchGet()` met meerdere `ranges` om in een enkele call meerdere bereiken op te halen. Dat scheelt round-trips en quota.

### Hoe maak ik een nieuwe spreadsheet aan?
Roep `spreadsheets().create()` aan met een body die de titel en eventuele bladen bevat. In het antwoord krijg je de nieuwe `spreadsheetId` terug.

### Werkt de Sheets API met grafieken?
Ja, via `batchUpdate` met een `addChart`-request voeg je grafieken programmatisch toe op basis van een databereik.

### Waarom krijg ik een 403-fout terwijl mijn code klopt?
Vrijwel altijd is de sheet niet gedeeld met het juiste account. Deel hem met het e-mailadres van je service-account, of controleer of de OAuth-gebruiker schrijfrechten heeft.

### Wat is het verschil tussen update en append?
`update` overschrijft exact het bereik dat je opgeeft. `append` zoekt de onderkant van je bestaande tabel op en voegt nieuwe rijen daaronder toe, ideaal voor logs.
:::

Met de Sheets API in Python automatiseer je rapportages en datastromen die anders veel handmatig knip- en plakwerk zouden kosten.