将新的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.

我创建了一个脚本，该脚本从我的BigQuery表之一将10个最新数据点加载到Google表格文档中.现在，当我手动将新数据点添加到该表的底部时，我希望运行一个加载脚本，该加载脚本会将新数据上传回BigQuery，并将其附加到我的原始表中.我读到某处的内容是，如果插入的表已经存在，则仅通过插入新表自动添加数据.但是，我还没有测试过那部分，因为我在生产线上早些时候被卡在一个错误中.以下是我从 https://developers.google.com/松散复制的加载脚本apps-script/advanced/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?

不幸的是，该脚本必须直接从当前打开的Spreadsheet工作表中加载，因为它是我将要运行的较大脚本的一部分.希望这不是太大的障碍！

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屋！