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

@Stellaxu19 If you open the script editor (Tools menu -> Script Editor) and then run the function directly from there, you can then View -> Execution transcript to see what happened. Sometimes if it's a really short piece of text, the script will successfully execute, but there won't be any entities to report. If there's an error, then you will see more information about the error there as well.

Thank you very much. the template works well. when I try to text the retrieveentitysentiment, there is same problem. However, the results can be output.

@stellaxu You need to run markEntitySentiment. If you want to run retrieveEntitySentiment on it's own to test, you will need to get rid of the line parameter, and then hard code in a sentence to test on next to content:...something like the below. Then it won't display in the spreadsheet, but you should be able to log the response.

function retrieveEntitySentiment () {
  var apiKey = myApiKey;
  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: 'I liked the sushi but the location was terrible.'
    },
    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 Got it. Thank you very much.

@aliciawilliams I get a Syntax error: Unexpected identifier (line 74, file "Code.gs") Could you please help with this? Thank you.

@AlXias It's hard for me to tell from this amount of information. Can you try to recreate your spreadsheet following the instructions on the new solutions page: https://developers.google.com/gsuite/solutions/feedback-sentiment-analysis ? That has the most up-to-date code. Many times, the problem can be with the format of the spreadsheet.

@AlXias It's hard for me to tell from this amount of information. Can you try to recreate your spreadsheet following the instructions on the new solutions page: https://developers.google.com/gsuite/solutions/feedback-sentiment-analysis ? That has the most up-to-date code. Many times, the problem can be with the format of the spreadsheet.

Thanks a lot, @aliciawilliams, it worked! I don't know what the issue was, but I made a copy of the mentioned Google Sheets and modified it. I really appreciate your assistance!

@aliciawilliams I get the same error..Tried serval ways but not sure why is happening..
Syntax error: Unexpected identifier (line 74, file "Code.gs")
It is with this statement for each (var entity in nlData.entities) copied the relevant script below in its entirety..
Pls help and thanks in advance

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");
 }

}
};

@sdarive Can you try to follow the same instructions I gave above; to start with the solutions gallery template and just paste in your text data? I'm not sure, but my hypothesis is that the response back from the NL API doesn't identify any entities, so make sure you try it with full sentences. Let me know how it goes.

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