Sentiment Analysis For Google Tables Using Apps Script

2024 Update: Table’s features and capabilities are now integrated into AppSheet. Read more here.

In this post we’ll use Google Cloud’s Natural Language API to do sentiment analysis on tickets submitted to a Google Tables Support Issue Tracker.

We’ll use Google Tables as the platform for our Support Tracker and Apps Script to connect to the Cloud Natural Language API.

Here’s a GIF showing a portion of the Issue tracker to illustrate what happens when an issue ticket is submitted:

The description (which someone fills out when they submit a support ticket) is sent to the Natural Language API for sentiment analysis, to determine if it’s positive (i.e. good) or negative (i.e. bad) feedback.

This information is returned to our Google Tables table and displayed as a tag alongside each row, so that we can group tickets by sentiment.

For example, we may want to prioritize tickets that are either extremely positive or extremely negative, as these are probably the most important items to double down on or fix!

Sentiment Analysis Google Tables Overview

Here’s the architecture of the system:

In words:

• User submits support ticket
• New row of data added to our table
• Bot is triggered and sends data to our Apps Script webhook
• The Apps Script receives data and parses it
• Apps Script sends data to Natural Language API
• And receives the sentiment scores back from API
• Apps Script parses score to create sentiment tag
• And updates the original row of data in our table

This all happens in a under a second, so it feels almost simultaneous.

Inspiration for this idea came from the excellent sentiment analysis in Google Sheets post originally published by Alicia Williams.

Note: Since I’ve set this up using a webhook triggered by each new row, the API is called once for every new row. If we were building a high volume ticket system, we’d want to consider a different set up where we send the data through in batches and have the Apps Script running on a timer trigger instead.

Sentiment Analysis Google Tables Set Up

For this example, I’m starting with the default “New ? Support Ticket Queue” template from the Google Tables team.

1. Open Google Tables, login and select Templates > New ? Support Ticket Queue

2. Add three new columns: 1) Sentiment Score (number), 2) Sentiment Magnitude (number) and 3) Sentiment Tag (Tags).

3. Edit the new Sentiment Tag column and add the following tags: Super happy!, Happy, Satisfied, No opinion, Frustrated, Angry, Super angry!

(You can edit these categories to whatever you want, but you’ll need them to match the tags in your Apps Script.)

Sentiment Analysis Apps Script Set Up

4. Create a blank Apps Script file in Drive or through the Apps Script dashboard

5. Clear out the boiler plate code and add the following code to declare the two global variables we need for this project (we’ll fill them in soon):

/**
 * global variables
 */
const API_KEY = ''; // <-- enter google cloud project API key
const TABLE_NAME = ''; // <-- enter google tables table ID

Since we just created our Table, let’s copy in the Table ID.

6. We find our table’s ID, by looking at the URL and copying the string right after /table/.

Table URLs can take the following form:

https://tables.area120.google.com/u/0/workspace/abcdefghijklmnop/table/TABLE_ID
https://tables.area120.google.com/u/0/table/TABLE_ID
https://tables.area120.google.com/u/0/table/TABLE_ID/view/abcedfghijk

Look for the string represented by the TABLE_ID in these fictional examples.

7. Paste this ID string into our Apps Script project, between the quotes in the line where we declare the variable TABLE_NAME:

const TABLE_NAME = ''; // <-- enter google tables table ID

Sentiment Analysis Google Cloud Set Up

This is probably the most difficult part of this whole project! 😉

For this to work, we need a Google Cloud account with billing set up. Don’t worry though, the Natural Language API is free for the first 5k “units” we use (each unit is worth 1000 characters). This is way more than we need to set this project up and test it out.

The full details of how to set up Google Cloud Natural Language API can be found in the documentation.

The steps to take are:

8. If you don’t already have a Cloud account, register for a Google Cloud account and set up billing.

9. Create a project in the Google Cloud account to use for this project.

10. Enable the Cloud Natural Language API, by clicking the link half-way down this page.

11. Create credentials for the Cloud Natural Language API. From the Cloud console choose the project we created in step 5 and navigate to APIs & Services > Credentials

Generate a new API Key through +CREATE CREDENTIALS > API key

12. Restrict the API key we generated to the Cloud Natural Language API

13. Copy the API key and paste it into our Apps Script file, between the quotes in the line where we declare the variable API_KEY (see code above):

const API_KEY = ''; // <-- enter google cloud project API key

Sentiment Analysis Google Tables Code

Staying in the Apps Script file, let’s add the webhook listener and main control function code for our program.

We use a special function called doPost(e) { } so that we can (eventually) publish our script as a web app. The doPost function sits there listening for a ping from our Google Tables bot (which we’ll set up later).

When a new row is added to our Google Table by a Form submission, the bot is triggered and sends the data we need through to our webhook.

This doPost function receives that data, parses it and sends it to the Natural Language API for sentiment analysis.

The returned sentiment data is parsed and sent back to our table to update the new row, using the patch method of the Area120Tables service.

14. Add the following doPost code:

/**
 * doPost webhook to catch data from Google Tables
 */
function doPost(e) {
  
  if (typeof e !== 'undefined') {

    // parse data
    const data = JSON.parse(e.postData.contents);

    // get the id and description
    const rowId = data.id
    const description = data.description;

    // analyze sentiment
    const sentiment = analyzeFeedback(description); // [nlScore,nlMagnitude,emotion]
    
    // combine arrays
    const sentimentArray = [rowId,description].concat(sentiment);

    // send score back to Google Tables
    const rowName = 'tables/' + TABLE_NAME + '/rows/' + rowId;
    const sentimentValues = {
      'Sentiment Score': sentiment[0],
      'Sentiment Magnitude': sentiment[1],
      'Sentiment Tag': sentiment[2]
    };
    Area120Tables.Tables.Rows.patch({values: sentimentValues}, rowName);

    return null;
  }
}

In the code above, we call a function called analyzeFeedback, so we had better declare it.

This function handles the logic around the NL scores and how to interpret them as human readable tags. Feel free to play around with the boundaries. The sentiment score is bounded between -1 (max negative) and 1 (max positive), but the magnitude only has a lower bound of 0, so can be any positive number.

For more about the interpretation of the sentiment scoring, have a read of this page in the documentation.

15. Add the following code to our Apps Script file to analyze the sentiment scores:

/**
 * Get each new row of form data and retrieve the sentiment 
 * scores from the NL API for text in the feedback column.
 */
function analyzeFeedback(description) {

  if (description !== '') {
      
      // call the NL API
      const nlData = retrieveSentiment(description);
      nlMagnitude = nlData.documentSentiment.magnitude ? nlData.documentSentiment.magnitude : 0; // set to 0 if nothing returned by api
      nlScore = nlData.documentSentiment.score ? nlData.documentSentiment.score : 0; // set to 0 if nothing returned by api
      //console.log(nlMagnitude);
      //console.log(nlScore);
    }
    else {
      
      // set to zero if the description is blank
      nlMagnitude = 0;
      nlScore = 0;

    }

    // turn sentiment numbers into tags
    let emotion = '';
    
    // happy
    if (nlScore > 0.5) { 
      
      if (nlMagnitude > 2) { emotion = 'Super happy!'; } // higher magnitude gets higher emotion tag
      else { emotion = 'Happy'; }

    }
    
    // satisfied
    else if (nlScore > 0) {  emotion = 'Satisfied'; }

    // frustrated
    else if (nlScore < 0 && nlScore >= -0.5) { emotion = 'Frustrated'; }

    // angry
    else if (nlScore < -0.5) { 
      
      if (nlMagnitude > 2) { emotion = 'Super angry!'; } // higher magnitude gets higher emotion tag
      else { emotion = 'Angry'; }
    
    }

    // if score is 0
    else { emotion = 'No opinion' }

    return [nlScore,nlMagnitude,emotion];
}

Finally we need to declare the function called retrieveSentiment to actually call the API.

16. Add the code to call the NL API:

/**
 * Calls Google Cloud Natural Language API with string from Tables
 */
function retrieveSentiment(description) {
  
  //console.log(description);

  const apiEndpoint = 'https://language.googleapis.com/v1/documents:analyzeSentiment?key=' + API_KEY;
  
  // Create our json request, w/ text, language, type & encoding
  const nlData = {
    document: {
      language: 'en-us',
      type: 'PLAIN_TEXT',
      content: description
    },
    encodingType: 'UTF8'
  };
  
  //  Package all of the options and the data together for the call
  const nlOptions = {
    method : 'post',
    contentType: 'application/json',  
    payload : JSON.stringify(nlData)
  };
  
  //  Try fetching the natural language api
  try {
    
    // return the parsed JSON data if successful
    const response = UrlFetchApp.fetch(apiEndpoint, nlOptions);
    return JSON.parse(response);
    
  } catch(e) {
    
    // log the error message and return null if not successful
    console.log("Error fetching the Natural Language API: " + e);
    return null;
  } 
}

Press save!

The full code is available here on GitHub.

Since we’re using the Area 120 Tables Apps Script service, we need to enable it for this project.

17. Go to Resources > Advanced Google services… and switch on Area120 Tables API:

Publish As Web App

18. Publish this file as a web app via the menu: Publish > Deploy as a web app…

19. Set the access to Anyone, even anonymous, as shown in this image:

We’ll be prompted to review permissions:

followed by a review of the project scopes:

Click Allow.

This is a one-time step the first time we publish to the web or run our script (unless we add additional services in the future).

20. Copy the URL of the web app so we can paste that into our Tables bot, which we’ll create next!

Sentiment Analysis Tables Bot Set Up

The final piece of the puzzle is the bot in Google Tables.

When the issue tracker form is submitted it creates a new row of data in our table, which triggers the bot. The bot sends the data to the webhook (i.e. the code above) that handles the rest.

21. Create a new bot with the following specification:

Trigger: row added
Action: Send to webhook
Webhook URL: Our Apps Script web app URL from step 20
Webhook format: POST with JSON
Request parameters:
id : [[record_id]]
description : {{description}}

Visually, this is the bot:

The red arrow indicates where we paste the Apps Script web app URL.

Test The Sentiment Analysis Google Tables Tool

Finally, we’re ready to submit the form.

22. From the Google Table, click on the Support Ticket Form to open it in a new tab:

23. Submit it with a strong positive or negative sentiment in the description field (which is the one we send to the Natural Language API) to test out the Natural Language scores.

You’ll see the row of data arrive when we submit the form and then, a few moments later, the sentiment analysis columns get automatically populated too!

That’s it! Let me know how you get on in the comments.