-
-
Save aliciawilliams/2f27bb592d16109c8c977dab5302af13 to your computer and use it in GitHub Desktop.
/** | |
* 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 | |
var newValues = [] | |
for each (var entity in nlData.entities) { | |
var 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); | |
} | |
// 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); | |
}; |
@aliciawilliams...figured out the earlier error..It is with the migration of this script execution from earlier Mozilla engine to Chrome V8 engine..I disabled V8 and worked like a charm..guess some syntax doesn't port over well..
Hi @aliciawilliams!
I encounter similar error when retrieving Entity Statement:
Exception: Request failed for https://language.googleapis.com 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 95, file "Code")
I used the script below:
var docDetails = {
language: 'en-us',
type: 'PLAIN_TEXT',
content: line
};
var nlData = {
document: docDetails,
encodingType: 'UTF8'
};
var nlOptions = {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify(nlData)
};
var response= UrlFetchApp.fetch(apiEndpoint, nlOptions);
Logger.log(response.getContentText());
return JSON.parse(response);
};
Thank you!
FYI, the
retrieveEntitySentiment()
function in this gist is correct, but the code sample in the following article is missing the leading quote for theapiEndpoint
variable on line 3:
Thanks!
@aliciawilliams...figured out the earlier error..It is with the migration of this script execution from earlier Mozilla engine to Chrome V8 engine..I disabled V8 and worked like a charm..guess some syntax doesn't port over well..
Thank you!
@madilk Thanks for the heads up on the blog, I am going to see if I can get it updated. Hopefully I can find some time to get the code updated for the new runtime too.
@aliciawilliams and others: The max runtime for Google Apps script is 6 min. I was trying NL API on cells where each cell had ~1000 char * 8 K rows. The closest SO thread I found on this topic was here but I'm not sure if/how this would translate to the NL API script posted above. From what I can read [as a non-dev] here, this generic script would check IF run time crosses 5 min THEN save results and start a new call ? https://stackoverflow.com/questions/41971806/how-can-i-increase-the-6-minute-execution-limit-in-google-apps-script
@aliciawilliams..It worked. Thanks!