2

I need to add (append) records to a Big Query table from Google Script. I have looked for similar questions and came up with this code (Also I activated the Advanced Service for BigQuery API):

 const data = {"Name":"TEST","Key":"TEST"};

var projectId = 'myid'
var datasetId = 'mydatasetid'
var tableId = 'mytableid'

var job = {
    configuration: {
      load: {
        writeDisposition:'WRITE_APPEND',
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        }       
      }
    }
  };

   var runJob = BigQuery.Jobs.insert(job, projectId, data);

I get an error: "The mediaData parameter only supports Blob types for upload."

I know I could use this method to add data from a CSV file, but I would like to add an array instead. Is it possible?

Filippo
  • 320
  • 2
  • 5
  • 22

1 Answers1

1

Problem:

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

Fix:

  • Use newBlob to convert data into blob form.
function test() {
  // data is currently an object
  var data = {"Name":"TEST","Key":"TEST"};

  // newBlob only accepts string, so convert data to string
  data = Utilities.newBlob(JSON.stringify(data), 'application/octet-stream');
  // data below is now of type Blob object
  Logger.log(data); 
}

Output:

output

  • You can now use data on BigQuery.Jobs as it is now Blob type

References:

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • 1
    Nice, I actually tried this before, but I was getting an error. Now I realize I was getting an error because I was using CONST and not VAR to define the array. Thanks! – Filippo Apr 02 '21 at 19:08