CODEWITHSUNDEEP
google-docsgoogle-docs-api
JLYK
JLYK

Reputation: 391

Can you create a Form that reads data from a spreadsheet on Google Docs?

So I've created a Form that asks a bunch of general contact information and the data gets stored in a spreadsheet all via Google Docs. This all works perfectly fine. Now is it possible to create another Form that queries the spreadsheet? For example, I would like users to be able to query for all contacts that live in the same area without exposing the entire database. Is this possible?

Upvotes: 1

Views: 182

Answers (1)

Scott Smith
Scott Smith

Reputation: 71

One way of doing this is using the Google Visualization API Query Language. This Looks like it is more for carts then data, but some examples I have seen on the web seem to use it like you are looking for.

If you are open to 3rd party tools that do this. There is a tool called Cloud Snippets. (www.cloudwrd.com). Snippets use a tag language with standard HTML that interface with Google Sheets that allow for creating forms, custom output and conditional filters on the data.

Here is one example of what the code would look like with a filter on gender in a contacts sheet:

<!-- If no search, present a form -->
<# if ("<#[url.search]#>" == "") { #>
  <form method="GET" action="<#[system.referring_page_url as html]#>">
    <# convert system.referrer to hidden HTML form inputs; #>
    <label >Gender</label>
    <select name="gender">
        <option value="male">Male</option>              
        <option value="female">Female</option>              
    </select>
    <input type="hidden" name="search" value="true" >
    <input type="submit" value="Lookup" />
  </form>

<!-- we have search paramaters - so lets do the query -->
<# } else { #>

<# start list for googlesheet "Contacts"; 
    // use the "is set" designator for conditional filter passed to a page in the "url" or "post"
    include when gender is "<#[url.gender]#>" if set;

#>

<# start header #>
<table>
    <tr>
        <th>Gender</th>
        <th>Name</th>
        <th>Address</th>
        <th>Email</th>
    </tr>
<# end header #>

<# start row #>
    <tr>
        <td><# gender #></td>
        <td><# first #><# last #></td>
        <td><# street #>, <# city #>, <# state #></td>
        <td><# email #></td>
    </tr>
<# end row #>

<# start footer #>
</table>
<# end footer #>

<# no results #>
No Results
<# end no results #>

<# end list #>

<# } #>

Upvotes: 1

Related Questions