Entity sentiment analysis on text data in a Google sheet using Cloud Natural Language

code.gs

/**
 * 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);
};

Thank you @aliciawilliams! I'll try that this evening..I did step through the overall script one step at a time without issues until that line..noticed that the that portion of the script is different than in your earlier versions..

@aliciawilliams..It worked. 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..

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 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

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

can't be saved as script: error SyntaxError: Unexpected identifier (line 74, file "Code.gs")