如何使用Google可视化查询搜索电子表格 [英] How to Search Spreadsheet Using Google Visualization Query

问题描述

我有简单的网页，它使用google.visualization.Query来提取值此电子表格中的三个特定单元格，然后设置值根据它们唯一的id属性设置三个相应的输入字段。

  google.load（'visualization'，'1'，{'包 '：[' corechart']}）; 
 google.setOnLoadCallback（work）; 
 
 function work（）{
 var queryWORK = new google.visualization.Query（'https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEnPgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&sheet=QUERY' ）; 
 
 queryWORK.send（handleQueryResponse）; 
} 
函数handleQueryResponse（响应）{
 if（response.isError（））{
 alert（'ID验证查询中的错误：'+ response.getMessage（）+' '+ response.getDetailedMessage（））; 
 return; 
} 
 
 var datatable = response.getDataTable（）; 
 var name = datatable.getValue（1,0）; 
 var job = datatable.getValue（1,1）; 
 var hours = datatable.getValue（1,2）; 
 
 document.getElementById（'name_out'）。value = name; 
 document.getElementById（'job_out'）。value = job; 
 document.getElementById（'hours_out'）。value = hours; 
 
 
 
 
 $ b $ p 
 $ b 
现在，我必须硬编码我想从中提取数据的每个单元格的行列索引。我怎样才能得到这个搜索和从电子表格中检索数据？例如，如果我有一个简单的输入字段，可以输入姓名，工作和小时将被返回。这甚至有可能吗？ 
 
 
 
谢谢。 
 
解决方案
您可以使用  Query.setQuery  来设置SQL类语句，
 
可用于选择某些列和行
 
 
 
以下将选择Job&小时列其中Name = Bill 
 
 'select B，C where A =Bill' 
 
 
 
您也可以搜索部分文本，这将选择Bill和Kim 
 
 'select B，C其中A像％i％' 
 
 
输入全名或部分名称，然后点击搜索即可查看结果... 
 
 $ b 
 
 
 
  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&安培;片= QUERY）; if（searchText！==''）{queryWORK.setQuery（'select B，C where A like like'％'+ searchText +'％''）; } queryWORK.send（function（response）{if（response.isError（））{console.log（'ID in 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）;}）;   > 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 =Name>作业：< / label>< input id =Jobtype =text/>< / div>< div>< label for =Name> Hours：< / label>< input id =Hourstype =text/>< / div>< div id =table_div> < / div>  

I've got this simple webpage which uses google.visualization.Query to pull the values of three specific cells from this spreadsheet, and then sets the values of three corresponding input fields based on their unique id attributes.

    google.load('visualization', '1', {'packages':['corechart']});
    google.setOnLoadCallback(work);

    function work() {
    var queryWORK = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEnPgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&sheet=QUERY');

    queryWORK.send(handleQueryResponse);
    }
    function handleQueryResponse(response) {
            if (response.isError()) {
                alert('Error in ID Validation Query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
                return;
            }

    var datatable = response.getDataTable();    
    var name = datatable.getValue(1,0);
    var job = datatable.getValue(1,1);
    var hours = datatable.getValue(1,2);

        document.getElementById('name_out').value = name;
        document.getElementById('job_out').value = job;
        document.getElementById('hours_out').value = hours;

        }

As it is currently, I have to "hard code" the row and column indexes for each cell I want to pull data from. How can I can get this to search through and retrieve data from the spreadsheet? What, for example, if I had a simple input field where I could enter a name and the "job" and "hours" would be returned. Is this even possible?

Thanks.

解决方案

you can use Query.setQuery to set a SQL-like statement,
which can be used to select certain columns and rows

the following will select the Job & Hours columns where Name = Bill
'select B, C where A = "Bill"'

you can also search for partial text, this will select both Bill and Kim
'select B, C where A like "%i%"'

following is a working snippet, the inputs are given the same names as the Columns

enter a full or partial name and click Search to see the results...

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 B, C where A like "%' + 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++) {
          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="Name">Job: </label><input id="Job" type="text" /></div>
<div><label for="Name">Hours: </label><input id="Hours" type="text" /></div>
<div id="table_div"></div>

这篇关于如何使用Google可视化查询搜索电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！