Monday, 30 May 2011

Make a Searchable Online Database -or- I Love Google Spreadsheets

So you've got a spreadsheet of useful stuff that you want to be able to easily update and turn into an online searchable resource, but you don't want to have to set up a database. Well, Google have the tools for that. Google provide a lot of info, but I found this overwhelming when I got started so am hoping this offers a simple way to get you going.

You'll need a Google account and a place you can edit php webpages (or something like a blog/Blackboard you can enter code).

We're going to create a Google Spreadsheet and then set up a webpage with some special 'visualisation' html to make it searchable, sortable and openly available online! Here's how...

First, set up a new Google Spreadsheet from your Google Docs account.


Fill the spreadsheet with your resources, list or whatever. You'll note I've left the top row blank - I've had problems getting Google to recognise my column headers so tend to leave it blank and we'll label it up later.

Once you've got some data we need to 'publish' the sheet so we can talk to it from our web page.

On the publish screen, click the 'start publishing' button which will make a long web address appear, you need to copy this into notepad or somewhere handy as we'll need the 'key' from this shortly (the bit from after "key=" up until "&output-html").

You also need to change the 'Sharing Settings' to Public, otherwise users need to be logged into Google to make this work - you could use sharing settings to only allow individuals, but in this case, just make it public.

Here is the magic, you need to copy this code into your favourite webpage editor. The code is here. To complete this demo and build a page that searches columns A and B and displays all three then the only edit you need to do is to swap the key from my version to the one you made a note of in the step above. Paste it in, save it (make sure you use .php as the file extension) and upload it to your web server.

If you're feeling imaginative, this is the line that does the magic you might want to explore:
'SELECT A, B, C where upper(A) like upper("%%") or upper(B) like upper("%%") order by A asc label A "Type", B "Title", C "Date added"'
What this does is says:
Show me columns A, B and C...
...where the thing you searched for matches (in upper case to make sure case doesn't matter) anything in column A or column B...
...order the results by column A is ascending alphabetical order...
...and label the columns like this.
 You can find out many more options of what you can do with this query on the Google pages.

The one I made now looks like this (see it for real here). 

A couple of real-life examples of this.

2. Virtual Research Unit Funding Opportunities (University of Brighton). This uses some extra category fields on the spreadsheet and then a new query is built depending on what the user clicked on allowing filtering as well as searching.

For those of you without 'proper' access to webpages, you'll be please to hear you can embed this in something like Blackboard. The search box bit doesn't work but you can display the spreadsheet and let the user view it and sort it. 

In Blackboard you'll need to enter a simple version of the code (simple version available here - like above but without the search box stuff) into an item when the visual editor is turned off. 

This is what it looks like in Blackboard.

Good luck - and if this works for you, please let me know in the comments, would love to see what you do with it!

113 comments:

Joyce Webber said...

I had a go with the 'simple code', into a Bb item, using MacBook Pro and Firefox, but got 'Access Denied'.

Was logged into to Google Docs but perhaps the code has to be shared?

Katie Piatt said...

Sorry Joyce - I forgot a step to make the spreadsheet public, which I've now done, so check now and it should work for you...my fault for not testing when logged out of Google!

Emily Bowe said...

Great post, but I'm having trouble once I finish the code. When I hit preview (in the Blogger code software - might be part of the problem), this is what I see:


$search= $_REQUEST['search'];
if ($search > ''){ $search = $search;} else { $search = '';}
?>


I don't see the spreadsheet itself, but I see the search box and I can type and search, but then when I hit submit query I get a message that says "Method Not Allowed Error 405".

Any idea what's going on?

Katie Piatt said...

Hi Emily - I expect Blogger is rejecting the code as it isn't a true web page editor. Try pasting in my 'simple code' instead:

http://nowlookhear.co.uk/katiepiatt/codesimple.html

Which is just the spreadsheet and not the searchbox. Just tried and think it works ok!

Emily Bowe said...

That worked great! So now that I have the spreadsheet showing up, what can I do to get the search box to show up? Should I retry the longer code?

Katie Piatt said...

Great - only you'll be out of luck with the search box embedding it in a blog I think, because I use some php code to sort out the search value, and that won't work unless it's run properly - ie an html page on a web server that supports php. It's the simple version only if you're embedding in a blog or Blackboard etc.

I'm sure there's someone out there who can figure out a way, and I hope they reply, whoever they are!

Emily Bowe said...

Ah ok that makes sense - thanks so much for your help!

Jane_says said...

This is a GREAT tutorial. The best I have come across trolling for help.

I tried Blogger no luck. Any idea a site that would work? Do I have to have access to a server?

Thansk

Katie Piatt said...

@Jane_says glad you found it useful. The simple code works in blogger, it's just if you want the search box as well you need a 'proper' server.

Alicia said...

This is great! Using to keep track of coupons in my coupon binder and where they are located. Super easy and awesome. Thanks!

teacherben said...

Hi. I have been having trouble getting this to work by embedding it into a weebly page or a wikispaces page and as I saw in a previous post, those may not support embedded php which makes sense. But then I just created a simple html page and uploaded that to my own server (which does have php installed) and I got the same errors. I even tried using your code exactly as it is and just copied it into an html document and uploaded that to my site. I still get the same error that looks like this:

''){ $search = $search;} else { $search = '';} ?>

Any help would be appreciated. I am quite eager to get this to work. I am building a searchable database of student websites for a school (a stopgap measure until we get a LMS set up properly later in the year.)

Katie Piatt said...

@teacherben

The line: if ($search > ''){ $search = $search;} else { $search = '';}
should if with all single quotes, so an opening and closing single quote, am guessing you used double quote?

danarchy said...

Hi! Great tutorial, I'm going to try something like this out for a database for my theatre department's video collection. I have a question though, would this work with a spreadsheet with multiple sheets?

Katie Piatt said...

Hi danarchy - to be honest I've never tried with multiple sheets, I'm not sure how you would change the query to look at a particular sheet. When you publish it, you can choose one sheet or all sheets, so you could certainly have multiple sheets, but I'm sure having all the data for your application on one sheet would be easier to query.

Katie

acapella said...

Don't forget to ensure you save your file with a .php reference nothing else else you'll never get it working.

Great tutorial Katie.

Scott Levy said...

Great post. Here’s a product with a powerful database in the cloud with ready-made apps. Just point-and-click to build your custom apps http://www.caspio.com/

lawtonterri said...

Great post. Here’s how you can convert Microsoft Access to web in minutes http://www.caspio.com/extend/platform-extensions/ms-access-database-online/convert-ms-access-to-web.aspx

Assured said...

Works perfectly thank you. Is it possible to add in hyperlinks? I've tried inserting them in the spreadsheet but the link disappears in the search.

Assured said...

Also, Is it possible to then create an offline copy? I can't see that it would but I have a demanding boss!

Sustainable Energy Rwanda said...

Hi Katie,
This is a great post! I am trying to develop a searchable database but ran into the same problem as Emily above, as I use blogger.

Is it feasible that the google doc can be queried without using php?

Thanks and best regards,
SER

Lizzie said...

Thanks so much for this great post! Did everything I wanted to do with just the simple code.

Mike W said...

Got this working eventually on a photo DB. Main problem I had was I had a year column and it was formatted as a number. Eventually discovered I had to change it to text. Then all ok. Many thanks for sharing. Now how to I stop it listing the whole spreadsheet on first load on the page?

PeteW said...

Hi Katie. I'm getting this to work almost perfectly. Had the same error message as Emily Bowe, but changed the .html to .php and seems to have fixed it.

Only thing is, when I hit "search" it just shows me the same details, almost as if it's ignored the text in the search box and searched for everything.

Any ideas?
thanks
Peter

Katie Piatt said...

Thanks for mentioning the .php file extension, I've edited the post to mention this explicitly now - it wouldn't work without that.

@Mike to stop it loading the whole sheet you'd need to pass a search result to it to limit the results.

@Pete hmm, indeed does sound like it's not reading the search value. Can you try adding a line under:
$search= $_REQUEST['search'];
to say:
echo "search for: ".$search;
this should just print the search string on the page, let me know what it does or share the URL where you have it and I'll take a look!

Katie Piatt said...

@assured you asked about using hyperlinks - sort of, but you have to enter html into the fields and have them display as html losing the default formatting. Change the 'visualization.draw' line in my code to read:

visualization.draw(data, {allowHtml: true, legend: 'bottom'});

Mike W said...

@Katie "to stop it loading the whole sheet you'd need to pass a search result to it to limit the results". Not sure how to do this but made an improvement by adding page: 'enable' and pageSize: 24 to the visualization.draw so that only 24 lines are loaded from the full list e.g. visualization.draw(data, {page: 'enable', pageSize: 24, showRowNumber: true, allowHtml: true, alternatingRowStyle: false, width: '100%', cssClassNames: StyleRows});

This now loads my initial page much faster as it only fetches 24 lines from the 21k in my spreadsheet.

Scuba Lessons Inc said...

Is there a way to embed this entire thing into a table? I am getting this error when I try...
Here is where I put it:

http://www.diesel-plus.com/Info%20Site/NEW/dealer3.html#

but.. I get a 404 error page not found error when searching????

www.LiedVirJuanita.com said...

Hi Katie. Do you think this code will work in a website builder like Yola?

I've copied your code exactly. It shows my table perfectly, but above it, the follwoing text appears -
''){ $search = $search;} else { $search = '';} ?>

I did use single quotes like you suggested.

Also, when I use the search box, the following error occurs - "404. Sorry, we can't seem to find that page, click here to return to the homepage"

What might be the matter? Am I missing something?

P.S. The simple code does work.

Kimberly said...

Hi Katie: Thank you so much for this post. I've been searching high and low to create a searchable database to embed in my blog. I use Wordpress and was also not able to add the 'search' box. I simply used the generated html code that was given to embed. (http://orlandosgreendeals.com/orlandos-green-deals-coupon-database/) I'd still LOVE to add the search box somehow. Any tips or direction you could offer is HIGHLY appreciated :)

Joey Beall said...
This comment has been removed by the author.
Michael Fallas said...

Hey Katie, having a bit of trouble with this, i copied and pasted your code into dreamweaver and changed the key to my own but when i run it i dont see my spreadsheet even though it is shared and published, i see the form but underneath it i see

''){ $search = $search;} else { $search = '';} ?> i have used single quotations, any help would be amazing please!

Katie Piatt said...
This comment has been removed by the author.
Katie Piatt said...

To the two people who are only seeing:
''){ $search = $search;} else { $search = '';} ?>

This means you have not saved your file as *something.php* (you've probably got it as something.htm). You need to use the php scripting language for the search field to do it's stuff and therefore the file extension eneds to be .php, you should check you can use php on your web server.

Katie

Katie Piatt said...

To 'Scuba Lessons Inc' and Kimberly, I'm afraid the links in your comments don't work so I can't see what you are trying to show me...sorry!

Michael Fallas said...

Thanks for that Katie, I have now ensured that it is saved as a .php file and i have tested that my web server is running php and all is well except when i run the code from the test server an error message appears saying:

PHP Notice: Undefined index: search in D:\Inetpub\WWWroot\intranet\search.php on line 18

and if i try and run it from my web browser using the http address it gives me a 500 internal server error, any ideas? sorry for being such a pain!

Priyankoo:প্ৰিয়ংকু said...

Thanks for the useful idea. I am trying to make an inventory list with this however, I see that when a piece of data is pulled from Google spreadsheet, the Alphanumeric data are left out. Any idea why?

Priyankoo:প্ৰিয়ংকু said...

Let me be a little more clear on that. If the entry is "1234" the php page displays it perfectly well, However, if the entry is "123ABC456", the php page does not display it!

Secondly, I am trying to find a way to print some selected data from the displayed table. Say, I just want to print the information about only the printers listed in the inventory. Any pointers on that. I have tried a lot, but nothing came up!!

Jan Alvear said...

I tried to save the file as a .PHP and load it on our server. Everything looks ok but when we tried to do a search it did not work. Any ideas why is it like that?

Adam Qutaishat said...

This was an awesome way add a table quickly. I used the simple code with a site I'm working on with Weebly. Is there a way to get comments and formating (text color, etc.) to appear on these embedded tables? Thanks

Priyankoo:প্ৰিয়ংকু said...

A small update on my 13 July post. I figured out the Alphanumeric display problem. I think in the source spreadsheet, I formatted the cells as numerals and hence, while displaying they were ignored.

I am still trying to find a way with the second problem!

Priyankoo:প্ৰিয়ংকু said...
This comment has been removed by the author.
Marcos Portillo said...

The "code is here" link doesn't work for me. What is the code?

George Hail Library said...

Katie,

this is fabulous! Do you know how to not have the entire spreadsheet show and only have the result show?

Kepler said...

Thank you very much Katie. I was working on recording details of lost/stolen mobiles and you have helped a lot in making it come live. Thank you very much.

Jeffrey Harrington said...

Is there a way to do wild arid searches?

Joe Matzzie said...

Your instructions seem clear but it didn't seem to work for me. :)
Could you look at it and see if I've done anything wrong?
http://www.studiomiracles.com/library06.php

Keith McCauley said...

Hi Katie! This is EXACTLY what I need ... but the code is killing me! I just can't seem to figure it out ... but I think it might have something to do with new new Google APIs and API keys???

Here's my spreadsheet that I'm trying to make searchable.

https://docs.google.com/spreadsheet/pub?key=0An7cxO0crlf8dEtxQ3hiWklMZ3dEbW1jalhZX1E0RkE&output=html

I need it to be searchable by each field ... I've been playing with the query.setQuery statement and I can't get that to work either.

I've got this in my own site, right here:

http://www.cantusyouthchoirs.com/thehub/library/search.php

If you could take a look and let me know what I'm doing wrong ... I would REALLY appreciate it.

Thanks,

SLC Hosting said...

Keith...and what you did is exactly what I need! Great work and thanks for sharing!

Ecumenigal Gal said...

Thanks for this! I've been looking for a solution for a while now.

Would this work with many many columns? I want to create an online searchable database of songs, with about 50 descriptive terms to search - like tempo, genre, topic, etc.

Also, right now I just have blogs, but I would pay GoDaddy to host a site if I felt sure that this would work. When you say that I need to have a server, is that the same is what I would get if GoDaddy "hosted" my site?

Thanks so much for your help.

Igor Petrushenko said...

I highly recommend you trying MyTaskHelper.com online database. It also allows import from Excel to online database

Psychedelic Reflection said...

katie i used your tip and works very wel on http://musicbandsofcrete.wix.com/musicbandsofcrete#!bands-list/cn4l

i have one challenge for you
i would like to have the links of the artists in a form that one can click on them to get transfered on their website ..
is that possible using this way ??

thanks alot for the tip :)

Frank Coleman said...

I have a strange problem.
After following your directions, I was able to get the searchable database working and a spreadsheet appears. Problem: column A lists number only values. Any value with a letter doesn't show up (before and after searching). So when you search a "letter number combo" nothing appears because it thinks it doesn't exist. If you search a number only, results appear.

I was able to use the Google Docs' share link to view the spreadsheet outside of Google Docs and away from the php file. It shows all values in column A fine.

All other columns look ok and show all values fine. It's only the first column missing values and displaying empty cells.

Jason Rector said...

Is there any way determined on how to stop the code from showing the entire spreadsheet and only provide the search option and result the searched request?

Amin Elhasadi said...

Thanks alot, you were a great help for me.

Amin Elhasadi said...
This comment has been removed by the author.
Colin said...

Hi Katie,

Thanks for the info. I have managed to stop all of the spreadsheet showing when page first deployed,which may help others see:-

http://www.weymouthsands.com/ROH_Search/search.php

Regards

Colin

Simon Davies said...

Hey Katie, Great bit of code thanks for sharing. couldn't quite find a nice loading gif to work like yours but the rest works great for our customers to enter a serial and pull up info on our specific products.

used a bit of Mike W's code also to hide the whole database and limit to viewing 1 line per search.

visualization = new google.visualization.Table(document.getElementById('table'));
visualization.draw(data, { page: 'enable', page: 1, pageSize: 1, legend: 'bottom'});

TopView Sports said...

Katie,
Great code. Put it to good use easily. But its a huge list and so it loads and refreshes slowly. Any suggestions? http://floridacycling.com/index.php/calendar-results/oldresults

cihat74 said...

As some commenters I'm too having difficulty in passing the search string to spreadsheets. It may be because the change in published text.

cihat74 said...

Google has changed the type of published text. I think that's why we can get the table appear but cannot pass search string to the sheet. Please help us Katie.

cihat74 said...

It's been 8 hours I'm trying to achieve filtering searched data. I continuously get the list all. I create a php with the content of Katie's working copy, just change the address of php. I even try with Katie's spreadsheets. Alas, nothing works. The last thing I can think of is the difference in server. I GIVE UP.

Daniel said...

Anybody have any idea what CSS this file is pointing to? I can't find it and I'd like to edit it to be able to change colors, etc.

Mike W said...

Thanks to Colin(4 January 2013 15:58) for his trick to get a start search page without searching the whole DB (21k rows in my case). Cheers Mike

Martin Schneider said...

Why bother coding - or using a spreadsheet as your database. Why not use a real database, and not bother with code. here's a tutorial on using an online database tool to quickly turn an Excel file into a truly searchable, shareable database:
http://blog.caspio.com/tech_tips/how-to-create-a-database-from-exce-import-spreadsheet/

Mike W said...

Because Caspio is not free

Mike W said...

My family photo Google Spreadsheet search is working you fine for me on a 21k row sheet on both PC and Android browsers. Maybe a little slow but satisfactory. The search results on the screen have a link to my photo collection stored on the web elsewhere so I can click on the link and see the photos using a lightbox Java script(added to a column in the sheet). However my ambition is to redirect the screen output from the search to a file(e.g. another Google Spreadsheet) and then use this as input to make an on the fly slide show of the search results. I have already the Java script code for the slide show but do not know how to output the search results to a file. Anyone a got any clues?

saskiasusanne said...

Thanks so much Katie for the easy-to-follow instructions! I had been intending to set up a searchable database for the organization I work for for months, and dreading the prospect. Your tutorial made it so easy.

When I first set up the database, it wouldn't display. Instead I kept getting the message "Error in query: Invalid query upper takes a text parameter."

I finally figured out the reason: the second cloumn in my spreadsheet was Publication Year, and apparently the presence of numbers and no text was messing the database up. When I moved the Year column to the end, everything worked perfectly.

Thanks again Katie!

dan sz. said...

Katie, this post was incredibly helpful to me.

One thing I thought was missing was a way of handling a situation where the search query yield zero results. Perhaps an h1 tag that reads "Your search yielded no results"

Ross Taylor said...

This is good post, it is simple way to maintain our personal database as well as for publishing, for companies where there is larger amount of data is available and needs also security other solutions would be needed. Online Database

mattihas heckenman said...

This post is really awesome, one of the best posts about 'online database'.
I have been using CollateBox as an online database but after reading your post I would love to get back to Google docs :-).. Thanks.

Maarten Koller said...

I got an 'access denied' message, until i deleted the language tag.
From the code:

.....pnaWc&hl=en_GB');
I made:
.....pnaWc&hl');

And it worked :)

Jennifer Drucker said...

Katie, this is a great post! Thank you!

Does anyone have any idea if this can be done without use of PHP? Can it be done by calling Javascript or DHTML? My issue is the site that I am using does not allow me to 'upload' php files, I can only add code in HTML but not executable. Any ideas? Thanks!

Jennifer Drucker said...
This comment has been removed by the author.
Josh Schuler said...

I am having an unexpected problem with my in-port. I have columns A-N being in-ported to my website. Column M has phone numbers that display fine like this: "999-888-0000". Column B has part names that are like this: "Shroud, Black" The problem is in column C. Here there are numbers, any row that has a single number in-ports fine, but if there are 2 or three number they don't in-port: "35" good, "35, 191" not good. Why?

hemcoined said...

This was an awesome way add a table quickly. I used the simple code with a site I'm working on with Weebly.
Check Valve Distributor

John Lux said...

Hi Posters. Thanks to Katie for putting this together.
For my project I ended up using this sweet searchable spreadsheet creator wizard at KDMC
http://multimedia.journalism.berkeley.edu/tools/freedive/wizard

Bassman said...

Thanks so much for this code Katie, it's helped me solve a problem with a website I'm working on. A few tips I've found that might help others are:
- I had a lot of trouble with some lines of my spreadsheet never showing. I added a new column to the right of my sheet with a serial number and sorted on that and everything then showed up even though I didn't make the serial number column visible.
- I got rid of the line of code with the .gif file in it - as I didn't have an image file it just left a small rectangle on the screen.
- I had sums of money in one column and wanted them to be sortable though they were text so I typed the £ symbol and then enough spaces to ensure that there were the same number of characters in each one e.g. £100 and £23456.42 and £1m would be
£ 1,000,000.00
£ 23,456.42
£ 100.00
The column then sorts correctly even though it's text.
I'm sure there is a way of identifying the values as money but this was a simple workaround.
- if you want to search more than 2 columns you need to use brackets to bracket pairs of "or"s together or it won't work and you'll get errors. e.g. to search 3 columns I used "where upper(B) like upper("%%") or (upper(C) like upper("%%") or upper(F) like upper("%%"))"

Thanks again and I hope these tips help other people.

Bassman said...

Just a follow up to my comments just posted which looked fine in monospaced type but don't in the on-screen comment as most of the spaces have been edited out:
Below are the 3 numbers with s where I put a space:
£s1,000,000.00
£ssss23,456.42
£sssssss100.00
I hope that makes it clear.

Mark Gruen said...

Greetings Katie,

Thanks for your post. I would like to create a searchable database for best practices in youth development for a nonprofit that I work for. Would this work? The submissions that would make up the searchable content may be long(i.e. multiple paragraphs). Thanks for your time and energy!

Mark

leeshink said...

spot on with this write-up, i like the way you discuss the things. i'm impressed, i must say. i'll probably be back again to read more. thanks for sharing this with us.

Lee Shin
www.trendone.net

Pat Acal said...
This comment has been removed by the author.
John Patrick Acal said...

Hi Katie,

Until how many columns does it limits? For example I have columns from A - BA (almost 60+ columns)

It seems that it does have on error on querying all the data on my spreadsheet that says: Error in query: Request time out. I think it's because I have many columns to show?

Thanks in advance.
Pat

Phil Tubman said...

thank you very much, this is exactly what i want to do <3

BuildingtheNextGen said...

I will concur with everyone that this is a great post. Thanks!

Can anyone help me with URLs in the google spreadsheet? I have a similar issue as Assured (Feb 2012) with the links. I have a spreadsheet with a column of links. I tried adding the allowHtml: true (using the exact capitalization because I read somewhere that it was case sensitive), but it still will not display the HTML. Since I couldn't embed in wix.com or another web builder, I ended up getting a free host to just store this one script. Does anyone have any idea how to make those links active for others to click on? They show as links on the actual google spreadsheet. I just can't get it to display in the search table for others to click on.

If anyone has the answer for this, they will be a life saver. :)

Zebbie said...

Does anyone have any ideas on how to upgrade this awesome device to allow multiple-part searches - either by using eg: + - etc in the search box and/or by having dropboxes to select from before doing the free text search.

My data is in various languages and I'd like to be able to use a drop down to let the user choose which language/all data initially (that being recorded in a column in my spreadsheet), then return results from that subset only.

84344470-36a4-11e3-8ba3-000bcdcb8a73 said...

Love this! I know it's a bit outdated but it worked fantastically for a lookup table I was building. I ask the user to input their zip code or all/part of their school name and it outputs a ceeb code (this is for a college admissions site). Fantastic blog post - thank you so much!

Cristos L-C said...

I also found this to be incredibly useful! Thank you!

Casper Hernández Cordes said...

Hi. I have the same kind of question like Zebbie: Can you make more search boxes, and drop down menus in order to search on multiple parameters?

I have a spreadsheet with data about courses for job seekers in Copenhagen, and I want it to be searchable according to different paramters.

Here's the spreadsheet: http://bit.ly/oversigten

الاستشارية said...

Hello

great artical, I have a question please, I have a google blog and i want to add a search box that only Retrieves queries to a web page that i only can access and nothing more, it will only tell the visitor "sorry no results"or something like that.

your help is very much appreciated

Casper Hernández Cordes said...

Maybe this is useful:

http://multimedia.journalism.berkeley.edu/tools/freedive/wizard

?

Ian Harvey said...
This comment has been removed by the author.
Ian Harvey said...

Hi All,

Has anyone tried to get this working on an iPhone? Mine works fine on a desktop browser e.g. Chrome or Safari. It also works fine on an iPad, but it doesn't work on an iPhone. I also noticed the nowlookhear example doesn't work on iPhone either.

Thanks
Ian

Will Ritter said...

This is terrific. I set up and executed this perfectly, first time, in a simple .php page, however, has anyone gotten this to work when putting it into a Drupal page? It's my own Drupal setup, so I can and did set the content type to php, but even though it displays correctly and appears functional, when I actually try to search the spreadsheet, it just routes me back to the home page of my drupal site.

Veronica Harris said...

Hi Katie,

Is it possible to wrap the php code/file in a xml wrapper, to make it a Google gadget instead of a file on a file server?

Great search function. Took a little fiddling on our part we we're filtering columns and then importing into another sheet before displaying, but this is great!

Janet Maas said...

Has someone figured out why this won't work on my Intuition phone or Galaxy tablet? I love how it works on my desktop, it was exactly what I was looking for but would really like it to work on the phone

thanks

Janet

browntown said...

To anyone trying to get this to work on a HTML page

Use the above code on a PHP page and save it as whatever you like, say 'example.php'

Then on your HTML page insert an iframe where you want it displayed, the comments will not let me put the iframe tag in but you simply need to add a < to the start and > to the end of the below code.

iframe src="example.php" scrolling="yes" width="900" height="600">
</iframe

Where example.php points to the php file you created (in this case in the same folder as the HTML page) and the other parameters are just the height and width of the iframe which this will be displayed inside.

Cheers
Will

Bao Tran said...

Great tools.

Beside the Berkeley's site, I also found another site that might be useful.

http://flexlists.com

It is not interacting with GoogleSpreadsheet directly, but you can easily import .csv format from Google Spreadsheet.

As far as I can tell it is no cost, but do not know if they are going to be around.

Others like grubba and sodadb is also available but I think your solution is simple and clean.

Great Share.

Bao

website design said...

Glad to read your post...Thanks for sharing such a nice information, its beneficial for me. I have you bookmarked to check
out new stuff you post. Keep sharing.
website design

kendrapadilla said...

i like the help i get here. thank you for sharing this.

www.joeydavila.net

sarah lee said...

I really enjoyed reading your article. I found this as an informative and interesting post, so i think it is very useful and knowledgeable. I would like to thank you for the effort you have made in writing this article.


edupdf.org

lee woo said...

I like your post a lot! You should write some more on this!Great job coming with such terrific post!


mocsbar.com

Bravery said...

Hi Katie,
Now I have problem
After published spreadsheet, create a query based on the new spreadsheet URLs (ie "https://docs.google.com/spreadsheets/d/{key}/"). So copied key in this URL to your PHP code and the request is redirected to "https://docs.google.com/spreadsheets/d/{key}/tq?tqx=reqId:0" which returns a 404 error.
Any solution?
Google Visualization API and new spreadsheet doesn't work

Daria Jones said...

I tried the complex code on my pbworks wiki page ( I used the insert>HTML & Javascript plugin and got the strangest error message:
Error in query: Invalid query Query parse error: encountered & quot;&It'ID&gt' here" at line 1, column 13.
Was expecting one of:
&It; EOF>
"where " ...
"group" ...
"pivot" ...
"order" ...
"skipping " ...
"limit" ...
"offset " ...
"label" ...
"format" ...
"options" ...
"," ...
"*" ...
"+" ...
"-" ...
"/quot; ...
"%" ...
"*" ...
"/" ...
"%" ...
"+" ...
"-" ...

The simpler code does work for adding the spreadsheet but I'd rather hold out for something searchable. Any ideas on what I could try with the original code?

mogolman said...

Hello everyone, thanks for this excellent script! I was wondering, is there a way to make it function for non-latin scripts? (In my spreadsheet there are columns with EN and columns with Greek characters) It seems that the GR chars are not being retrieved by the search query

Thanks

Erik Collou said...

To not show the entire spreadsheet after entering the page, add a random string "here a lot of xx's"
in the following line:
if ($search > ''){ $search = $search;} else { $search = 'xxxxxxxxxx';}

Christie Jones said...

Hi Katie , this is an awesome tutorial . Just having problems when entering mixed data like date(may) with number(2) like Frank said. When I type in mixed data my website gets an query error "Error in query: Request timed out". This is realy urgent , I appreciate your time.

Yo said...

Hi,

I'm having a problem with my sheet:

https://docs.google.com/spreadsheets/d/1yFpSiDkpsXy-UKHaEWapMbQFARDbnMZaPZA97E-0tw0/edit#gid=0

The search isn't working. I keep getting the 404 error that other people have mentioned. Has anybody solved this?

Yo said...
This comment has been removed by the author.
Ryan said...

Error in query: Request timed out

The original sheet in the tutorial still shows up just fine when i plug it in. But with the changes in google docs/drive - spreadsheets are now shared/embeded with a different url line than when this tutorial was written.

https://docs.google.com/spreadsheets/d/*YOURKEYHERE*
gives me the error

Ryan said...

replacing "&hl=en_GB" with "&pub=1" from the spreadsheet link in the original tutorial seems to have done the trick.

'http://spreadsheets.google.com/tq?key=*********************&pub=1');

lee woo said...

Love it! Very interesting topics, I hope the incoming comments and suggestion are equally positive. Thank you for sharing this information that is actually helpful.


matreyastudios
matreyastudios.com

Marc Zo said...

Its very usefull to store a lots of information and easily find the database.The safest Renters website, experience online. Rent from verified owners and managers, and read reviews before you book.

VipReal .Danang said...

It's really awesome but I can not make it happen in Blog. From: "https://docs.google.com/spreadsheets/d/1uYebffrWdjR7ATqdo-3UGSsv1v1V4yNBp-m8YL0iAYE/pubhtml?" I've changed to

'http://spreadsheets.google.com/tq?key=*********************&pub=1');
but error with $search= $_REQUEST['search']; and time out.

I try to use simple code also but nothing shows. Plz help me. I really neeed this. Tks much.