Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
Dear web fanatics and IT boffins.
I am a complete n00b to all this web stuff and built my first website using Google Sites last year which has developed nicely.
However I have a database in Excel/Google Sheets format with a fair amount of entries on it, that I wish to make searchable and easy to use whilst not being monumentally slow, hosted on a webpage (or at least interactive/searchable)
The Google Doc is 31,000 entries and only 9 columns. This amount of entries will become smaller shortly but still expected to be at least half that.
I found a wicked example of what I want and how it could work here:
http://katiepiatt.blogspot.co.uk/2011/05/make-searchable-online-database-or-i.html
The trouble I have is two fold.
One, the database/sheet needs to be private. Two; I only have Google Sites which apparently doesn't allow php 
I have read so much on this I am now cross-eyed and more confused than when I started. Anyone got any bright ideas what I could do?
I trialled loading the core spreadsheet in an iFrame and took about eleventy billion years to open, so Ctrl+F is not much of an option.
Please help 
[Edited on 19-05-2014 by Jambo]
[Edited on 29-05-2014 by Jambo]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
31k rows is no problem at all.
How often will the data change?
Could import it in to to a MySQL db and put a little PHP front end on it, that would solve the problem but you'd still have the challenge of updating the data.
Might mean you need a more complicated PHP front end, or might mean you get familiar with phpMyAdmin.
Privacy can be sorted by authentication on the app, not five minutes but also quite possible if you have your own PHP. Need to decide whether you're going to go with a new set of user records each with a pass, or share one secret pass, or take your list of users from somewhere else.
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
Data will change throughout the month, but only 2-3 entries at a time max. It remains largely unchanged just added to sporadically.
This is a little project for work that I was hoping I could have a stab at doing something basic. However it would appear that this is going to require an SQL license and PHP hosting... Meaning it will cost to much and be pushed back to the relevant department.
Balls.
PHP hosting much for a year with a domain etc?
|
VrsTurbo
Premium Member
Registered: 8th Jun 10
User status: Offline
|
quote:
Originally posted by Jambo
Data will change throughout the month, but only 2-3 entries at a time max. It remains largely unchanged just added to sporadically.
This is a little project for work that I was hoping I could have a stab at doing something basic. However it would appear that this is going to require an SQL license and PHP hosting... Meaning it will cost to much and be pushed back to the relevant department.
Balls.
PHP hosting much for a year with a domain etc?
MYSQL is free for a small projects. MSSQL is stupid licence wise
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
I won't go into details but I am involved in license infringement in one area of my job, double standards comes to mind 
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
No license with MySQL.
No hosting fee if I host it.
quote:
Originally posted by Jambo
PHP hosting much for a year with a domain etc?
http://my.vidahost.com/aff.php?aff=912
Don't forget your CS dizzy - CS10HOST
[Edited on 19-05-2014 by Ian]
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
Cheers me dears. If it goes to requiring hosting etc, then its not impossible. But It really ideally needs to be done through Google sites as our corporate system runs through this and I can lock the sites to be viewable by anyone with a work.com email address.
I have read some things on there hinting that Google Apps Script can emulate/run (I don't know the proper lingo) PHP in an embedded object.
So I am looking into this, else it looks to be a fancy Excel spreadsheet or palming it off onto IT to sort out.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Can Google sites not iframe the faster externally hosted web site?
Will look like its coming from in there but in fact Google is bringing in the page content.
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
Pretty sure it could, but it has a flaw. The iFrames never load until you confirm a security option on Chrome (The browser this will be used in)
Wont work smoothly enough for people here.
I am looking at using Google Apps script to see if I can perform searches, if not make elements to jazz up the spreadsheet.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Ian
http://my.vidahost.com/aff.php?aff=912
Don't forget your CS dizzy - CS10HOST
Tsohost's ('same' company/datacentre) 'lite' hosting is cheaper at £15pa.
Jambo - If your company uses Google Apps (so i'm assuming everyone has a Google account), then why not use Google Sites (the link you've posted etc) and lock it to 'Private'?
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
It is Dom, built a few of them.
issue being the lack of PHP for the database.
The issue is, I am doing this as a small project to be inexpensive. If it requires SQL and PHP stuff, it is totally possible but will be farmed out to a different department.
Wanted to get this done myself but its looking like the only real option I have is using the source Google Sheet and some filters, itl work but wanted something a bit cleaner with a good search function. Its basically a Libray database and users will be searching for different publications, the users need speed and simplicity. Google sheets is "fine" but will just be a bit clunky and messy.
Reading up on HTML database now, and using the built in Google search function on the google site, see if that helps.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Jambo
It is Dom, built a few of them.
issue being the lack of PHP for the database.
My bad, thought publishing Google Spreadsheets (etc) as a 'site' integrated it with Google App Engine. Saying that, i believe Google offer free usage of their App Engine otherwise similarly Amazon offer a free package on their AWS service (you could deploy a small web server and use that to host the files).
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
Just having a look at their app engine now (Google)
Says you can do PHP. But this will be an add on, not a seperate web page?
is there a way that could work if I put the code in there and make it an "app" and place that on the google site, using the Google doc as the database.
If that makes sense.
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
Found another workaround
http://csessig.wordpress.com/2013/01/03/turning-excel-spreadsheets-into-searchable-databases-in-five-minutes/
Can upload the xml file to Google drive to host it, and in theory use it as a Gadget to make it work.
However, so far I just keep getting erorrs probably because I know bugger all about xml, Java and a teenie tiny amount of html
Frustrating. I tried loading it in the Google Apps editor and keep getting all sorts of xml syntax errors even though I am copying code from Google and other sources that are live and working! Probably the incorrect order, but I used the example above as a template and just changed the style sheets reference and datatables reference for their own cdn stuff which it recommends you do anyhow.
Looks like I am not going to be able to achieve this which is probably unsurprising, but nonetheless annoying!
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Sounds like a complete bodge and arse-about way of doing things, especially as you'll have to continuously manually update the HTML table data. If you desperately want to use Google Spreadsheet's then you can publish (can fetch the data if the spreadsheet is private, just have to implement OAuth) the data as JSON and use that with one of the JQuery sortable table plugins (DataTables, Dynatable etc).
Still confused why you can't host this with the company (either internally on the intranet or on their internet hosting) or purchase some hosting or use Google Apps/Amazon AWS free tiers? 
Edit - Similarly i'm confused why you're mentioning XML - there's no mention of it on the link you posted nor any need for it; all you're doing is a copy-pasta of the spreadsheet data and using 'Mr. Data Converter' to format it to HTML.
Edit 2 - Could you not use Access and forms? That way it could be stored on a network share.
[Edited on 29-05-2014 by Dom]
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
We can Dom, but it wouldnt be me who sorted that kind of thing. The idea was with my limited knowledge I could knock up a basic searchable database online hosted on our Google Site to keep it within our network (i.e not published to wider web and only employees with correct email domain can log in)
Its entirely possible to get this done by the dedicated IT department who would have the knowledge and tools etc, this is simply a chance to get the job done on the cheap and "quickly" although this is turning out to not be the case
Thanks for the input, very helpful 
I have since found using CoffeeCup and preview that my xml is infact OK and its more than likely the fact Google sites wont display it, so looking to go back to option one and get a .php hosted page here with a username/password if not, then it gets farmed out.
Cheers
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
JQuery stuff means 31k rows client side - you sure?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
If you've got an intranet site, can you not just ask them to store an ASP (server-side scripting language like PHP) file and create a SQL DB (or worse case use a Access DB) and you do the leg work?
Edit - Again, you're not handling XML data, rather just converting spreadsheet data to HTML and using a JQuery table plugin to make it sortable and look 'pretty'.
[Edited on 29-05-2014 by Dom]
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
Thats my plan Dom, have asked them. Seeing what they come back with.
And Ian, I have no idea, but that sounds like a bad one!
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Just that your browser needs to download, query and sort the entire data set. I suppose that's not a complete disaster as you're probably running fairly new machines etc. but perhaps I'm old school, that used to be an absolute no no when I was a lad.
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
I know nothing mate. My machine is brand new with SSD/i7 and a TB broadband line but others will struggle I guess. Defaulting to the PHP idea if I can get the hosting if not not my problem any more so thanks for all the info you beauties
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
You're probably OK even on older stuff. Don't forget I learnt this in the late 90s
|
Jambo
Member
Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
|
lol. Well I am self taught on all of it and know very little. I am enjoying learning though, Feel like I should have been this inspired at school tho
|
