Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Apps Script, convert a Sheet range to Blob

Background: I'm trying to upload an individual row of data from a Google Sheet and append it to a BigQuery table.

Method: I've been using https://developers.google.com/apps-script/advanced/bigquery to do this, but instead of a file of data as the example is, I am using my own sheet with data from a specific row:

 var file = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = file.getSheetByName(name);
  var lastRow = currentSheet.getLastRow()
   var lastC = currentSheet.getLastColumn()
   var rows = currentSheet.getRange(2,1,1,lastC).getValues();

"rows" is the row of data to be imported to BQ. I've tried a multitude of things, and according to another StackOverflow question, "rowsCSV" makes the 2D array of values CSV.

  var rowsCSV = rows.join("\n");

 var data = rowsCSV.getBlob().setContentType('application/octet-stream');

Problem: Every time I run the function, I get the error "Cannot find function getBlob in object Blob. " or, "Cannot convert Array to (class)[][]", or "Cannot find function getBlob in object Tue May 16 2017 00:00:00 GMT+0200 (CEST),58072.4,,,,,,,,,,,test ", where the last bit ("Tue May..") is the actual data of the row.

What am I doing wrong here?

like image 916
nikooters Avatar asked May 16 '17 13:05

nikooters


2 Answers

There is no getBlob method for an array. You will have to use the Utilities.newBlob() to get your blob from a string. You can find the documentation on the same here

 var rowsCSV = rows.join("\n");
 var blob = Utilities.newBlob(rowsCSV, "text/csv")
 Logger.log(blob.getDataAsString())
 var data = blob.setContentType('application/octet-stream');

Equivalently you can do this

var rowsCSV = rows.join("\n");
var data = Utilities.newBlob(rowsCSV, 'application/octet-stream')
like image 120
Jack Brown Avatar answered Oct 02 '22 16:10

Jack Brown


For anyone else viewing this, Jack Brown's answer is correct, you just need to change

var rows = currentSheet.getRange(2,1,1,lastC).getValues();

to

var rows = currentSheet.getRange(2,1,lastRow,lastC).getValues();

like image 45
Trevor Theodore Avatar answered Oct 02 '22 16:10

Trevor Theodore