Coding level: Intermediate
Duration: 20 minutes
Project type: Automation with a custom menu
About this solution
You can analyze text data, such as open-ended feedback, at scale. To perform entity and sentiment analysis from within Google Sheets, this solution uses UrlFetch Service to connect to the Google Cloud Natural Language API.
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
Step 1: Get an API key for the Google Cloud Natural Language API
- Go to the Google Cloud Console. Make sure your billing-enabled project is open.
- At the top of the console, click Menu > APIs & Services.
- Click Enable APIs and Services.
- Search for
Cloud Natural Language APIand enable it.
- Click Menu > APIs & Services > Credentials.
- At the top, click Create credentials > API Key.
- Take note of your API key for use in a later step.
Step 2: Set up the spreadsheet
- Click the button below to make a copy of the Sentiment analysis for
feedback sample spreadsheet.
Make a copy
- Click Extensions > Apps Script.
- Update the following variable in the script file with your API key:
const myApiKey = 'YOUR_API_KEY'; // Replace with your API key.
- Click Save .
Step 3: Add text data
- Return to the spreadsheet.
- 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
- 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.
- When prompted, authorize the script.
- Click Sentiment Tools > Mark entities and sentiment again.
- 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.
This sample is maintained by Google with the help of Google Developer Experts.
- Blog: Analyzing text in a Google Sheet using Cloud Natural Language API and Apps Script
- Cloud Natural Language API documentation