2011-03-28

Apply quota on Online registration by Form at Google Docs

I often used the Online Form from Google Docs, which I find very useful to solicit user inputs for event registration.

However, so far, the logic exposed in the form editor is none. Therefore the form content is quite static.

Recently I have a need to enforce a quota on the event registration. After searching on internet (even at the Google Help), there is no straight answer. However, inspired by some pages realizing the extra logic at the Google Spreadsheet, I finally come out with the following approach:

(1) Design the Form and the spreadsheet sheet as usual. Please note that I still set the spreadsheet to "private" lest the user input data are improperly exposed.



(2) Add a new sheet (called stat, shaded shaded in yellow below).


In the sheet, I do some calculation logic:
B1: count the number of registration at Sheet1
B2: calculate the number of remaining seats (I set the total number of sheet to 5 for the sake of example)
B3: use the hyperlink function to set a link. The URL is actually the form link as marked in red below:



B4: contain the rejection message when the event is full
B5: use the if function to give a conditional output

(3) Then I publish the content of B5. Please remember to click the "Automatic republish when changes are made"


Please note that although my spreadsheet is set to private, there is no contradiction to the data publishing, as explained in the following Google Help.


(4) Finally, I write a html page with IFRAME to refer to the published data:



The final result is:



The link will be automatically changed when the number of registration reaches 5