If you are using Google Docs for Forms or Surveys, You might be getting lots of fake (spam) form submissions. The situation becomes more critical when you are sharing data, survey result or summary report to public and It would mislead the objective of forms or surveys. By default, Google forms doesn't support Captcha to stop spam submission. But you can't block spammers. In this case, you want to moderate the data and share approved/ filtered data to public.
Let us take an example to implement this step by step.
Suppose you have a "Comment" form having Name, Email, Website and Comment fields Then In spreadsheet, you have following structure:
1. You have to add one more column (say "IsValid") manually to approve/reject data.
2. Copy the key value from spreadsheet URL (in image it's "0AuqF9Lo00cllnUFB0Nmc")
3. Create a New spreadsheet which will have only approved data and will be shared to public.
4. Suppose you have to show Name, Website and Comment columns only. In first row, type column names manually.
5. In A2 cell, type following
----------------------------------------------------
=QUERY(ImportRange("Key","Sheet1!B1:F");"Select Col1,Col3,Col4 Where Col5 = TRUE";0)
----------------------------------------------------
Replace Key to copied key value. In our case the formula will be
----------------------------------------------------
=QUERY(ImportRange("0AuqF9Lo00cllnUFB0Nmc","Sheet1!B1:F");"Select Col1,Col3,Col4 Where Col5 = TRUE";0)
----------------------------------------------------
It'll display all approved/valid data.
In our formula, two functions are used:
Query: It's a built-in function that allows you to perform a query over an array of values using the Google Visualization API Query.
ImportRange: It lets you reference another sheet from the spreadsheet that you're currently editing. the common syntax is:
=importRange(spreadsheet-key, range).
In our formula, Sheet1's column B to F data are selected then it is filtered where IsValid = true and selected columns(1,3,4) are displayed in new spreadsheet.
Now, you can share this spreadsheet to public(file >share > private..Change > Public On the web) OR publish as a web page (File > Publish to the web).
When the form is submitted, an entry goes to original spreadsheet. When you set IsValid = true for the entry then It'll appear in shared spreadsheet. Thus you can filter spam out.
You can see another live example here.
Hope, It helps.