Apps 脚本，将工作表范围转换为 Blob [英] Apps Script, convert a Sheet range to Blob

问题描述

背景:我正在尝试从 Google 表格上传单行数据并将其附加到 BigQuery 表.

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

方法:我一直在使用 https://developers.google.com/apps-script/advanced/bigquery 来执行此操作，但与示例中的数据文件不同，我使用的是我自己的工作表，其中包含来自特定行的数据:

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" 是要导入到 BQ 的数据行.我尝试了很多方法，根据另一个 StackOverflow 问题，rowsCSV"使二维值数组为 CSV.

"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("
");

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

问题:每次运行该函数时，都会收到错误无法在对象 Blob 中找到函数 getBlob."或无法将数组转换为(类)[][]"或在对象中找不到函数 getBlob"Tue May 16 2017 00:00:00 GMT+0200 (CEST),58072.4,,,,,,,,,,,,test ", 其中最后一位 ("Tue May..") 是该行的实际数据.

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.

我在这里做错了什么?

推荐答案

没有用于数组的 getBlob 方法.您必须使用 Utilities.newBlob() 从字符串中获取 blob.您可以在同一此处找到该文档

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("
");
 var blob = Utilities.newBlob(rowsCSV, "text/csv")
 Logger.log(blob.getDataAsString())
 var data = blob.setContentType('application/octet-stream');

同样可以做到这一点

var rowsCSV = rows.join("
");
var data = Utilities.newBlob(rowsCSV, 'application/octet-stream')

这篇关于Apps 脚本，将工作表范围转换为 Blob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！