Testing the Text Analysis API in Google Sheets

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

Sometimes you just need a quick and easy way of testing out an API and one way to do that is by leveraging Google Sheets. We’ve made it really easy to run sample queries against our API directly from a spreadsheet so that you can quickly evaluate and understand the type of results the Text Analysis API returns.

To get started you will need two things:

  • some sample text data you want to analyze, in a column on a Google Sheets spreadsheet
  • Text Analysis API credentials, (which you can get by signing up to the free plan here)

Once you have these two things, you can get started. It just takes three steps:

Step 1

First, in your spreadsheet, open up Tools > Script Editor. Google sheets open script editor

Step 2

Delete everything in the Code.gs tab and in its place paste the script on the right into the Script Editor. Now you can paste your credentials (your App Key and API ID) into the script (where it says "YOUR_APP_KEY" and "YOUR_APP_KEY" in the first lines), and hit save. Google sheets paste script

Step 3

Back in your spreadsheet, in an empty cell next to your sample data, copy this text in =TextAPIResults(A1, "sentiment") and hit Enter. Make sure that the quotation marks are formatted correcetly - if they change the colour of the endpoint name to green, you're good to go!

Now you can extend that cell to analyze as many cells as you want.

Textapi sheets

Try out all of the Text API's endpoints

You can currently use this script to test a number of the API's endpoints, which you can do by entering the endpoint's name in the place of sentiment in the previous example. Here are the endpoints you can try out:

  • Summarization (summarize)
  • Concept extraction (concepts)
  • Entity extraction (entities)
  • Classification (classify/iptc-subjectcode or classify/iab-qag, depending on the taxonomy you want to use)
  • Sentiment analysis (sentiment)
  • Language prediction (language)
  • Article extraction (extraction)

If you want to test out one of the Text API's endpoints but can't see on the list above, get in touch with us and we can add it for you.