title	description	labels	material_icon	create_time	update_time
Analyze sentiment of open-ended feedback	Analyze text data, such as open-ended feedback, at scale by performing entity and sentiment analysis directly in Google Sheets.	Apps Script, Sheets, Cloud Natural Language	feedback	2019-08-21	2019-08-29

There are many formulas in Google Sheets for analyzing quantitative data, but spreadsheets often capture valuable text data as well. Text data in Google Sheets can come from many sources: Google Form responses, notes columns, descriptions, and more. As humans we can make sense of this data by reading it, but this becomes difficult as your data grows into hundreds or thousands of rows.

The Cloud Natural Language API takes the machine learning technology used by Google Search and Google Assistant and makes it possible for anyone to perform sentiment and entity analysis on their own data.

This solution uses Apps Script in a Google Sheet to perform entity and sentiment analysis on vacation rental reviews using the Cloud Natural Language API.

Technology highlights

• Uses the Cloud Natural Language API to perform entity and sentiment analysis on text.
• Uses the SpreadsheetApp and URL Fetch services to programmatically send multiple rows of text data to the Cloud Natural Language API service and paste response data into a spreadsheet.
• Adds a custom menu to Google Sheets using the Ui service.

Try it

Set up a Google Cloud Platform project

This solution requires a Google Cloud Platform account and project. The service used in this solution, Cloud Natural Language API, has a free tier that you can use to test this solution for free.

1. Sign in to your Google Account. If you don't already have one, sign up for a new account.
2. In the GCP Console, on the project selector page, select or create a GCP project.
3. Make sure that billing is enabled for your Google Cloud Platform project.
4. Enable the Google Natural Language API for your project.
5. Create an API key following these instructions.

Set up a spreadsheet

1. Make of copy of the spreadsheet here.
2. From the spreadsheet, open the script editor by selecting Tools > Script editor.
3. Copy and paste your API key into line 1 of code.gs replacing YOUR_API_KEY_HERE and maintaining the quotes.
4. Save the changes by navigating to File > Save.

Add text data

1. Return to the sheet, and reload your browser.
2. Add sample text data to columns A-F. Sample vacation property reviews with a matching schema to the template are available on Kaggle and can be copied and pasted directly from the data preview pane. Alternatively, you may add your own data. Keep in mind that the script requires data in column B (id) and column F (comments) at a minimum in order to execute successfully.
3. Ensure that columns G and H, which use built-in Sheets functions to detect the comment language and provide an English translation, are properly populated using the formula provided in row 2 of the template.

Run the script

1. To run the script, navigate to the custom menu Sentiment tools > Mark entities and sentiment. If the menu is not present, reload your browser window.
2. When prompted, click the Review permissions button.
3. Select your Google Workspace account from the list.
4. Click the Allow button.
5. As the script executes, you will see response data populating the Entity Sentiment Data sheet and a completion flag in each row of the Review Data sheet.

Analyze the results

1. When the script finishes execution, navigate to the Pivot Table tab to see a pivot table summarizing the average sentiment score for each entity mentioned across all rows of text data.
2. More information on interpreting sentiment scores can be found in the Cloud Natural Language API documentation.

Next steps

To learn more about how it was built, check out this blog post.

A video of a talk stepping through a similar solution can be found on YouTube.