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"
  });
}

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>

Run your web app and you should see your new chart. This chart uses data from the "Trend" table in our database (Example Google Sheets File) to create a scatter plot (also called scatter chart).

More Charts and More Options

See the Code Examples section for different charts and options you may want to use in your final dashboard, like titles and colors.

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.

• Google Charts API - This library will allow us to create charts in our web app.

Instructions

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

Use the code from Getting Started to create your HTML, Javascript, and CSS files. Verify that the dashboard displays an example bar chart and table.

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

• Notice that we load an example table from the "Degrees" tab in a Google sheet that you can see here.

• 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.

Structured Query Language (SQL) is a standard language for accessing and manipulating databases. Databases are just a group of data tables. Below is an example of a table called "Degrees" that could be in a database.

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.

4 - Go to this Google Sheet to see the example database and notice how each sheet (tab) is a table with a unique name.

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.