How to search a table using a Google Visualization query

Question

I have this simple webpage that uses google.visualization.Query to pull the values ​​of three specific cells from this spreadsheet , and then sets the values ​​of the 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 of now, I need to “hard-code” the row and column indexes for each cell from which I want to extract data. How can I get this to search and retrieve data from a spreadsheet? What, for example, if I had a simple input field where I could enter a name, and the "task" and "hours" would be returned. Is it possible?

Thank.

Answer 1

Query.setQuery SQL- ,

Job and Hours, Name = Bill
'select B, C where A = "Bill"'

, Bill, Kim 'select B, C where A like "%i%"'

, ,

"", ...

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>

Hide result