Google App Script中的类似SQL的查询功能可从Google表格中提取数据 [英] SQL like query features in Google App Script to pull data from Google Sheets
问题描述
我正在尝试构建一个Google Apps脚本网络应用程序，该应用程序将从Google表格中提取数据并在浏览器的HTML页面中以行和列的形式显示它们.
I am trying to build a Google Apps Script web app that will pull data from a Google sheet and display it in rows and columns in an HTML page in the browser.
通过遵循示例等，我编写了该代码，很有效！
By following the samples etc I wrote this code that WORKS!
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;
}
通过以下HTML模板提取并正确显示A，B，C，D列中的数据
the data lying in columns A,B,C,D is getting pulled and being displayed correctly through the following HTML template
<? 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查询来检索带有WHERE子句的某些列，例如SQL.我了解在电子表格中起作用的= QUERY()函数在GAS内部不起作用.所以我的下一个尝试是通过使用getBatch方法来检索某些行..这就是我得到错误的地方
Instead of getting all the rows and all the columns from A,B,C,D I would like to run an SQL Query to retrieve some of the columns with a WHERE clause like SQL. I understand that the =QUERY() function that works in the spreadsheet does not work inside the GAS. So my next attempt was to retrieve SOME of the rows by using a getBatch method .. and this is where I get ERRORs
在这种情况下，我想排除C列，而只获得A，B和D，E引发错误的代码如下:
in this case, i want to exclude column C and get only A,B and D,E the code that throws an error is as follows :
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
In the corresponding HTML template, all that changes is getData is replaced with getData2
<? var data = getData2(); ?>
使用此代码，出现以下错误:
with this code, I get the following error :
TypeError:无法从未定义中读取属性"length".(第6行，文件代码"，项目"Report003")
TypeError: Cannot read property "length" from undefined. (line 6, file "Code", project "Report003")
现在我有两个问题:
- 我的代码有什么问题，我该如何解决?
- 是否可以使用SQLite简化提取所需行和列的过程
我已经看到了这个问题，但我无法充分理解答案
I have seen this question but I am not able to understand the answer adequately
推荐答案
您可以使用Google Visualization API查询语言，用于对数据源的查询进行数据处理.查询语言的语法类似于SQL
You can use Google Visualization API Query Language to perform data manipulations with the query to the data source. The syntax of the query language is similar to 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>
这篇关于Google App Script中的类似SQL的查询功能可从Google表格中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
