1 - Create a Google Sheets File and Add Data

Create a Google Sheet file then create a simple table, like below, that could be used for a Scatter Chart. Also, name your individual sheet (the tab at the bottom) something like "myData".

2 - Publish Your Google Sheets File

Create a Google Sheets file and publish it by selection file>Publish to the web... then click the Publish button.

3 - Get the Public Link for a Table

4 - Load Your Table in Javascript

Copy the code below and paste it just after this line. Now replace the link with your table link copied in the previous step and change the tab label (e.g. 'myData') to match your sheet tab label. You can change the composeMyCharts function name as you wish or keep it the same.

viz.loadTable('https://docs.google.com/spreadsheets/d/e/2PACX-1vQUwF7K2lCH8CxQPJW-X7NqENwuwUM4eAeNUKw3j6yppO0tipC6yUB2hQPtRBZf19mmVbM5TdkkengZ/pub?gid=1030028695&single=true&output=csv', 'myData')
    .then(composeMyCharts);

Now you can access your database in your SQL statements. Remember that your individual sheet names are the tables you can select from in SQL. So if you want to get all the columns from a sheet (table) called "myData", then you would use the following:SELECT * FROM myData

5 - Create Charts from Your New Table

Create a function in your Javascript that will then define the charts for your new table. You can use the example below as a template, but you want the ensure the function name used in step 3 (e.g. composeMyCharts) matches this function name. You will also want to change the SQL and ensure that you have HTML elements with ID's to display the charts.

function composeMyCharts() {
  viz.chart({
    sql:"SELECT x, y FROM myData",
    chartType:"ScatterChart",
    containerId:"chart1"
  });

  viz.chart({
    sql:"SELECT * FROM myData",
    chartType:"Table",
    containerId:"chart2"
  });
}

The "ORDER BY" keyword simply sorts the rows of the table based on the column name provided. You can follow the ORDER BY columnName with "ASC" or "DESC" (for ascending or descending) to dictate the direction of the sort.

SELECT Major, StartingMedianSalary FROM Degrees ORDER BY StartingMedianSalary DESC

We can then use SQL to "filter" the table. For example the following SQL statement..

SELECT Major, StartingMedianSalary FROM Degrees

would generate the following table.

We simply SELECT columns FROM a table. Above we selected the columns "Major" and "StartingMedianSalary" from the "Degrees" table.

We can also use * (a wild card) for the columns to get all the columns. So, SELECT * FROM Degrees will result in the full table.

Try It OUT

1 - Find the following Javascript in your code.js file.

viz.chart({
    sql:"SELECT * FROM Degrees",
    chartType:"Table",
    containerId:"chart2"
});

Notice how this code generates the table in your web view, where the table has a lot of columns (actually, all of them).

2 - Now change the SQL to the following to select only two columns.

SELECT Major, StartingMedianSalary FROM Degrees

Run your web app and notice how the table has changed.

3 - Now try the following SQL to get all the columns from a different table.

SELECT * FROM States

This gets a different table, "States", that can be found in our example database.

You will use SQL to get the right data from a database (Google Sheets file) for the charts you want to display. Next, let's look at producing the actual charts.

Making basic charts with the "viz" library is pretty easy. Let's get started.

Try It Out

1 - Find the following Javascript in your code.js file.

  viz.chart({
    sql:"SELECT Major, StartingMedianSalary FROM Degrees",
    chartType:"BarChart",
    containerId:"chart1"
  });

This generates a bar chart (chartType: "BarChart") using data specified by the SQL statement(sql: "SELECT Major, StartingMedianSalary FROM Degrees") and places the chart in an HTML element with an id of "chart1" (containerId:"chart1"). Notice that you should have a <div> tag with that id in your HTML file.

2 - Change the chartType to "ColumnChart"

In your Javascript, change the chart type to "ColumnChart". Run the web app and notice how the chart changes. It may not look good since there are so many columns squished together. Choosing the right way to visualize the data will be part of the project challenge.

3 - Change the chartType to "Histogram"

In your Javascript, change the chart type to "Histogram". Run the web app and notice how the chart changes.

4 - Add Another Chart that Uses a Different Data Table

Copy and add the following Javascript into your composeCharts( ) function.

viz.chart({
    sql:"SELECT Year, Total FROM Trend",
    chartType:"ScatterChart",
    containerId:"chart3"
 });

You will also want to add the following HTML to your index.html file to provide a place for your chart.

<div class="card">
   <div id="chart3"></div>
</div>

More Charts and More Options

The dashboard template provides the HTML/CSS/JS needed to get started with web-based data visualization. The template uses the following tools to make things easier for us.

• Google Sheets - This will function as our "database" of tables.

• Structured Query Language (SQL) - SQL will be used to pull the right data from the database for visualizing.

Instructions

1 - Create files in a new Repl.it or other code environment.

2 - Review the HTML

Notice the basic page structure for the "cards" that contain elements for the charts. See below for reference.

<div id='page'>
    <div class="card">
		<div id="chart1"></div>
	</div>
	<div class="card">
		<div id="chart2"></div>
	</div>
</div>

3 - Review the CSS

Notice the "card" class that creates the shadow effect you see with the charts.

.card {
  box-shadow: 0 4px 8px 0 rgba(0, 0, 0, 0.4), 0 6px 20px 0 rgba(0, 0, 0, 0.4);
  overflow: hidden;
  background: white;
}

4 - Review the Javascript

• The function composeDegreeCharts() runs automatically once the data is loaded.

• In composeDegreeCharts() we create our charts by specifying SQL (more on this later), the chart type, and the HTML element it should be placed.

function setup() {
  var degreesLink = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vQUwF7K2lCH8CxQPJW-X7NqENwuwUM4eAeNUKw3j6yppO0tipC6yUB2hQPtRBZf19mmVbM5TdkkengZ/pub?gid=1030028695&single=true&output=csv';
  viz.loadTable(degreesLink,"Degrees").then(composeDegreeCharts);
  window.onresize = composeDegreeCharts;
}

function composeDegreeCharts() {
  viz.chart({
    sql:"SELECT Major, StartingMedianSalary FROM Degrees",
    chartType:"BarChart",
    containerId:"chart1"
  });

  viz.chart({
    sql:"SELECT * FROM Degrees",
    chartType:"Table",
    containerId:"chart2"
  });
}

window.onload = setup;

That's the quick overview of the template. We will dig into the details and how to customize things next.

The "options" parameter included below demonstrates many style changes you can make to a bar chart.

Create the HTML, CSS, and Javascript files below and you are off to the races. Click on the file name to get the code content for each.

Copyright and License

Copyright © 2018-2019 Jim Lyst and Michael Frontz, Indiana University School of Informatics and Computing at IUPUI

The LIKE operator is used with the WHERE clause and searches for a string pattern in a column. The statement below will return only the rows where the "Major" is "Accounting".

SELECT Major, StartingMedianSalary FROM Degrees WHERE Major LIKE 'Accounting'

The statement below uses "%" as wildcard. So any row with a "Major" that begins with "A" will be returned--Accounting, Agriculture, Art, etc.

SELECT Major, StartingMedianSalary FROM Degrees WHERE Major LIKE 'A%'

Use "LIMIT" at the end to return only a defined number of rows from the top of the result. It is often used with "ORDER BY" like below.

SELECT Major, StartingMedianSalary FROM Degrees ORDER BY StartingMedianSalary DESC LIMIT 3

The WHERE clause is used to filter records based on a condition. The following SQL statement will return a table that only contains rows where the "StartingMedianSalary" is greater than 60000.

SELECT Major, StartingMedianSalary FROM Degrees WHERE StartingMedianSalary > 60000

You can use the following operators for the comparison.

The "options" parameter included below demonstrates many style changes you can make to a Column chart.

viz.chart({
    sql: "SELECT Major, StartingMedianSalary, MidCareerMedianSalary FROM Degrees",
    chartType: "ColumnChart",
    containerId: "chart1",
    options: {
      title: "Salaries by Degree",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
      legend: {
        position: "right"
      },
      colors: ["#bb0000", "#ffaa00"],
      vAxis: {
        title: "Annual Salary",
        format: "currency"
      }
    }
});

The "options" parameter included below demonstrates many style changes you can make to a Histogram chart.

viz.chart({
    sql: "SELECT Major, StartingMedianSalary, MidCareerMedianSalary FROM Degrees WHERE StartingMedianSalary <> 46000",
    chartType: "Histogram",
    containerId: "chart1",
    options: {
      title: "Salaries by Degree",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
        legend: {
        position: "right"
      },
      colors: ["#bb0000", "#ffaa00"],
      vAxis: {
        title: "Frequency",
      }
    }
});

The "options" parameter included below demonstrates many style changes you can make to a Scatter Chart.

  viz.chart({
    sql: "SELECT Year, Total FROM Trend", 
    chartType: "ScatterChart",
    containerId: "chart3",
    options: {
      title: "Trend in Tech Jobs",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
        legend: {
        position: "right"
      },
      colors: ["#bb0000"],
      vAxis: {
        title: "Jobs in US \n(in Thousands)"
      },
      hAxis: {
        title: "Year",
        format: "####"
      }
    }
  });

The "options" parameter included below demonstrates many style changes you can make to a Line Chart.

viz.chart({
    sql: "SELECT Year, Total FROM Trend", 
    chartType: "LineChart",
    containerId: "chart3",
    options: {
      title: "Trend in Tech Jobs",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
        legend: {
        position: "right"
      },
      colors: ["#bb0000"],
      vAxis: {
        title: "Jobs in US \n(in Thousands)"
      },
      hAxis: {
        title: "Year",
        format: "####"
      }
    }
  });

This example demonstrates how you can use a dropdown menu to allow a user to select, or filter, the data displayed on a column chart.

HTML

The select element will be used to filter the data for a column chart that will be inserted in the "chart2" div.

<div class="card">
  <select id="salary-select">
    <option value="StartingMedianSalary" selected>Starting Salary</option>
    <option value="MidCareerMedianSalary">Mid-Career Salary</option>
  </select>
  <div id="chart2"></div>
</div>

Javascript

Add the following code to the bottom of the setup() function. This will "listen" for changes to the dropdown selector and re-run the composeCharts() function when it is changed.

$("#salary-select").change(function () {
    composeCharts();
  });

The following "viz.chart" function defines a column chart that will use the value selected by the user in the SQL to retrieve the right column of data to chart.

viz.chart({
    sql: `SELECT Major, ${$("#salary-select").val()} FROM Degrees`,
    chartType: "ColumnChart",
    containerId: "chart2"
});

The "options" parameter included below demonstrates many style changes you can make to a Bubble Chart.

To create a grid layout like the sketch above, adjust your HTML and CSS like the following examples. But be careful not to simply copy and paste everything here.

HTML

Notice that the "card" for the first chart also has a class called "full-width". We will use this class to make a chart span the full width of the page, while the others will fall into a grid with 3 charts per row.

	<div id='page'>
    	<div class="card full-width">
			<div id="chart1"></div>
		</div>
		<div class="card">
			<div id="chart2"></div>
		</div>
		<div class="card">
			<div id="chart3"></div>
		</div>
		<div class="card">
			<div id="chart4"></div>
		</div>
	</div>

CSS

First, notice the id="page" (#page) style defines the normal grid design of 3 elements (or 3 columns) per row. grid-template-columns: 1fr 1fr 1fr; This applies to all the grid elements inside the id = "page" element.

Next, any element with the class "full-width" will start at the first position and span 3 columns. grid-column: 1 / span 3;

We can also add a style (look for @media ... below ) that makes our dashboard mobile-friendly where all the charts just stack on top of each other if the screen is less than 700px wide.

/* This is not the complete CSS file contents.*/ 
/* It simply demonstrates the important styles for this layout example. */

#page {
  display: grid;
  grid-template-columns: 1fr 1fr 1fr;
  grid-gap: 15px;
}

.full-width {
  grid-column: 1 / span 3;
}

@media only screen and (max-width: 700px) {
  .card {
    grid-column: 1 / span 3;
  }
}