Analyzing Text Data with Google Sheets and Cloud Natural Language

This is the code from my NEXT 2018 talk of the same title.

In the demo, I use Google Forms, Cloud Natural Language, Google Sheets, and Apps Script to analyze vacation rental reviews. I use Cloud Natural Languages's Entity Sentiment Analysis method, which combines both entity analysis and sentiment analysis and attempts to determine the sentiment (positive or negative) expressed about entities within the text.

A video of the talk is available on YouTube.

Setup

Form and Spreadsheet Setup

1. Create a Google Form modeled after this template: Post-stay review form template.

2. Choose to collect form responses in a spreasheet following the instructions in this support article.

3. Access the response spreadsheet and add two additional tabs. Name these tabs "Review Data" and "Entity Sentiment Data", and add headers in row 1 exactly as in this template: Post-stay reviews spreadsheet template.

Apps Script Setup

1. From your response spreadsheet, access the Script Editor from the Tools menu.
2. Create three script files preprocess.gs, nlcall.gs, and apikey.gs.
3. Click Save.

Google Cloud Platform Project Setup

1. The API call to Cloud Natural Language (in nlcall.gs) requires a Google Cloud Platform account and project. You can sign up for the Free Trial of Google Cloud Platform at https://cloud.google.com/free/.
2. Once you have a project, enable the Google Natural Language API and create an API key.
3. Copy the API key into apikey.gs, replacing YOUR_KEY_HERE but maintaining the quotes.

Apps Script Trigger Setup

1. From the Edit menu within the Script Editor, choose Current Project Triggers.
2. Choose to run "onFormSubmit", "From spreadsheet", "On form submit". Set notifications to immediately (for any debugging; you may change this later).

Try it out!

Now's your chance to try it out!

1. Open the Google Form and submit a response (you may submit your own text, or use the reviews from the Boston Airbnb Open Data on Kaggle.
2. Check to see the response populates on both the "Form Responses" tab and the "Review Data" tab.
3. Confirm that columns F and G of the "Review Data" tab have populated with the formula to detect the text language and translate non-English text into English (these are part of preprocess.gs).
4. Finally, from the 'Demo Tools' menu in the responses sheet, select 'Mark Entities and Sentiment' (if you do not see this menu, reload the spreadsheet). This will kick off the markEntitySentiment function from nlcall.gs. You should see column H in the "Review Data" tab start to populate each row as "complete", while the API response data received back from Cloud Natural Lanaguage (entities and their sentiment) are flowing into the "Entity Sentiment Data" tab.

Bonus: Once you have processed your data, you may consider visualizing it using Data Studio. You can view my Data Studio report if you like.

Appendix

For more detailed information on a similar demo, check out my blog post "Analyzing text in a Google Sheet using Cloud Natural Language API and Apps Script"

Let me know if you run into any problems!

NOTE: This is not an official Google product.