CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-bigquery
nikooters
nikooters

Reputation: 311

Appending new Google Sheets Data into BigQuery Table

So I'm new to all of this, both BigQuery and AppScript (coding in general..) and I'm learning as I go, so maybe to some my question may seem stupid. Please just hear me out.

I have created a script that loads 10 of the most recent data points into a Google Sheets doc from one of my BigQuery tables. Now, when I manually add new data points to the bottom of this table, I would like to have a load script run that uploads that new data back into BigQuery, and appends it to my original table. I read somewhere that just by inserting a new table the data is automatically appended if the table mentioned already exists. However, I haven't tested that part yet since I get stuck on an error earlier up the line.. Below is the load script I have loosely copied from https://developers.google.com/apps-script/advanced/bigquery

 function loadCsv() {
  var projectId = 'XX';
  var datasetId = 'YY';
  var tableId = 'daily_stats_testing';


  var file = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = file.getActiveSheet().getRange("A12:AK10000").getValues();
  var data = sheet.getBlob().setContentType('application/octet-stream');

  var job = {
   configuration: {
    load: {
     destinationTable: {
      projectId: projectId,
      datasetId: datasetId,
      tableId: tableId
     },
     skipLeadingRows: 1
    }
   }
  };

  job = BigQuery.Jobs.insert(job, projectId, data);
   var Msg = "Load started. Check status of it here:" + 
   "https://bigquery.cloud.google.com/jobs/%s", projectId
  Logger.log(Msg);
  Browser.msgBox(Msg);
  return;
}

Now the error I get (in a variety of forms, since I've been testing stuff out) is that the BigQuery.Jobs function only accepts Blob data, and that the data from my current sheet (with rage A12 marking the first manually imputed row of data) is not a Blob recognizable data set.

Is there any way (any function?) I can use that will directly convert the selected data range and make it Blob compatible? Does anyone have any recommendation on how to do this more efficiently?

Unfortunately the script has to load directly out of the current, open Spreadsheet sheet, since it is part of a larger script I will be running. Hopefully this isn't too much of a hinder!

Thanks in advance for the help :)

Upvotes: 1

Views: 1331

Answers (1)

ReyAnthonyRenacia
ReyAnthonyRenacia

Reputation: 17613

Is there any way (any function?) I can use that will directly convert the selected data range and make it Blob compatible?

There is actually a function that does convert objects into blob type, namely newBlob(data).

To test this I got a range from my spreadsheet and used it.

function blobTest(){

   var sheet = SpreadsheetApp.getActiveSheet();
   var range = sheet.getRange("A1:C1");
   Logger.log(range); //here, range data is of type Range object

   var blob = Utilities.newBlob(range); 
   Logger.log(blob); //here, range data is now of type Blob object
}

Upvotes: 1

Related Questions