Survey quotas in Google forms

A few days ago I figured out that with some effort, I can utilize the google forms API to create a google forms survey that is able to update itself with quotas, and filter out respondents when quotas are reached.
It's an option all commercial survey (like SurveyMonkey, FluidSurveys and many more...) tools have, but for google forms to have it, we have to work a little with the API.

Basically, what survey quotas mean, is that we put some background question at the beginning of the survey (e.g., what type is your car?) and once we get "enough" respondents (reach the quota) of a certain car type, we close the option to answer the questionnaire if you have that type (the respondent will see a text thanking him, and saying we have enough respondents from his group, or any other disqualification text).

This can be especially useful if you plan to give some kind of incentive to the respondents (because you'll want to limit the expense, and using quotas will allow you to control the number of answers you get from each group.

I decided to put it here, maybe it'll be useful for someone (and save some time).

I assume you have some basic knowledge on google forms. The process is fairly simple and I show it step-by-step.

Here is the how-to:

First step: build your questionnaire with a quota control question

Decide on the quota question. Some examples are:
What is your age group? (1) 18-30; (2) 31-40; (3) 41-50; (4) 51-60; (5) 61-70; (6) Above 70
In the example I show here, I used: what kind of car do you own? with a multiple response (single value), to choose from: Volvo; Honda; Hyndai; Opel; Don't own a car. This (the quota control question) should probably be the first or one of the first questions in your questionnaire. With some effort you can do this with most item types google forms have, but the reasonable options would be: multiple choice, checkbox, choose from a list).

Example for a "kind of car" quota question positioned at the first page of the form.

Second step (optional): add a disqualification page

Once you reached the quota of some group (Volvo for example) you might want to show a custom message (e.g. saying we've reached that quota). If you are interested in such a message add a page break right before the end of the questionnaire. I'm not showing it here because if someone do wants to answer the questionnaire, he can just click "back" and choose a different option. It's better to disguise the fact that he's been filtered out (especially if you have incentives for answering).

Third step: create a "counting table" in the response spreadsheet

Each google form is associated with a spreadsheet. The spreadsheet is what we are going to use to track the respondents under each option and is also the trigger for the API code. What I call a "counting table" is just a bunch of formulas we are going to write in order to count whatever it is we want (in this case the answers to the quota question). You can find the response sheet in the same folder as the form itself (usually it has the same name and "(Responses)" next to it).
Open the spreadsheet. What you see is a blank table with headers representing the questions you added in the form. If the quota question is the first question of the survey, it should appear in column B.
You need to open up a new sheet (by clicking on the small "+" at the bottom of the spreadsheet. This will open up a new sheet called "sheet1" or something similar. By right-clicking you can change its name (e.g., "FollowQuotas") and then open it up.

Open up a new sheet by clicking on the plus and then rename the sheet to something meaningful, like "FollowQuotas".

Now, you can create the "counting table". For this you can use a pivot table (Insert -> Pivot table report, and then it is automated by the google sheet, and you won't have to open up a new sheet as I explained before because this command opens up a new sheet for you), but I rather have more control and write the formulas myself. You can experiment with all kinds of formulas and/or combination of formulas and questions, but the simplest version goes something like this: use the first link for titles, and the first column for names (note that it should be identical to the possible answers in the form otherwise it won't work). The second column does the counting. You can use a simple formula called countif: "=COUNTIF('Form Responses 1'!B:B,A2)". Change the sheet name, column and second parameter (A2) according to needs and to the row. Third column should contain the quota itself (e.g. how much answers do we want), and the fourth column is the remaining questionnaires to be collected from each option (the difference between the third column to the second column).

The "FollowQuota" sheet with a custom made counting table. Range B2:B6 is computed with COUNTIF formulas, column C is defined by typing the limit you want, and column D is the difference of C-B.

Last but not least: coding the API to manipulate the form while answers are being collected

Now we're almost done, what remains is some triggering and coding.

First in the response sheet go to "Tools" and then click "Script Editor". This opens up the very powerful scripting option of google docs. If you are asked about the type of code you want to open you can just choose start a blank project (because I'm going to give you the code). For future reference I recommend experimenting and viewing the ready codes offered by google, because they can teach you a lot...

Paste the following code. What this script does is to loop through the "counting table" and check if we reached a quota in one of the rows (remaining to quota = 0). If he finds such a row, it changes the navigation rules from navigating to the next page into submitting the questionnaire. Don't make an effort reading this very small text size, just paste it - it will color up and have a good letter size):

function QuotaManagement() {

  var sheet = SpreadsheetApp.openById(YOUR_SPREADSHEET_ID);

  var rows = sheet.getSheets()[1].getDataRange(); // Get the second sheet which contains the information we need on how much was collected at each option

  var numRows = rows.getNumRows();

  var values = rows.getValues();

  var formID = YOUR_FORM_ID // This is the form's identifier. Make sure its shared and you have "edit acces" (or you are the owner)

  for (var i = 1; i <= numRows - 1; i++) { // starts from i=1 (which is row 2 on the sheet = first row of data in sheet FollowQuotas)

    var row = values[i];

    if (row[3] <= 0) { // we've reached the quota and has to stop collecting these answers

      var editForm = FormApp.openById(formID); // open the form for editing mode

      var quotaQ = editForm.getItems()[0].asMultipleChoiceItem() // Must be cast as the correct type before editing (otherwise we get a TypeError). Change the multiple choice if you have a list or a checkbox.

      //var qt = quotaQ.getTitle(); // I added this for debugging. Just to make sure I get the right item...

      var choices = quotaQ.getChoices(); // get the choices of this question (in our case: car type)

      var newChoices = choices;

      for (var j = 0; j <= choices.length-1; j++) { // now go through all choices to change the goto parameter of the relevant choice

        //newChoices[j] = quotaQ.createChoice(choices[j].getValue(), FormApp.PageNavigationType.CONTINUE);

        if (choices[j].getValue() == row[0]) { // This is the choice which should now be filtered out

          newChoices[j]= quotaQ.createChoice(choices[j].getValue(), FormApp.PageNavigationType.SUBMIT); // Changes the option to submit (with a simple modification, can also set to a custome redirect if you want to collect background data...)

        }

      }

      quotaQ.setChoices(newChoices);

    }

  }

};

Do note that you need to change "YOUR_SPREADSHEET_ID" to the spreadsheet ID (which you can just copy from the spreadsheet URL, its the weird key right after the "/d/" (not including) and up to the "/edit" (not including). The same goes for "YOUR_FORM_ID" which you can get from the form's URL in a similar manner. I'm using google apps for business so the link might be a bit different (in terms of the "/d/"), but you'll figure it out. It's quite clear what's the ID and what's not. If your quota is not the first question you must also change the "0" in line "var quotaQ = editForm.getItems()[0].asMultipleChoiceItem()" (0 stands for the first item, 1 for the second item, etc.). If your quota question is of type multiple choice or item list you will also have to change the "asMultipleChoiceItem" to whatever it is you are using...
As a last step you have to set up the code's triggers. In the scripting window click Resources-> Curernt project's triggers and set it up to run "QuotaManagement" in the event of "From spreadsheet" + "On form submit" and click "Save". You might be asked to authorize the script ("this script requests authorization to make changes...") just confirm it.

Add the current project's triggers. Select the function we've created and the events to trigger it and then click "Save".

You're done!

All this can probably be automated in API to import the code, set the counting table automatically, and do the rest, but it would take some time to create this automation. If you're up to the task and do something like this, post it somewhere and send me the link.
If you have any comments, or something doesn't work correctly - notify me by leaving a comment.
Good luck experimenting further.
You can also visit Sarid Institute's website, and find out some more information about what we do.