Build a Charts Dashboard with Google Sheets and HTML Service

The school has a Google Form where teacher enter the performance grades of their students. This form data is stored in a Google Spreadsheet and they are using Google Charts with the Google Visualization API to convert these tables of rows into visual charts that can be easily visualized.

The principal needs a public dashboard (in other words, a web page) where the charts can be displayed to external users without having to give them access to the Google Spreadsheet. This can be easily done with the HTML Service of Google Script and the Google Visualization API.

Here’s a simple example that fetches data from a Google Spreadsheet and display the corresponding chart on a web page using the HTML Service. The Google script needs to be published as a Web App and the access should be set to anyone (including anonymous) or you can also limit it to users of your own Google Apps Domain.


function doGet(e) {
  return HtmlService.createTemplateFromFile('index')
    .setTitle('Google Spreadsheet Chart')

function getSpreadsheetData() {
    sheet = SpreadsheetApp.openById(ssID).getSheets()[0],
    data = sheet.getDataRange().getValues();

  return data;

Next create an html file inside the script editor and save it as index.html

<!DOCTYPE html>
    <script src=""></script>

    <div id="main"></div>

      google.load('visualization', '1', {
        packages: ['corechart', 'bar'],


      function getSpreadsheetData() {;

      function drawChart(rows) {
        var options = {
          title: 'Population (in Millions)',
          legend: 'none',
          chartArea: {
            width: '60%',
          vAxis: {
            textStyle: {
              fontFamily: 'Arial',
              fontSize: 12,

        var data = google.visualization.arrayToDataTable(rows, false),
          chart = new google.visualization.BarChart(document.getElementById('main'));

        chart.draw(data, options);

The above example fetches the rows of a single sheet but if your dashboard is complex and requires you to fetch data from multiple sheets in a spreadsheet, you can use the JSON form.

On the server side, create a JSON of your data and pass that to the HTML template after converting the JSON into a string (using JSON.stringify). On the client side, parse the string to convert it into JSON (using JSON.parse) and build your charts and graphs.

Amit Agarwal

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory


Awards & Titles

Digital Inspiration has won several awards since it's launch in 2004.

Google Developer Expert

Google Developer Expert

Google awarded us the Google Developer Expert award recogizing our work in Google Workspace.

ProductHunt Golden Kitty

ProductHunt Golden Kitty

Our Gmail tool won the Lifehack of the Year award at ProductHunt Golden Kitty Awards in 2017.

Microsoft MVP Alumni

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional (MVP) title for 5 years in a row.

Google Cloud Champion

Google Cloud Champion

Google awarded us the Champion Innovator title recognizing our technical skill and expertise.

Email Newsletter

Sign up for our email newsletter to stay up to date.

We will never send any spam emails. Promise.