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?
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')
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With