How to build a Custom Sentiment Analysis System for Operations Feedback Using Google Forms AppSheet and Vertex AI

February 14, 2025

20 min read

How to build a Custom Sentiment Analysis System for Operations Feedback Using Google Forms AppSheet and Vertex AI

This tutorial empowers Operations Managers and Directors to build a powerful, yet simple, sentiment analysis system using Google’s no-code platform. We’ll walk you through every step, from setting up Google Forms for feedback collection, to using AppSheet for administration and visualization, and leveraging Vertex AI for automated sentiment analysis. No coding skills needed! Discover how to gain immediate, actionable insights from your team’s feedback, improve communication, and drive a more positive and productive operational environment. This is your end-to-end guide to AI-powered operational improvement.

For a deeper understanding of the AppSheet application demonstrated in this tutorial, we are pleased to offer full exploratory access. To request access, kindly:

Register via this Google Form: link
Ensure your submission utilizes your Google Workspace email address.
Google Group link: link
Final Google Form: link

Part 1: Setting up the Feedback Collection with Google Forms

Welcome to the first hands-on part of our tutorial! We’re going to start by building the foundation of our sentiment analysis app: The feedback collection form using Google Forms.

If you are already comfortable with Google Forms and its settings, you can skip directly to Part 2. However, please take a moment to review the list of questions provided in this Part, as these are carefully designed to capture the feedback needed for effective sentiment analysis. Otherwise, follow along in this section for a step-by-step guide to setting up your feedback form and understanding the question rationale.

Why Google Forms for Operational Feedback?

Google Forms is the perfect starting point for gathering feedback from your operations team, and here’s why:

Simplicity and Ease of Use: Google Forms is incredibly intuitive and requires no special technical skills to create or fill out. Your team members can quickly submit their feedback without any hassle.
Accessibility: Being a Google product, Forms is easily accessible to anyone with a Google account (which most organizations already use for email and other services).
Automatic Data Collection: Responses are automatically organized and stored in a Google Sheet, which seamlessly integrates with other Google Workspace tools like AppSheet and Apps Script – making our workflow smooth and efficient.
Customizable and Versatile: While simple to use, Google Forms is also surprisingly flexible. We can create various question types, customize the look and feel, and control form settings to meet our specific needs.
Cost-Effective: Google Forms is free to use as part of Google Workspace or even with a personal Google account, making it a budget-friendly solution for organizations of all sizes.

In this section, we’ll walk through creating a Google Form specifically designed to capture monthly operational feedback that we can then analyze for sentiment.

Creating the “Monthly Operational Feedback” Form: A Step-by-Step Guide

Let’s get started! Follow these steps to create your feedback form:

Open Google Forms:
- Go to your Google Drive (drive.google.com).
- Click on the “New” button (usually in the top-left corner).
- Hover over “More” and then select “Google Forms” > “Blank form”.

Screenshot 1 - Google Drive New Menu showing Google Forms option

Title Your Form:
- At the top left, where it says “Untitled form,” click and enter the title: Monthly Operational Feedback.
- You can also add a form description below the title to provide context for your team. For example: “Please provide your honest feedback on our monthly operations. Your input is valuable for continuous improvement.”

Screenshot 2 - Google Form with Title “Monthly Operational Feedback” and Description

Add Question 1: Email Address
- By default, your form likely starts with an “Untitled Question.” Click on it.
- Question Title: Type in: Email Address
- Question Type: Ensure it’s set to “Short answer” (it’s likely the default).
- Required: Toggle the “Required” switch to ON. We need the email to identify respondents and departments.
- Description: Click the three dots “More options” icon at the bottom right of the question and select “Description.” Type: “Please enter your company email address. This will help us understand feedback in the context of departments and teams.”

Screenshot 3 - Google Form Question 1: Email Address - Short Answer, Required, with Description

Add Question 2: Department (Dropdown)
- Click the ”+” button on the floating menu on the right side to add a new question.
- Question Title: Type in: Department
- Question Type: Click on the dropdown menu (likely says “Multiple choice” by default) and select “Dropdown”.
- Required: Toggle the “Required” switch to ON.
- Options: In the “Option 1” field, type your first department name (e.g., “Production”). Press Enter to add more options. Add the following example departments (customize these to your organization’s structure):
  - Production
  - Logistics
  - Customer Service
  - Sales Operations
  - IT Operations
  - HR Operations
  - Finance Operations
  - Other (Please specify)
- Description : Add a description: Please select your department to help us categorize feedback effectively.

Screenshot 4 - Google Form Question 2: Department - Dropdown, Required, with Department Options and Description

Add Question 3: What’s going well and what to improve (Paragraph)

Click the ”+” button again to add a new question.
Question Title: Type in: What is one thing that is currently going well within your team, and one thing that could be improved to help your team perform even better this month?
Question Type: Select “Paragraph”.
Required: Toggle “Required” to ON.
Description: Add a description: “We want to celebrate successes and identify areas for growth. Please share one positive aspect of your team’s performance this month and one area where improvements could be made.”

Screenshot 5 - Google Form Question 3: Positive & Improvement - Paragraph, Required, with Description

Add Question 4: Describe any process challenges (Paragraph)

Click the ”+” button one last time to add the final question.
Question Title: Type in: Describe any challenges or frustrations you are experiencing with our current operational processes this month. (Copy and paste).
Question Type: Select “Paragraph”.
Required: Leave the “Required” switch OFF (as this question is optional, but encourage responses).
Description: Add a description: Your feedback on process challenges is crucial for streamlining our operations. Please detail any frustrations or obstacles you’ve encountered in our processes this month.

Screenshot 6 - Google Form Question 4: Process Challenges - Paragraph, Optional, with Description

Why These Questions Matter for Operations Managers/Directors

As an Operations Manager or Director, your success hinges on the smooth and efficient functioning of your teams and processes. This isn’t just about hitting targets; it’s about fostering a productive environment, proactively addressing roadblocks, and continuously optimizing for peak performance. That’s precisely why this monthly feedback form is a critical tool in your arsenal. The ‘Email Address’ and ‘Department’ questions provide essential context, allowing you to segment feedback and pinpoint trends within specific teams or departments. Understanding who is saying what is the first step to targeted action. Question 3, focusing on both successes and areas for improvement, offers a balanced view of team morale and performance, highlighting what’s working well to reinforce and where focused attention is needed. Finally, Question 4 directly tackles process challenges, the very lifeblood of operational efficiency. By consistently gathering input on process frustrations, you gain frontline insights into bottlenecks and inefficiencies that might otherwise remain hidden.

This form isn’t just another survey; it’s a direct line to the pulse of your operations, providing actionable data to drive continuous improvement, boost team morale, and ultimately achieve operational excellence.

Exploring Form Settings

While the default settings will work perfectly for our tutorial, Google Forms offers several customization options you might want to explore later:

Confirmation Message: You can customize the message users see after submitting the form in “Settings” (gear icon) > “Presentation” > “Confirmation message.”
Response Collection Options: In “Settings” > “Responses” you can explore options like:
Collect email addresses: Although we ask for email in the form, this setting can additionally automatically collect and verify email addresses. For our tutorial, explicitly asking for email in the form question is sufficient.
Send responders a copy of their response: Allow users to receive a copy of their submission.
Limit to 1 response: Restrict each user to submitting only once (requires sign-in).
Theme and Appearance: Click the “Customize Theme” icon (palette icon) to change the form’s colors, fonts, and header image to align with your organization’s branding.

Feel free to explore these settings, but for now, our basic form setup is complete and ready to collect feedback!

Testing Your Form

Before deploying the form to your team, it’s always a good idea to test it yourself:

Click the “Preview” icon (eye icon) at the top right of the form. This will open a live version of your form in a new browser tab.
Fill out the form with sample data, including your email, department, and some test feedback for questions 3 and 4.
Click “Submit.”
Go back to your Google Form editor. Click on the “Responses” tab. You should see “1 response” recorded.
Click the “Link to Sheets” icon (Google Sheets icon) to the right of “Responses.” If prompted, choose “Create a new spreadsheet.”

Screenshot 7- Google Form “Responses” tab with “Link to Sheets” icon

A new Google Sheet will open. You should see your test response data automatically populated in the spreadsheet, with columns for Timestamp, Email Address, Department, and your feedback answers.

Congratulations! You’ve successfully created your Google Form for monthly operational feedback and confirmed that it’s collecting data correctly in Google Sheets.

In the next part of our tutorial, we’ll take this Google Sheet data and connect it to AppSheet to build a user-friendly interface for managing and visualizing this feedback. Stay tuned!

Part 2: Connecting Google Forms to AppSheet for Administration and Visualization

Welcome back! In Part 1, we successfully created our Google Form for collecting operational feedback and saw how responses are neatly organized in a Google Sheet. Now, we’re going to take things up a notch by connecting this Google Sheet to AppSheet.

Why AppSheet for Operational Data Management?

While Google Sheets is excellent for data storage, AppSheet transforms it into a powerful, user-friendly application without requiring any coding. For Operations Managers and Directors, AppSheet offers significant advantages:

No-Code App Building: AppSheet is a true no-code platform. You don’t need to write a single line of code to create a functional and visually appealing application.
User-Friendly Interface: AppSheet automatically creates a mobile and web interface from your Google Sheet data. This makes it easy to view, manage, and analyze feedback data in a structured and intuitive way, far beyond a raw spreadsheet.
Data Visualization and Analysis: AppSheet allows you to create charts, dashboards, and reports directly from your data. This enables you to visualize trends, identify patterns, and gain deeper insights from the operational feedback.
Easy Administration and Collaboration: AppSheet apps can be easily shared with your team. You can control user access and roles, making it a collaborative platform for managing operational feedback.
Extensibility: While we’re starting with basic visualization, AppSheet is highly extensible. Later, you can add more advanced features like automated workflows, notifications, and even integrate with other systems if needed.

In this part, we’ll create a basic AppSheet application connected to our Google Sheet and set up a simple interface to view the collected feedback.

Creating an AppSheet App from Your Google Form Responses Sheet: Step-by-Step

Let’s turn your Google Sheet into a functional app! Follow these steps:

Open your Google Form Responses Sheet:

Go back to your Google Form (if you closed it, find it in your Google Drive).
Click on the “Responses” tab.
Click the “Link to Sheets” icon (Google Sheets icon) to open the Google Sheet containing your form responses.

Screenshot 8 - Google Form “Responses” tab with “Link to Sheets” icon, and the Google Sheet open

Create an AppSheet App directly from Google Sheets:

With your Google Sheet open, go to “Extensions” in the menu bar.
Select “AppSheet” > “Create app”.

Screenshot 9 - Google Sheets menu: Extensions > AppSheet > Create app

AppSheet App Creation Process:

AppSheet will open in a new browser tab and automatically start creating an app based on your Google Sheet data.
You may be prompted to grant AppSheet authorization to access your Google Sheet. Click “Allow.”
Wait for AppSheet to complete the initial app generation. This usually takes just a few moments.

Screenshot 10 - AppSheet loading screen or initial app interface

Explore the Initial AppSheet Interface:

Once the app is created, you’ll be in the AppSheet editor. You should see a preview of your app on the right side (it might be a web preview or a simulator).
By default, AppSheet often creates a “Table” view showing the data from your Google Sheet. You should see columns like “Timestamp,” “Email Address,” “Department,” and your feedback questions displayed as columns in the table.

Screenshot 11 - Initial AppSheet interface showing a Table view of the Google Sheet data

Basic AppSheet Setup - Data Source:

On the left-hand menu in AppSheet, ensure you are in the “Data” section (usually the first icon, looks like a database cylinder).
You should see your Google Sheet listed as a data source (e.g., named after your Google Sheet file).
Click on your data source name.
Here, you can review the columns AppSheet has detected from your Google Sheet. AppSheet usually intelligently infers the data types (Text, Number, Enum, etc.). For now, the defaults are likely fine.

Screenshot 12 - AppSheet “Data” section showing the Google Sheet data source and column list

Basic AppSheet Setup - Views:

Go to the “UX” section in the left-hand menu (icon looks like a paintbrush or design palette).
You’ll see a list of “Views.” AppSheet likely created a default view for your table (often named “Form Responses” or similar).
Click on the existing view to edit its properties.
View Name: You can rename the view to something descriptive, like “Feedback Responses Table.”
View Type: Ensure it’s set to “Table.”
Data: Confirm it’s connected to your Google Sheet data source (the table name from your sheet).
Columns (Important): In the “Columns” section, you can control which columns from your Google Sheet are displayed in this view and their order.
Click “Columns” to expand the column selection.
You can rearrange the order of columns by dragging and dropping them.
You can hide columns by unchecking the checkbox next to their name (for now, let’s show all of them).
Consider ordering them for readability: Timestamp, Email Address, Department, Question 3, Question 4.

Screenshot 13 - AppSheet “UX” section, editing a Table view, showing column selection and ordering

Customizing the AppSheet UI (Basic Formatting):

Within the View settings (still in “UX” > your view), explore other formatting options to enhance readability:
Column Width: Adjust column widths if needed to better fit the content.
Text Wrapping: You might want to enable text wrapping for the paragraph feedback questions so that long answers are fully visible within the table rows. Look for options like “Wrap text” or “Text mode” in the column settings (you might need to click into individual column settings to find this).
Theme: In the main “UX” section (not within a specific view), you can explore “Themes” to change the overall look and feel of your app (colors, fonts).

Benefits of AppSheet Interface for Operations Managers:

Now, instead of looking at raw data in a spreadsheet, you have a functional application! As an Operations Manager, you can immediately see the advantages:

Clear, Organized View: The table view in AppSheet presents the feedback data in a clean and structured format, making it much easier to read and understand than a spreadsheet.
Easy Filtering and Searching: AppSheet provides built-in filtering and searching capabilities. You can quickly filter responses by department, date range, or search for keywords within the feedback text. (We’ll explore filtering in more detail later).
Mobile Accessibility: AppSheet apps are automatically responsive and work well on mobile devices. You can access and review feedback data from anywhere, anytime, on your phone or tablet.
Foundation for Advanced Features: This basic app is just the starting point. We can easily add more sophisticated features in AppSheet, such as charts, dashboards, detailed views for each response, automated workflows, and more, as we progress in this tutorial and beyond.

Exploring Your AppSheet App

Take some time to explore your newly created AppSheet app:

Preview and Interact: Use the web preview in the AppSheet editor, or if you’re using the simulator, interact with your app. Try sorting columns by clicking on the column headers.
Add Test Data (Optional): If you want to see more data in your AppSheet app, submit a few more test responses through your Google Form. The AppSheet app will automatically update with the new responses.
Share Your App (Optional): To share your app with colleagues (e.g., to get feedback on the interface), click the “Share” button in the top right corner of the AppSheet editor. You can share it with specific email addresses or make it publicly accessible within your organization (depending on your AppSheet plan and organizational settings).

Congratulations! You’ve successfully connected your Google Form data to AppSheet and created a basic application to manage and visualize operational feedback. You now have a much more user-friendly way to interact with this data.

In the next part of our tutorial, we’ll dive into the exciting world of Vertex AI and learn how to integrate sentiment analysis into our system to automatically understand the emotional tone of the feedback we’ve collected. Get ready to add AI power to your operational insights!

Part 3: Integrating Vertex AI for Sentiment Analysis with Apps Script

Now for the exciting part – adding the AI magic! In this section, we’ll integrate Vertex AI, Google’s powerful AI platform, into our system using Google Apps Script. This will allow us to automatically analyze the sentiment of the feedback submitted through Google Forms.

Why Vertex AI for Sentiment Analysis?

Vertex AI offers several compelling reasons for incorporating sentiment analysis into our operational feedback system:

Powerful AI Models: Vertex AI provides access to Google’s state-of-the-art pre-trained AI models, including those for Natural Language Processing (NLP) and sentiment analysis. These models are highly accurate and continuously improving.
Scalability and Reliability: Vertex AI is a cloud-based platform designed for scalability and reliability. It can handle large volumes of feedback data efficiently and consistently.
Ease of Integration: Google Apps Script provides seamless integration with Google Cloud services, including Vertex AI. This makes it relatively straightforward to connect our Google Forms and Sheets to Vertex AI for sentiment analysis.
Automated Insights: By automating sentiment analysis, we can transform qualitative feedback into quantifiable data (sentiment scores). This allows for objective and data-driven analysis of team sentiment at scale.
Deeper Understanding of Feedback: Sentiment analysis goes beyond just reading the words. It helps us understand the emotional tone behind the feedback – whether it’s positive, negative, or neutral – providing a richer understanding of team sentiment.

In this section, we will outline the steps to write a Google Apps Script function that will be triggered whenever a new form response is submitted. This script will:

Retrieve the feedback text from the Google Form response.
Send this text to Vertex AI’s sentiment analysis API.
Receive the sentiment analysis results (score and magnitude).
Update our Google Sheet with these sentiment scores for each response.

Let’s get coding (don’t worry, it’s still low-code!).

Setting up Vertex AI API Access (Briefly)

Before we proceed with the Apps Script, we need to ensure Vertex AI API is enabled in your Google Cloud Project.

Google Cloud Project: You’ll need a Google Cloud Project. If you don’t have one, you can create a free one. (Search for “Google Cloud Console” and follow the steps to create a project). If you already use Google Cloud services, you likely have a project already.
Enable Vertex AI API:

Go to the Google Cloud Console.
Make sure you have selected your Google Cloud Project at the top.
In the left-hand menu, navigate to “APIs & Services” > “Enabled APIs & services”.
Click ”+ ENABLE APIS AND SERVICES”.
In the API Library, search for “Vertex AI API” and select it.
Click “ENABLE”.

Screenshot 14 - Google Cloud Console: Enabling Vertex AI API

Writing the Apps Script Function for Vertex AI Sentiment Analysis

Now, let’s outline the steps to write the Apps Script to connect to Vertex AI. (You will insert the Apps Script code block here in your blog post.)

Open your Google Form Responses Sheet: (The Google Sheet linked to your feedback form).
Open Apps Script Editor:

In the Google Sheet menu, go to “Extensions” > “Apps Script”.

Screenshot 15 - Google Sheets menu: Extensions > Apps Script 3. Replace the existing onFormSubmit function (or create it if it’s not there) with the Apps Script code provided below. (Insert Apps Script Code Block Here)

/**

 * Triggers when a form response is submitted.

 * Sends feedback text to Vertex AI for sentiment analysis and updates the sheet.

 *

 * @param {Object} e The form submit event object.

 */

function onFormSubmit(e) {

  // 1. Get the form response

  var formResponse = e.response;

  var itemResponses = formResponse.getItemResponses();

  // 2. Extract relevant feedback text (Question 3 and Question 4 responses)

  var positiveImprovementFeedback = itemResponses[2].getResponse(); // Question 3 (index 2, assuming 0-based indexing)

  var processChallengesFeedback = itemResponses[3].getResponse();   // Question 4 (index 3)

  // Combine feedback texts for analysis (optional - you could analyze them separately)

  var combinedFeedbackText = "Positive/Improvement Feedback: " + positiveImprovementFeedback + ". Process Challenges: " + processChallengesFeedback;

  // 3. Call Vertex AI Sentiment Analysis API

  var sentimentResult = analyzeSentimentWithVertexAI(combinedFeedbackText);

  // 4. Extract sentiment score and magnitude from the result

  var sentimentScore = sentimentResult.documentSentiment.score;

  var sentimentMagnitude = sentimentResult.documentSentiment.magnitude;

  // 5. Get the response row in the Google Sheet

  var sheet = SpreadsheetApp.getActiveSheet();

  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; // Get headers from the first row

  var responseRow = formResponse.getRow(); // Get the row number of the submitted response

  // 6. Add or update sentiment score and magnitude columns in the sheet (if columns don't exist, add them)

  var scoreColumnHeader = 'Sentiment Score';

  var magnitudeColumnHeader = 'Sentiment Magnitude';

  var scoreColumnIndex = headers.indexOf(scoreColumnHeader) + 1; // +1 because indexOf is 0-based, column indices are 1-based

  var magnitudeColumnIndex = headers.indexOf(magnitudeColumnHeader) + 1;

  if (scoreColumnIndex === 0) { // Column doesn't exist, add it

    sheet.insertColumnAfter(sheet.getLastColumn());

    scoreColumnIndex = sheet.getLastColumn();

    sheet.getRange(1, scoreColumnIndex).setValue(scoreColumnHeader); // Set header

  }

  if (magnitudeColumnIndex === 0) { // Column doesn't exist, add it

    sheet.insertColumnAfter(sheet.getLastColumn());

    magnitudeColumnIndex = sheet.getLastColumn();

    sheet.getRange(1, magnitudeColumnIndex).setValue(magnitudeColumnHeader); // Set header

  }

  // 7. Write sentiment score and magnitude to the sheet in the response row

  sheet.getRange(responseRow, scoreColumnIndex).setValue(sentimentScore);

  sheet.getRange(responseRow, magnitudeColumnIndex).setValue(sentimentMagnitude);

  Logger.log('Sentiment analysis completed for response row: ' + responseRow + ', Score: ' + sentimentScore + ', Magnitude: ' + sentimentMagnitude);

}

/**

 * Analyzes the sentiment of text using Vertex AI API.

 *

 * @param {string} text The text to analyze.

 * @return {Object} The sentiment analysis result from Vertex AI.

 */

function analyzeSentimentWithVertexAI(text) {

  // **IMPORTANT: Replace with your Google Cloud Project Number**

  var projectNumber = 'YOUR_GOOGLE_CLOUD_PROJECT_NUMBER'; // <----  🔑🔑🔑  IMPORTANT!  🔑🔑🔑

  var language = LanguageApp.getLanguageService();

  var document = language.newDocument();

  document.setContent(text).setType(LanguageApp.DocumentType.PLAIN_TEXT);

  var result = language.analyzeSentiment(document, {

    projectId: projectNumber // Specify your project ID

  });

  return result;

}

Save the Apps Script: Click the “Save” icon (floppy disk icon) in the Apps Script editor. You can name the script project something like “SentimentAnalysisScript”.
Set up Form Submit Trigger:

In the Apps Script editor, click the “Triggers” icon (alarm clock icon) on the left-hand menu.
Click ”+ Add Trigger”.
Choose which function to run: Select onFormSubmit.
Choose which deployment should run: Select “Head”.
Choose event source: Select “From form”.
Choose event type: Select “On form submit”.
Failure notification settings: Choose your preferred notification settings (e.g., “Immediately” to get notified if the script fails).
Click “Save”.

Screenshot 16 - Apps Script Triggers setup for onFormSubmit function

Authorization (if prompted): You might be asked to authorize the script again to access Google Cloud Language API and your Google Sheets. Grant the necessary permissions.

Testing the Vertex AI Integration

Now, let’s test if the sentiment analysis integration is working:

Open your Google Form (Live Form - Preview Link): Use the “Preview” link of your Google Form to submit a new response.
Submit a Test Response: Fill out the form with new feedback, including some text in Questions 3 and 4 that clearly expresses either positive or negative sentiment (e.g., “Everything is fantastic, the team is doing great!” for positive, or “Processes are very slow and frustrating, causing delays” for negative).
Check your Google Sheet: After submitting the form, go back to your Google Sheet. You should see a new row added with your latest response.
Look for “Sentiment Score” and “Sentiment Magnitude” Columns: You should see two new columns added to your sheet: “Sentiment Score” and “Sentiment Magnitude.” These columns should now be populated with numerical values for your latest response.

Sentiment Score: Ranges from -1 (negative) to +1 (positive), with 0 being neutral.
Sentiment Magnitude: Indicates the strength of the sentiment, from 0 (no sentiment) upwards. Higher magnitude means stronger sentiment (positive or negative).

Screenshot 17 - Google Sheet showing new “Sentiment Score” and “Sentiment Magnitude” columns populated with values

Understanding Sentiment Scores

Score: The sentiment score is the key indicator.
Positive Score (e.g., 0.5, 0.8): Indicates positive sentiment. Higher positive scores mean more positive sentiment.
Negative Score (e.g., -0.3, -0.7): Indicates negative sentiment. More negative scores mean more negative sentiment.
Neutral Score (around 0): Indicates neutral or mixed sentiment.
Magnitude: The magnitude helps you understand the intensity of the sentiment, regardless of whether it’s positive or negative. A higher magnitude suggests stronger emotions are expressed in the text. It’s often less critical than the score itself for basic sentiment analysis, but can be useful for nuanced analysis.

If you see the Sentiment Score and Magnitude columns populated with values after submitting a test response, congratulations! You have successfully integrated Vertex AI sentiment analysis into your feedback system!

In the next section, we’ll go back to AppSheet and learn how to visualize and analyze this sentiment data to gain actionable insights for operational improvements.

Part 4: Visualizing and Analyzing Sentiment Data in AppSheet

We’ve done the heavy lifting of setting up sentiment analysis! Now, let’s return to AppSheet and make our application even more insightful by visualizing and analyzing the sentiment data we’re now collecting.

Enhancing AppSheet Views with Sentiment Data

Open your AppSheet App Editor: Go back to the AppSheet editor for the app you created in Part 2.
Navigate to the “UX” Section and your Table View: Select “UX” in the left menu and then click on your “Feedback Responses Table” view (or whatever you named it).
Ensure “Sentiment Score” and “Sentiment Magnitude” Columns are Visible: In the “Columns” section of your view settings, make sure “Sentiment Score” and “Sentiment Magnitude” columns are checked (visible). You can also adjust their order for better presentation.

Screenshot 18 - AppSheet View settings - “Columns” section showing Sentiment Score and Magnitude columns selected

Conditional Formatting for Sentiment Visualization: Let’s use conditional formatting to visually highlight responses based on sentiment score in our table view.

In the “UX” section, navigate to “Formatting Rules” (it might be a sub-tab or separate section depending on your AppSheet layout).
Click ”+ NEW FORMATTING RULE”.
Rule Name: Enter a name like “Negative Sentiment”.
Table: Select your Google Sheet data source table (e.g., “Form Responses”).
Condition: Click “Condition” and enter the following expression: [Sentiment Score] < 0 (This means: apply formatting if the “Sentiment Score” is less than 0, i.e., negative).
Format Properties: Choose formatting styles to visually highlight negative sentiment. For example:

Background color: Select a light red or orange color.
Text color: Keep it black or dark gray for readability.
Click “Save”.

Screenshot 19 - AppSheet Formatting Rule setup for Negative Sentiment

Create More Formatting Rules (Optional): You can create additional formatting rules for neutral and positive sentiment if desired:

Neutral Sentiment: Condition: AND([Sentiment Score] >= 0, [Sentiment Score] <= 0.2) (You can adjust the neutral range as needed). Formatting: Light yellow background.
Positive Sentiment: Condition: [Sentiment Score] > 0.2 (or a higher threshold for stronger positive sentiment). Formatting: Light green background.

Screenshot - AppSheet Formatting Rules list showing rules for Negative, Neutral, and Positive sentiment

Now, when you view your “Feedback Responses Table” in the AppSheet app, you should see rows automatically color-coded based on the sentiment score! Negative feedback will be visually highlighted, making it easy to quickly identify areas needing attention.

Creating Charts and Dashboards for Sentiment Analysis

Let’s go beyond the table and create charts to visualize sentiment trends.

Create a Chart View:

In AppSheet editor, go to the “UX” section.
Click ”+ NEW VIEW”.
View Name: “Sentiment Trend by Department”.
View Type: Select “Chart”.
Data: Select your Google Sheet data source table.
Chart Type: Choose “Column” or “Bar” chart.
Category Column: Select “Department”.
Series Columns:
Click ”+ Add Series”.
Column: Select “Sentiment Score”.
Aggregation: Choose “Average” (to show average sentiment score per department).
Customize Chart (Optional): You can customize chart colors, labels, etc., in the “Chart” settings.
Click “Save”.

Screenshot 20 - AppSheet Chart View setup for Sentiment Trend by Department

Create a Dashboard View (Optional - for combining views):

In AppSheet editor, go to the “UX” section.
Click ”+ NEW VIEW”.
View Name: “Operational Sentiment Dashboard”.
View Type: Select “Dashboard”.
Views in Dashboard: Add your “Feedback Responses Table” view and your “Sentiment Trend by Department” chart view to the dashboard.
Click “Save”.

Screenshot 21 - AppSheet Dashboard View setup including Table and Chart views]

Interpreting Sentiment Data for Operational Improvements

With sentiment data visualized in AppSheet, Operations Managers and Directors can now:

Quickly Identify Negative Feedback: Conditional formatting instantly highlights responses with negative sentiment in the table, allowing for immediate attention to potential issues.
Analyze Sentiment Trends by Department: The “Sentiment Trend by Department” chart provides a visual overview of average sentiment scores across different departments. You can easily see which departments are expressing more positive or negative sentiment overall.
Track Sentiment Over Time (Future Enhancement): By adding date filtering or grouping to your chart, you could analyze sentiment trends over time (e.g., month-by-month) to see if sentiment is improving or declining after operational changes.
Data-Driven Decision Making: Sentiment data provides quantifiable evidence to support operational decisions. For example, consistently negative sentiment in a particular process area might trigger a process review or improvement initiative.
Proactive Issue Resolution: By monitoring sentiment regularly, you can proactively identify and address emerging issues before they escalate and impact team morale or operational efficiency.

Exploring Further Analysis (AppSheet Features):

AppSheet offers many more features for data analysis and visualization that you can explore to further enhance your sentiment analysis app:

Filtering and Searching: Use AppSheet’s powerful filtering and search capabilities to drill down into specific subsets of feedback data (e.g., filter for “negative sentiment” and “department = Logistics”).
Grouping and Aggregation: Explore grouping and aggregation options in charts and table views to summarize sentiment data in different ways.
Detailed Views: Create detailed views for individual feedback responses to see the original feedback text alongside the sentiment scores.
Reports and Summaries: Generate automated reports or summary views of sentiment data for regular operational reviews.

By visualizing and analyzing sentiment data in AppSheet, you transform raw feedback into actionable intelligence, empowering you to make data-driven decisions and drive continuous improvement in your operations.

Part 5: Conclusion: Empowering Operations with AI-Driven Insights

Congratulations! You’ve reached the end of this tutorial and successfully built a Custom Sentiment Analysis System for Operations Feedback using the power of Google’s no-code and AI tools: Google Forms, AppSheet, and Vertex AI.

Recap of Our End-to-End Solution:

Let’s quickly recap what we’ve achieved:

Google Forms for Feedback Collection: We created a user-friendly Google Form to collect monthly operational feedback from your teams, focusing on both positive aspects and areas for improvement, as well as process challenges.
Google Sheets for Data Storage: Form responses are automatically collected and organized in a Google Sheet, providing a structured data source.
AppSheet for Administration and Visualization: We built a no-code AppSheet application on top of the Google Sheet data, creating a user-friendly interface for viewing, managing, and visualizing feedback.
Vertex AI for Automated Sentiment Analysis: We integrated Vertex AI using Google Apps Script to automatically analyze the sentiment of the open-ended feedback text, adding quantifiable sentiment scores to our data.
Sentiment Data Visualization in AppSheet: We enhanced our AppSheet app with conditional formatting and charts to visually represent sentiment trends and quickly identify areas of positive and negative feedback.

Reiterating the Value Proposition for Operations Managers/Directors:

This end-to-end solution provides significant benefits for Operations Managers and Directors:

Proactive Issue Identification: Sentiment analysis helps you proactively identify and address emerging issues and frustrations within your operations teams before they escalate.
Data-Driven Decision Making: You can now make data-driven decisions based on quantifiable sentiment data, rather than relying solely on anecdotal feedback or gut feelings.
Improved Team Morale and Efficiency: By actively listening to and addressing team feedback, you can foster a more positive and productive operational environment, leading to improved morale and efficiency.
Continuous Process Improvement: Sentiment data provides valuable insights for continuous process improvement initiatives. You can identify process bottlenecks and areas causing negative sentiment and focus improvement efforts where they are most needed.
No-Code/Low-Code Accessibility: This entire system is built using no-code and low-code tools, making it accessible to Operations professionals without requiring extensive programming expertise.

Next Steps and Further Exploration:

This tutorial provides a solid foundation. Here are some “next steps” and ideas for further exploration to enhance your sentiment analysis system:

Advanced AppSheet Features:
Workflows and Notifications: Set up automated workflows in AppSheet to trigger notifications when negative sentiment is detected, alerting relevant managers to take action.
User Roles and Security: Implement user roles and security settings in AppSheet to control access to feedback data and ensure data privacy.
More Complex Dashboards and Reports: Create more sophisticated dashboards and reports in AppSheet to track sentiment trends over time, compare sentiment across different operational areas, and generate summary reports for leadership.
Actions and Buttons: Add custom actions or buttons in AppSheet to allow users to mark responses as “addressed,” assign follow-up tasks, or trigger other operational workflows directly from the app.

Advanced Vertex AI Capabilities:

Entity Sentiment Analysis: Explore Vertex AI’s entity sentiment analysis to identify specific entities (e.g., processes, tools, teams) mentioned in the feedback and analyze sentiment associated with those entities for more granular insights.
Custom Models (Advanced): For very specific operational contexts or languages, you could explore training custom Vertex AI sentiment analysis models for even greater accuracy.
Integrations with Other Systems: Integrate your AppSheet app with other operational systems (e.g., CRM, project management tools) to further enrich your data and workflows.
Refine Survey Questions: Continuously review and refine your Google Form questions to ensure they are eliciting the most valuable and actionable feedback for your operational needs.

Embrace the Power of AI in Operations:

You’ve now witnessed firsthand the power of combining no-code tools with AI to transform operational management. By leveraging Google Forms, AppSheet, and Vertex AI, you can create intelligent systems that provide deeper insights, automate processes, and empower you to lead your operations with data-driven confidence.

We encourage you to implement this sentiment analysis system in your own organization and adapt it to your specific operational context. The possibilities are vast, and the journey of continuous operational improvement is just beginning!