Introduction
Google Sheets, much like Microsoft Excel, incorporate many different chart types, which can help the user to visualize his/her data. In this post, we will learn how to create and customize a simple column chart in Google Sheets. There will be the “manual way”, the “coding way” – using Google Apps Script – as well a bonus tip for selecting chart colors.
The manual way to create a column chart
To create a column chart manually just follow these 2 simple steps:
Step 1: First, select the range containing the data, e.g. A1:C4.
Step 2: Then, go to the menu Insert and click the Chart option.
The generated (default) chart would look like this:
To customize the chart, right-click upon the chart, and on the context menu that pop-ups, select the appropriate option. For example, to customize the chart title, you have to select the Chart & axis titles option and then the Chart title option.
The Chart editor form appears, where you can customize several options. Here, for example, we have changed the text, font, font size, format and the text color of the title.
Similarly, you can customize every available chart option (e.g. series, legend, axes, and gridlines).
The coding way to create a column chart
The manual way can be quite daunting and time-consuming, especially if you have to customize several chart options. Thanks to Apps Script, though, the same process can be automated, allowing you to create beautiful charts in a few seconds. The code below contains probably the most common options that someone will need to customize when creating a column chart. It has the following options:
- Generic chart options: chart position and size.
- Chart title options: text and text style.
- Legend options: position and text style.
- X-axis options: title and text style.
- Y-axis options: title, gridlines and text style.
- Series options: color, data labels, error bars, and text style.
- Trendline options: type and line style.
Most of these options are set using mainly the setOption method of the chart object. The various options are passed using CSS syntax. Additional options can be found on this page.
// Start of the GAS code.
function createColumnChart()
{
/*
------------------------------------------------------------------------------
The function creates a custom column chart in the active sheet using the data
that exist in the range that is specified in the dataRange variable.
Written By: Christos Samaras
Date: 30/10/2018
Last Updated: 07/11/2018
E-mail: [email protected]
Site: https://myengineeringworld.net
------------------------------------------------------------------------------
*/
// Range to get the data.
var dataRange = 'A1:C4';
// Variables for customizing colors.
// Use this tool to generate "good" colors:
// http://paletton.com
var color1 = '#EE295A'; // Fill and data label color for the first series.
var color2 = '#FFD42C'; // Fill and data label color for the second series.
var color3 = '#8D7CEE'; // Trendline and axis titles color.
var color4 = '#A6F870'; // Gridlines, axis labels and chart title color.
// Text for titles (chart and both axes).
var chartTitle = 'Vehicles Comparisons';
var xAxisTitle = 'Vehicle Category';
var yAxisTitle = 'Speed & Power';
// Get the active sheet.
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
// Create a chart.
var chart = sheet.newChart()
.asColumnChart()
.addRange(spreadsheet.getRange(dataRange))
// Set the generic options.
.setNumHeaders(1)
.setBackgroundColor('#333333')
// .setOption('is3D', true) // For a 3D chart.
// .setOption('height', 400) // Custom height.
// .setOption('width', 600) // Custom width.
// Set the chart title options.
.setOption('title', chartTitle)
.setOption('titleTextStyle', {
color: color4,
alignment: 'center',
fontName: 'Verdana',
fontSize: 24,
bold: true
})
// Set the legend options.
.setOption('legend', {
position: 'top',
alignment: 'center',
textStyle: {
color: color3,
fontName: 'Verdana',
fontSize: 12,
bold: true
}
})
// Set the X-axis options.
.setOption('hAxis', {
title: xAxisTitle,
titleTextStyle: {
color: color3,
alignment: 'center',
fontName: 'Verdana',
fontSize: 16,
bold: true
},
textStyle: {
color: color4,
fontName: 'Verdana',
fontSize: 14,
bold: true
}
})
// Set the Y-axis options.
.setOption('vAxes', {
0: {
title: yAxisTitle,
format: 'none',
titleTextStyle: {
color: color3,
alignment: 'center',
fontName: 'Verdana',
fontSize: 16,
bold: true
},
textStyle: {
color: color4,
fontName: 'Verdana',
fontSize: 14,
bold: true
},
gridlines: {
count: -1,
color: color4,
width: 5
}
}
})
// Set the series options.
.setOption("series", {
0: {
labelInLegend: 'Speed [km/h]',
color: color1,
hasAnnotations: true,
dataLabel: 'value',
dataLabelPlacement: 'outsideEnd',
errorBars: {
magnitude: 20,
errorType: "percent"
},
dataLabel: "value",
textStyle: {
color: color1,
fontSize: 12,
fontName: 'Verdana',
bold: true
}
},
1: {
labelInLegend: 'Power [kW]',
color: color2,
hasAnnotations: true,
dataLabel: 'value',
dataLabelPlacement: 'outsideEnd',
errorBars: {
magnitude: 20,
errorType: "percent"
},
dataLabel: "value",
textStyle: {
color: color2,
fontSize: 12,
fontName: 'Verdana',
bold: true
}
}
})
// Set the trendline options (here only for the first series).
.setOption('trendlines', {
0: {
color: color3,
type: 'linear',
lineWidth: 4,
opacity: 0.8,
labelInLegend: 'Trendline',
visibleInLegend: true
}
})
// Position: row and column of top left corner (cell E2 here).
// Optional: offset X and Y in pixels.
.setPosition(2, 5, 0, 0)
// Finally, build the chart.
.build();
sheet.insertChart(chart);
}
// End of the GAS code.
If you run the above script on the same dataset (A1:C4), the final chart will look like this:
How to select “good” chart colors
If you saw the chart that was generated from the previous Apps Script code you would probably notice that the color combination is quite “pleasing to the eye”. This set of colors was selected using this tool. If you play with it for a few minutes, you will discover that it has an extensive set of options for generating color palettes and harmonies. If you don’t know where to start, simply click the randomize button at the top of the screen to start your color scheme.
Unlike Excel, the chart colors in Google Sheets can be customized more extensively. Therefore, you can create more impressive charts if you are willing to experiment a little bit with the color combinations.
Page last updated: 20/02/2020
