SpreadsheetApp service volledig uitgelegd

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:

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:

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.

Schrijven naar cellen

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

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

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()));
}

Filters en sorteren

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

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.

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.