从Google脚本将数据插入BigQuery:遇到“" [英] Insert data into BigQuery from a Google Script : Encountered ""

问题描述

我正在尝试通过Google App脚本将数据从Google Spreadsheet导入到BigQuery.我可以下载数据，但是在尝试插入时出错.错误消息是

I am trying to import data from a Google Spreadsheet to BigQuery, via Google App Script. I can download data, but I have an error when I try to do INSERT INTO. The error message is

在第1行第31列遇到".[尝试使用标准SQL(这是我的代码:

function insertRowsInBigQuery(){
var projectId = 'xxx';
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName("EPC");

var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
var sqlData = "INSERT INTO ean.eanToAnalyze (EPC, EAN) VALUES (";

for (i = 0; i < data.length; i++){
  if (i > 0) {
    sqlData += ",(";
  }
  for (j = 0; j < data[0].length; j++){
    sqlData += "'" + data[i][j] + "'";
    if (j < data[0].length -1){
      sqlData += ",";
    } else {
      sqlData +=")";
    }
  }     
}

var request = {
  query: sqlData,
  writeDisposition: 'WRITE_TRUNCATE'
};

var queryResults = BigQuery.Jobs.query(request, projectId);
var jobId = queryResults.jobReference.jobId;

// Check on status of the Query Job.
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
  Utilities.sleep(sleepTimeMs);
  sleepTimeMs *= 2;
  queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}

Logger.log(queryResults);

}

具有几乎相同的功能，仅通过SELECT更改INSERT INTO即可获得数据.

With almost the same function, only changing the INSERT INTO by a SELECT, I get data.

推荐答案

由于错误状态，您需要 DML .最简单的方法似乎是为查询添加前缀:

As the error states, you need to enable standardSQL to use DML. The easiest way seems to be by prefixing your query:

var sqlData = "#standardSQL\nINSERT INTO ean.eanToAnalyze (EPC, EAN) VALUES (";

这篇关于从Google脚本将数据插入BigQuery:遇到“"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！