Testing the Text Analysis API in Google Sheets

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

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

Watch Full Tutorial Video

Step 1

In your Google spreadsheet, open Tools and then click on Script Editor.

Step 2

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

Then from your ParallelDots 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: =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)
            for (var t = 0; t < list1.length; t++) {
              results.push(list1[t]['keyword'])
            }
          }

          /* 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'];
            for (var t = 0; t < list1.length; t++) {
              results.push(list1[t]['name'])
            }
          } 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 ("");
        }
      }
    }