Help with Google Spreadsheets?

Right, here's the problem.

People fill in some data on a Google Form which is automatically collected in Sheet1 of a spreadsheet. Some formulae are applied as it comes in and some results are calculated. On Sheet2 of that spreadsheet there is a cell where someone can type their name and a simple LOOKUP formula gets their result from Sheet1 and displays it.

The real data and formulae are vast and quite complex but the essence can be simplified in this example:

Sheet1: responses and the calculated 'feedback'

Sheet2: an example of how a respondent could see their result

So far, so good. I can put a link to the sheet at the end of the survey. But...

Problem #1

Users would need editing rights in order to be able to enter their name. How can I publish the spreadsheet but only give editing rights to one sheet.

Problem #2

Users can see (and edit!) Sheet1 too which could be disastrous. How can I hide Sheet1 without their editing rights being able to access Unhide from the menu?

Problem #3

Even if I can restrict users to seeing only Sheet2, there is a danger of their messing up the formula in D4 that shows the result, rendering it useless for the next user. How can I protect everything except cell B4? (The 'protect range' instructions seem very confusing indeed in practice!)

Problem #4

Assuming that one or other of these problems isn't readily soluble, how about having Sheet2 accessible as a totally separate file so that there would be no access to the source data and other people's data? In Excel it is possible to link to data across different files but how can you do this in Google Spreadsheets? There is something called importrange which did collect and display the data from one file to another but it didn't collect the formulae so no matter how often a user tried to enter another name in the sample above, he or she remained Fred (or whoever had been displayed when importrange was run).

I am sure that there's a script or some way to get this working but it has escaped me for a day or two now. So over to anyone out there who can think of a nice solution. 

Here is a link to the simple sample if you want to work with it. (Make a copy - or several people working on the same file could get confusing!)

Web display - some statistics to bear In mind these days

Windows 7 overtook Windows XP at last a little while ago. Mac and Linux maintain a small but constant share and it will be interesting to see how Android and mobile browser use statistics grow.

There are three clear leaders in the browser field: Firefox, Chrome and Internet Explorer. Google and Mozilla continue to eat away at Microsoft's domination. Will IE9 halt that decline? Will Chrome take over the No.1 spot?

Time was when we designed web pages for users with 800 pixel screens with some nods towards the 1024 folk with nice backgrounds to occupy the space either side. Now, it would seem safe to move to utilise an extra 25% of monitor estate although we may have to make mobile versions more readily available too. Unless your site is going to appear as a small box on the screen it may be time to look again at all those 760 pixel pages! But let us hope text lines don't start stretching.


These charts have been made using Google Documents' Spreadsheets and feature data from W3Schools. The nice thing about Google Charts is that now you can add them to sites not as a mere image but as an interactive feature where you can move the mouse to see more data and if I change the 'home' spreadsheet these will be automatically updated too. That is just so cool.

Google Charts are still in development and I noticed that I wasn't able to add a text box (for the thanks to W3Schools bit, for instance) and the Spreadsheet is still a bit clunky compared to Excel. Try as I did, changing the dates so that they would actually be interpreted as dates not text proved impossible (unless I added 1 to each which would have been a drag) and percentages seem to have to be either 0 or 2 decimals (or umpteen, perhaps) just not 1 decimal point as I wanted. No doubt someone will tell me how to fix these things under the bonnet before long!

Beware! Statistics Are Often Misleading


You cannot - as a web developer - rely only on statistics. Statistics can often be misleading. Global averages may not always be relevant to your web site. Different sites attract different audiences. Some web sites attract professional developers using professional hardware, while other sites attract hobbyists using old low spec computers. Also be aware that many statistics may have an incomplete or faulty browser detection. It is quite common by many web-stats report programs, not to detect the newest browsers. (The statistics above are extracted from W3Schools' log-files, but they are also monitoring other sources around the Internet to assure the quality of these figures).

Woo-hooo, I can do forms!

There are probably a hundred other things I should be doing this evening but I am so pleased about what I can do with Zoho Creator that I just have to tell someone.

Forms on the web. Things that collect information. Things that people can type into on a web page and hit a button and you get the data a few seconds later. Now all you PHP geeks can stop reading but those of you who, like me, have no clue about PHP, don't have MySQL or other strange sets of initials on their web server and especially those who haven't the foggiest whether they do, but can copy and paste some html code into a web page or a web log should read on . . .

I have used CoffeeCup's Form Builder to create some data gathering forms on some sites but two things almost brought me to my knees. The first was that many clients don't have hosting for their web sites that has all the extra bits that can handle forms (and didn't want to pay the not inconsiderable sums demanded to get it). The second was that for those who had (or did) the flaming thing didn't work. Eventually I discovered that an extra line of PHP code was needed to make it work on at least one well-known host. It wasn't the sort of code that comes naturally, with odd expressions, essential spaces and e-mail references that were enough to confuse a giraffe in a forest. Still more communication proved necessary to persuade the host to make the mail address work too. However, at the time, it was the best I could do and it worked. I just dreaded the client asking me to change it one day!

Now I've discovered what Zoho Creator can do. It is amazingly simple. There are ready-made examples available. You edit one, get the code and paste it on a suitable page. That's it! Zoho seem to take care of all the 'server side' stuff. What's more, the data can be collected and viewed in a spreadsheet amongst other options and you can provide links for others to see the data on-line. That bit, sharing data on-line, I have referred to before and is excellent. I just hadn't noticed the forms before, despite circulating the video guide in a previous post.

So I shall now be up all night putting samples everywhere and writing to people whom I hadn't been able to help before. All sorts of uses, including getting student feedback and giving visitors a place to comment on the webtools site. That one is my first effort. Took 5 minutes start to finish.

London, because it's the least inconvenient . . . ?

Ten people trying to decide where to meet. Everyone has a good reason for not wanting to go somewhere as well as their own preferences. How do you decide? The boss says "London, because it's the least inconvenient" and he may well be right but it would be nice to see it in writing. Rather foolishly, I thought it would be easy to knock up a spreadsheet that would add up people's votes and work out what was actually the most popular, least inconvenient or whatever. Partly because I decided to include all sorts of checking things, validation and partly because I'd never used =AND before, it took ages!

No doubt the meeting's been arranged in London by now and it really needs to work on the web to do what I wanted but maybe all the effort will be of use to someone teaching Excel or trying to figure out some formulae. The sheet's protected but there's no password. Let me know if you use it, have a better idea or spot errors.

It's a small file that you can get here. There's an on-line version that actually seems to work using Google spreadsheets. Amazing! As I might really use this I'll just give the team the address for now but will release the link genrally soon.