How to do Sentiment Analysis with Google’s Natural Language API in Google Sheets (Apps Script)

Ever wished you could instantly gauge the sentiment behind a mountain of customer reviews, social media comments, or survey responses? Sentiment analysis is the way to do exactly this.

This tutorial will equip you, even as a complete beginner, to harness the power of Google’s Natural Language API for sentiment analysis within Google Sheets. No coding experience is required – I’ll walk you through the process step-by-step, empowering you to unlock insights for the emotional tone of your text data.

Whether you’re analysing product feedback, your organisation’s reviews, or tracking brand perception online, this guide will show you how to leverage Google’s powerful API to make data-driven decisions with ease.

About the method: How does sentiment analysis work?

Sentiment analysis is a fundamental Natural Language Processing (NLP) task that involves interpreting and classifying the emotions conveyed in text data. This is done through extracting qualitative characteristics from user’s text data, such as sentiment, opinions, thoughts, and behavioral intent using natural language processing methods.

Like entity extraction, sentiment analysis is typically approached as a supervised machine learning problem where the model is trained on labeled examples. Especially for traditional sentiment analysis, where the goal was to detect polarity and provide a label of positive, negative, neutral, supervised machine learning was a suitable approach.

Since many have argued this approach fails to capture objectivity and subjectivity in human expression, like if a model is tasked to categorize between fake news or someone’s opinions and facts, traditional sentiment analysis might not be enough, modern methods are more advanced, using unsupervised and semi-supervised machine learning.

To train a sentiment analysis model effectively, you can utilize a range of algorithms, from basic statistical methods to more sophisticated deep learning models that employ neural networks. The use of word embeddings is particularly advantageous as they help to grasp the nuanced emotional context of words, thereby enhancing the model’s precision in classifying sentiments.

Sentiment analysis models are crucial for extracting emotional insights from large volumes of text, aiding in understanding consumer sentiments, enhancing customer experience, and facilitating smarter business decisions by gauging public opinion and emotional trends.

About the model: Google Cloud’s Natural Language API

The Natural Language API is a versatile API that draws from a vast library filled with knowledge about language structure, grammar structure, sentiment, and real world entities. It’s trained on massive amounts of text data, allowing it to:

• Identify key syntax elements and structures: It recognises nouns, verbs, phrases, and relationships between words, just like we do when we read, and can create more complex parsing structures like syntax trees.
• Understand context and entities: It goes beyond individual words, considering the surrounding text and even real-world knowledge to grasp the meaning of text via entity analysis.
• Detect sentiment: It can sense emotions polarity and magnitude expressed in the text, not only at a document level (the entire text) but also at the entity level (sentiment associated with a specific entity mentioned in the text).
• Classify text: It has pre-training applied to identify whether the text you analyze aligns with either of more than 1,300+ categories

How sentiment analysis works

The analyzeSentiment module of Google’s Natural Language API is designed to interpret and classify the emotional tone conveyed in the text. This includes distinguishing sentiments such as positive, negative, or neutral, and providing further nuances with sentiment scores and magnitudes.

With the same API, you can achieve a deeper emotional insight as it assigns each segment of text both a sentiment score (emotion intensity) and sentiment magnitude (emotion depth):

• Sentiment Score: A numerical score that ranges from -1 (very negative) to +1 (very positive), indicating the overall emotional tone of the text.

• Sentiment Magnitude: A non-negative number that indicates the overall strength of emotion expressed in the text, irrespective of the sentiment score. Higher magnitudes reflect more emotionally expressive texts.

If working with Python, you can also specify document language as well, otherwise, the language will be automatically detected from the list of supported languages.

Additional resources on sentiment analysis

Check out the additional resources by Google Cloud to practice working with this API, and the sentiment analysis module specifically:

• Code lab for sentiment analysis in Python for Google Cloud
• Sentiment Analysis module documentation

Step-by-step guide on using the Natural Language API Sentiment Analysis Module in Google Sheets via Apps Script

Prerequisites

• You have a project set-up in Google Cloud – see how to create a project in Google Cloud
• For this project, you have enabled the Google Cloud’s Natural Language API – see how to enable a Google Cloud API
• You have billing enabled for your project – see how to enable billing for your project

Get your API key

Having selected your Google Cloud project, navigate to the APIs and Services menu > Credentials.

Then, click on the Create Credentials button from the navigation next to the page title, then select API Key from the drop-down menu.

This is the easiest to use, but least secure method of authentication – you might consider alternatives for more complex projects.

Once you click on the Create API key button, there will be a pop-up menu that will indicate that the API key is being created, after which it will appear on the screen for you to copy.

You can always navigate back to this section of your project, and reveal the API key at a later stage, using the Show Key button. If you ever need to edit or delete the API key, you can do so from the drop-down menu.

Extract and organise the text content you want to analyse sentiment for

The next step is to decide on and organise the content you want to analyse sentiment for into Google Sheets.

For almost any platform out there, there are APIs and data scrapers to help you build a customer reviews universe. For a no-code web content scraping approach, I recommend using the Instant Data Scraper Chrome Extension. It is versatile and great for quick review scraping from web pages.

With it, you can download reviews from Google Business Profiles of any organisation. Find any organisation, click to expand the reviews, select the Instant Data Scraper Chrome Extension icon, and download the data in a format of your choice.

With this approach, you can quickly get a dataset of scraped service reviews from a Business profile on Google in a csv file or in Google Sheets, depending on your file selection.

For the purposes of the demo today, and also in case your customer reviews or other text you want to analyse sentiment for is in a language other than English, I’ve added a line in the code in Apps Script that channels the translate function.

With the created function, you can run a formula on your content to translate it directly in Google Sheets, as a step to normalising the data and preparing it for sentiment analysis.

Before moving on, I would also suggest naming the columns something more suitable, as the data scraper tool does not always provide accurate names for the text it is collecting. You can also at this point delete any data or columns that are not relevant to your analysis.

Once you have your content organised into a spreadsheet-suitable format, you can move on to the next step.

Make a copy of the Google Sheets Template and paste your content and API key

Sentiment Analysis with Google Cloud Natural Language API (Google Sheets Template and Apps Script)

This Google Sheets template, enhanced with an Apps Script, utilizes the Google Cloud Natural Language API for sentiment analysis. It enables users to identify sentiment and categorize sentiment expressed, with its magnitude into categories (or otherwise – Sentiment Tags) directly in Google Sheets, streamlining data processing of reviews and feedback. Ideal for analysing sentiment in customer product reviews, service reviews, organisation reviews, user feedback from forms, and so on. Perfect for beginners – no coding required.

Get it now

To prepare the data for analysis, we need to do two things – organize the content for analysis, and paste the API key in the script.

Paste your API key

In Google Sheets, open the Extensions menu, and click on Apps Script.

Open the SentimentAnalysis.gs script attached, and select the text that says enterAPIkey. Replace it with your Google Cloud API project key. Then click on the disk icon to Save, and return to the Google Sheet file.

Replace the content in the Working Sheet with your own

Paste your content for analysis in the Working Sheet, modifying the sheet as it suits your project and data. Keep following columns:

Run the analysis to classify sentiment from the provided text

To run the analysis, paste the following formula in the cell from the Sentiment Score column:

=transpose(analyzeFeedback({your content cell}))

Then, drag the formula to analyse all cells with content for sentiment analysis, as shown in the image below.

You will get the sentiment score and sentiment magnitude as a direct API response. You can modify the sentiment tag categorisations to something more suitable for your project or analysis (e.g. Instead of “slightly negative” have “Customer not happy”, or make them more granular from the Apps Script.

Visualise the sentiment analysis data in Looker Studio (optional)

Although this step is optional, it is highly recommended that you visualise this data. For this purpose, I created a Sentiment Analysis Looker Studio Dashboard Template that offers the following functionalities:

• Flexible Data Input: Upload text directly or connect various data sources for broader analysis options.
• Customizable Content Dimensions: Define custom fields relevant to your content type (source, author, product, etc.).
• Sentiment Breakdown: See the overall sentiment distribution (positive, negative, neutral) through charts.
• Sentiment Trends: Track sentiment changes over time (daily, weekly, monthly) using line charts.
• Topic and Sentiment: Identify key topics and their emotional tone with a sentiment-colored word cloud.
• Sentiment vs. Other Metrics: Explore correlations between sentiment scores and other relevant metrics (ratings, sales) using scatter plots.
• Granular Analysis: Segment data using advanced filters based on different dimensions and regular expressions for in-depth exploration.
• Customizable Dashboard: Adapt chart titles, labels, and filters to your specific content type.

This comprehensive dashboard provides a versatile solution for sentiment analysis across various content formats.

Why use Google’s Natural Language API sentiment analysis in Google Sheets

There are several advantages to using Google’s Natural Language API for sentiment analysis within Google Sheets:

• Powerful and Accurate: Google’s Natural Language API is a machine learning model trained on a massive dataset of text and code. This translates to robust sentiment analysis capabilities, offering more nuanced results than simpler methods, or even to building your own sentiment classifier from scratch.
• Built-in Integration: Since both Google Sheets and the Natural Language API are Google products, they integrate seamlessly. This eliminates the need for complex data transfer or external tools, keeping your workflow smooth. Not to mention, you can also do this analysis directly in Big Query for bigger dataset analysis, or integrating with Python through Google Colab, with Google’s ready-to-use code snippets for this API.
• Scalability: The Natural Language API can handle large datasets efficiently. This makes it ideal for analyzing big batches of text data, like customer reviews or social media comments, directly within your Google Sheet.
• Customization: Google Sheets offers flexibility in how you display and analyze the sentiment data. You can create pivot tables or charts to visualize sentiment trends or combine sentiment scores with other data points for deeper insights.
• Cost-Effective: Google Sheets is free to use, and the Natural Language API offers a free tier with a generous quota. This makes it an attractive option for individuals and small businesses starting with sentiment analysis.

In addition to these core benefits, consider mentioning:

• Ease of Use: While some coding knowledge might be required to set up the initial connection between Google Sheets and the API, the process itself is relatively straightforward, especially with the help of available tutorials and code samples.
• Multilingual Support: The Natural Language API supports various languages, allowing you to analyze sentiment in your preferred language or across a multilingual dataset.

By leveraging Google’s Natural Language API within Google Sheets, you gain a powerful and accessible tool for sentiment analysis, empowering you to make data-driven decisions based on the emotional tone of your text data.

Learn how to implement sentiment analysis in your Organic Search strategy

Analysing sentiment is not typically a step that organic search marketers do, even though it can be extremely useful in many areas of digital marketing. Here are just some of the projects, where sentiment analysis can be help you create a better organic search strategy:

• Analyzing Customer Reviews: Identify and categorise sentiment behind customer product or business reviews from different sources like Google Business Profile (previously Google my Business), Amazon stores, G2, Trustpilot or others. This can help you create a sentiment monitor from key products or your business, and use the insights to tailor your digital or campaign messaging.
• Analyse social comment interactions: Any legitimate business nowadays has a social presence that spans over multiple social platforms and generates thousands of interactions, many of which are text-based (like comments). These can be analysed for sentiment to determine the receptiveness of your audience towards key product launches, campaigns, or your overall messaging.
• Online Reputation Management: With organic search marketing often involving digital PR campaigns and new product or content releases, inevitably there will be a point, where your brand generates a discussion on other websites. By doing sentiment analysis on the content that mentions your brand you can ensure that you take a proactive approach towards your online reputation management like amplifying positive media that mentions you, and addressing negative press swiftly.

We will soon publish follow-up resources for you to learn how to harness this data to improve your strategy. For now, see this post to learn how to implement sentiment analysis in digital marketing and SEO tasks.

See what else you can do with to this API

As mentioned at the start, the Natural Language API has several additional capabilities that include text classification, entity analysis (which also includes entity sentiment analysis), and syntax analysis. Explore other step-by-step guides for this API by visiting the resources, linked below:

How to do content moderation with Google’s Natural Language API in Google Sheets (Apps Script)

Lazarina Stoy. May 8, 2024

How to do Syntax Analysis with Google’s Natural Language API in Google Sheets (Apps Script)

Lazarina Stoy. April 23, 2024

How to do Entity Extraction with Google’s Natural Language API in Google Sheets (Apps Script)

Lazarina Stoy. March 27, 2024