11

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!

doebtown
  • 553
  • 3
  • 10
  • 21

2 Answers2

6

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.

OnenOnlyWalter
  • 437
  • 2
  • 8
  • Thanks for your response! My site is in Google Sites, yes. I've done enough Google Apps Scripting that I know the general contours. So I could certainly conceive of how I would take the range of data, put it into an array, then export the array data mixed in with labels to do what I want to do. What I STILL don't understand is exactly how to put that into HTML that I can get on a webpage. Can you point me in the right direction on that front? – doebtown Oct 11 '12 at 16:24
  • You probably will need to generate HTML in your Apps Script. For that you can use `HtmlService` - the intro documentation [is here](https://developers.google.com/apps-script/html_service). – OnenOnlyWalter Oct 11 '12 at 16:34
  • Thanks! Your suggestions got me 92% of the way to where I wanted to be. I can't quite figure out the last 8%, but I'm happy [enough] with where I am. Thanks again! – doebtown Oct 11 '12 at 20:32
0

What you want to do is more or less a database interface that uses a spreadsheet as 'data holder', depending on your programming skills it can be quite easy or very hard... Here is an example of such a webapp, its has probably too many fields and features but the general idea is the same (a quick search tool and a window to show results). It that what you had in mind ?

If so I can share the code to help you to get started but if you are not familiar with javascript it will probably need too much effort to get through.

Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • Serge, thanks for your response. What you've got is along the right road for what I want to do. But I don't even need the search tool, I'm fine showing ALL the results (there won't be THAT many babysitters in our little directory!). I've dipped my toe in the water of Google Apps Scripts coding. So if you don't mind sharing the code, I'll take a look at it and see if it's over my head or something I can muddle through. Thanks! – doebtown Oct 11 '12 at 16:03
  • here is the ss link, please make a copy before working on it ;-) https://docs.google.com/spreadsheet/ccc?key=0AnqSFd3iikE3dFV3ZlF5enZIV0JQQ0c1a3dWX1dQbGc#gid=0 – Serge insas Oct 11 '12 at 17:06
  • Can you reshare. It's saying I need authorization! – Ashish Sharma Sep 08 '20 at 11:48
  • sorry but this script isn't working anymore because it uses obsolete methods, 2012 is quite a long time ago ;) – Serge insas Sep 09 '20 at 12:36