开发者问题收集

SQL喜欢Google App脚本中的查询功能，以从Google表中获取数据

2017-10-25
9428
sql sqlite google-apps-script google-sheets-api

我正在尝试构建一个 Google Apps Script 网络应用，该应用将从 Google 工作表中提取数据并将其显示在浏览器 HTML 页面的行和列中。

通过遵循示例等，我编写了此有效代码！ 

function doGet(){
return HtmlService
       .createTemplateFromFile('Report3')
       .evaluate();
}

function getData(){

var spreadsheetId = '1Z6G2PTJviFKbXg9lWfesFUrvc3NSIAC7jGvhKiDGdcY';
var rangeName = 'Payments!A:D';

var values = Sheets
             .Spreadsheets
             .Values
             .get(spreadsheetId,rangeName)
             .values;
return values;

}

位于 A、B、C、D 列中的数据正通过以下 HTML 模板被提取并正确显示 

<? var data = getData(); ?>
    <table>
      <? for (var i = 0; i < data.length; i++) { ?>
        <tr>
          <? for (var j = 0; j < data[i].length; j++) { ?>
            <td><?= data[i][j] ?></td>
          <? } ?>
        </tr>
      <? } ?>
    </table>

我不想从 A、B、C、D 中获取所有行和所有列，而是想运行 SQL 查询以使用类似 SQL 的 WHERE 子句检索某些列。我理解在电子表格中有效的 =QUERY() 函数在 GAS 中无效。因此，我的下一次尝试是使用 getBatch 方法检索某些行。这就是我收到错误的地方

在这种情况下，我想排除 C 列并仅获取 A、B 和 D、E 引发错误的代码如下： 

function getData2(){

var spreadsheetId = '1Z6G2PTJviFKbXg9lWfesFUrvc3NSIAC7jGvhKiDGdcY';

/* var rangeName1 = 'Payments!D'; */
/* var rangeName2 = 'Payments!A'; */
var values = Sheets
             .Spreadsheets
             .Values
             .batchGet(spreadsheetId,{ranges: ['Payments!D:E', 'Payments!A:B']})
             .values;
return values;

}

在相应的 HTML 模板中，所有更改都是将 getData 替换为 getData2 

<? var data = getData2(); ?>

使用此代码，我收到以下错误：

TypeError: Cannot read property "length" from undefined. (line 6, file "Code", project "Report003")

现在我有两个问题：

  1. 我的代码有什么问题，我该如何修复？
  2. 是否可以使用 SQLite 简化提取所需行和列的过程

我见过这个 问题 ，但我无法充分理解答案

2个回答

我终于明白了 这个解决方案 是什么，并对其进行了如下修改。现在我们可以使用 QUERY() 函数支持的任何 SQL。 

function mostSQL(){

var spreadsheetId = '1Z6G2PTJviFKbXg9lWfesFUrvc3NSIAC7jGvhKiDGdcY';
var targetRange = 'Payments!A:G';
var SQL = 'select A, G where G >= 700 and G <= 800'
var Query = '=QUERY('+targetRange+',\"'+SQL+'\")'

var currentDoc = SpreadsheetApp.openById(spreadsheetId)
var tempSheet = currentDoc.insertSheet();

var pushQuery = tempSheet.getRange(1, 1).setFormula(Query);
var pullResult = tempSheet.getDataRange().getValues();

currentDoc.deleteSheet(tempSheet); 

return pullResult;

}
Calcutta
2017-10-25

您可以使用 Google Visualization API 查询语言 对数据源进行查询，从而执行数据操作。查询语言的语法类似于 SQL

code.gs 

function doGet() {
 // SpreadsheetApp.openById("SSID"); // To define the oAUTH Scope - https://www.googleapis.com/auth/spreadsheets
 var output = HtmlService.createTemplateFromFile('index');
  output.token = ScriptApp.getOAuthToken();
  return output
        .evaluate()
        .setTitle('SQL Query');
}

index.html 

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>

    <div id="dataTable"><h4>Loading...</h4></div>

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> 
    <script src="https://www.gstatic.com/charts/loader.js"></script>
    <script>

    google.load('visualization', '1.0', {packages: ['corechart','table']});
    google.setOnLoadCallback(loadEditor);

      function loadEditor() {
        var queryString = encodeURIComponent("SELECT A,B,D,E where A!= 'JACK'");
        var SSID = "ADD YOUR SPREADSHEET"
        var SHEET_NAME = "SHEET NAME"
        var query = new google.visualization.Query('https://spreadsheets.google.com/tq?key='+SSID+'&sheet='+SHEET_NAME+'&oauth_token=<?=ScriptApp.getOAuthToken()?>&headers=1&tq=' + queryString);
        query.send(handleSampleDataQueryResponse);
      }

      function handleSampleDataQueryResponse(response) {
        console.log(response)
        var data = response.getDataTable();
        console.log(data);
        var chartTbl = new google.visualization.Table(document.getElementById('dataTable'));
        chartTbl.draw(data);
      }

    </script>
  </body>
</html>
Ritesh Nair
2017-10-25