# BigQuery bevragen vanuit Apps Script

[[TOC]]

## BigQuery Advanced Service inschakelen

Om BigQuery te bevragen vanuit Apps Script activeer je de BigQuery Advanced Service. Open in de Apps Script editor het menu **Services**, voeg **BigQuery API** toe en kies versie v2. Je gekoppelde Google Cloud project heeft daarnaast facturering nodig, want BigQuery rekent af op basis van verwerkte data.

```javascript
function testBigQueryVerbinding() {
  try {
    const projectId = 'mijn-gcp-project-id';
    const datasets = BigQuery.Datasets.list(projectId);
    Logger.log(`Datasets: ${JSON.stringify(datasets)}`);
  } catch (e) {
    Logger.log('Fout: ' + e.message);
  }
}
```

:::tip title="Koppel het juiste Cloud-project"
Standaard draait je script in een verborgen Google-project zonder facturering. Ga in de editor naar **Projectinstellingen**, koppel een Cloud-project met BigQuery-toegang en gebruik datzelfde project-ID als `projectId` in je code.
:::

## Synchrone query uitvoeren

Voor queries die binnen enkele seconden klaar zijn werkt `BigQuery.Jobs.query()` het prettigst: je krijgt de resultaten direct terug. Let op de tabelnotatie `project.dataset.tabel` zonder accenttekens, zodat je geen syntaxbotsing krijgt met de JavaScript template literal.

```javascript
function voerQueryUit() {
  const projectId = 'mijn-gcp-project-id';

  const request = {
    query: `
      SELECT
        DATE(timestamp) AS datum,
        COUNT(*) AS aantalSessies,
        SUM(totaalOmzet) AS omzet
      FROM mijn-project.analytics.sessies
      WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
      GROUP BY datum
      ORDER BY datum DESC
    `,
    useLegacySql: false,
    maxResults: 1000,
    timeoutMs: 30000,
  };

  const resultaat = BigQuery.Jobs.query(request, projectId);

  if (!resultaat.jobComplete) {
    Logger.log('Query nog niet klaar, gebruik de asynchrone methode');
    return;
  }

  const rijen = resultaat.rows || [];
  Logger.log(`Rijen: ${rijen.length}`);

  rijen.forEach(rij => {
    const waarden = rij.f.map(v => v.v);
    Logger.log(waarden.join(', '));
  });

  return rijen;
}
```

Elke rij komt terug als een object met een veld `f`, een array van cellen. Elke cel heeft een veld `v` met de waarde. Daarom haal je waarden op met `rij.f.map(v => v.v)`.

## Asynchrone query met polling

Voor zware queries start je een job met `BigQuery.Jobs.insert()` en pol je de status tot die `DONE` is. Zo blijf je binnen de uitvoertijd van Apps Script.

```javascript
function asynchroneQuery() {
  const projectId = 'mijn-gcp-project-id';

  const job = BigQuery.Jobs.insert({
    configuration: {
      query: {
        query: 'SELECT naam, score FROM project.dataset.tabel LIMIT 500',
        useLegacySql: false,
      },
    },
  }, projectId);

  const jobId = job.jobReference.jobId;
  Logger.log(`Job gestart: ${jobId}`);

  let status;
  let wachtTeller = 0;
  do {
    Utilities.sleep(2000);
    status = BigQuery.Jobs.get(projectId, jobId);
    wachtTeller++;
    if (wachtTeller > 30) throw new Error('Query timeout na 60 seconden');
  } while (status.status.state !== 'DONE');

  if (status.status.errorResult) {
    throw new Error(status.status.errorResult.message);
  }

  const queryResultaat = BigQuery.Jobs.getQueryResults(projectId, jobId, { maxResults: 1000 });
  const rijen = queryResultaat.rows || [];
  return rijen.map(rij => rij.f.map(cel => cel.v));
}
```

:::warn title="Apps Script stopt na 6 minuten"
Een script mag maximaal 6 minuten draaien (30 minuten voor Workspace-accounts met betaalde licentie). Zware BigQuery-queries kunnen langer duren dan de wachtlus aankan. Start de job dan via een tijdgestuurde trigger en lees de resultaten in een latere run op met het bewaarde `jobId`.
:::

## Resultaten naar Sheets schrijven

```javascript
function schrijfResultatenNaarSheets() {
  const projectId = 'mijn-gcp-project-id';

  const request = {
    query: `
      SELECT afdeling, COUNT(*) AS medewerkers, AVG(salaris) AS gemSalaris
      FROM hr-project.personeels.medewerkers
      GROUP BY afdeling
      ORDER BY medewerkers DESC
    `,
    useLegacySql: false,
  };

  const resultaat = BigQuery.Jobs.query(request, projectId);
  const schema = resultaat.schema.fields;

  const blad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BQ Resultaten')
    || SpreadsheetApp.getActiveSpreadsheet().insertSheet('BQ Resultaten');

  blad.clearContents();

  const headers = schema.map(f => f.name);
  blad.getRange(1, 1, 1, headers.length).setValues([headers]);
  blad.getRange(1, 1, 1, headers.length).setFontWeight('bold');

  if (resultaat.rows) {
    const data = resultaat.rows.map(rij => rij.f.map(cel => cel.v));
    blad.getRange(2, 1, data.length, data[0].length).setValues(data);
  }

  Logger.log(`${resultaat.rows ? resultaat.rows.length : 0} rijen geschreven`);
}
```

Schrijf altijd in één keer met `setValues()` op een hele range. Cel voor cel schrijven is tientallen keren trager en raakt de quota op Spreadsheet-aanroepen sneller.

## Paginering voor grote datasets

`getQueryResults()` geeft per aanroep maximaal `maxResults` rijen terug. Voor grote resultaten loop je door met de `pageToken`.

```javascript
function haalAlleRijen() {
  const projectId = 'mijn-gcp-project-id';
  const jobId = 'BESTAAND_JOB_ID';
  const alleRijen = [];
  let pageToken;

  do {
    const opties = { maxResults: 1000 };
    if (pageToken) opties.pageToken = pageToken;

    const pagina = BigQuery.Jobs.getQueryResults(projectId, jobId, opties);
    if (pagina.rows) {
      alleRijen.push(...pagina.rows.map(r => r.f.map(c => c.v)));
    }
    pageToken = pagina.pageToken;
  } while (pageToken);

  return alleRijen;
}
```

## Kosten in de hand houden

BigQuery rekent af op het aantal bytes dat een query inleest, niet op het aantal rijen dat je terugkrijgt. Een paar gewoontes schelen direct geld.

- Selecteer alleen de kolommen die je nodig hebt; vermijd `SELECT *` op brede tabellen.
- Filter op een gepartitioneerde kolom (vaak een datum), zodat BigQuery hele partities overslaat.
- Test met een `dryRun` om vooraf te zien hoeveel data een query zou lezen.

```javascript
function schatQueryGrootte() {
  const projectId = 'mijn-gcp-project-id';

  const job = BigQuery.Jobs.insert({
    configuration: {
      dryRun: true,
      query: {
        query: 'SELECT afdeling, COUNT(*) FROM hr-project.personeels.medewerkers GROUP BY afdeling',
        useLegacySql: false,
      },
    },
  }, projectId);

  const bytes = job.statistics.totalBytesProcessed;
  Logger.log(`Geschatte verwerkte data: ${(bytes / 1e9).toFixed(2)} GB`);
}
```

:::faq
### Kan ik ook schrijven naar BigQuery vanuit Apps Script?
Ja. Gebruik `BigQuery.Tabledata.insertAll()` voor streaming inserts van losse rijen, of start een job met `configuration.load` voor een batch-upload van een bestand uit Drive.

### Hoe vermijd ik hoge BigQuery-kosten?
Selecteer specifieke kolommen in plaats van `SELECT *`, voeg een `LIMIT` toe aan testqueries en filter op een gepartitioneerde kolom. Gebruik `dryRun: true` om de querygrootte vooraf te schatten zonder dat je betaalt.

### Waarom krijg ik een quota-fout?
De BigQuery API kent rate limits per project. Bouw bij bulk-operaties wat ruimte in met `Utilities.sleep()` tussen aanroepen en bundel werk in zo min mogelijk jobs in plaats van veel kleine queries.

### Hoe verwerk ik NULL-waarden in BigQuery-resultaten?
BigQuery geeft een ontbrekende waarde terug als `null` in het veld `v`. Controleer met `cel.v !== null` voordat je de waarde verder verwerkt, zodat je geen foutmelding krijgt.

### Wat als mijn query langer duurt dan de 6 minuten van Apps Script?
Start de job asynchroon met `BigQuery.Jobs.insert()` en bewaar het `jobId`, bijvoorbeeld in `PropertiesService`. Lees de resultaten in een latere trigger-run op met `getQueryResults()`, zodat één run nooit op de uitvoertijd vastloopt.

### Werkt dit ook met BigQuery in een ander Google Cloud project?
Ja. Het `projectId` dat je aan de methodes meegeeft bepaalt waar de query draait en wordt afgerekend. Je hebt wel de juiste IAM-rechten nodig op de dataset die je bevraagt.
:::

:::howto title="Dagelijkse KPI-rapportage via BigQuery en Sheets"
1. Schrijf een BigQuery-query die de KPI-data voor de afgelopen 24 uur ophaalt.
2. Voer de query uit met `BigQuery.Jobs.query()` binnen een dagelijkse, tijdgestuurde trigger.
3. Schrijf de resultaten met `setValues()` naar een vaste tab in je Sheet.
4. Pas voorwaardelijke opmaak toe om waarden boven of onder je drempel te markeren.
5. Maak een grafiek van de trend met `blad.newChart()`.
6. Verstuur de Sheet als PDF naar de stakeholders met `GmailApp.sendEmail()`.
:::

BigQuery vanuit Apps Script slaat een brug tussen je data warehouse en de dagelijkse workflows in Google Workspace. Zo automatiseer je analyses op grote datasets en zet je de uitkomst direct klaar in Sheets, Gmail of Slides.