copy

Tuesday, 30 October 2018



Last updated: 07/11/2018, 2 min read (without the code)

Creating & Customizing Column Charts In Google Sheets



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 manually just follow these 2 simple steps:

Step 1: First, select the range containing the data, e.g. A1:C4.

Range Selection

Step 2: Then, go to the menu Insert and click the Chart option.

Insert Chart

The generated chart would look like this:

Default Column Chart

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.

Customizing Chart Title

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.

Chart Editor

In a similar way, you can customize every available chart option (e.g. series, legend, axes and gridlines).



The coding way



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 a CSS syntax. Additional options can be found on this page.

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:        xristos.samaras@gmail.com
  Site:          https://www.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);
};

If you run the above script on the same dataset (A1:C4), the final chart will look like this:

Column Chart With Code



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”.  The particular 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.

Palleton

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.

Did you like this post? If yes, then share it with your friends. Thank you!



Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info