standalone script for loading data from google spreadsheet to bigquery

Question

I have written a standalone script for loading data from csv file to bigquery but i want to know how to load data fro spreadsheet to bigquery. Here is my code for loading data from csv to bigquery.I would like to know where to make changes for google spreadsheet instead of csv file.

function loadCsv() {
  // Replace this value with the project ID listed in the Google
  // Developers Console project.
  var projectId = '775034665452';
  var datasetId = 'DEV';
  // Sample CSV file of Google Trends data conforming to the schema below.
  // https://docs.google.com/file/d/0BwzA1Orbvy5WMXFLaTR1Z1p2UDg/edit
  var csvFileId = '0BwzA1Orbvy5WMXFLaTR1Z1p2UDg';

  // Create the table.
  var tableId = 'pets_' + new Date().getTime();
  var 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'}
      ]
    }
  };
  table = BigQuery.Tables.insert(table, projectId, datasetId);
  Logger.log('Table created: %s', table.id);

  // Load CSV data from Drive and convert to the correct format for upload.
  var file = DriveApp.getFileById(csvFileId);
  var data = file.getBlob().setContentType('application/octet-stream');

  // Create the data upload job.
  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        skipLeadingRows: 1
      }
    }
  };
  job = 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);
}

Android Enthusiast Android Enthusiast · Accepted Answer · 2016-08-29T00:36:45

You must first load your data into BigQuery before you can run queries.

You can load data in the following ways:

Load from Google Cloud Storage, including CSV, JSON (newline-delimited), and Avro files, as well as Google Cloud Datastore backups.
Load directly from a readable data source.
Insert individual records using streaming inserts.

Loaded data can be added to a new table, appended to a table, or can overwrite a table.

BigQuery supports loading data from several source formats, including CSV, JSON, Avro, and Google Cloud Datastore backup files. For more information, see Data Formats.

Loading CSV files, check this: https://cloud.google.com/bigquery/loading-data#loading_csv_files

standalone script for loading data from google spreadsheet to bigquery

1 Answers