Analyze sentiment of open-ended feedback

Coding level: Intermediate
Duration: 20 minutes
Project type: Automation with a custom menu

About this solution

Analyze text data, such as open-ended feedback, at scale by performing entity and sentiment analysis from within Google Sheets.

diagram of how sentiment analysis works

How it works

The script gathers text from the spreadsheet and connects to the Google Cloud Natural Language API to analyze entities and sentiment present in the string. A pivot table summarizes the average sentiment score for each entity mentioned across all rows of text data.

Apps Script services

This solution uses the following services:

  • Spreadsheet Service: Sends the text data to the Cloud Natural Language API and marks each row as "Complete" once its sentiment has been analyzed.
  • UrlFetch Service: Connects to Cloud Natural Language API to perform entity and sentiment analysis on the text.

Before you begin

To use this sample, you need the following prerequisites:

  • A Google Account (Google Workspace accounts might require administrator approval)
  • A web browser with access to the internet

Try it

Step 1: Get an API key for the Google Cloud Natural Language API

  1. Go to the Google Cloud Console. Make sure your billing-enabled project is open.
  2. At the top of the console, click Menu > APIs & Services.
  3. Click Enable APIs and Services.
  4. Search for Cloud Natural Language API and enable it.
  5. Click Menu > APIs & Services > Credentials.
  6. At the top, click Create credentials > API Key.
  7. Take note of your API key for use in a later step.

Step 2: Set up the spreadsheet

  1. Click the button below to make a copy of the Sentiment analysis for feedback sample spreadsheet.
    Make a copy
  2. Click Extensions > Apps Script.
  3. Update the following variable in the script file with your API key: 
    const myApiKey = 'YOUR_API_KEY'; // Replace with your API key.
  4. Click Save Save icon.

Step 3: Add text data

  1. Return to the spreadsheet.
  2. Add text data to the id and comments columns. You can use sample vacation property reviews from Kaggle or use your own data. You can add more columns if needed, but to run successfully, the script must have data in the id and comments columns.

Step 4: Run the sentiment analysis

  1. At the top of the spreadsheet, click Sentiment Tools > Mark entities and sentiment. You might need to refresh the page for this custom menu to appear.
  2. When prompted, authorize the script.
  3. Click Sentiment Tools > Mark entities and sentiment again.
  4. When the script finishes, switch to the Pivot Table sheet to see the results.

Open the Apps Script project

The sample script's code includes in-line comments to help you understand how it works. To access the code, in the spreadsheet, click Extensions > Apps Script.

Contributors

This sample is maintained by Google with the help of Google Developer Experts.