Build a Charts Dashboard with Google Sheets and HTML Service

B
Published in: Google Apps Script - Google Sheets

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.

google-charts-dashboard

// Code.gs
function doGet(e) {
  return HtmlService
  .createTemplateFromFile("index")
  .evaluate()
  .setTitle("Google Spreadsheet Chart")
  .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function getSpreadsheetData() {

  var ssID   = "PUT_YOUR_SPREADSHEET_ID",
      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>
<html>

<head>
  <script src="https://www.google.com/jsapi"></script>
</head>

<body>

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

  <script>
    google.load('visualization', '1', {
      packages: ['corechart', 'bar']
    });

    google.setOnLoadCallback(getSpreadsheetData);

    function getSpreadsheetData() {
      google.script.run.withSuccessHandler(drawChart).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);

    }
  </script>
</body>

</html>

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.

📮  Subscribe to our Email Newsletter for Google tips and tutorials!
Published in: Google Apps Script - Google Sheets

Looking for something? Find here!

Meet the Author

Web Geek, Google Developer Expert
A
Amit Agarwal

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. He is the developer of Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory

Get in touch

Google Add-ons

Do more with your Gmail and GSuite account
G

We build bespoke solutions that use the capabilities and the features of Google Workspace for automating business processes and driving work productivity.

  1. Mail Merge with Attachments
    Send personalized email to your Google Contact with a Google Sheet and Gmail
  2. Save Emails and Attachments
    Download email messages and file attachments from Gmail to your Google Drive
  3. Google Forms Email Notifications
    Send email notifications to multiple people when a new Google Form is submitted
  4. Document Studio
    Create beautiful pixel perfect documents merging data from Google Sheets and Google Forms
  5. Creator Studio for Google Slides
    Turn your Google Slides presentations into animated GIFs and videos for uploading to YouTube