BigQuery bevragen vanuit Apps Script

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.

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

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.

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.

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

Resultaten naar Sheets schrijven

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.

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.

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

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.