将新的 Google 表格数据附加到 BigQuery 表中 [英] Appending new Google Sheets Data into BigQuery Table

问题描述

所以我对所有这些都是新手，BigQuery 和 AppScript(一般编码..)并且我正在学习，所以对于某些人来说，我的问题可能看起来很愚蠢.请听我说完.

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.

我创建了一个脚本，可将 10 个最新数据点从我的 BigQuery 表之一加载到 Google 表格文档中.现在，当我手动将新数据点添加到此表的底部时，我希望运行加载脚本，将新数据上传回 BigQuery，并将其附加到我的原始表中.我在某处读到只要插入一个新表，如果提到的表已经存在，数据就会自动附加.但是，我还没有测试过那部分，因为我之前遇到了一个错误.下面是我从 https://developers.google.com/松散复制的加载脚本应用程序脚本/高级/bigquery

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;
}

现在我得到的错误(以各种形式，因为我一直在测试内容)是 BigQuery.Jobs 函数只接受 Blob 数据，并且我当前工作表中的数据(愤怒 A12 标记为第一个手动插补的数据行)不是 Blob 可识别的数据集.

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.

有什么方法(任何函数?)我可以使用它来直接转换选定的数据范围并使其与 Blob 兼容?有没有人对如何更有效地做到这一点有任何建议?

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!

预先感谢您的帮助:)

推荐答案

有什么方法(任何函数?)我可以使用它来直接转换选定的数据范围并使其与 Blob 兼容?

实际上有一个函数可以将对象转换为 blob 类型，即 newBlob(数据).

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
}

这篇关于将新的 Google 表格数据附加到 BigQuery 表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！