Google Sheets Text Analysis Add-on deprecation

In 2014, we built a simple Add-on for Google Sheets that allowed its users to leverage our Text Analysis API from their spreadsheets, without having to write any code.

This Add-on allowed our users generate reliable insights from their data in spreadsheet with just one click.

After almost five years of serving a phenomenally diverse group of hackers, analysts, and anyone curious enough to try out Deep Learning-powered NLP on their data, we’ve decided that the Add-on has run its course and we’re going to deprecate it as of this week.

But wait! You can still test out the Text Analysis API from Google Sheets

Even though the Add-on won’t be available any longer, you can still make calls to the Text Analysis API from your spreadsheets, which is a great way to check out the API’s abilities. You won’t need to start writing code, and seeing what insights you can generate from your data is still just a couple of clicks away

We’ve written a script (check it out at the bottom of the page) to help you start making calls to the Text Analysis API. To try it for yourself, simply paste the script into the Script Editor on the Tools tab, hit Save, and then enter this into the cell where you want your insights to appear:

=TextAPIResults(A1, "sentiment")

You can then extend that cell to analyze as many cells as you want at once:


This code works with all the Text Analysis API’s endpoints, so for example if you want to do more than Sentiment Analysis we’ve got you covered. To call one of the other endpoints (Classification, Entity Extraction, Summarization etc…) simply change “sentiment” to whichever one you want to use (just make sure the spelling and capitalization is correct!). “A1” in the function refers to the cell, so be sure to identify the right one before you hit enter

So let’s say I want to extract any entity mentioned in a piece of text I’d run the following function by adding it to the cell we want the results to appear in. Simple!

=TextAPIResults(A1, "entities")

The endpoints that the script currently supports are:

  • classify/iptc-subjectcode
  • classify/iab-qag
  • sentiment
  • concepts
  • extraction
  • summarize

NOTE: once you run this script, be sure to copy & paste the values in the cells, otherwise the script will execute every time you restart your browser or reopen the sheet.

How much does this new method cost?

Nothing. Nada. Zilch. It’s free of charge.

The Text Analysis API has a Free plan, and free means free (no trial period, no credit card details). You get 30,000 calls per month free of charge – that’s a lot of enriched content! Just sign up for the Free plan here, and paste in your credentials where you see the two prompts (“YOUR_APP_ID” and “YOUR_APP_ID“) in the script we just shared with you.

Hit Sign me up below the code to sign up for the Free plan and grab your credentials.

Enjoy digging into your data, and if you have any questions about setting up a permanent, robust workflow with the Text Analysis API, be sure to reach out with an email to support@aylien.com or hit us up on Intercom!

Here’s the script:


function getAppKey() {
  return "YOUR_APP_KEY";
}

function getAppID() {
  return "YOUR_APP_ID";
}


function getApiUrl(endpoint) {
  Logger.log("https://api.aylien.com/api/v1/" + endpoint);
  return "https://api.aylien.com/api/v1/" + endpoint;
}

function validateURL(input) {
  return /^(https?|ftp):\/\/(((([a-z]|\d|-|\.|_|~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(%[\da-f]{2})|[!\$&'\(\)\*\+,;=]|:)*@)?(((\d|[1-9]\d|1\d\d|2[0-4]\d|25[0-5])\.(\d|[1-9]\d|1\d\d|2[0-4]\d|25[0-5])\.(\d|[1-9]\d|1\d\d|2[0-4]\d|25[0-5])\.(\d|[1-9]\d|1\d\d|2[0-4]\d|25[0-5]))|((([a-z]|\d|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(([a-z]|\d|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])([a-z]|\d|-|\.|_|~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])*([a-z]|\d|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])))\.)+(([a-z]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(([a-z]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])([a-z]|\d|-|\.|_|~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])*([a-z]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])))\.?)(:\d*)?)(\/((([a-z]|\d|-|\.|_|~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(%[\da-f]{2})|[!\$&'\(\)\*\+,;=]|:|@)+(\/(([a-z]|\d|-|\.|_|~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(%[\da-f]{2})|[!\$&'\(\)\*\+,;=]|:|@)*)*)?)?(\?((([a-z]|\d|-|\.|_|~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(%[\da-f]{2})|[!\$&'\(\)\*\+,;=]|:|@)|[\uE000-\uF8FF]|\/|\?)*)?(\#((([a-z]|\d|-|\.|_|~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(%[\da-f]{2})|[!\$&'\(\)\*\+,;=]|:|@)|\/|\?)*)?$/i.test(input);
}

function detect_type(text) {
  if (validateURL(text)){
    return "url";
  } else {
    return "text";
  }
}

function sleep( Duration ){
    var now = new Date().getTime();
    while(new Date().getTime() < now + Duration){ }
}

function CallTextAPI(params) {
  var payload = {};
  var maxTries = 3;

  if (!params["input"] || params["input"].trim() == '')
    return [];

  params["input"] = params["input"].trim();
  var type = detect_type(params["input"]);

  if (type == "url")
    payload["url"] = params["input"]
  else
    payload["text"] = params["input"]

  var result;
  var count = 0;
  var catch_count = 0;

  while(true) {
    var api_url = getApiUrl(params["endpoint"]);
    var options = {
      "method" : "post",
      "headers": {
        "X-AYLIEN-TextAPI-Application-Key": getAppKey(),
        "X-AYLIEN-TextAPI-Application-ID": getAppID()
      },
      "payload": payload,
      "muteHttpExceptions": true
    };

    result = UrlFetchApp.fetch(api_url, options);
    Logger.log(result);

    var status_code = result.getResponseCode();
    Logger.log(status_code);
    if (status_code !== 200) {
      if (status_code == 429) {
        Utilities.sleep(5000 * (count + 1));
        return("Rate limit exceeded, please check rate limits at developer.aylien.com");
        } else if (status_code == 404) {
        return("404 - Not found");
        } else if (status_code == 403) {
        return("403 - Authentication Rejected");
        }
    if (++count == maxTries) {
      throw "An error occured in the Text Analysis API";
      return("Error");
      }
    } else {
      break;
    }
  }

  var response = JSON.parse(result);
  return response;
}

/**
 * Returns the analysis results from the Text API from a text or URL.
 */
function TextAPIResults(input, endpoint) {
  input = input.replace('"', '');
  Logger.log(input);
  Logger.log(endpoint);
  if (input.map) {
    return input.map(TextAPIResults);
  } else {
      if (input !== "") {
        var response = CallTextAPI({input: input, endpoint: endpoint});
        Logger.log(response);
        var results = [];
        if (endpoint == 'classify/iab-qag') {
          // classify iab
          Logger.log("found classify");
          if (response['categories'] !== 'undefined' && Object.keys(response['categories']).length > 0) {
            results.push(response['categories'][0]['label']);
            results.push(response['categories'][0]['confidence']);
            } else {
            results.push('No categories found');
          }
        } else if (endpoint == 'classify/iptc-subjectcode') {
            // classify iptc
            Logger.log("found classify");
            if (response['categories'] !== 'undefined' && Object.keys(response['categories']).length > 0) {
              results.push(response['categories'][0]['label']);
              results.push(response['categories'][0]['confidence']);
              } else {
              results.push('No categories found');
          }
        } else if (endpoint == 'sentiment') {
            // sentiment
            results.push(response['polarity']);
            results.push(response['polarity_confidence']);
        } else if (endpoint == 'extraction') {
            // extraction
            results.push(response['article']);
        } else if (endpoint == 'language') {
            // language
            results.push(response['lang']);
            results.push(response['confidence']);
        } else if (endpoint == 'summarize') {
            // summarize
            results.push(response['sentences'][0]);
        } else if (endpoint == 'concepts') {
            // concepts
            Logger.log(response['concepts']);
            if (response['concepts'] !== 'undefined' && Object.keys(response['concepts']).length > 0) {
              var concepts = response['concepts'];
              for (var i in concepts) {
                var concept = i.replace('http://dbpedia.org/resource/', '');
                var concept = concept.replace('_', ' ')
                results.push(concept);
                }
             } else {
                results.push('No concepts found');
                }
            }
            else if (endpoint == 'entities') {
            // entities
            if (response['entities'] !== 'undefined' && Object.keys(response['entities']).length > 0) {
              var entities = response['entities'];
              for (var i in entities) {
               if (i !== "text") {
                for (var listed_entity in entities[i]) {
                  results.push(entities[i][listed_entity]);
                }
             } else {
                results.push('No entities found');
                }
              }
            }
           }else {
              // if the endpoint value is spelled wrong
              results.push("No endpoint found, please make sure the endpoint is listed at docs.aylien.com/textapi/endpoints");
        };
      Logger.log("Results:");
      Logger.log(results);
      sleep( 1000 );
      return [results];
    } else if (input == "") {
        // if the input value is an empty cell
        return("");
        }
   }
}
Text Analysis API - Sign up
Let's Talk