Sentiment Analysis For Google Tables Using Apps Script

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:

Sentiment Analysis Google Tables

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:

Google Tables Sentiment Analyzer architecture

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!

Sentiment analysis tags in Google Tables

(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:

Apps Script Advanced Services

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:

Apps Script deploy as Web App

We'll be prompted to review permissions:

Google Tables Sentiment Analyzer authorization

followed by a review of the project scopes:

Apps Script 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:

Google Tables 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:

Google Tables Support Tracker Form

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!

Sentiment Analysis Google Tables

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

Google Tables: How I Use Google’s New Workflow Tool

Here’s something to get excited about: Google just launched a new workflow automation tool!

Google Tables is a tool for teams and businesses that combines the flexibility of a spreadsheet with the power of a database.

Best of all, it provides a more visual way to present information than a spreadsheet.

There are so many ways to use this tool, and I’ll show some of them later in this post. I could see teams and individuals using it to organize and track projects for both work and home life, similar to how many people already use tools like Trello, Asana or Airtable — and yes, there’s even a kanban view!

Google Tables frees your data from boring spreadsheets and puts it into dazzling Tables like this:

Google Tables Bug Tracker

Then you can group and link these tables into Workspaces to create process workflows:

Weekly Planner workspace with 4 tables
Weekly Planner workspace with 4 tables

Finally, sprinkle them with automation magic to save yourself time, using customizable, no-code Bots:

Google Tables no-code bot
No-code bot to move a record from the Weekly Planner Table to the Archive Table

What is Google Tables?

Spreadsheets excel (sorry!) at working with small tabular datasets. They’re perfect for analyzing your business data or keeping track of your finances.

But even if you love spreadsheets as much as I do, they’re not suitable for everything.

We’re all guilty of using spreadsheets to do things they’re not designed for.

For example, they’re not the best tool for managing workflows and automating multi-step processes. Spreadsheets set up like that often end up being complex and unwieldy to use.

Those workflows we track with spreadsheets — managing events, onboarding new hires, managing complex projects, etc. — are better suited to managing with this new Google Tables tool.

Google Tables is a product from Area 120, Google’s in-house incubator.

Google Tables Basics

Tables are the fundamental construct of the Google Tables product. They’re containers that hold structured data, i.e. ordered data recorded in rows.

Workspaces are collections of Tables grouped together. Tables can belong to multiple workspaces. When you open a workspace, you open all the Tables included in that workspace.

Columns in each Table are strongly-typed, meaning the data type you store in that column is predefined when you select the column type. This is different from a spreadsheet where you can store any type of data in any cell (unless you have data validation in place).

Views are saved versions of a Table with the data shown in a specific way. You can have multiple saved versions of a single Table, for example with different filters applied.

How much does Google Tables cost?

Google Tables is generally available to anyone with a Google account in the US at the moment.

Every country has different rules and norms around data privacy etc. so the team is starting in the US and will expand around the world in time. If you’re outside the US, you can express your interest via this form.

It’s currently a beta version, which means the product is still evolving and improving.

Free and paid tiers are available.

The paid tier costs $10/month and gives you additional storage, more tables and more bot (automation) actions. There’s a 3-month free trial of the paid tier, so you can try out all the features.

How I Use Google Tables

I’ve had access to the alpha version of Tables for the past 6 months. It’s quickly become an indispensable tool for the day-to-day running of my business.

I use it for two major workflows at the moment:

  1. My weekly planner
  2. An issue tracker for my courses

I also plan to move several other workflows from Google Sheets into Tables in the near future: my site content planning / SEO spreadsheet, my newsletter tracker, and my business process directory.

Workflow 1: Weekly Planner Kanban Board View

For years I used Trello’s kanban board (card) layout to manage my business week-to-week tasks.

Now I use Google Tables to do that.

I use it as a sort of rolling 7-day calendar, but I prefer it to a calendar because of the flexibility it affords.

Ultimately, it’s a combination of Trello (kanban board) + Tasks (To-Do list) + Calendar (events).

Google Tables Weekly Planner

Zooming in a little, here’s an example of my tasks for a given day:

Google Tables Weekly Planner

Each record is a row of data in a Table, presented in the kanban board view. I can drag records to move tasks to a different day. I can easily add new tasks or notes, and I can archive tasks when I complete them, using a bot.

Automation With Bots

Bots are automations that carry out a predefined set of instructions. In Tables, bots are created without writing any code.

In this weekly planner, I use them to move records from one Table to another.

For example, I like to archive tasks when I complete them.

I check an archive checkbox and then a bot moves the record into the Archive table.

Google Tables no-code bot
No-code bot to move a record from the Weekly Planner Table to the Archive Table

You can do lots of other things with bots too.

They can be triggered when something happens (e.g. a record gets added), on a set schedule (daily or weekly) or even by another bot.

They can perform actions like modifying records, adding records, sending emails or pinging webhooks (to send a chat notification to Slack for example).

Accessing Tables With Apps Script

And yes, whilst we’re on the subject of automation, Tables has an API and is also accessible programmatically via Apps Script!

(Here’s a Google Apps Script explainer if you haven’t used it before.)

For Apps Script, you must first enable the Tables API under the Advanced Service menu. Then you can access Tables by the Table ID, found after the /table/ part of the URL.

A basic Apps Script code to get the Table rows looks like this:

var tableName = "tables/XXXXXXXXXXXXXXXX";
var tableRows = Area120Tables.Tables.Rows.list(tableName).rows;

Workflow 2: Issue Tracker For My Online Courses

The other workflow I’ve setup in Tables is an issue tracker for my online courses.

Whenever someone contacts me with an issue on one of my courses, I log it in this Table, with tags to indicate which course, how urgent it is, where I’m up to etc.

It’s much easier to organize and see the issues compared to a plain data table in a spreadsheet. It requires a lot less effort to view the information.

Here’s an example of the issue tracker in a simple Google Sheet:

Google Sheet bug tracker

And here is that same tracker in a Google Tables workspace:

Google Tables Bug Tracker

It’s pre-filtered by course and the information is organized and emphasized with the use of colored tags.

It’s much, much easier to navigate and get a sense of the overall picture.

Using Forms To Submit Tickets

Google Tables includes forms to allow users to submit data. These are not the same as G Suite Google Forms, but rather a form builder specific to the Tables product.

I’ve created a Form for my course issue tracker Table.

And now that Google Tables has officially launched, I can include this Form in my online school so students are able to submit tickets directly.

Google Tables Form

FAQ About How To Use Google Tables

Can I turn my existing Google Sheets into Tables?

Yes! When creating new Tables, you can import data directly from existing Google Sheets.

Tables Import From Sheets

How is Tables different from Google Sheets?

The simplest way I can describe it is that Google Sheets is for your data and Google Tables is for your information.

Google Sheets does calculations, summarizes large datasets and creates charts and dashboards. Tables doesn’t do any of those things.

Instead, Google Tables makes it easy to store and organize information, and automate actions. Tables lets you quickly create workflow documents that are easier to use than spreadsheet equivalents.

Should I move to Google Tables from Trello or Airtable?

The Kanban board layout within Tables is similar to how Trello operates. The bots in Tables allow you to automate tasks in a similar way to Trello’s Butler tool.

Google Tables is similar to Airtable in many ways too. Like Airtable, Google Tables combines some of the best features of spreadsheets with databases, to create an ideal small business workflow and information tool.

Trello and Airtable are more mature products so they do have deeper feature sets, but Tables is new and is bound to develop quickly. Google has deprecated products in the past but I think this is a great tool with enormous potential and I hope Google Tables becomes a major player in this space.

What Else Can You Do With Google Tables?

Google Tables is designed for businesses, so anytime you’re using spreadsheets for tracking a process, ask if that’s something better suited to Tables.

The Tables team have created a huge number of templates to get you started, everything from a Product Roadmap to an Employee Directory:

Google Tables Templates

I plan to share more experiences, tips and use cases for Tables in the coming months.

I’m really excited by this product and see so many opportunities in my own business to improve my existing processes.

Resources