How To Search A Spreadsheet Using Google Visualization Query - Based On Mulitple Search Criteria

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

Solution 1:

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();

    functionsearchSheet() {
      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: 6px6px6px6px;
}

<scriptsrc="https://www.gstatic.com/charts/loader.js"></script><div><labelfor="Name">Enter Name: </label><inputid="Name"type="text"value="Bill" /></div><div><labelfor="Field">Field: </label><inputid="Field"type="text"value="Job" /></div><div><inputid="Search"type="button"value="Search" /></div><div><labelfor="Result">Result: </label><inputid="Result"type="text" /></div><divid="table_div"></div>