1

I am basically trying to create a Web App using Google Apps Script using an HTML interface that would allow a user to search a Google Sheet for names, dates, and keywords and have the matched rows display on the search site.

My sheet contains numerous rows, but I am only concerned with 3 rows in particular: the name, the date, and the keywords. I would like to be able to search for all of the rows in my sheet that match the name, the date, and a keyword.

So far, I've tried following --> How to have a Google Form retrieve spreadsheet data and display it on a Google Site? this link's instructions, however every time I hit submit it displays as null for the search results. 

<!DOCTYPE html>

<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form method="get">

    Responsible: <input type="text" id="responsible"/><br>

    Date From: <input type="date" id="datefrom"/>  
    Date To: <input type="date" id="dateto"/><br>

    Description: <input type="text" id="description"/><br>

    <button onclick="gatherSearch()">Search</button>

    <input type="reset" value="Reset">

    </form>
  </body>
</html>

Ideally, this is what the form would look like. I'd be able to either specify a name, a date, and a description (or a mix of a bunch) and it would return the rows that match my search.

Any help or pointers in the right direction would be greatly appreciated :)

EDIT

Below is my gatherSearch function, which gathers the text in the search bars from the HTML page and forwards it off to my .gs file to handle the searching. 

function gatherSearch() {
      var responsible = document.getElementById('responsible').value;
      var datefrom = formatDate(document.getElementById('datefrom').value);
      var dateto = formatDate(document.getElementById('dateto').value);
      var description = document.getElementById('description').value;

      google.script.run.basicSearch(responsible, datefrom, dateto, description);
      }
    }
Jackson Gayda
  • 31
  • 1
  • 5
  • 1
    Where is `gatherSearch()` function? – TheMaster Mar 27 '19 at 08:18
  • @TheMaster I provided the gatherSearch() function, the actual searching of the rows is done on the .gs side of things (haven't actually developed this yet) – Jackson Gayda Mar 27 '19 at 15:39
  • Review https://developers.google.com/apps-script/guides/html/communication (you never tell your server function where to send the output...) Also review the allowed serializable data types. – tehhowch Mar 27 '19 at 16:07
  • A little late perhaps, but this may also help you: [https://stackoverflow.com/questions/46681662/how-to-have-a-google-form-retrieve-spreadsheet-data-and-display-it-on-a-google-s](https://stackoverflow.com/questions/46681662/how-to-have-a-google-form-retrieve-spreadsheet-data-and-display-it-on-a-google-s) – NMALM Oct 09 '19 at 23:20

1 Answers1

1

I've just done something very similar to you Jackson using the Google Sheets Api. I followed this guide to get it working. Assuming you have already setup the Google Sheets Api go to Step 2. The example function they have there called listMajors() is what you will be looking to do.

function listMajors() {
    gapi.client.sheets.spreadsheets.values.get({
      spreadsheetId: 'YOUR_SPREADSHEET_ID',
      range: 'THE_RANGE_YOU_NEED_TO_SELECT',
    }).then(function(response) {
      var range = response.result;
      // If you get results back
      if (range.values.length > 0) {
        appendPre('Name, Major:');
        for (i = 0; i < range.values.length; i++) {
          var row = range.values[i];
          // Print columns A and E, which correspond to indices 0 and 4.
          appendPre(row[0] + ', ' + row[4]);
        }
      } else {
        appendPre('No data found.');
      }
    }, function(response) {
      appendPre('Error: ' + response.result.error.message);
    });
  }

What you would need to do is for each result you get back, append it to a list, or table to display the list of results. So say you get Name, Date, and Keyword you would then inside the for loop in the above example append each row, var result = result.value[i] to a table or something like that.

You can check out more on reading and writing to Google Sheets using the API here

mullac
  • 693
  • 6
  • 17