This tutorial will show you how to make a dynamic chart title linked to a spreadsheet cell in Google Sheets.
Charts are great tools for visualizing data. They’re quite commonly used because they’re simple and easy to understand. Aside from the title section, almost all other parts of a chart in Google Sheets are highly customizable and dynamic.
This post will show how you can create a dynamic chart title in Google Sheets using values in a particular cell.
How to Connect a Chart Title to a Cell to Make It Dynamic
Chart titles are very important. They help give users a quick summary of the chart’s contents and make the process of understanding a lot easier. In Google Sheets, you can add chart titles using the title placeholder when creating the chart. However, this is a static solution that doesn’t change automatically if you were to update your data.
Having a chart title that changes dynamically can give your chart an upgrade in terms of functionality. This is especially handy when you have a chart that is frequently updated with new data. As the underlying data is updated, the information on the chart will likely change, and, as such, the title of your chart should reflect the new perspective.
Finding a native Google Sheets solution that can allow your chart title to change dynamically might be difficult or impossible. But you don’t have to go through the hassle because we’ve created a customized solution using Google Apps Script.
Sample Data
Before using the script, start by inserting a chart. The data in our sample contains mileage information of specific drivers for 2016.
And here’s the chart that visualizes the data:
When the chart updates with 2017 data, you can use an Apps Script solution to change the chart title based on the values of a cell by following these steps.
Step 1. Open the Apps Script Editor
To open the Apps Script editor, follow these steps:
1. Click on the Extensions menu.
2. Select Apps Script from the drop-down options.
Step 2. Create a Custom Apps Script Function
When the Apps Script editor opens, copy and paste the code below.
function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var range = e.range;
var activeColumn = range.getColumn();
var activeRow = range.getRow();
if(
activeColumn == 1 &&
activeRow == 1
) {
var newTitle = sheet.getRange(activeRow, activeColumn).getValue();
var charts = sheet.getCharts()[0];
var chart = charts.modify()
.setOption(‘title’, newTitle)
.build()
sheet.updateChart(chart)
}
}
After pasting the code into the editor, do the following:
1. Click on the Save command.
2. Click on Run.
When you click on the Run command, you will be prompted to grant some permissions before the script can work.
The script creates a function called onEdit(e). To understand how it works, here’s a breakdown of every line of code in the script:
- function onEdit(e) { }: This first line of code creates and names the function using the “function” keyword. The “function” keyword has its syntax as follows:
function <function-name> ( ) { //code to be executed };.
Using this syntax, the “function” keyword defines this Apps Script. In the <function-name> parameter, we have the “onEdit(e)”. While “onEdit(e)” names the function, onEdit(e) is a trigger.
Triggers are keywords that allow functions to run automatically when certain events happen. The “onEdit(e)” trigger runs the function when you edit the contents of a cell in the spreadsheet.
With this, you don’t have to call the function by writing a formula or clicking anything as the script will run when a cell is edited.
All subsequent lines of code are contained in the “//code to be executed” parameter inside the curly brackets.
- var sheet = SpreadsheetApp.getActiveSheet(): This creates a variable named sheet. The variable acts as a container that stores the currently active spreadsheet.
- var range = e.range: The range variable stores the cell(s) range(s) where the edit event has taken place.
- var activeColumn = range.getColumn(): getColumn() is a method that returns the index of the first column in a range. In this case, it will return the column number of the cell that has been edited.
- var activeRow = range.getRow(): The getRow() method, like the getColumn() method, returns the index of the first row in a range. Likewise, it will return the row number of the cell that has been edited in this script.
At this point, the rest of the code is contained in an IF statement expression. The IF expression performs the same function as your regular spreadsheet function. Its syntax goes as follows:
If (condition) { // block of code to execute if condition is true }
- activeColumn == 1 && activeRow == 1: This code forms the condition parameter to be tested. Essentially, it checks if the value in the activeColumn variable is equal to 1 and activeRow variable is equal to 1. The implication of this condition is that only edits that take place in cell A1 will directly affect the title of the chart in the spreadsheet.
In JavaScript, the language on which the Apps Script is based, the equal-to operator uses double equal-to signs “==” while one equal-to sign “=” is called an assignment operator.
The two ampersands “&&” is a logical operator that means AND. Consequently, if any of the conditions returns false, then the preceding block of code will not run.
The lines of code that will run if the condition returns true are explained below.
- var newTitle = sheet.getRange(activeRow, activeColumn).getValue(): The getRange method returns the range of a given cell. Its syntax getRange(row, column), takes row and column index and returns a specific range.
In this code, the activeRow and activeColumn variables are used to provide the row and column arguments in the getRange method. Since this block of code will only run when the activeRow and activeColumn variables are equal to one, the getRange method will return cell A1.
The getValue() method returns the contents of a given cell. In this code, it will return the contents in cell A1. This value is stored in the variable named newTitle.
- var charts = sheet.getCharts()[0]: The getCharts() method returns an array of all the charts in the active spreadsheet. sheet.getCharts()[0] returns the first chart in the active spreadsheet which is stored in the variable called charts.
- var chart = charts.modify(): The modify() method allows changes to be made to specific sections of the chart by creating an embedded chart builder that is used to edit a chart. With this line of code, you can choose any part of the chart to change.
- .setOption(‘title’, newTitle): While the previous code allows you to edit the chart, this method allows you to choose which part of the chart you want to modify and what kind of modification you want to implement. The setOption() method has two parameters. The first parameter references the part of the chart you want to edit, while the second parameter references the code containing the type of change you want to make. In this case, the title of the chart will be modified using the values from the newTitle variable.
- .build(): After all the changes are made, this method creates a new chart and applies all the modifications specified in the previous codes. All of this is stored in the chart variable.
- sheet.updateChart(chart): This code applies all the changes created in the chart variable to the selected chart in the active spreadsheet.
When you Run this code in the Apps Script editor, you will get an execution error message.
When you get this, there’s no need to worry as the script will still work perfectly. This happens every time the onEdit(e) trigger is used in a script. The reason for this error is because the edit event doesn’t exist yet, which results in the engine reading the range variable as undefined.
After you’ve pasted and saved this script in the editor, go back to your spreadsheet and refresh it.
Step 3. Execute the Script to Dynamically Change the Chart Title
From earlier, the sample data only contained 2016 information. When the 2017 data is added to the chart, you can change the chart title by simply updating the contents in cell A1.
This solution opens a lot of other possibilities. You can link the title cell to another cell or use data validation.
