Connect to an API: Analyze feedback sentiment

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

Objectives

• Understand what the solution does.
• Understand what the Apps Script services do within the solution.
• Set up your environment.
• Set up the script.
• Run the script.

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 Google Cloud Natural Language API and marks each row as "Complete" once its sentiment has been analyzed.
• UrlFetch service–Connects to Google Cloud Natural Language API to perform entity and sentiment analysis on the text.

Prerequisites

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.

• A Google Cloud project with an associated billing account. Refer to Enable billing for a project.

Set up your environment

Open your Cloud project in the Google Cloud console

If it's not open already, open the Cloud project that you intend to use for this sample:

1. In the Google Cloud console, go to the Select a project page.

   Select a Cloud project

2. Select the Google Cloud project you want to use. Or, click Create project and follow the on-screen instructions. If you create a Google Cloud project, you might need to turn on billing for the project.

Turn on the Google Cloud Natural Language API

This solution connects to the Google Cloud Natural Language API. Before using Google APIs, you need to turn them on in a Google Cloud project. You can turn on one or more APIs in a single Google Cloud project.

• In your Cloud project, turn on the Google Cloud Natural Language API.

  Turn on the API

Configure the OAuth consent screen

This solution requires a Cloud project with a configured consent screen. Configuring the OAuth consent screen defines what Google displays to users and registers your app so that you can publish it later.

1. In the Google Cloud console, go to Menu > APIs & Services > OAuth consent screen.

   Go to OAuth consent screen

2. For User type select Internal, then click Create.
3. Complete the app registration form, then click Save and Continue.

4. For now, you can skip adding scopes and click Save and Continue. In the future, when you create an app for use outside of your Google Workspace organization, you must change the User type to External, and then, add the authorization scopes that your app requires.

5. Review your app registration summary. To make changes, click Edit. If the app registration looks OK, click Back to Dashboard.

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. In the Google Cloud console, go to Menu > APIs & Services > Credentials.

   Go to Credentials

3. Click Create credentials > API key.

4. Take note of your API key for use in a later step.

Set up the script

Create the Apps Script project

1. Click the button below to make a copy of the Sentiment analysis for feedback sample spreadsheet. The Apps Script project for this solution is attached to the 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 .

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.

Run the script

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. If the OAuth consent screen displays the warning, This app isn't verified, continue by selecting Advanced > Go to {Project Name} (unsafe).

3. Click Sentiment Tools > Mark entities and sentiment again.

4. When the script finishes, switch to the Pivot Table sheet to see the results.

Review the code

To review the Apps Script code for this solution, click View source code below:

View source code

Code.gs

solutions/automations/feedback-sentiment-analysis/code.js

View on GitHub

// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/feedback-sentiment-analysis

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

// Sets API key for accessing Cloud Natural Language API.
const myApiKey = 'YOUR_API_KEY'; // Replace with your API key.

// Matches column names in Review Data sheet to variables.
let COLUMN_NAME = {
  COMMENTS: 'comments',
  ENTITY: 'entity_sentiment',
  ID: 'id'
};

/**
 * Creates a Demo menu in Google Spreadsheets.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Sentiment Tools')
    .addItem('Mark entities and sentiment', 'markEntitySentiment')
    .addToUi();
};

/**
* Analyzes entities and sentiment for each comment in  
* Review Data sheet and copies results into the 
* Entity Sentiment Data sheet.
*/
function markEntitySentiment() {
  // Sets variables for "Review Data" sheet
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let dataSheet = ss.getSheetByName('Review Data');
  let rows = dataSheet.getDataRange();
  let numRows = rows.getNumRows();
  let values = rows.getValues();
  let headerRow = values[0];

  // Checks to see if "Entity Sentiment Data" sheet is present, and
  // if not, creates a new sheet and sets the header row.
  let entitySheet = ss.getSheetByName('Entity Sentiment Data');
  if (entitySheet == null) {
   ss.insertSheet('Entity Sentiment Data');
   let entitySheet = ss.getSheetByName('Entity Sentiment Data');
   let esHeaderRange = entitySheet.getRange(1,1,1,6);
   let esHeader = [['Review ID','Entity','Salience','Sentiment Score',
                    'Sentiment Magnitude','Number of mentions']];
   esHeaderRange.setValues(esHeader);
  };

  // Finds the column index for comments, language_detected, 
  // and comments_english columns.
  let textColumnIdx = headerRow.indexOf(COLUMN_NAME.COMMENTS);
  let entityColumnIdx = headerRow.indexOf(COLUMN_NAME.ENTITY);
  let idColumnIdx = headerRow.indexOf(COLUMN_NAME.ID);
  if (entityColumnIdx == -1) {
    Browser.msgBox("Error: Could not find the column named " + COLUMN_NAME.ENTITY + 
                   ". Please create an empty column with header \"entity_sentiment\" on the Review Data tab.");
    return; // bail
  };

  ss.toast("Analyzing entities and sentiment...");
  for (let i = 0; i < numRows; ++i) {
    let value = values[i];
    let commentEnCellVal = value[textColumnIdx];
    let entityCellVal = value[entityColumnIdx];
    let reviewId = value[idColumnIdx];

    // Calls retrieveEntitySentiment function for each row that has a comment 
    // and also an empty entity_sentiment cell value.
    if(commentEnCellVal && !entityCellVal) {
        let nlData = retrieveEntitySentiment(commentEnCellVal);
        // Pastes each entity and sentiment score into Entity Sentiment Data sheet.
        let newValues = []
        for (let entity in nlData.entities) {
          entity = nlData.entities [entity];
          let row = [reviewId, entity.name, entity.salience, entity.sentiment.score, 
                     entity.sentiment.magnitude, entity.mentions.length
                    ];
          newValues.push(row);
        }
      if(newValues.length) {
        entitySheet.getRange(entitySheet.getLastRow() + 1, 1, newValues.length, newValues[0].length).setValues(newValues);
      }
        // Pastes "complete" into entity_sentiment column to denote completion of NL API call.
        dataSheet.getRange(i+1, entityColumnIdx+1).setValue("complete");
     }
   }
};

/**
 * Calls the Cloud Natural Language API with a string of text to analyze
 * entities and sentiment present in the string.
 * @param {String} the string for entity sentiment analysis
 * @return {Object} the entities and related sentiment present in the string
 */
function retrieveEntitySentiment (line) {
  let apiKey = myApiKey;
  let apiEndpoint = 'https://language.googleapis.com/v1/documents:analyzeEntitySentiment?key=' + apiKey;
  // Creates a JSON request, with text string, language, type and encoding
  let nlData = {
    document: {
      language: 'en-us',
      type: 'PLAIN_TEXT',
      content: line
    },
    encodingType: 'UTF8'
  };
  // Packages all of the options and the data together for the API call.
  let nlOptions = {
    method : 'post',
    contentType: 'application/json',  
    payload : JSON.stringify(nlData)
  };
  // Makes the API call.
  let response = UrlFetchApp.fetch(apiEndpoint, nlOptions);
  return JSON.parse(response);
};

Contributors

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

Next steps

• Blog: Analyzing text in Google Sheets using Google Cloud Natural Language API and Apps Script
• Google Cloud Natural Language API documentation