Use a Google Docs Spreadsheet as a datasource for a dynamic Google Sites webpage

Question

I have a Google Form that feeds a Google Docs Spreadsheet. I'd like to--in turn--have that Google Docs Spreadsheet feed a webpage.

In plainer English, babysitters fill out the form to sign up to be in our community's Babysitter Directory. The spreadsheet houses all of the data. I'd like to code a webpage to pull selected bits of the data for the online directory.

I've tried doing a separate sheet in the spreadsheet, using a QUERY to select the columns that I want to include (and the order in which I want to include them), publishing that sheet to the web, then embedding that sheet into the webpage in an iFrame. And that works.

But even with the QUERY, there are SO many columns that users need to scroll WAY over to the right to see all the data for each babysitter. It's unwieldy.

What would be way better would be if I could break the data for each entry over multiple lines and do some nice formatting for a directory, rather than just a linear spreadsheet. So that, essentially, each babysitter's "entry" in the directory is more than 1 line long. Does that make sense?

If I was working in Office, I would know exactly what to do: use the Excel spreadsheet as the datasource for a Word Mail Merge and I would put move the fields around on the page to make it all look good.

And, to be sure, if I can do this in a Google Doc, then embed the Doc into the webpage, that's fine, too. But I would think there's some way I can do it directly in the Google Site?

Can I?

If anybody has even just a reference page for me to take a look at, I'd appreciate it.

Thanks!

Answer 1

Are you trying to do this in Google Sites? If so, you can embed the entire spreadsheet on the page, but if you only want certain columns, you can try inserting an Apps Script widget on the page.

You need to know how to write a Google Apps Script that will run JS functions and render HTML, here is a tutorial

To create the Script that can run on your page, go to:

More > Manage site > Apps Script > Add new script

Here's also a link to how to interact with Spreadsheet data.