Adding Charts to Google Workspace Add-on Sidebar Apps with Apps Script

Creating dynamic charts in the sidebar of your Google Workspace Add-on can be an effective approach to getting across a lot of meaning in a somewhat confined space.

In this tutorial, we will use Google’s Chart API to generate a live chart, first from some static data and then live from an external data source like a Google Sheet. All with the help of a little Google Apps Script magic.

If you want to take things further still and learn how to create a dynamic chart dialogue overlay along with learning other approaches to displaying charts and handling errors, you can find this in the bonus material to my much larger and more details course Create and Publish Google Workspace Add-ons with Google Apps Script: Master Class. If you are already a course member, head over there now, ya magnificent legend you!

Let’s get into it!

Prerequisites

You will need to create your Google Apps Script project, set it up and connect it to a Google Cloud project. You can follow the Setup chapter from this tutorial to do this.

Some knowledge of Card Service and Google Workspace Add-ons is recommended.

You can also grab a copy of the spreadsheet data here:

Chart Data – Google Sheet

 

The Video

Build a Static Chart

To get started it’s probably a good idea to prototype a static chart so we know what we are doing.

Setup and onhomepage(e)

In our demonstration tutorial script, we will generate our card in a separate function. This is pretty good practice so that we can use the same function to rebuild the card should we need to in the future.

Your JSON manifest file should contain this onhomepage function in your common property.

I have my own testing project and my manifest file looks a little like this:

Don’t forget to add the scopes to your project too. The main one for this tutorial is:

"https://www.googleapis.com/auth/spreadsheets",

Add any other that pair with the location that you want to open your sidebar.

Card build – createChartCard(e)

Line 10 – This builds the new card that we will display our chart in. For this example, we will keep it simple and just add our chart to the card. Here, we generate our card with the CardBuilder.newCardBuilder() method.

Line 11- We will then set a name for the card, should we need to call it again from a different card.

Line 13-16 – Next, we generate the header card. This contains a title and a chart image icon.

Line 19 – We will add a section outside our initial builder variable just in case we want to dynamically add other sections at a later date.

Line 21 – We build the addon. Ensure that both the build variable, in our case ‘builder’ calls the build() method to generate the card JSON on the return statement.

Section Build – buildChartSection()

In this chart section, we are going to display our chart image, set our header to chart and set some text to the title of the chart.

Line 9 – First, we call the getChart() function. This will build our chart URL at the same time as we build the card. The function will return an object containing a ‘title’ and a data ‘url’.

Lines 11-13 – Here we call the Google Apps Script Card Service newImage() constructor class to set both our alt text and the chart image.

Line 15 – We create the section card with the newCardSection() class.

Line 16 – This builds the header name.

Line 17 – An inline text widget is added to this line with the newTextParagraph() method.

Line 18 – Adds the chart image as a widget to the section.

Line 20 – Finally, we return the section build back to the card build.

Generating the Chart Data – getChart()

This is where the magic happens.

In this example, we will use Google’s built-in Charts API.

Let’s start off with a static example so we can see how the chart builder works.

We will build out a simple stacked line chart for this example. However, keep in mind that there are a number of chart options that can be found in the API.

Line 9 – We first set the title for the chart.

Building the table data

Lines 11-22  – Next, we call the Chart API calling the newDataTable() builder class. This creates the data for the chart that we want to build. It allows us to set column titles and then add our row data as an array.

As you can imagine. This builder is not normally designed for a static build like we have here, but this example does make it clear how we can generate our data.

Once we have added in our columns and rows we build our chart. This validates and generates a table object that we can insert into our selected chart.

In our example, we add 3 columns to our chart:

1. Month
2. Direct (Sales)
3. Affiliate (Sales)

We will need to ensure that each row that we add has an equal number of values to match our 3 columns. We do this by inserting an abbreviated month and then sales figures into an array.

It is important to remember that charts don’t normally contain a huge amount of data points. Rather they summarise the data points in a visually understandable way. This means that you should carry out any statistical calculations to generate your data table.  There won’t be an intermediary point to run analysis between table generation and the chart build.

Building the chart

Lines 24-30 – Next, we build our chart. For us, this will be a line or stacked area chart. So we call the newAreaChart() builder class to help us generate this. Next, we need to fill out some properties to complete the build:

• setDataTable: this takes the data table that we generated above.
  Alternatively, you can use the setDataSource method to extract the table data from something like a Google Sheet instead of building the table.
• setStacked: If this optional method is called, then the data will be stacked.
• setLegendPosition: Here you will need to use the Charts.Position enumerator to set where you want to position your legend, if at all. For our example, we have set the legend to the bottom. You might find due to the smaller size of the sidebar that you may even remove the legend from the chart.
• setDimension: This sets the pixel size of the chart that you will build. One of the cool things about the Google Charts API is that it will increase or decrease details for the chart depending on the chart’s set dimensions. Take the two dimensions below for example:
  
  You will need to play around with these dimensions to get the chart looking how you would like.
• setTitle – Generates the title for the chart. This may be another thing you may wish to leave to a text widget in your add-on.
• build – Builds the area chart png image blob.

Converting a PNG image into a (data) URL

We won’t be able to directly embed the PNG blob into the add-on. Rather we will need to create an HTML data URL. These are special types of dynamically generated URLs that generate live data. They take a ‘data:’ prefix, a media type and a base64 encoded data.

data:[<mediatype>][;base64],<data>

Line 33 – We can convert our chart png image blob to base 64 by using the built-in Google Apps Script Utilities API base64Encode() method. This method requires the blob data to be converted to bytes. We can do this by using the getAs(‘image/png’) method on the ‘chart’ png image blob and then calling the getBytes() method on that.

Line 34 – Now that we have our image data encoded into base 64, we create our image URL string:

"data:image/png;base64," + encodeURI(imageData)

Notice here that we need to encode our image data further into a URI. This is done with the JavaScript encodeURI function.

Lines 38-41 – Finally, we return an object containing our image title and URL. This will feed back into our section builder.

There may be some extreme outer limits to the size of the data URL built here. I have yet to come across them, though.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Building a Dynamic Chart in a Google Workspace Add-on

Now that we have the basics let’s convert our chart data to something more dynamic that will show updated data whenever the card is built or refreshed.

For our example, we will use some Google Sheets data, but you can retrieve data from any source. We will opt to use the Charts API to build our data table for us again.

Both onHomepage and createChartCard functions will remain the same.

Updating the buildChartSection() function

We are going to make a few modifications to our section’s building function now. Our primary change is to add a refresh button so that our users can update the chart with the latest data.

Line 31 – Look down the bottom of the function at our section build, you can see that we have added an extra widget. This is where we have our refresh button.

Our sidebar app will now look a little like this:

Lines 15-22 – Here we create a decorated text widget that will contain a text item and a clickable refresh icon. We will first set some text to identify the refresh button explicitly.

Then we will set the button with the Card Service newImageButton class. This class allows us to set the alternative text and the icon’s URL.

Most importantly for us, this class allows us to create a setOnClickAction. Let’s make this more clear by putting the action button above it.

Lines 12-13 – The refreshAction variable creates a new action and in our case, we want to assign a callback function that will be executed when the button is clicked. Let’s create that function now.

Create the refresh card – refreshChartCard()

This is a simple function. All we want to do is refresh the existing card. We can do this by rebuilding the card and then set the build of the card with the newNavigation().updateCard() method.

One thing to note here is that if you have input fields in your card, then you will want to update those values when you rebuild the card.

Retrieving the Google Sheet Data – getSheetData()

Let’s create a function to gather our table data set for our chart. We will need to gather three things:

1. Chart title.
2. Column header info.
3. Array of values.

It is at this stage that you may want to consider either extracting the raw data from your data source and then carrying out your calculations to generate the table data that you need. For example, you may need to get the aggregate of all the sales each month or combine all subsets of sales into our two ‘Affiliate’ and ‘Direct’ categories.

Alternatively, you may wish to complete your calculations within your sheet on a separate tab first or a calculated table/document of your preferred database.

Feel free to use the link to the current Google Sheet. I have shared it with the public with view-only access so you can extract the data. However, if you want to see some live data changes you will need to build your own dataset.

Here is what our Google Sheets data looks like:

Lines 7-10 – First, we gather our source data location information. You may wish to abstract this out to a global object in a variables file so you don’t have to hunt for it when you need to make changes. For the purpose of this tutorial, it makes it easier to understand things if everything is kept in one place.

Line 12 – Calls the spreadsheet using the ‘openById()’ method of the SpreadsheetApp class.

Line 13 – Next,  we gather the sheet by the sheet’s name.

Line  14 – Here, the title is extracted from the first row of the sheet tab.

Line  15 – We then extract a 2D array of all the column headers and rows that we want to display.

Lines 18-25 – You might recall from the first example, that every time we set a column with the Google Charts API, we need to set the data type for that column:

.addColumn(Charts.ColumnType.STRING, "Month")

All of our column titles are in the first row (zeroeth) of our values 2D array. We will iterate over this array collecting the column title and the data type for that column using a JavaScript map function and return that to the columnInfo variable.

We will also need to determine the type of data in each column. Here it can be either ‘date’, ‘number’ or ‘string’ data. We will abstract this workflow out a component function that we will call validateType(). This function will take the first data row as a sample of items in each row to check the type and return one of the 3 options (More on this in a bit).

Line 27 – Next, we with remove the header row from our values to get a 2D array of just our data for each column and store it in our valuesArray variable.

Line 29 – Lastly, we will return an object containing the title, column info and data array for our chart back to our getChart() function.

The type validation component – validateType()

This function takes an individual sample from each item in the first row of the data set and assigns it one of the three Charts Column Type enumerators; DATE, NUMBER or STRING.

Lines 9 – First we convert the current ‘value’ parameter to a JavaScript object type using the toString().call() method to allow us to detect the object’s class.

Lines 11-19 – Next, we compare the results of the object call with either the Date or Number string object and set the Charts enumerator accordingly.

If there is no match with the date or number object, then we will simply identify the value as a string.

Building the chart dynamically – getChart()

Now we need to upgrade our getChart() function to make it more dynamic.

Let’s take a look at the code.

So what’s changing? Better, yet, what’s staying the same?

Everything from the new area chart down hasn’t changed. It is still the same process thankfully.

All that we are really doing is resetting our initial variables to take a dynamic input and creating two iterators for our addColumn and addRow methods.

This will make it somewhat reusable across other data sets with the same chart type.

Line 9 – First, we retrieve the sheet data from our newly created getSheetData() function. This will provide us with the object:

Lines 11-13 – Next, we assign the object properties to arrays for clarity. You may decide to skip this step and add the object paths directly.

Line 15 – Now, we call the Google Charts API a new data table, but this time we don’t directly add in the class’ methods.

Lines 17-20 – The  JavaScript forEach method is used to iterate over each of the colData items creating an addColumn() method for each one.

Lines 22-25 – Similarly, we will iterate over the values 2D array adding each child array with the addRow() method.

Line 28 – Finally, we build the data table.

 

And you are done!

If you have made your own copy of the Google Sheet, go ahead and change a value or add a new row. Then refresh the card.

Go ahead and play with the Charts API and then try some other chart formats and see your results.

Taking Charts Further in Cards Service

Simple charts may look okay in the sidebar but it would be really cool if we could quickly display a larger version of the chart dynamically in an overlay dialogue box just by clicking on the chart.

Something like this:

You can find out how to do this along with learning about an alternative API called Quick Charts all as part of the bonus material in the Create and Publish Google Workspace Add-ons with Google Apps Script: Master Class course.

Check it out now!

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.