Google Sheets Enable Primer Natural Language Processing

Copy a simple Apps Script into any Google spreadsheet to quickly run multiple Primer Models on text in any data cell

Understanding a large corpus of text-based documents is getting a lot easier with NLP. Where previously we might need a human to perform a manual review of social media posts, news articles, or financial and legal documents, now we can process them using NLP models to extract entities and key phrases, understand sentiment, and classify documents.

This tutorial shows you how to run a sentiment model and a key phrase extractor with Primer Engines to analyze customer reviews. We’ll get our reviews from Amazon and then use Primer’s pre-trained models (we call them Engines) to analyze sentiment and extract phrases from each review to see what patterns we can detect. To make things even simpler, this tutorial lets you play around with Engines inside of Google Sheets.

To get your Engines API key, Sign up here: https://primer.ai/engines-sign-up.

Once you have an account you’ll have the ability to create your api key within the app:
https://developers.primer.ai/account/api-keys

All you’ll need to do to run this tutorial is have a Google spreadsheet and to sign up for free for Primer Engines. Let’s get started. 

Set-up

To start, let’s collect the top 10 customer Amazon reviews from this Bubble machine, as well as a few one star reviews. You can do this yourself, but we’ve gone ahead and collected them in this spreadsheet that you can clone

Once we have built our dataset, we want to empower the spreadsheet with NLP. To do this I’m just going to copy this snippet of code into the Apps Script module of the Extensions tab.

var api_key_row = 2;
var api_key_col = 2;
var API_KEY = SpreadsheetApp.getActiveSheet().getRange(api_key_row, api_key_col).getValue();
 
// flag for editing without triggering requests.
let calls_disabled = false
 
// default Engines request data
class EnginesRequest {
 constructor(endpoint) {
   this.base_url = “https://engines.primer.ai/api/”;
   this.endpoint = endpoint
   this.headers = {“Authorization”: “Bearer ” + API_KEY,”Content-Type”: “application/json”};
 }
 
 post(json_body_as_string) {
   // Fastest option but subject to 30/minute Engines rate limiting
   if (calls_disabled) {
   return “Engine Offline”
   }
   let request_url = this.base_url + this.endpoint;
   let options = {“method”:’post’,’payload’:json_body_as_string,’headers’:this.headers};
   let response = UrlFetchApp.fetch(request_url,options) ;
   return [[this.endpoint,response.getContentText()]]
 }
 
 post_async(json_body_as_string) {
   if (calls_disabled) {
   return “Engine Offline”
   }
   let request_url = this.base_url + this.endpoint;
   let options = {“method”:”post”,’payload’:json_body_as_string,’headers’:this.headers};
   let response = UrlFetchApp.fetch(request_url,options) ;
   if (response.getResponseCode() !== 200) {
     return [[this.endpoint,response.getContentText()]]
   }
 
   let result_id = response.getContentText();
   Utilities.sleep(200);
   options = {“method”:”get”, ‘headers’:this.headers};
   let get_url = this.base_url + “v1/result/” + result_id.replace(/[‘”]+/g, ”);
   let get_response = UrlFetchApp.fetch(get_url,options);
   let count = 0;
   while (get_response.getResponseCode() == 202 && count < 1000) {
     Utilities.sleep(1000);
     count += 1;
     get_response = UrlFetchApp.fetch(get_url,options);
   }
   return [[this.endpoint,get_response.getContentText()]]
 }
}
 
function genericEngineSync(endpoint, text) {
 if (text === “” || text === null || text === undefined) {
   return “Engine Disabled – Text Field Required”
 }
 let req = new EnginesRequest(endpoint);
 let body = JSON.stringify({ text });
 return req.post(body)
}
 
function genericEngine(endpoint, text) {
 // By default generic engine is async as synchronous requests are rate limited.
 if (typeof API_KEY === ‘undefined’) {
     return “Engine Disabled – API Key Required”;
 }
 if (text === “” || text === null || text === undefined) {
   return “Engine Disabled – Text Field Required”
 }
 let req = new EnginesRequest(endpoint);
 let body = JSON.stringify({‘text’ : [text]});
 return req.post_async(body)
}
var api_key_row = 2;
var api_key_col = 2;
var API_KEY = SpreadsheetApp.getActiveSheet().getRange(api_key_row, api_key_col).getValue();
Once you’ve pasted in the data be sure to save the script.

Extracting sentiment with Primer’s NLP models

Now that we have the logic in place that we’ll call from our spreadsheet. Let’s give it a try with the Primer’s Sentiment model (For a full list of models available, check out the API documents here: https://engines.primer.ai).

In our sheet let’s put a cell next to the first review and use following function call:

=genericEngine(“v1/classify/sentiment”,A4)

Our cell will have an error stating we need to include the API key.

You can update the script to have the key defined, or you can update location the script is currently looking for the key: Column B, Row 2.

To get your Engines API key, if you haven’t already: Sign up here: https://primer.ai/engines-sign-up. Once you have an account you’ll have the ability to create your api key within the app:
https://developers.primer.ai/account/api-keys

With the key added in – and partially obscured 🙂 – you should now see the results displayed for your input data. A model’s magnitude, 0.97 in this case, shows the model’s confidence in this sentiment label. The closer to 1, the higher the confidence in the label. For full details on our Sentiment model, please see the documentation here: https://developers.primer.ai/docs#v1-classify-sentiment

You can now start getting a quick look at the data by dragging the function to include our other data cells.

Looking at the data manually, it looks like the bubble maker wasn’t a positive experience for one cat owner. We can run the Primer Key Phrase extractor model on the data to get additional details that may help us understand why. 

Just add the following code next to that cell and let’s take a look at phrases in this negative review.

=genericEngine(“v1/generate/phrases”,A8)

Some keywords come to the surface: “motor”, “noisy”, “scares” that give us an idea of where this negativity is coming from. The span field shows us the exact character index the phrase was found in the text! Let’s add this code to the whole sheet so we’re both determining sentiment for every review and also extracting phrases to give us a dataset that we can parse and display for some high level insight.

Ok, we’ve extracted the sentiment and key phrases of the document. To make the data easier to parse, we can copy the following bit of JavaScript Apps Script we copied over. It will add some formatting logic.


class DefaultDict {
 constructor(defaultVal) {
   return new Proxy({}, {
     get: (target, name) => target[name] || defaultVal
   })
 }
}
 
function aggregatePhraseSentiment(sentiment_range, phrase_range) {
 if (typeof API_KEY === ‘undefined’) {
     return “aggregatePhraseSentiment script”;
 }
 let positive_sentiment_phrases = new DefaultDict(0);
 let negative_sentiment_phrases = new DefaultDict(0);
 let phrase_seen_count = new DefaultDict(0); // for sort by value descending to show most popular phrase
 let phrase_name = “”
 for (const phrase_list_index in phrase_range) {
   phrase_dict = JSON.parse(phrase_range[phrase_list_index]);
   if (“phrases” in phrase_dict) {
     for (phrase_index in phrase_dict[“phrases”]) {
       phrase_name = phrase_dict[“phrases”][phrase_index][“phrase”]
       phrase_seen_count[phrase_name]++;
       // check corresponding sentiment e.g. {“sentiment”: “negative”, “magnitude”: 0.973}
       sentiment_dict = JSON.parse(sentiment_range[phrase_list_index])
       if (“negative” === sentiment_dict[“sentiment”]) {
         negative_sentiment_phrases[phrase_name]++;
       }
       else if (“positive” === sentiment_dict[“sentiment”]) {
         positive_sentiment_phrases[phrase_name]++;
     }
   }
   }
 }
 
 var popular_phrases_descending = Object.keys(phrase_seen_count).map(function(key) {
   return [key, phrase_seen_count[key]];
 });
 
 popular_phrases_descending.sort(function(first, second) {
   return second[1] – first[1];
 });
 results = [[“aggregatePhraseSentiment script”, “Phrase”, “Count Positive”, “Count Negative”]];
 for (phrase in popular_phrases_descending) {
   key = popular_phrases_descending[phrase][0];
   count_positive = positive_sentiment_phrases[key];
   count_negative = negative_sentiment_phrases[key];
   results.push([“”, key, count_positive, count_negative]);
 }
 return results;
}

Now from our sample set reviews, we can see at a glance which features were positive and which were negative about the bubble maker. You can add as many reviews into this dataset as you’d like to understand the customer experience of a particular product. Or you could do this with any unstructured dataset for your organization. 

All Primer Engines can be called using the Google Sheet Apps Script function calls. Here’s a collection of a few Primer Engines being run on sample data.

+Link to the Primer Engines in Google spreadsheet.

For more information about Primer and to access product demos, contact Primer here.