Embedding Google Spreadsheets in a Website
There are many ways to add free gadgets and widgets to your blog or website, but perhaps a lesser known technique is to embed a Google Spreadsheet.
You can always include a simple link to your published Google Spreadsheet, but what I'm talking about is displaying the actual spreadsheet or a portion of the spreadsheet on your site like the example below which shows a school event calendar for a fictional elementary school.
Why Use a Google Spreadsheet
Before listing some ideas for embedding Google Spreadsheets in your blog or website, let me address a few reasons WHY you might want to use a spreadsheet instead of using some other approach.
1. Calendars and Schedules
Spreadsheets are a popular tool for creating calendars because of the flexibility of formatting. You can also do some pretty fancy stuff with an embedded spreadsheet. For example, you could embed an automatically updating holiday calendar on your blog or website. You could display your team's practice and game schedule, or your school's event calendar.
If you inspect the source code of this page, you will see that in the <iframe> tag that was generated by Google Sheets for embedding the calendar, I have added &range=A1:W37 to the src url. Doing this allows you to publish only a portion of a worksheet rather than the entire worksheet.
2. Frequently Changing Data or Content
Perhaps you want to allow someone to update a list on your website, but they don't know HTML or you don't want to give them FTP access. You might be able to use an embedded spreadsheet as an alternative to programming in PHP/Java/ASP/etc or installing a script or content management system. Just create the list as a Google Spreadsheet and give the person permission to edit the spreadsheet. After they edit the spreadsheet within Google Docs, if they republish it, the updates will show up automatically on the website(s) where it has been embedded.
The example below shows an embedded Reading List.
3. Displaying Tabulated Data
Creating a nice looking table in HTML can be tricky, especially if you want to do fancy stuff with background colors. If you're handy with a spreadsheet, you could just put the data in a spreadsheet and embed the table into your blog or website. Or, if your data is already in a spreadsheet - why go through the extra work of converting it to an HTML table? The data is stored in an <iframe> tag, so if the table is large, you can set the size of the embedded iframe so that the user can scroll through the data without scrolling the entire web page, just like in the reading list example above.
20+ Ideas for Embedded Spreadsheets
School / Academics
- School Calendar - Many schools post yearly event calendars on their websites. For a detailed calendar, you might want to just link to a full spreadsheet, but it would also be nice to see a full year at a glance, with the main dates highlighted (like the example shown above).
- Class Schedule - This might be just a list of the times and periods for a high school, or you could display your own personal class schedule on your blog or class website.
- Homework Assignments / Syllabus - It is pretty common for a class syllabus to contain a table listing the homework assignements and due dates. You could embed the table on your class website.
- Reading List - Share your list of favorite recent reads and indicate which book you are currently reading. Put your book club's reading list on your blog. On a blog, this would allow others to comment on your current book, but be careful - maybe somebody would end up spoiling the ending.
- Favorite [fill in blank] List - List your favorite movies, music, games, etc.
- To Do Lists - I can't think of a very good reason to share this with the world, but hey, it's an idea.
- Quote/Tip/Fact/Joke of the Day - With the Quote of the Day template, you can create a list of quotes and the spreadsheet loops through the quotes based on the current day. The example below is an embedded "quote of the day" gadget.
- Price List - For a small business, home business, online auction ...
If you run a website or have a blog devoted to your favorite team, here are a few ideas for ways to use embedded spreadsheets. These ideas may also work for a local club or organization that you belong to.
- Training Schedule - This might be used on your school website if it's for your team, or perhaps you train with a partner or group and want to show which days you'll be in the gym.
- Tournament Bracket - Do you have a tournament that spans multiple days? If so, you could use a tournament bracket template and display it on a blog or website.
- Team Roster - Spreadsheets are great for tabulated lists.
- Win-Loss Records or other Statistics - Keep track of your favorite teams, or the stats for your club or local organization.
- Game Schedule - Embed your team's game schedule on your school site, or if you are a devoted fan, show the game schedule along with a win-loss record on your blog.
- Record Book - Use a spreadsheet to list the standing records for your school, club, or organization.
Business or Organization
Remember that publishing a document will make it viewable by the rest of the world. However, if you aren't sharing secrets, it may still be useful to display on a portion of your website viewed mostly only by employees.
- Conference Room Schedule
- Shift Schedule - You could modify one of the calendars, use the Shift Schedule template included in the template gallery, or the Embedded Schedule template like the one used to create the school calendar shown above.
- Work Schedule
- Project Timelines or Outlines
- Contact Lists
While I'm not to keen on putting a bunch of personal stuff on a blog, I know many people who like to share with the world their plans, progress, successes, or life stories.
- Car Pool Schedule - This is something that might be constantly changing, and could either be a shared spreadsheet that you share with others, or something you embed in your blog or website.
- Fitness Log - In a social setting, sharing your fitness log may help inspire others (or in my case, show others how lazy I am)
- Personal Record Book - An idea for fitness enthusiasts who like to keep track of their personal bests.
- Weight Loss Status / Chart - I've heard it said that if you write it down and share it with others, you are much more likely to reach your goal.
- Countdown Timer - The following example shows a live countdown gadget embedded in this web page. It only updates about every 5 minutes (keep that in mind before you create something thinking it will update every second).
How to Embed a Google Spreadsheet
There are a few different ways you can embed a spreadsheet, and you can experiment with the different approaches. The steps below outline how to embed a specific range of cells into a blog or website.
- In Google Docs, create a new spreadsheet starting from the Embedded Calendar, Embedded Schedule, Reading List, Quote of the Day, or Countdown Timer templates (or use one of the other templates if you get permission from the owner to do so, or create your own spreadsheet from scratch).
- Publish as a Web Page: Go to Share and select "Publish as Web Page". Click on the Publish button.
- Click on "More Publishing Options" at the bottom of the "Publish as Web Page" dialog box.
- Check the "Automatically re-publish when changes are made" option. If you have a dynamic formula like =TODAY() or other automatically updating content, this will cause your embedded spreadsheet to update every 20 minutes or so, even without you having to make manual changes.
- Select "HTML to embed in a web page" from the "File format" options.
- Select just the Sheet you want to publish from the "What sheets" options.
- Enter the range of cells you want to publish in the "What cells" text box, or if you want to show the entire sheet, leave the default as "All Cells".
- Click on Generate URL
- Copy the HTML, and edit the width and height values as needed to size the iframe for your blog or web page. See the guidelines below about sizing the horizontal width.
- Horizontal Width - By default, scrollbars will appear if the spreadsheet is wider or taller than the size of the iframe. The pixel width of an embedded spreadsheet in FireFox is wider than in Internet Explorer or Safari. Therefore, if you change the width='xx' attribute of the iframe, you should probably base the width value on how it appears in FireFox.
One way to avoid having the scrollbars appear is to set the scrolling='no' attribute in the iframe tag like this: <iframe ... scrolling='no' ... >. The problem with this approach is that if the spreadsheet is larger than the iframe, the right-size will be truncated and the viewer will not be able to scroll to see it.
Note: If your embedded spreadsheet is N cells wide, then the width within Safari is N pixels narrower and within Internet Explorer the width is N+1 pixels narrower. I haven't checked this with different browser versions or on different computers.
- Publish the Entire Sheet or a Range of Cells? Depending on your content, you may want to publish the entire worksheet instead of a range of cells. For example, if you create a list and only publish a specific range of cells, then if you edit the list later and it extents beyond the range specified in your blog, the list will show up truncated.
- The method you use to publish and embed your spreadsheet (entire workbook, entire sheet, range of cells, scrolling or non-scrolling) should depend on how the spreadsheet might change in the future. Ask yourself the following questions:
(a) Will the spreadsheet possibly change size (height or width)?
(b) Will the number of columns or rows change?
(c) Will the spreadsheet fit/display well within a blog or web page?