Class-DB.gs

function DB(projectID) {
  this.projectID = projectID;
  
  this.types = {
    'INT64'     : "INT64",
    'NUMERIC'   : "NUMERIC",
    'BIGNUMERIC': "BIGNUMERIC",
    'FLOAT64'   : "FLOAT64",
    'BOOL'      : "BOOL",
    'STRING'    : "STRING",
    'BYTES'     : "BYTES",
    'DATE'      : "DATE",
    'DATETIME'  : "DATETIME",
    'TIME'      : "TIME",
    'TIMESTAMP' : "TIMESTAMP",
    'GEOGRAPHY' : "GEOGRAPHY",
    'ARRAY'     : function(subtype) {
      return {
        'type'     : "ARRAY",
        'arrayType': subtype
      };
    },
    'STRUCT'    : function(subtype) {
      return {
        'type'      : "STRUCT",
        'structTypes': subtype
      };
    }
  };

  this.parameter = function(name, type, value) {
    var paramType = BigQuery.newQueryParameterType();
    if (typeof type === 'object') {
      if (type.type == 'ARRAY') {
        paramType.type = type.type;
        paramType.arrayType = type.arrayType;
      } else if (type.type == 'STRUCT') {
        paramType.type = type.type;
        paramType.structTypes = type.structTypes;
      } else {
        throw new Exception("Invalid Parameter Type.");
      }
    } else {
      paramType.type = type;
    }
    var paramValue = BigQuery.newQueryParameterValue();
    paramValue.value = value;
    return {
      'name'          : name,
      'parameterType' : paramType,
      'parameterValue': paramValue
    }
  }

  this.query = function(query, params) {
    var queryRequest = BigQuery.newQueryRequest();
    
    // configure query request
    queryRequest.dryRun = false;
    queryRequest.query = query;
    if(params && params.length) {
      queryRequest.queryParameters = params;
      queryRequest.parameterMode = 'NAMED';
    }
    queryRequest.useLegacySql = false;
    queryRequest.useQueryCache = true;
    
    // run query job and wait for results
    var queryResults = BigQuery.Jobs.query(queryRequest, this.projectID);
    var jobID = queryResults.jobReference.jobId;
    var sleepTimeMs = 100;
    while (!queryResults.jobComplete) {
      Utilities.sleep(sleepTimeMs);
      queryResults = BigQuery.Jobs.getQueryResults(this.projectID, jobID);
    }
    
    // check for errors
    if (queryResults.errors) {
      var errorMessages = queryResults.errors.map(function (error) {
        return error.message;
      });
      throw new Exception(errorMessages.join('\n'));
    }
    
    // return num affected rows for insert/modify/delete
    if (typeof queryResults.numDmlAffectedRows !== 'undefined') {
      return parseInt(queryResults.numDmlAffectedRows);
    }
    
    // Get the table headers.
    var tableFieldSchema = queryResults.schema.fields;
    var headers = tableFieldSchema.map(function (field) {
      return field.name;
    });
    
    // concat all the results
    var rows = queryResults.rows;
    while (queryResults.pageToken) {
      queryResults = BigQuery.Jobs.getQueryResults(this.projectID, jobID, {
        pageToken: queryResults.pageToken
      });
      rows = rows.concat(queryResults.rows);
    }
    
    // map results to headers
    rows = rows.map(function (row) {
      var fields = row.f;
      row = {};
      for (var i = 0; i < fields.length; i++) {
        var fieldName = this.headers[i];
        var value = fields[i].v;
        row[fieldName] = value;
      }
      return row;
    }, { headers: headers });
    
    return rows;
  }
}

examples.gs

// Replace this value with the project ID listed in the Google
// Cloud Platform project.
var projectId = "XXXXXXXX";

function example_string_query() {
  var db = new DB(ACCT_ID);
  var query = 'SELECT * FROM publicdata.samples.shakespeare WHERE LENGTH(word) > 10 LIMIT 10;';
  var results = db.query(query);
  Logger.log(JSON.stringify(results));
}

function example_parametric_query() {
  var db = new DB(ACCT_ID);
  var query = 'SELECT * FROM publicdata.samples.shakespeare WHERE LENGTH(word) > @wordlength LIMIT @limit;';
  var params = [
    db.parameter('wordlength', 'INT64', 10),
    db.parameter('limit', 'INT64', 10)
  ];
  var results = db.query(query, params);
  Logger.log(JSON.stringify(results));
}