Instantly share code, notes, and snippets.

@aliciawilliams /code.js Secret
Last active Nov 14, 2018

Embed
What would you like to do?
Entity sentiment analysis on text data in a Google sheet using Cloud Natural Language
/**
* Performs entity sentiment analysis on english text data in a sheet using Cloud Natural Language (cloud.google.com/natural-language/).
*/
var COLUMN_NAME = {
COMMENTS: 'comments',
LANGUAGE: 'language_detected',
TRANSLATION: 'comments_english',
ENTITY: 'entity_sentiment',
ID: 'id'
};
/**
* Creates a ML Tools menu in Google Spreadsheets.
*/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('ML Tools')
.addItem('Mark Entities and Sentiment', 'markEntitySentiment')
.addToUi();
};
/**
* For each row in the reviewData sheet with a value in "comments" field,
* will run the retrieveEntitySentiment function
* and copy results into the entitySentiment sheet.
*/
function markEntitySentiment() {
// set variables for reviewData sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName('reviewData');
var rows = dataSheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var headerRow = values[0];
// checks to see if entitySentiment sheet is present; if not, creates new sheet and sets header row
var entitySheet = ss.getSheetByName('entitySentiment');
if (entitySheet == null) {
ss.insertSheet('entitySentiment');
var entitySheet = ss.getSheetByName('entitySentiment');
var esHeaderRange = entitySheet.getRange(1,1,1,6);
var esHeader = [['Review ID','Entity','Salience','Sentiment Score','Sentiment Magnitude','Number of mentions']];
esHeaderRange.setValues(esHeader);
};
// find the column index for comments, language_detected, comments_english
var commentsColumnIdx = headerRow.indexOf(COLUMN_NAME.COMMENTS);
var languageColumnIdx = headerRow.indexOf(COLUMN_NAME.LANGUAGE);
var translationColumnIdx = headerRow.indexOf(COLUMN_NAME.TRANSLATION);
var entityColumnIdx = headerRow.indexOf(COLUMN_NAME.ENTITY);
var 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 reviewData tab.");
return; // bail
};
ss.toast("Analyzing entities and sentiment...");
// Process each row
for (var i = 0; i < numRows; ++i) {
var value = values[i];
var commentEnCellVal = value[translationColumnIdx];
var entityCellVal = value[entityColumnIdx];
var reviewId = value[idColumnIdx];
// Call retrieveEntitySentiment function for each row that has comments and also an empty entity_sentiment cell
if(commentEnCellVal && !entityCellVal) {
var nlData = retrieveEntitySentiment(commentEnCellVal);
// Paste each entity and sentiment score into entitySentiment sheet
for (var j = 0; j < nlData.entities.length; ++j) {
var entityInResponse = nlData.entities[j];
var lastRowIdx = entitySheet.getLastRow() + 1;
var newValues = [[reviewId, entityInResponse.name, entityInResponse.salience, entityInResponse.sentiment.score, entityInResponse.sentiment.magnitude, entityInResponse.mentions.length]];
var pastingRange = entitySheet.getRange(lastRowIdx,1,1,6);
pastingRange.setValues(newValues);
}
// Paste "complete" into entity_sentiment column to denote completion of NL API call
dataSheet.getRange(i+1, entityColumnIdx+1).setValue("complete");
}
}
};
/**
* Calls the NL API with a string
* @param {String} line The line of string
* @return {Object} the entities and related sentiment present in the string.
*/
function retrieveEntitySentiment (line) {
var apiKey = "your key here";
var apiEndpoint = 'https://language.googleapis.com/v1/documents:analyzeEntitySentiment?key=' + apiKey;
// Create our json request, w/ text, language, type & encoding
var nlData = {
document: {
language: 'en-us',
type: 'PLAIN_TEXT',
content: line
},
encodingType: 'UTF8'
};
// Package all of the options and the data together for the call
var nlOptions = {
method : 'post',
contentType: 'application/json',
payload : JSON.stringify(nlData)
};
// And make the call
var response = UrlFetchApp.fetch(apiEndpoint, nlOptions);
return JSON.parse(response);
};
@abhibeta

This comment has been minimized.

abhibeta commented Mar 26, 2018

I am getting error
TypeError: Cannot call method "getSheetByName" of null.
I am using google spread sheet in js first time,
can you help.
I uploaded excel in google sheet.
I am wondering if there is any path to be defined script is not able to reach my sheet I guess

@urwa

This comment has been minimized.

urwa commented Mar 28, 2018

@abhibeta

LINE 33 var dataSheet = ss.getSheetByName('reviewData');
Replace reviewData with the name of your sheet.

@shawben

This comment has been minimized.

shawben commented Apr 20, 2018

This is great thanks! How would I change this to return entities for each row separately? I'm trying to figure out how to process each comment entry and return associated entities separately. Any suggestion would be really helpful.

@demoive

This comment has been minimized.

demoive commented May 26, 2018

FYI, the retrieveEntitySentiment() function in this gist is correct, but the code sample in the following article is missing the leading quote for the apiEndpoint variable on line 3:

https://cloud.google.com/blog/big-data/2017/12/analyzing-text-in-a-google-sheet-using-cloud-natural-language-api-and-apps-script#closeImage

@francescozitelli

This comment has been minimized.

francescozitelli commented Jun 5, 2018

Hi Alicia, in some do you think it is possible to access my own airbnb listing data to create with your guide my own reviews sentiment analysis? (I am a host on airbnb)
Thanks a lot!

@aliciawilliams

This comment has been minimized.

Owner

aliciawilliams commented Jun 21, 2018

@francescozitelli

You should check http://insideairbnb.com/get-the-data.html to see if your listing is represented. Otherwise, you'd have to scrape the data yourself. If you use Gmail, you could potentially use the Gmail API to search and parse the data from the email notifications you get when reviews are completed. Good luck!

@aliciawilliams

This comment has been minimized.

Owner

aliciawilliams commented Jun 21, 2018

@demoive
Thank you, just requested that to be fixed!

@aliciawilliams

This comment has been minimized.

Owner

aliciawilliams commented Jun 21, 2018

@shawben
The entities are given a row each in the entitySentiment tab of the spreadsheet, and they are marked with the reviewID. This means each review can have multiple rows associated with it. If you want to look at only entities for one comment, you could filter on that reviewID. Does this answer your question?

@francescozitelli

This comment has been minimized.

francescozitelli commented Jul 10, 2018

Thanks a lot Alicia, I will try it!

@VanGTO

This comment has been minimized.

VanGTO commented Sep 17, 2018

Hi all,

I'm getting the following error:

Request failed for https://language.googleapis.com/v1/documents:analyzeEntitySentiment?key=myProjectApiWasHere returned code 400. Truncated server response: { "error": { "code": 400, "message": "One of content, or gcs_content_uri must be set.", "status": "INVALID_ARGUMENT", "details": ... (use muteHttpExceptions option to examine full response) (line 111, file "Code")

Line of code highlighted:

var response = UrlFetchApp.fetch(apiEndpoint, nlOptions);

Any ideas where i'm going wrong on this one?

@nick-weaver

This comment has been minimized.

nick-weaver commented Sep 27, 2018

I'm getting the same:

Request failed for https://language.googleapis.com/v1/documents:analyzeEntitySentiment?key=[APIKEY] returned code 400. Truncated server response: { "error": { "code": 400, "message": "One of content, or gcs_content_uri must be set.", "status": "INVALID_ARGUMENT", "details": ... (use muteHttpExceptions option to examine full response). (line 111, file "Code")

Anyone any ideas?

@UncouthScientist

This comment has been minimized.

UncouthScientist commented Sep 30, 2018

Same line of code, different issue:

Request failed for https://language.googleapis.com/v1/documents:analyzeEntitySentiment?key=[myAPIkey - redacted] returned code 400. Truncated server response: { "error": { "code": 400, "message": "API key not valid. Please pass a valid API key.", "status": "INVALID_ARGUMENT", "details": ... (use muteHttpExceptions option to examine full response) (line 111, file "Code")

I've set up the NLP API key but I'm not sure why I've made it pitch a fit!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment