Lesson 11: Google Forms

11.1 The Spring Kickoff

As spring break approached, the club planned its first official meeting, the Spring (Ground) Break Kickoff, where students and community members could tour the garden site, meet one another, and choose the roles they were most excited about. To keep things organized, you and Maya decided to collect sign-ups ahead of time with an online form, shared on flyers around campus with a QR code that links to it. People could still sign up in person at the event, but most would register beforehand, which would help the club estimate attendance, prepare materials, and group people by interest.

That's a job for Google Forms. A form collects answers in a consistent shape, and it can save every response straight into a spreadsheet, where your code can work with it. No more retyping names and email addresses from paper sign-up sheets.

Besides contact information, the club wants to know what each person would like to learn, so the form needs a list of interests to choose from. Maya's list:

• Home Food Growing: turning patios, balconies and backyards into productive mini-gardens.
• Community Gardening: collaborative growing techniques and shared harvests.
• Sustainable Living: everyday practices that reduce waste and conserve resources.
• Teaching Through Gardening: skills you can pass on to friends, family and students.
• Nutrition & Wellness: how fresh produce supports a healthy lifestyle.
• Houseplant Care: keeping indoor plants thriving with the right light, soil and water.
• Organic Gardening: growing without synthetic chemicals, using compost and mulch.
• Hydroponics: growing herbs and greens in water instead of soil.
• Pest Management: safe, targeted ways to protect plants, such as companion planting.
• Microgreens & Sprouts: nutrient-dense greens on a windowsill.
• Vertical Gardening: growing up trellises and pallets when space is short.
• Container Gardening: the best vegetables for pots and buckets.
• Composting & Vermiculture: turning kitchen scraps into compost with the help of worms.
• Rainwater Harvesting: collecting rain in barrels to water the garden.
• Native Plant Restoration: attracting local birds and butterflies.
• Heirloom Seeds: the history and art of saving seeds for next year.
• Pollinator Gardens: plots designed for bees and butterflies.
• Mushroom Cultivation: growing oyster or shiitake mushrooms in shady spots.
• Medicinal Herbs: a "healing garden" of lavender, echinacea and mint.
• Farm-to-Table Cooking: what to do with a huge harvest of zucchini.
• Canning & Preservation: pickling, drying and fermenting the harvest.
• Garden Photography: capturing the garden through the seasons.
• Therapeutic Horticulture: gardening for mental health and stress relief.

11.2 Building the Sign-Up Form

You can create a form on its own at forms.google.com and connect it to a spreadsheet afterward. It's simpler to create it from the spreadsheet, because then the connection is made for you.

Before you start, it's worth asking your assistant how, both to see what it says and to practice checking it. Here's the question, in a new chat with your learner profile from Lesson 10. College Community Garden: Case setup: College Community Garden: Case Setup:

The assistant was right that this needs no code, and the overall idea is right: create the form from the sheet, or link an existing form to it, and responses arrive in a new tab. But check each step against your screen. Google moves these menus from time to time. At the time of writing, Google Sheets doesn't have a Form item in its Insert menu; creating a form from a spreadsheet is under Tools, then Create a new form. And in Google Forms, the Responses tab has a button labeled Link to Sheets. Your screen may differ again by the time you read this. As in Lesson 4. Getting Started with Google Apps Script in Google Sheets, when an assistant's directions don't match what you see, trust your screen, and tell the assistant what you actually see.

Here are the steps, starting from the club's spreadsheet (or any spreadsheet you want to practice in):

1. In Google Sheets, click Tools, then Create a new form. A new form opens in another tab, already linked to the spreadsheet, and a new sheet named Form Responses 1 appears in the spreadsheet.
2. Give the form a title, such as Spring (Ground) Break Kickoff Sign-Up, and a short description.
3. Add a question for First Name, and set its type to Short answer. Turn on Required.
4. Add Last Name, Email and Phone the same way, each as a short answer. Make the first three required. Phone can be optional.
5. Add a question titled Interests, and set its type to Checkboxes. Add each interest from the list above as an option. Checkboxes let people choose as many as they like.
6. Click Preview (the eye icon) to see the form as others will, and try submitting a response. Then look at the Form Responses 1 sheet: your response is there.

To share the form, click Send in the form editor. You can copy a link, and Google can shorten it for you. Many free tools turn a link into a QR code for a flyer.

11.3 Where Responses Go

Every submission becomes a new row on the Form Responses 1 sheet. Google adds a Timestamp column first, recording when the form was submitted, followed by one column per question, in the order the questions appear on the form.

Here are the sign-ups from before the kickoff, one from each of the club's first twelve members:

{"sheetName": "Form Responses 1", "rows": 16, "columns": 8, "data": [{"range": "A1:F13", "values": [["Timestamp", "First Name", "Last Name", "Email", "Phone", "Interests"], ["2027-03-02 09:24", "Isaac", "Cohen", "isaac.cohen@example.com", "555-0119", "Nutrition & Wellness, Native Plant Restoration, Mushroom Cultivation, Medicinal Herbs"], ["2027-03-02 09:34", "Elena", "Rossi", "elena.rossi@example.com", "555-0115", "Sustainable Living, Vertical Gardening, Container Gardening, Garden Photography"], ["2027-03-03 14:39", "Ben", "Okafor", "ben.okafor@example.com", "555-0112", "Community Gardening"], ["2027-03-03 18:29", "Dev", "Patel", "dev.patel@example.com", "555-0114", "Home Food Growing, Organic Gardening, Medicinal Herbs"], ["2027-03-05 15:18", "Keisha", "Brown", "keisha.brown@example.com", "555-0121", "Hydroponics, Canning & Preservation, Therapeutic Horticulture"], ["2027-03-07 09:13", "Hana", "Kim", "hana.kim@example.com", "555-0118", "Nutrition & Wellness, Hydroponics, Microgreens & Sprouts, Pollinator Gardens"], ["2027-03-08 20:32", "Jordan", "Lee", "jordan.lee@example.com", "555-0120", "Heirloom Seeds, Farm-to-Table Cooking, Therapeutic Horticulture"], ["2027-03-09 08:55", "Ava", "Lopez", "ava.lopez@example.com", "555-0111", "Native Plant Restoration"], ["2027-03-09 20:46", "Maya", "Thompson", "maya.thompson@example.com", "555-0110", "Pollinator Gardens, Mushroom Cultivation"], ["2027-03-12 10:19", "Farah", "Haddad", "farah.haddad@example.com", "555-0116", "Teaching Through Gardening, Houseplant Care, Hydroponics, Vertical Gardening"], ["2027-03-12 11:34", "Gabe", "Martinez", "gabe.martinez@example.com", "555-0117", "Vertical Gardening, Native Plant Restoration, Therapeutic Horticulture"], ["2027-03-12 15:19", "Cam", "Nguyen", "cam.nguyen@example.com", "555-0113", "Houseplant Care, Pollinator Gardens"]]}], "formats": [{"range": "A1:F1", "fontWeight": "bold"}, {"range": "A2:A13", "numberFormat": "@"}]}

Look at the Interests column. A checkbox question can have several answers, but they all go into one cell, separated by a comma and a space: Hydroponics, Pollinator Gardens. That's convenient to read, and it's something code has to deal with.

11.4 Counting One Interest

Maya wants to know how many people are interested in hydroponics, to decide whether to run a workshop. The response sheet is new, so describe it, including the detail about the Interests cell. Here's the request, in a new chat with your learner profile:

The assistant started by flagging something new: split. It's a string method that breaks text into an array, cutting it wherever it finds the separator you give it:

const interestsText = "Hydroponics, Pollinator Gardens, Heirloom Seeds"
const interestsList = interestsText.split(", ")
console.log(interestsList)
console.log(interestsList.length)
console.log(interestsList[1])

The separator is removed, and each piece becomes an item. If the text has no separator in it, you get an array with one item, the whole string. Try splitting "Community Gardening".

The description of column F made this possible. Without "separated by a comma and a space," the assistant would have had to guess what the cell looks like, and a separator of "," instead of ", " would leave a space at the start of every interest after the first, so " Hydroponics" wouldn't equal "Hydroponics".

Read. There's a loop inside a loop, written in one function this time. The outer loop goes through the sign-ups, one row at a time. For each row, split turns the Interests cell into a list, and the inner loop goes through that list, adding 1 to count for each exact match. The inner loop's counter is called j, because i is already in use by the outer loop. That's a common convention: i for the outer loop, j for the one inside it.

Why split at all, rather than checking whether the cell contains the word? Because comparing whole items with === is exact. If the form had options named Hydroponics and Hydroponics Basics, as an early draft of Maya's list did, a "contains" check would count both.

Run it. The answer should be 3: Keisha, Hana and Farah.

function countHydroponicsSignUps() {
  // Get the sheet by its name
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1")

  // Read all the data, including the header row, as an array of arrays
  const data = sheet.getDataRange().getValues()

  // This will keep track of how many people chose Hydroponics
  let count = 0

  // Start at row 1 to skip the header row
  for (let i = 1; i < data.length; i++) {

    // Column F is index 5, since columns start counting at 0
    const interestsText = data[i][5]

    // Break the cell's text into separate interests
    const interestsList = interestsText.split(", ")

    // Check each interest in this person's list
    for (let j = 0; j < interestsList.length; j++) {
      if (interestsList[j] === "Hydroponics") {
        count++
      }
    }
  }

  // Show the final count in the Execution log
  Logger.log(count)
}

11.5 Counting Every Interest

Counting one interest is useful. A table of every interest and how many people chose it would be better. Continuing the chat:

This is a harder problem than it looks. Counting Hydroponics needed one counter. Counting every interest needs a counter for each interest, and the code doesn't know in advance what the interests are. The assistant solved it with what you know, and it's worth reading slowly.

Read.

• Two arrays that work together. interestNames holds each interest the first time it's seen, and interestCounts holds its count at the same index. If interestNames[3] is "Hydroponics", then interestCounts[3] is how many people chose it. Arrays used in pairs like this are called parallel arrays.
• A helper function that searches. findIndexOfInterest loops through interestNames and returns the index where it finds a match. If there's no match, it returns -1. Since no real index is ever negative, -1 is a safe way to say "not found," and you'll see it used this way often, including by JavaScript's own methods.
• The counting. For each interest in each sign-up, the code asks the helper for its index. If it's -1, the interest is new, so it's added to interestNames with a count of 1 in interestCounts. Otherwise, the count at that index goes up by one.
• Writing the results. The two arrays are combined into an array of arrays, one [name, count] row per interest, which setValues writes to the sheet. It uses the numeric form of getRange you've seen before: start at row 1, column 1, and take as many rows as there are interests, and 2 columns.

One more thing about this code: the helper is written above the function you run. That makes findIndexOfInterest the first function in the file, so in the Apps Script editor, and on this page, it's the one selected in the menu at first. Choose countAllInterests before you click Run.

11.5.1 Read the warnings

The assistant ended with two warnings, and both are worth taking seriously:

• The Interest Counts sheet has to exist already. If it doesn't, getSheetByName returns null, and the next line stops with an error. Create the sheet before you run the code. (On this page, there's an empty one below.)
• Old rows can be left behind. The code writes over the top rows each time it runs. If there are fewer interests the next time, the extra rows from the last run stay underneath, and the table would look longer than it really is.

This is the kind of thing you learned to look for in Lesson 8. Loops and Repetition: which sheet the code changes, which cells, and what's already there. Here, the assistant pointed it out for you. It doesn't always, so keep reading for it yourself.

Notice also what the output doesn't have: a header row. The table starts in A1 with the first interest. You might want to ask for "Interest" and "Count" headers in row 1.

Run it. Choose countAllInterests in the menu first:

{"sheetName": "Interest Counts", "rows": 25, "columns": 4, "data": [], "formats": []}

// Searches an array for a value and returns its index, or -1 if not found
function findIndexOfInterest(interestNames, interestToFind) {
  for (let i = 0; i < interestNames.length; i++) {
    if (interestNames[i] === interestToFind) {
      return i
    }
  }
  return -1
}

function countAllInterests() {
  // Get the form responses sheet
  const formSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1")

  // Read all the data as an array of arrays
  const data = formSheet.getDataRange().getValues()

  // These two arrays work together: same index means matching name and count
  const interestNames = []
  const interestCounts = []

  // Start at row 1 to skip the header row
  for (let i = 1; i < data.length; i++) {

    // Column F is index 5
    const interestsText = data[i][5]

    // Break the cell's text into separate interests
    const interestsList = interestsText.split(", ")

    // Check each interest this person chose
    for (let j = 0; j < interestsList.length; j++) {
      const interest = interestsList[j]

      // See if we've already recorded this interest
      const existingIndex = findIndexOfInterest(interestNames, interest)

      if (existingIndex === -1) {
        // New interest: add it with a count of 1
        interestNames.push(interest)
        interestCounts.push(1)
      } else {
        // Already seen: add one to its existing count
        interestCounts[existingIndex] = interestCounts[existingIndex] + 1
      }
    }
  }

  // Build a 2D array to write to the sheet: one row per interest
  const outputData = []
  for (let i = 0; i < interestNames.length; i++) {
    outputData.push([interestNames[i], interestCounts[i]])
  }

  // Get the sheet to write the results into
  const outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Interest Counts")

  // Write all the rows at once, starting at row 1, column 1
  const numRows = outputData.length
  const numColumns = 2
  outputSheet.getRange(1, 1, numRows, numColumns).setValues(outputData)
}

Twenty of the 23 interests were chosen at least once. Five are tied as the most popular, with 3 people each: Native Plant Restoration, Vertical Gardening, Hydroponics, Therapeutic Horticulture and Pollinator Gardens. Check a couple of the counts against the Form Responses 1 sheet yourself.

Parallel arrays work, but keeping two arrays in step is fiddly: add to one and forget the other, and every count after that is attached to the wrong name. JavaScript has a better way to keep a name and its count together, called an object. It's the first new idea in the next part of the book, and when you've learned it, this code gets simpler.

11.6 Your Learner Profile

Four new items. As before, the response sheet's description goes in your prompt, not the profile. You may want to add it to your garden context, since the club will use this form all season.

11.7 Summary

A Google Form collects answers in a consistent shape, and when it's created from a spreadsheet, each response arrives as a new row on a Form Responses sheet, with a Timestamp column first. A checkbox question puts all of a person's choices in one cell, separated by a comma and a space, and split(", ") turns that text into an array. Counting every value in a list takes a counter for each value; with what you know, that's two parallel arrays and a helper function that returns an index, or -1 when there's no match. Assistants' instructions for using an app can be out of date, so check each step against your screen, and pay attention when an assistant warns you about what its code will do to your data. Personal information collected by a form deserves care: collect only what you need, and keep real responses out of your chats. In the next part of the book, you'll put the club's spreadsheet to work across Google Workspace, starting with objects.