20 September 2012

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!)


18 September 2012

If you have to use Facebook, you need Fluff Busting!


I do have to use Facebook sometimes. Thank heavens for the excellent 'clean-up' tool that F.B. Purity (a chap called Steve) have developed. F.B. stands for Fluff Busting and it simply gets rid of all the annoying rubbish including the Timeline.

There are so many features that I am going to cheat and simply paste what is listed on the F.B. Purity site. I'm sure Steve won't mind, and it ensures I don't get any bits wrong. I did correct some apostrophes though but I have yet to meet a really smart programmer who also can do apostrophes and I'd prefer he carries on developing the former skill!

All this is free! It's so worthwhile, though, that I made a donation and would urge anyone that can afford a pound or two to do the same. You do need to stop using Internet Explorer, though, to get the benefits. It runs on Firefox, Chrome, Opera and Safari but does not work on mobile phone browsers yet.

F.B. Purity can be used to display the Timeline in a single column

Hide "Read an Article" / "Trending Articles" / "Trending Videos" / Facebook Actions + Frictionless Sharing App stories

If you are not interested in what news articles others are reading, FB Purity gives you an option to hide these types of stories; this option also hides all the other FB Actions aka "frictionless sharing" app's stories, such as "watched a video" "read a book" "pinned something to pinterest" etc.

Fixes news article links that redirect via Facebook applications
FBP has an option for fixing the news article links that redirect via Facebook applications, and instead lets you go directly to the news article, this means what you read won't show up in other people's tickers or newsfeed, and you wont get hassled with the news organisations' application page wanting you to add the application every time you click a news article link.

Hide Facebook Questions
FB Purity has an option to hide other people's Facebook Questions and answers from showing in your news feeds

Restore Facebook's old Comment system, where pressing Enter or Return adds a new line
By default FB Purity reverts Facebooks comment system, back to adding new lines to your comment when you press Enter or Return. If you dont want this, you can disable it in the FBP Options screen.

Lets you force Facebook to sort your newsfeed by "Most Recent" stories first
Although Facebook has an option to let you choose what order you want your newsfeed sorted in, it seems to keep "forgetting" the user's preference. This option lets you force Facebook to always show you the "Most Recent" stories first.

Hide the News Ticker / Hide the Chat Interface
F.B. Purity adds options to hide both the new FB Chat interface and also the "News Ticker" aka the "Happening Now" sidebar, which is the box of constantly scrolling news in the top righthand side corner of your page.

Change the Font Size and Font and Background Colours
Facebook changed the font size really small on the homepage / newsfeeds, luckily FB Purity now has a font size feature, to access it, you just need to go into the F.B. Purity settings, by clicking the F.B Purity link above your Facebook homepage.

You can also choose what colour scheme or colors you want to view facebook with, you can enter any colour name or HTML/CSS colour code on FBP's options screen, and then Facebook will be displayed in your chosen colors.

Application Whitelist
The application whitelist lets you specify particular applications, whose messages you *do* want to see on your homepage. The script comes with a default list of applications that will not be hidden by the F.B. Purity app filter.

This list includes the following applications: FB for Iphone, FB for Blackberry, FB for Palm, FB for Android, Tweetdeck, Digsby, Tumblr and Selective Twitter.

For instructions on how to add applications you *want* to see to the list, see the F.B. Purity Application Whitelist Guide

If you do want to see all application messages in your stream, you can turn off the app/games filtering in your feed.

Extras Blacklist
By default the script hides the following "Extras" (though you can choose to see them if you wish):
Liked (Became a Fan Of)
Joined a Group
Became a Friend of
Is Attending an event
Attended an Event

Additional optional "Extras" that can also be hidden, but are shown by default are:

Facebook Questions (and answers), Created an event, Changed location (FB Places), Updated profile, Changed profile picture, Changed relationship, Commented on or likes status, Uploaded photo, Shared Photo, Tagged in, likes or commented on photo, Tagged in, likes or commented on album, Uploaded video, Tagged in video, Share a link, Commented on or likes link, Read an article, Trending articles, Posted a note, Tagged in note, Commented on wall, Commented on group wall, Commented on page wall, Commented on Event wall, Comments/Likes on "Top News" feed, Fan Page Stories, Recent Activity (on profile pages)

If you would rather see one, some or all of these messages, You can edit the default list, to your taste. Instructions on how to do this are here:
F.B. Purity Extras Blacklist Guide