Section 4: Excel/Google Sheets, data analysis and reporting

Scenario 34: Grouping open-ended survey responses into themes

Level: Intermediate · Tools: Google Forms, Google Sheets AI function and Gemini in Google Forms

Who and in what situation

Ece, who works on the administrative services team at a private hospital in İstanbul, classifies responses to “your suggestions or comments” each week. Responses can touch on multiple topics, such as cleanliness, waiting, wayfinding and communication.

How it is done today

Ece reads the responses one by one, assigns theme labels and copies example sentences into the report. She may label the same comment differently from week to week.

How to do it with AI

  1. She opens the Responses tab in Google Forms and reviews the automatic summary of the open-text question. [77]
  2. She enables See theme percentages. Gemini groups the themes; the result is displayed in a new Google Sheet with theme counts and a pie chart. [77]
  3. She edits names that do not match the organisation’s classification using Modify themes, Add or Delete category. [77]
  4. To label individual rows, she enters =AI() or =Gemini() in Sheets, adds the response range and selects Generate and insert. AI function can perform categorisation and sentiment analysis. [78]

Example prompt

Label the response in {the comment cell} with only one of these themes: cleanliness, waiting, wayfinding, communication, thanks, other. If there is more than one topic, choose the main complaint theme and provide a brief reason. Do not reproduce any personal health information.

Evidence and real-world example

No published independent case study was found for this hospital administration scenario. Google’s official pages document Forms response summaries, theme generation, percentage views and categorisation with Sheets AI function. [78] [77]

Points to consider

Theme generation works best with 8-200 responses; the option is unavailable for more than 200 responses. Some Actionable Insights features are in beta and subject to plan requirements. [77] AI function does not access all Drive files; it generates text and must be supplied with the specified range. [78] Mask personal data according to organisational policy and validate the labels against sample responses.

Sources for this scenario

  1. Summarize and analyze responses with Gemini in Google Forms (support.google.com)
  2. Use the AI function in Google Sheets (support.google.com)