Page Summary

outlined_flag

• This solution allows for large-scale analysis of text data, like feedback, directly within Google Sheets.

• It uses the UrlFetch Service in Apps Script to connect to the Google Cloud Natural Language API for entity and sentiment analysis.

• The script processes text from a spreadsheet, sends it to the Natural Language API, and summarizes the average sentiment score for each identified entity in a pivot table.

• Setting up the environment requires a Google Account, a web browser, a Google Cloud project with billing enabled, and enabling the Google Cloud Natural Language API.

• The solution uses Apps Script's Spreadsheet service to manage data in Google Sheets and the UrlFetch service to interact with the Google Cloud Natural Language API.

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

Objectives

• Understand what the solution does.
• Understand what the Apps Script services do within thesolution.
• 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 performentity and sentiment analysis from within Google Sheets, this solution usesUrlFetch Service to connect to theGoogle CloudNatural Language API.

How it works

The script gathers text from the spreadsheet and connects to the Google CloudNatural Language API to analyze entities and sentiment present in the string.A pivot table summarizes the average sentiment score for each entity mentionedacross all rows of text data.

Apps Script services

This solution uses the following services:

• Spreadsheet service–Sends the text datato the Google Cloud Natural Language API and marks each row as "Complete" once itssentiment has been analyzed.
• UrlFetch service–Connects to Google CloudNatural 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 mightrequire administrator approval).

• A web browser with access to the internet.

• A Google Cloud project with an associated billing account. Refer toEnablebilling 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 usefor this sample:

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

   Select a Cloud project

2. Select the Google Cloud project you want to use. Or, clickCreate project and follow the on-screen instructions. If you create a Google Cloud project, you might need toturn 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 consentscreen. Configuring the OAuth consent screen defines what Google displays tousers and registers your app so that you can publish it later.

1. In the Google Cloud console, go to Menumenu>Google Auth platform>Branding.

   Go to Branding

2. If you have already configured the Google Auth platform, you can configure the following OAuth Consent Screen settings inBranding,Audience, andData Access. If you see a message that saysGoogle Auth platform not configured yet, clickGet Started:
1. UnderApp Information, inApp name, enter a name for the app.
2. InUser support email, choose a support email address where users can contact you if they have questions about their consent.
3. ClickNext.
4. UnderAudience, selectInternal.
5. ClickNext.
6. UnderContact Information, enter anEmail address where you can be notified about any changes to your project.
7. ClickNext.
8. UnderFinish, review theGoogle API Services User Data Policy and if you agree, selectI agree to the Google API Services: User Data Policy.
9. ClickContinue.
10. ClickCreate.
3. For now, you can skip adding scopes. In the future, when you create an app for use outside of your Google Workspace organization, you must change theUser type toExternal. Then add the authorization scopes that your app requires. To learn more, see the fullConfigure OAuth consent guide.

Get an API key for the Google Cloud Natural Language API

1. Go to theGoogle Cloud console.Make sure your billing-enabled project is open.

2. In the Google Cloud console, go to Menumenu>APIs & Services>Credentials.

   Go to Credentials

3. ClickCreate 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 theSentiment analysis forfeedback sample spreadsheet. The Apps Scriptproject for this solution is attached to the spreadsheet.
   Make a copy
2. ClickExtensions>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 theid andcomments columns. You can use samplevacation property reviews fromKaggle or use your own data. You can add more columns if needed, but to runsuccessfully, the script must have data in theid andcommentscolumns.

Run the script

1. At the top of the spreadsheet, clickSentiment Tools>Mark entities and sentiment. Youmight 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 selectingAdvanced>Go to {Project Name} (unsafe).

3. ClickSentiment Tools>Mark entities and sentiment again.

4. When the script finishes, switch to thePivot Table sheet to see theresults.

Review the code

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

// 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 LLCLicensed 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.0Unless required by applicable law or agreed to in writing, softwaredistributed 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 andlimitations under the License.*/// Sets API key for accessing Cloud Natural Language API.constmyApiKey="YOUR_API_KEY";// Replace with your API key.// Matches column names in Review Data sheet to variables.constCOLUMN_NAME={COMMENTS:"comments",ENTITY:"entity_sentiment",ID:"id",};/** * Creates a Demo menu in Google Spreadsheets. */functiononOpen(){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. */functionmarkEntitySentiment(){// Sets variables for "Review Data" sheetconstss=SpreadsheetApp.getActiveSpreadsheet();constdataSheet=ss.getSheetByName("Review Data");constrows=dataSheet.getDataRange();constnumRows=rows.getNumRows();constvalues=rows.getValues();constheaderRow=values[0];// Checks to see if "Entity Sentiment Data" sheet is present, and// if not, creates a new sheet and sets the header row.constentitySheet=ss.getSheetByName("Entity Sentiment Data");if(entitySheet==null){ss.insertSheet("Entity Sentiment Data");constentitySheet=ss.getSheetByName("Entity Sentiment Data");constesHeaderRange=entitySheet.getRange(1,1,1,6);constesHeader=[["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.consttextColumnIdx=headerRow.indexOf(COLUMN_NAME.COMMENTS);constentityColumnIdx=headerRow.indexOf(COLUMN_NAME.ENTITY);constidColumnIdx=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(leti=0;i <numRows;++i){constvalue=values[i];constcommentEnCellVal=value[textColumnIdx];constentityCellVal=value[entityColumnIdx];constreviewId=value[idColumnIdx];// Calls retrieveEntitySentiment function for each row that has a comment// and also an empty entity_sentiment cell value.if(commentEnCellVal &&!entityCellVal){constnlData=retrieveEntitySentiment(commentEnCellVal);// Pastes each entity and sentiment score into Entity Sentiment Data sheet.constnewValues=[];for(letentityinnlData.entities){entity=nlData.entities[entity];constrow=[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 */functionretrieveEntitySentiment(line){constapiKey=myApiKey;constapiEndpoint=`https://language.googleapis.com/v1/documents:analyzeEntitySentiment?key=${apiKey}`;// Creates a JSON request, with text string, language, type and encodingconstnlData={document:{language:"en-us",type:"PLAIN_TEXT",content:line,},encodingType:"UTF8",};// Packages all of the options and the data together for the API call.constnlOptions={method:"post",contentType:"application/json",payload:JSON.stringify(nlData),};// Makes the API call.constresponse=UrlFetchApp.fetch(apiEndpoint,nlOptions);returnJSON.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 NaturalLanguage API and Apps Script
• Google Cloud Natural Language API documentation