1

I have a blank table in big query with the schema set up.

I have data in a Google sheet with the columns in the same order as the table Schema.

When I get the data from the Google spreadsheet I'm given a 2D array [[xxx1,yyy1,zzz1],[xxx2,yyy2,zzz2]] etc

How do I INSERT the array content into the Big Query table?

I think I need to use the following code - but what is the format of the data variable and how would I get my array to that format

let job = {
configuration: {
  load: {
    destinationTable: {
      projectId: projectId,
      datasetId: datasetId,
      tableId: tableId
    }
  }
}
};
job = BigQuery.Jobs.insert(job, projectId, data [what do I need here??]);

Many thanks

EDIT: I should add I can't just connect the google sheet directly to the table and stream. The sheet is simply a stop gap to gather weekly rows of data and add a few bits to it then dump it to Big query - will end up with many 100's of thousands of rows so can't stay in Google sheets

EDIT2: Also the data is various types of Strings, Integers and Dates

MrPea
  • 635
  • 1
  • 6
  • 22

1 Answers1

3

Problem:

  • BigQuery.Jobs accepts blob data. So you need it in blob form.

Fix:

  • Use newBlob to convert data into blob form.
function test() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getRange("A1:B2").getValues();
  // rows data is 2D string array
  Logger.log(rows); 

  // newBlob only accepts String so we convert rows into one
  var rowsString = rows.join("\n");
  Logger.log(rowsString);

  // data below is now of type Blob object
  var data = Utilities.newBlob(rowsString, 'application/octet-stream');
  Logger.log(data); 
}

Output:

output

References:

NightEye
  • 10,634
  • 2
  • 5
  • 24