Extract table from Google BigQuery to Google Cloud Storage using Apps Script

Question

I am trying to extract an existing BigQuery table to a Cloud Storage bucket using Apps Script because I need to schedule it for frequent runs. The Cloud set up (billing, api enabled) is complete and when I extract the table via the Web UI it all works fine.

Please help me with the Apps Script code. Below you can see what I am writing. It works with no error messages but it does not create the json file in Storage.

function SQLTest() {

var projectId = 'xx_my_project';
var datasetId = 'xx_my_dataset';      
var tableId = 'xx_my_table';   
var destinationUris = 'gs://my_bucket/my_file.json';
var destinationFormat = 'NEWLINE_DELIMITED_JSON';


var job = {            
  configuration: {

extract: {
  sourceTable: {
    projectId: projectId,
    datasetId: datasetId,
    tableId: tableId
  }
  ,
 destinationUris: [destinationUris],
 destinationFormat: destinationFormat

}  
} 
}
} ;

Answer 1

Unless I'm missing something, you didn't invoke any API here.

Please follow this guide on how to call a Bigquery API from Apps Script: https://developers.google.com/apps-script/advanced/bigquery

Please also follow this guide to extract table: https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/insert

Answer 2

Full corrected script

function SQLTest() {

var projectId = 'xx_my_project';
var datasetId = 'xx_my_dataset';      
var tableId = 'xx_my_table';   
var destinationUris = 'gs://my_bucket/my_file.json';
var destinationFormat = 'NEWLINE_DELIMITED_JSON';          
var job = {            
configuration: {

extract: {
  sourceTable: {
    projectId: projectId,
    datasetId: datasetId,
    tableId: tableId
  }
  ,
 destinationUris: [destinationUris],

  destinationFormat: destinationFormat

}  
}
};

var request = BigQuery.Jobs.insert(job,projectId);
}