Gegevens lezen uit een Google Spreadsheet met Apps Script

Gegevens lezen uit een Google Spreadsheet is een van de eerste dingen die je met Apps Script wilt doen. Bijna elke automatisering begint met data, en in Workspace staat die data vaak in Sheets. In dit artikel leer je hoe je een blad opent, cellen en bereiken leest en de waarden efficient verwerkt.

Het juiste blad openen

Voordat je iets kunt lezen, moet je het juiste spreadsheet en blad te pakken hebben. Werk je met een container-bound script (een script dat aan het bestand hangt), dan gebruik je getActiveSpreadsheet(). Werk je vanuit een los script, dan open je het bestand op id met openById().

function openBladen() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const blad = ss.getSheetByName('Klanten');
  Logger.log('Aantal rijen met data: ' + blad.getLastRow());
}

getSheetByName() is veiliger dan getActiveSheet(), omdat je expliciet het blad kiest en niet afhankelijk bent van welk tabblad de gebruiker open heeft.

Een enkele cel lezen

De eenvoudigste leesactie is een losse cel. Je selecteert het bereik met A1-notatie en vraagt de waarde op:

function leesEenCel() {
  const blad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Klanten');
  const naam = blad.getRange('A2').getValue();
  Logger.log('Eerste klant: ' + naam);
}

Let op het verschil: getValue() (enkelvoud) geeft een losse waarde terug, getValues() (meervoud) geeft een array terug. Dit verschil is een veelgemaakte beginnersfout.

Een heel bereik in een keer lezen

Cel voor cel lezen is traag, want elke aanroep gaat naar de servers van Google. Veel sneller is om een heel bereik in een keer op te halen met getValues(). Dat geeft een tweedimensionale array: een lijst met rijen, waarbij elke rij zelf een lijst met celwaarden is.

function leesAlleKlanten() {
  const blad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Klanten');
  const laatsteRij = blad.getLastRow();
  const data = blad.getRange(2, 1, laatsteRij - 1, 3).getValues();

  data.forEach(function(rij) {
    const naam = rij[0];
    const email = rij[1];
    const status = rij[2];
    Logger.log(naam + ' (' + email + ') heeft status ' + status);
  });
}

In getRange(2, 1, laatsteRij - 1, 3) lees je de getallen als: begin op rij 2, kolom 1, lees laatsteRij - 1 rijen en 3 kolommen. Je trekt 1 af omdat rij 1 de kopregel is.

De data doorlopen

Een tweedimensionale array doorloop je met een loop. De moderne, leesbare manier is forEach, maar een klassieke for-loop kan ook en geeft je het rijnummer:

function verwerkMetIndex() {
  const blad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Klanten');
  const data = blad.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {
    const rij = data[i];
    if (rij[2] === 'actief') {
      Logger.log('Rij ' + (i + 1) + ': actieve klant ' + rij[0]);
    }
  }
}

getDataRange() selecteert automatisch alle cellen met inhoud, dus je hoeft de afmetingen niet zelf te berekenen. De loop begint bij i = 1 om de kopregel over te slaan.

De stappen op een rij

Het lezen van een spreadsheet volgt steeds dezelfde vier stappen:

Datums en getallen

Let erop dat getValues() waarden teruggeeft in hun oorspronkelijke type. Een datumcel komt terug als een JavaScript Date-object, een getal als een number en tekst als een string. Wil je de zichtbare, opgemaakte tekst (bijvoorbeeld een geformatteerde datum), gebruik dan getDisplayValues() in plaats van getValues().

Met deze basis kun je elke spreadsheet uitlezen. De volgende stap is wegschrijven: in een dagelijks rapport automatiseren combineer je lezen en versturen tot een werkende automatisering.