Quick sentiment analysis macros for Google Sheets

main.gs

/**
 * @file 
 * Macros for getting sentiment scores and categories for NLP.
 */

// Google API Key for making a call to the NLP API.
var GoogleAPIKey = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

/**
 * Sheets custom function for getting text out of a range, concatenating the text, 
 * and getting NLP data from it.
 */
function sentiments(range) {
  var concat = '',
    returnArr = [[0,0,'',0]];
  
  // Concatenate all the text in the selected range as sentences.
  if (range.map) {
    range.forEach(function(row, index) {
      concat += row.join('. ');
    });
    concat = concat.toString();
  }
  else {
    concat = range.toString();
  }
  
  var nlpData = naturalLanguageCall(concat);
  if (nlpData.documentSentiment && nlpData.documentSentiment.magnitude) {
    if (nlpData.documentSentiment.score) {
      returnArr[0][0] = nlpData.documentSentiment.score.toString();
    }
    
    if (nlpData.documentSentiment.magnitude) {
      returnArr[0][1] = nlpData.documentSentiment.magnitude.toString();
    }
  }
  
  if (nlpData.categories && nlpData.categories[0]) {
    if (nlpData.categories[0].name) {
      returnArr[0][2] = nlpData.categories[0].name.toString();
    }
    
    if (nlpData.categories[0].confidence) {
      returnArr[0][3] = nlpData.categories[0].confidence.toString();
    }
  } 
  
  returnArr[0][4] = JSON.stringify(nlpData);
  
  // Returns 4 cells in a row: Sentiment Score, Sentiment Magnitude, Main Category, Category Confidence.
  return returnArr;
}

/**
 * Sheets custom function for getting text out of a range, concatenating the text, 
 * and getting NLP data from it.
 */
function sentimentPerSentence(range) {
  var concat = '',
    returnArr = [['','',0,0,'',0]];
  
  // Concatenate all the text in the selected range as sentences.
  if (range.map) {
    range.forEach(function(row, index) {
      concat += row.join('. ');
    });
    concat = concat.toString();
  }
  else {
    concat = range.toString();
  }
  
  var nlpData = naturalLanguageCall(concat);
  
  // First, special row.
  returnArr[0][0] = nlpData.sentences.length + ' sentences total.';
  if (nlpData.documentSentiment && nlpData.documentSentiment.magnitude) {
    if (nlpData.documentSentiment.score) {
      returnArr[0][2] = nlpData.documentSentiment.score.toString();
    }
    
    if (nlpData.documentSentiment.magnitude) {
      returnArr[0][3] = nlpData.documentSentiment.magnitude.toString();
    }
  }
  
  if (nlpData.categories && nlpData.categories[0]) {
    if (nlpData.categories[0].name) {
      returnArr[0][4] = nlpData.categories[0].name.toString();
    }
    
    if (nlpData.categories[0].confidence) {
      returnArr[0][5] = nlpData.categories[0].confidence.toString();
    }
  } 
  
  returnArr[0][6] = JSON.stringify(nlpData);
  
  // Rows per sentence.
  nlpData.sentences.forEach(function(sentence, index){
    var thisRow = ['','',0,0,'',0];
    if (sentence.text && sentence.text.content) {
      thisRow[1] = sentence.text.content.toString();
    }
    if (sentence.sentiment) {
      if (sentence.sentiment.score) {
        thisRow[2] = sentence.sentiment.score.toString();
      }
      if (sentence.sentiment.magnitude) {
        thisRow[3] = sentence.sentiment.magnitude.toString();
      }
    }
    
    returnArr.push(thisRow);
  });
  
  return returnArr;
}

/**
 * A quieter version of UrlFetchApp.fetch().
 *
 * @see https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app
 *
 * @arg String url
 * @arg Object params
 * @return String
 */
function urlFetch(url, params) {
  if (!params) {
    params = {
      method: 'GET',
      muteHttpExceptions: true
    };
  }
  else {
    params.muteHttpExceptions = true;
  }
  
  var returnval = '{}';
  var response = UrlFetchApp.fetch(url, params);
  if (response.getResponseCode() === 200) {
    returnval = response;
  }
  
  return returnval;
}

/**
 * Send some text to the Natural Language API and get analysis back.
 *
 * The three analyses are overall document sentiment, a broad category,
 * and entity extraction from the text.
 *
 * @arg String text
 * @return Object data
*/
function naturalLanguageCall(text) {
  // Set the NLP features to run.
  var features = {
    'extractEntities': true,
    'extractDocumentSentiment': true
  };
  if (text.split(' ').length > 20) {
    features.classifyText = true;
  }
  
  // Set up the payload for the request.
  var requestUrl = [
    'https://language.googleapis.com/v1beta2/documents:annotateText?key=',
    GoogleAPIKey
  ].join("");
  var data = {
    'document': {
      'language': 'en-us',
      'type': 'PLAIN_TEXT',
      'content': text
    },
    'features': features,
    'encodingType': 'UTF8'
  };
  
  // Make the request and turn it into vars.
  var options = {
    method : 'POST',
    contentType: 'application/json',
    payload : JSON.stringify(data)
  };
  var response = urlFetch(requestUrl, options);
  var data = JSON.parse(response);
  
  return data;
}