BigQuery Service

Stay organized with collections Save and categorize content based on your preferences.

The BigQuery service allows you to use the Google BigQuery API in Apps Script. This API gives users the ability to manage their BigQuery projects, upload new data, and execute queries.

Reference

For detailed information on this service, see the reference documentation for the BigQuery API. Like all advanced services in Apps Script, the BigQuery service uses the same objects, methods, and parameters as the public API. For more information, see How method signatures are determined.

To report issues and find other support, see the Google Cloud support guide.

Sample code

The sample code below uses version 2 of the API.

Run query

This sample queries for the top 300 most common words over 10 characters long that appear in the Shakespeare sample dataset.

advanced/bigquery.gs
/**
 * Runs a BigQuery query and logs the results in a spreadsheet.
 */
function runQuery() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  const projectId = 'XXXXXXXX';

  const request = {
    // TODO (developer) - Replace query with yours
    query: 'SELECT TOP(word, 300) AS word, COUNT(*) AS word_count ' +
      'FROM `publicdata.samples.shakespeare` WHERE LENGTH(word) > 10;',
    useLegacySql: false
  };
  let queryResults = BigQuery.Jobs.query(request, projectId);
  const jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  let sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  let rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (!rows) {
    Logger.log('No rows returned.');
    return;
  }
  const spreadsheet = SpreadsheetApp.create('BiqQuery Results');
  const sheet = spreadsheet.getActiveSheet();

  // Append the headers.
  const headers = queryResults.schema.fields.map(function(field) {
    return field.name;
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (let i = 0; i < rows.length; i++) {
    const cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (let j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }
  sheet.getRange(2, 1, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s',
      spreadsheet.getUrl());
}

Load CSV data

This sample creates a new table and loads a CSV file from Google Drive into it.

advanced/bigquery.gs
/**
 * Loads a CSV into BigQuery
 */
function loadCsv() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  const projectId = 'XXXXXXXX';
  // Create a dataset in the BigQuery UI (https://bigquery.cloud.google.com)
  // and enter its ID below.
  const datasetId = 'YYYYYYYY';
  // Sample CSV file of Google Trends data conforming to the schema below.
  // https://docs.google.com/file/d/0BwzA1Orbvy5WMXFLaTR1Z1p2UDg/edit
  const csvFileId = '0BwzA1Orbvy5WMXFLaTR1Z1p2UDg';

  // Create the table.
  const tableId = 'pets_' + new Date().getTime();
  let table = {
    tableReference: {
      projectId: projectId,
      datasetId: datasetId,
      tableId: tableId
    },
    schema: {
      fields: [
        {name: 'week', type: 'STRING'},
        {name: 'cat', type: 'INTEGER'},
        {name: 'dog', type: 'INTEGER'},
        {name: 'bird', type: 'INTEGER'}
      ]
    }
  };
  try {
    table = BigQuery.Tables.insert(table, projectId, datasetId);
    Logger.log('Table created: %s', table.id);
  } catch (err) {
    Logger.log('unable to create table');
  }
  // Load CSV data from Drive and convert to the correct format for upload.
  const file = DriveApp.getFileById(csvFileId);
  const data = file.getBlob().setContentType('application/octet-stream');

  // Create the data upload job.
  const job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        skipLeadingRows: 1
      }
    }
  };
  try {
    BigQuery.Jobs.insert(job, projectId, data);
    Logger.log('Load job started. Check on the status of it here: ' +
      'https://bigquery.cloud.google.com/jobs/%s', projectId);
  } catch (err) {
    Logger.log('unable to insert job');
  }
}