Our Google Sheet Add-On is back

Testing the Text Analysis API in Google Sheets

Now, there are two ways to use our APIs in Google Sheets

  1. Google Sheet Add-On Script
  2. Google Sheet Add-On

Using Our Google Sheet Add-On Script

Komprehend Google Sheet add-on script provides a fast and easy way to analyze open-ended textual data from the comfort of your spreadsheets.

To use our script, you’ll need to create a Komprehend account. You can signup for an account here.

Watch Full Tutorial Video

Step 1

In your Google spreadsheet, open Extensions and then click on the App Script.

Step 2

On the App Script page, delete everything that is on the Code.gs section and then copy/paste your script there.

Then from your Komprehend dashboard, copy your API Key and paste it where it says “YOUR API_KEY” in the second line and then hit save.

Step 3

Now go back to the spreadsheet where you have your data and in an empty cell next to the first-row copy/paste this text(given that the input text is in cell A2): =TextAPIAnalysis(A2, "sentiment") and hit Enter. Make sure to write the correct cell number and the Quotation marks are formed correctly - the endpoint turns green when all the details are correct. (The endpoint may vary according to the API you want to use) Now, you can extend that cell to analyze the remaining rows of data.

Currently, you can use this script to test the following endpoints:
  • Sentiment (sentiment)
  • Emotion (emotion)
  • Keywords (keywords)
  • Intent (intent)
  • Abuse (abuse)
  • Named Entity Recognition (ner)

If you have any questions or queries, reach out to us at [email protected].

Script to copy:

    function getAPIKey() {
      return "Your API_Key";
    }

    function getAPIUrl(endpoint) {
      return "https://apis.paralleldots.com/v3/" + 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 find_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 CallTextAnalysisAPI(params) {
      var payload = {};
      var maxTries = 3;

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

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

      if (type == "url") {
        payload["url"] = params["input"]
      } else {
        payload["text"] = params["input"]
      }
      payload["api_key"] = getAPIKey();
      var result;
      var count = 0;

      while (true) {
        var api_url = getAPIUrl(params["endpoint"]);
        var options = {
          "method": "post",
          "payload": payload,
          "muteHttpExceptions": true,
          "headers": {
            "Source": "GoogleSheets",
            "code": "gsScript"
          },
        };

        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");
          } else if (status_code == 404) {
            return ("404 - Not found");
          } else if (status_code == 403) {
            return ("Authentication Rejected");
          }
          if (++count == maxTries) {
            return ("Error");
          }
        } else {
          break;
        }
      }

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

    /* main function */

    function TextAPIAnalysis(input, endpoint) {
      input = input.replace('"', '');

      if (input.map) {
        return input.map(TextAPIAnalysis);
      } else {
        if (input !== "") {
          var response = CallTextAnalysisAPI({ input: input, endpoint: endpoint });
          var results = [];

          /* emotion */
          if (endpoint == 'emotion') {
            results.push(response['emotion'])
          }

          /* sentiment */
          else if (endpoint == 'sentiment') {
            results.push(response['sentiment'])
          }

          /* keywords */
          else if (endpoint == 'keywords') {

            var list1 = response['keyword']
            Logger.log("list =", list1.length)
            if (list1.length != null){
              for (var t = 0; t < list1.length; t++) {
                results.push(list1[t]['keyword'])
              }
            }
            else{
              results.push("")
            }
          }

          /* intent */
          else if (endpoint == 'intent') {
            results.push(response['intent']);
          }

          /* abuse */
          else if (endpoint == 'abuse') {

            if (Math.max(response['abusive'], response['hate_speech'], response['neither']) == response['abusive'] ||
              Math.max(response['abusive'], response['hate_speech'], response['neither']) == response['hate_speech']) {
              results.push('Offensive')
            } else {
              results.push('Non Offensive');
            }
          }

          /*  ner */
          else if (endpoint == 'ner') {
            var list1 = response['entities'];
            if (list1.length != 0){
              for (var t = 0; t < list1.length; t++) {
                results.push(list1[t]['name'])
              }
            } else {
              results.push("")
            }
          } else {
            /* if the endpoint value is wrong */
            results.push("No endpoint found");
          };
          Logger.log("Results:", results);
          sleep(1000);
          return [results];
        } else if (input == "") {
          /* if the input value is an empty cell */
          return ("");
        }
      }
    }