Publishing a Sheet to the web (part 3)

Previously, on equals mc²...

• Part 1: Automating homework tweets to calendar entries using IFTTT and scripts (the overview)
• Part 2: Sorting course tweets in Sheets using query() (part 2) 

In this post I'll show you how to create the code to create a link to a webpage of (or embed) Sheets^* data and talk about some things you have to watch out for. The 'Publish to the Web' feature is the other thing^** that makes not just Sheets, but all of the GAFE tools supremely useful. I use it for lots of things, including self-grading quizzes and drill sheets, but I'll talk about it in the context of embedding my sorted homework tweets on the course webpage.

Update 17-03-17: You could probably do a lot of this with something like Awesome Tables, but if you're a noodler like me, you might do it this way.

Something to note: publishing to the web lets anyone who can visit your webpage (or who has the link) see your data; it makes a copy of the sheet and that's what is published. Any permissions you had on the original Sheet won't be copied over, and people might be able to see the underlying data for charts.

First, decide whether you want to create a web page, PDF, or a spreadsheet format, or if you want to embed the Sheet/tab^*** in an existing webpage. Embedding a sheet looks like the screenshot above. Publishing it as a separate webpage looks like this:

Here's how to do it. I'll talk about creating a link first, since embedding means a bit more work if you have columns to hide.

If you want to link to only part of a tab or more than one of the tabs (but fewer than all) in a sheet, you have to hide everything you don't want seen. Highlight any columns you want to hide (in the case of the homework tweets, that would be columns A-T), click on the triangle in the column header, and select "hide column."  If you want to share multiple (but not all) tabs, click on the triangle on the tabs you want hidden and select "hide sheet." Bear in mind that if you unhide anything, it will be visible on the web until you hide it again. I also take the time to get rid of the gridlines to keep it looking uncluttered.

Once you've taken care of that, you're ready to publish. Click on File->Publish to web.

The default choice is to create a link to a web page, but you can also create a PDF.

The "automatically republish when changes are made" should be checked -- unchecking it creates a static page that won't update.

If you only want to publish one tab at a time, select it from the list. If you want to publish more than one tab in the same page, select "Entire document."

I've been playing around with this a bit, and if you select several tabs from the lower menu, it will only publish one tab at a time. It looks like if you want to publish each tab separately, you have to close the 'publishing' pop-up and reopen it, choosing a separate tab to publish (getting an entirely new link). You would need to check off each tab you want to publish from the lower menu. However, I might be wrong and it might stop publishing the previous tabs. (I should say that I import my columns U-W into an entirely new Sheet for each course because of the calendar script, so I don't have to worry about hiding columns and I have only ever published one tab at a time from any given sheet.)

Here's what it looks like if you publish more than one tab at the same time using "Entire document." The user can click the links at the top to visit the different courses.

In the above example, I tried choosing to publish only the first four tabs, but it published the last one anyway. I hid Sheet1 and another tab, and as you can see they didn't get published. Hide what you don't want others to see, because the selection defaults to only one tab or the entire (viewable) document.

Any time you change the original Sheet, the webpage (or PDF, etc.) will also update. If at any time you want to stop publishing the Sheet, go to the publishing tab and click "Stop publishing" at the bottom. That way even if someone still has a link, they'll get a "We're sorry; that page is not published" error message.

Embedding a Sheet works the same way for the most part, except that you get html <iframe> code that you add to your site or blogpost. The main difference is, because you're embedding a copy of the Sheet, it will publish every column, including any you have hidden. To fix this, you need to either a) bring only the desired information into a new tab or Sheet and embed that (this is what I do) or b) manually add code to limit the range you embed. This post from hellotumo.com explains how to do that.

You will need to manually add width and height codes since the default is pretty small. I add width="90%" but keep the height the same since I only want the most recent homework tweets to be visible; users can scroll down to see older tweets. The code will look something like this:

<iframe src="https://docs.google.com/spreadsheets/d/1hgVADAoR-TlKYPGawmMCmxaQKZ77lkKpbSdNozjzcCQ/pubhtml?gid=769621547&amp;single=true&amp;widget=true&amp;headers=false" width=90%></iframe>

Below is what happens if you embed a sheet (with multiply tabs) and try to hide columns.

I did hide columns A-T in the original sheet, but embed doesn't care. It does care that I hid a few tabs, which is nice of it.

In my next post, I`ll talk about how to import data from one Sheet to another. The final post will deal with the script to create a Calendar entry.

^*If you are using Sites or Classroom, embedding the Sheet is easy; however I recommend importing the relevant information (such as the formula for column U) into a new Sheet since there doesn't seem to be a way to embed only one tab without hiding the rest and it won't let you hide the other columns. Here's hoping that the new Sites gives us an html code widget soon.

^**The query() function being the best thing, of course. Oh, and that collaboration business.

^***Again, by "tabs" I mean the different "sheets" within any one "Sheet," because that's just confusing.