# SpreadsheetApp service volledig uitgelegd

[[TOC]]

## Wat is de SpreadsheetApp service

De SpreadsheetApp service is de kern van elke Apps Script integratie met Google Sheets. Via deze service heb je volledige controle over spreadsheets, werkbladen, cellen, formules, opmaak en grafieken. Het is een van de meest gebruikte services binnen het Apps Script ecosysteem.

Je opent een spreadsheet op drie manieren:

```javascript
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ss2 = SpreadsheetApp.openById('1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms');
const ss3 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/.../');
```

`getActiveSpreadsheet()` werkt alleen wanneer het script gebonden is aan een spreadsheet (een container-bound script). Voor standalone scripts gebruik je `openById()` met de spreadsheet-ID uit de URL. Die ID is het lange deel tussen `/d/` en `/edit` in de adresbalk.

## Werkbladen en ranges

Nadat je een spreadsheet hebt geopend, werk je met werkbladen (sheets) en ranges:

```javascript
function leesGegevens() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const blad = ss.getSheetByName('Data');

  const waarde = blad.getRange('A1').getValue();
  const bereik = blad.getRange('A1:C10').getValues();

  const rijCount = blad.getLastRow();
  const kolCount = blad.getLastColumn();
  const alles = blad.getDataRange().getValues();

  Logger.log(waarde);
  Logger.log(bereik);
}
```

`getValues()` retourneert een tweedimensionaal array (rijen van kolommen). Dit is veel efficienter dan cel-voor-cel lezen via `getValue()` in een lus. Gebruik altijd batch-operaties om quota en uitvoeringstijd te sparen.

:::tip title="Begin altijd met getDataRange()"
Twijfel je hoe groot je dataset is? Met `getDataRange()` pak je in een keer precies het gebied dat inhoud bevat, zonder dat je vooraf het aantal rijen en kolommen hoeft te kennen. Combineer het met `getValues()` voor de snelste manier om een heel tabblad in te lezen.
:::

## Schrijven naar cellen

```javascript
function schrijfGegevens() {
  const blad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Resultaten');

  blad.getRange('A1').setValue('Hallo wereld');

  const data = [
    ['Naam', 'Score', 'Datum'],
    ['Anna', 95, new Date()],
    ['Bob', 87, new Date()],
  ];
  blad.getRange(1, 1, data.length, data[0].length).setValues(data);

  blad.getRange('D1').setFormula('=SUM(B2:B3)');
}
```

Let op dat `setValues()` een exact passende range nodig heeft: het aantal rijen en kolommen in de range moet overeenkomen met de array-dimensies. Komt dat niet overeen, dan krijg je de foutmelding dat het aantal rijen of kolommen in de data niet klopt met het bereik. De aanroep `getRange(rij, kolom, aantalRijen, aantalKolommen)` is hiervoor het handigst, omdat je de afmetingen rechtstreeks uit je array haalt.

## Rijen en kolommen beheren

```javascript
function beheerStructuur() {
  const blad = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  blad.insertRowAfter(3);
  blad.insertRowsBefore(1, 2);
  blad.deleteRow(5);
  blad.deleteRows(10, 3);

  blad.insertColumnAfter(2);
  blad.deleteColumn(4);

  blad.setColumnWidth(1, 200);
  blad.setRowHeight(1, 40);
}
```

## Werkbladen beheren

```javascript
function beheerWerkbladen() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const nieuwBlad = ss.insertSheet('NieuwWerkblad');
  const kopie = ss.duplicateActiveSheet();

  ss.setActiveSheet(nieuwBlad);
  nieuwBlad.setName('Hernoemd');

  ss.deleteSheet(ss.getSheetByName('OudWerkblad'));

  const bladen = ss.getSheets();
  bladen.forEach(b => Logger.log(b.getName()));
}
```

:::warn title="deleteSheet is onomkeerbaar"
`deleteSheet()` kun je vanuit Apps Script niet ongedaan maken. Controleer altijd met `getSheetByName()` of het werkblad bestaat (de methode geeft `null` terug als dat niet zo is) voordat je het verwijdert, en overweeg eerst een kopie te maken met `copyTo()` of `duplicateActiveSheet()`.
:::

## Filters en sorteren

```javascript
function filterEnSorteer() {
  const blad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const bereik = blad.getDataRange();

  bereik.sort([{column: 2, ascending: false}, {column: 1, ascending: true}]);

  const filter = bereik.createFilter();
  const criteria = SpreadsheetApp.newFilterCriteria()
    .whenTextContains('actief')
    .build();
  filter.setColumnFilterCriteria(3, criteria);
}
```

Een bestaande filter verwijder je met `blad.getFilter().remove()` voordat je een nieuwe aanmaakt. Per tabblad kan er namelijk maar een filter actief zijn.

## Named ranges en bescherming

```javascript
function namedRangesEnBescherming() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const blad = ss.getSheetByName('Config');

  ss.setNamedRange('KlantNamen', blad.getRange('A2:A100'));
  const nr = ss.getRangeByName('KlantNamen');

  const bescherming = blad.protect();
  bescherming.setDescription('Alleen beheerders');
  bescherming.removeEditors(bescherming.getEditors());
  bescherming.addEditor('admin@bedrijf.nl');
}
```

De `protect()` methode op een sheet of range is de actuele manier om werkbladen te beveiligen. De oudere `PageProtection` klasse is verouderd en moet je niet meer gebruiken in nieuwe scripts.

:::faq
### Waarom geeft getLastRow() soms een te hoog getal terug?
Apps Script telt rijen tot en met de laatste rij die ooit inhoud heeft gehad, ook als die rij nu visueel leeg lijkt. Voor een betrouwbaarder resultaat filter je de gelezen data zelf, bijvoorbeeld met `getDataRange().getValues().filter(r => r[0] !== '')`.

### Kan ik meerdere spreadsheets tegelijk openen?
Ja. Je kunt in een script meerdere `openById()`-aanroepen doen en zo data tussen bestanden verplaatsen. Houd wel rekening met de quota, want elke open- en schrijfactie telt mee in de dagelijkse limieten.

### Hoe voorkom ik dat formules overschreven worden?
Gebruik `getFormula()` om te controleren of een cel al een formule bevat voordat je `setValue()` aanroept. Of bescherm het bereik met `range.protect()` zodat alleen aangewezen editors het mogen wijzigen.

### Wat is het verschil tussen getValue() en getDisplayValue()?
`getValue()` geeft de ruwe waarde terug, dus een getal, datum of boolean. `getDisplayValue()` geeft de opgemaakte string zoals die zichtbaar is in de cel, inclusief valutasymbolen, datumnotatie en afronding.

### Waarom is mijn script traag bij veel cellen?
Vrijwel altijd komt dat door cel-voor-cel lezen of schrijven binnen een lus. Lees de hele range in een keer met `getValues()`, verwerk de data in JavaScript en schrijf het resultaat in een keer terug met `setValues()`. Dat scheelt makkelijk tientallen seconden.

### Wanneer heb ik flush() nodig?
`SpreadsheetApp.flush()` dwingt af dat alle openstaande wijzigingen direct naar de sheet worden geschreven. Dat is handig vlak voordat je iets uitleest dat je net hebt geschreven, of voor het einde van een lange run. Roep het niet binnen een lus aan, want dat vertraagt de uitvoering juist sterk.
:::

:::howto title="Gegevens lezen en schrijven in batch"
1. Open de spreadsheet met `SpreadsheetApp.openById(id)` of `getActiveSpreadsheet()`.
2. Selecteer het werkblad met `getSheetByName('Naam')`.
3. Bepaal het databereik met `getDataRange()` of een expliciete range.
4. Lees alle waarden in een keer met `getValues()` in een lokale variabele.
5. Verwerk de data in JavaScript met `filter`, `map` en `reduce`.
6. Schrijf het resultaat in een keer terug met `setValues(tweedimArray)` op de doelrange.
:::

## Tips voor productief werken met SpreadsheetApp

Gebruik `getValues()` en `setValues()` in plaats van cel-voor-cel bewerkingen. Een script dat 1000 cellen een voor een leest kan makkelijk 30 seconden duren, terwijl batch-lezen minder dan een seconde kost.

Sla het blad-object op in een variabele als je het meerdere keren gebruikt. Elke aanroep van `getSheetByName()` is een aparte operatie, dus hergebruik de referentie in plaats van hem telkens opnieuw op te halen.

Gebruik `SpreadsheetApp.flush()` als je tussentijds wilt forceren dat wijzigingen worden weggeschreven, bijvoorbeeld na een grote batch. Houd het buiten je lussen om onnodige vertraging te voorkomen.