Publishing charts with Google Sheets
Introduction
This is part of a four-part series on publishing Google Charts. Published charts are live. Any changes made to the working chart are reflected in the published chart. Updates are not automatic for all forms of published charts. Updates have to be manually pushed to the published version.
Google Sheets is a useful tool for the development of a variety of charts. Once those charts are created it may be necessary to share them with others. There are a variety of reasons for sharing the information and a variety of ways to share it. I am going to focus on examples related to education.
The first example focuses on the publication of data for a geography assignment. Like all assignments, this assignment is connected to other subjects. It is part of an overall student product students.
The second example comes from something I was part of for one year. The administrator at the campus wanted to increase student attendance. She offered a pizza party for the class or classes with the highest attendance. The parties would be part of the traditional holiday parties for December.
Product preview
Use the links below to see a preview of the final products.
The highest mountain peaks in North America:
Holiday party attendance:
Working documents
Use the links below to get a copy of the working document. The document includes the charts. The charts are already formatted for publication.
Just the chart
The spreadsheet has a chart and table on the first sheet. The first sheet is titled USA. The second sheet is titled peaks. That sheet contains the raw data.
Click once on the chart. Look for the action menu.
Click the action menu and select Publish chart.
A publication configuration box opens.
Click the share option selector. Choose the chart itself. The chart is titled Highest Mountain Peaks in USA.
Leave the other option set at Interactive. Click the Publish button.
Google Drive prompts for confirmation. Click the OK button.
A special publication link is generated and selected. Copy the link.
Create a new tab and paste the link into the address bar. Press the Return key to load the published chart.
The chart appears on the far left side of the browser. Nothing but the formatted chart appears on the page. Use the link and share it with anyone who needs to see the chart. The chart does not need special permission. Anyone who has the link can see it.
Charts created within an organization have additional options to share with the world. Use those options to share the map with the world. Otherwise, the chart can only be viewed by members of the organization.
Roll the mouse over one of the columns in the chart. The name of the mountain and height appear. This is the extent of the interactivity.
Interactivity is nice; it isn’t needed in this chart. We can choose to publish the chart as a non-interactive image. Click in the address bar and move to the end of the link. The last word in the link is interactive.
Replace the word interactive with "image". Press the Return key.
The image appears at the center of the page. Roll your mouse over a column and nothing will happen. Change the link name back to interactive if you want to use the interactive version.
The chart data is live. The chart updates on its own when we update the information related to the chart. For example, the chart would update if we chose to chart only the top five highest peaks. All we do is update the chart on the sheet. The update on the published version takes care of itself. Let’s take a look at how this works. Leave the tab open and return to the spreadsheet tab.
Stop and Republish
The sheet still has the publication options dialogue open. Click the Published content & settings option.
This is where we stop publishing the chart. The chart will always be viewable by anyone with the link as long as the publish option is enabled.
There are other ways to stop the published chart. These are not the ideal way to stop publishing the chart but you need to be aware of them. Deleting the chart from the sheet will stop the publication. Deleting the spreadsheet or Sheet will also stop the publication. Anyone visiting the site with a missing chart link will see an error message.
The republish option is automatically enabled. I recommend unchecking the option before making changes. This gives you time to make sure the information is ready for publication.
Remove the check from the box and close the configuration box.
Go to the table and click the Peak heading.
Look at the Formula bar. The table is generated with a Query function. The function has a limit parameter. The limit is currently set to 10. Only the first 10 records are displayed.
Replace the 10 with 5 and press the Return key.
The chart on the sheet updates with the changes.
Click the chart action menu; select Publish chart.
Place a check back on the option to automatically republish changes. Close the configuration box.
Switch back to the published chart tab. The chart has been updated with the changes. Click the browser refresh button if the chart does not show the change.
Close the published chart tab. We don’t need it for the remaining lessons.
Dashboard option
Sharing charts as we did is easy but the view is kind of plain. There is another way to share the chart and other content on the sheet. This is often referred to as publishing a dashboard.
This is a dashboard of a kind. Real dashboards created with Google Data Studio have interactive components. They include selectors and ways to update the information in charts.
Go to the menu and click File; choose to Publish to the web.
This is the same configuration option we just used.
Click the selector and choose USA. This is the Sheet itself.
Click the Publish button. Confirm you want to publish the document.
Copy the publish link. This link is different from the chart link we created earlier.
Create a new tab and paste the link into the address bar. The sheet with the table and chart open on the page. The name of the spreadsheet and sheet appears in the information bar. The table is missing half the mountain peaks. We changed the Query limit in the previous lesson.
Updating the dashboard
Return to the spreadsheet tab. Close the configuration box. Click on the Peak heading; go to the formula bar. Change the Limit value from 5 to 10. Press the Return key to update the table and chart.
Go to the spreadsheet dashboard tab. Click the browser refresh button.
updated published version of chart
I like this method of publishing charts. It allows me to choose the position and background color of the chart. I can also include other elements like the table.
Attendance dashboard
In the attendance dashboard, I applied the same process. The dashboard has several charts with additional formatting. Use the link above to see the published version of this dashboard.