如何使用Google可视化查询搜索电子表格 - 基于多种搜索标准 [英] How to Search a Spreadsheet Using Google Visualization Query - Based on Mulitple Search Criteria
问题描述
我在下面的Google可视化查询脚本中使用 Query.setQuery 来搜索这个简单的电子表格。该脚本通过列A查找输入到html输入字段的名称，然后匹配该人的相应作业和小时（来自其他列），并将这些数据返回到html输入字段。 （感谢论坛会员 WhiteHat 寻求帮助！）
google.charts.load（'current'，{callback：function（）{document.getElementById（'Search'）。addEventListener（'click'，searchSheet，false）; searchSheet（）; function searchSheet（） {searchName = document.getElementById（'Name'）。value; var queryWORK = new google.visualization.Query（'https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEnPgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&sheet=QUERY'）; if （searchName！==''）{queryWORK.setQuery（'select * where A =''+ searchName +''）;} queryWORK.send（function（response）{if（response.isError（））{console。 log（'ID验证查询中的错误：'+ response.getMessage（）+''+ response.getDetailedM essage（））; return;} var datatable = response.getDataTable（）; for（var i = 0; i< datatable.getNumberOfColumns（）; i ++）{document.getElementById（datatable.getColumnLabel（i））。value =（datatable.getNumberOfRows（）> 0）？ datatable.getValue（0，i）：''; } var chart = new google.visualization.Table（document.getElementById（'table_div'））; chart.draw（数据表）; }）; > div {padding：6px 6px 6px;}
< ;脚本src =https://www.gstatic.com/charts/loader.js>< / script>< div>< label for =Name>输入名称：< / label> < input id =Nametype =textvalue =Bill/>< / div>< div>< input id =Searchtype =buttonvalue =Search/> ;< / div>< div>< label for =Job>作业：< / label>< input id =Jobtype =text/>< / div>< div>< label for =Hours> Hours：< / label>< input id =Hourstype =text/>< / div>< div id =table_div> < / div>
是添加另一个搜索条件，然后将匹配发送到通用结果html输入字段。搜索将需要始终查看电子表格的A列和第1行中的单元格以查找匹配。
例如，如果在我输入的第一个搜索字段中 ，然后在第二个字段中输入 Job ，我希望 返回到结果输入字段。或者说我输入 Adam 和 Hours ，那么应该返回 1.5 的匹配。单元格A1将始终包含相同的数据（名称）;但是，我将无法提前知道行1中剩余单元格的内容（B1，C1，D2 ...），因为这些内容将会改变。
任何想法？谢谢
您可以使用星号来返回所有列
'select * where A =''+ searchText +''
请参阅以下工作片段...
$ b
> div {margin：6px 6px 6px;}
< ;脚本src =https://www.gstatic.com/charts/loader.js>< / script>< div>< label for =Name>输入名称：< / label> < input id =Nametype =textvalue =Bill/>< / div>< div>< label for =字段>字段：< / label>< input id =Fieldtype =textvalue =Job/>< / div>< div>< input id =Searchtype =buttonvalue =Search/>< / div>< div>< label for =Result>结果：< / label>< input id =Resulttype =text/>< / div>< div id = table_div>< / div>
I'm using Query.setQuery in the Google visualization query script below to search through this simple spreadsheet. The script looks though Column A for a name entered into an html input field, then matches that person's corresponding job and hours (from other columns) and returns these data to html input fields. (Thanks to forum member WhiteHat for the help with that!)
google.charts.load('current', {
callback: function () {
document.getElementById('Search').addEventListener('click', searchSheet, false);
searchSheet();
function searchSheet() {
searchName = document.getElementById('Name').value;
var queryWORK = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEnPgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&sheet=QUERY');
if (searchName !== '') {
queryWORK.setQuery('select * where A = "' + searchName + '"');
}
queryWORK.send(function (response) {
if (response.isError()) {
console.log('Error in ID Validation Query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var datatable = response.getDataTable();
for (var i = 0; i < datatable.getNumberOfColumns(); i++) {
document.getElementById(datatable.getColumnLabel(i)).value =
(datatable.getNumberOfRows() > 0) ? datatable.getValue(0, i) : '';
}
var chart = new google.visualization.Table(document.getElementById('table_div'));
chart.draw(datatable);
});
}
},
packages:['table']
});
div {
padding: 6px 6px 6px 6px;
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div><label for="Name">Enter Name: </label><input id="Name" type="text" value="Bill" /></div>
<div><input id="Search" type="button" value="Search" /></div>
<div><label for="Job">Job: </label><input id="Job" type="text" /></div>
<div><label for="Hours">Hours: </label><input id="Hours" type="text" /></div>
<div id="table_div"></div>
What I'm looking to do now is add another search criterion, and then have the match sent to a generic "results" html input field. The search will need to always look though the cells in column A and row 1 of the spreadsheet to find the match.
For instance, if in the first search field I enter Janet, and in second field I enter Job, I'd like Cooking to be returned in the "results" input field. Or say I enter Adam and Hours, then a match of 1.5 should be returned.
Cell A1 will always contain the same data ("Name"); however, there won't be a way for me to know the contents of the remaining cells in row 1 ahead of time (B1, C1, D2...) as these will change.
Any ideas??? Thanks
you can use an asterisk to return all columns
'select * where A = "' + searchText + '"'
then use similar logic as before, to find the requested column
see following working snippet...
google.charts.load('current', {
callback: function () {
document.getElementById('Search').addEventListener('click', searchSheet, false);
searchSheet();
function searchSheet() {
searchText = document.getElementById('Name').value;
var queryWORK = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEnPgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&sheet=QUERY');
if (searchText !== '') {
queryWORK.setQuery('select * where A = "' + searchText + '"');
}
queryWORK.send(function (response) {
if (response.isError()) {
console.log('Error in ID Validation Query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var datatable = response.getDataTable();
for (var i = 0; i < datatable.getNumberOfColumns(); i++) {
var field = document.getElementById('Field').value;
if (datatable.getColumnLabel(i) === field) {
document.getElementById('Result').value = (datatable.getNumberOfRows() > 0) ? datatable.getValue(0, i) : '';
}
}
var chart = new google.visualization.Table(document.getElementById('table_div'));
chart.draw(datatable);
});
}
},
packages:['table']
});
div {
margin: 6px 6px 6px 6px;
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div><label for="Name">Enter Name: </label><input id="Name" type="text" value="Bill" /></div>
<div><label for="Field">Field: </label><input id="Field" type="text" value="Job" /></div>
<div><input id="Search" type="button" value="Search" /></div>
<div><label for="Result">Result: </label><input id="Result" type="text" /></div>
<div id="table_div"></div>
这篇关于如何使用Google可视化查询搜索电子表格 - 基于多种搜索标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
