A great feature of Google Apps is the ability to embed live spreadsheets in your website, updating automatically whenever your spreadsheet changes.
However, you aren’t allowed much control over the appearance of your spreadsheet – which can be inconvenient if you want to direct viewers to a particular part of the sheet.
Fortunately, there are a few undocumented ways to add a tiny bit more control to the way your spreadsheets appear. You can embed a single sheet and restrict the display to only a rectangle within it.
In this tutorial we’ll talk through the available options. Here, I’m using our Google Drive Embedder plugin for WordPress – but the steps should be almost identical if you’re just using the ‘iframe’ embed code, obtained from Publish in your Google Spreadsheet, to embed in any site. Either way, we just need to modify the URL in the code.
Using the Add Google File button when editing your post, select the spreadsheet to obtain a shortcode similar to this:
[google-drive-embed url="https://docs.google.com/a/mycompany.com/spreadsheets/d/15B___SOYjsRmU9tiwZly318HZnFHOHeayS6UTHx7Pu2I/htmlembed" width="100%" height="260" style="embed"]
which will display as follows:
That’s nice, but really we just wanted to direct our viewer to the colored summary on the right. Instead, we get the whole spreadsheet including access to the monthly sheets holding more of the data.
So you can modify the ‘url’ attribute of the shortcode, adding some extra parameters as follows:
[google-drive-embed url="https://docs.google.com/a/mycompany.com/spreadsheets/d/15B___SOYjsRmU9tiwZly318HZnFHOHeayS6UTHx7Pu2I/htmlembed?single=true&gid=419657423&range=f2:g6&widget=false&chrome=false" width="100%" height="260" style="embed"]
To display this instead:
Explaining the parameters
In our modified url, after the ‘?’ you can see a series of parameter names with their value set after a ‘=’. Each parameter/value pair is separated by a ‘&’, like this (but without spaces):
? single=true & gid=419657423 & range=f2:g6 & widget=false & chrome=false
single – setting to true displays just one sheet in the spreadsheet. The default sheet it will display is the first one created in the spreadsheet. This can be changed using ‘gid’ below.
gid – this needs to be a numeric identifier of the sheet you want to display. The first sheet created is 0, but others will have longer identifiers. To find the ‘gid’ for the sheet you want to display, go to the spreadsheet in Google Apps itself, and click on to your desired sheet. The URL in your browser should change to something like this: https://docs.google.com/a/mycompany.com/spreadsheets/d
The bit at the end of the URL shows you the ‘gid’ for the sheet, and that is the number you need to bring into the shortcode in your website.
range – set this to the cell range you want to display within the sheet (or leave it out to display the whole sheet). In our example, we set this to f2:g6. Note if you’re being pedantic then you should really change the ‘:’ to ‘%3A’ since colons should be encoded in URLs – but most browsers should understand the colon, so don’t worry. If you like to be correct, you’d use range=f2%3Ag6 instead.
widget – setting this to false removes the sheet selector bar when it’s embedded.
chrome – setting this to false removes the title bar showing the spreadsheet name, above the sheet.
As always, you need to consider the sharing settings of your Google Spreadsheet so that it can embed correctly while ensuring only the right users have access. Setting sharing to ‘Anyone with the link can view’ is easiest, allowing all visitors to view the spreadsheet without having to be logged into a Google account. Or you can share with specific users to ensure only they can see it, even if the link is shared outside your company.
Crucially, remember that hiding sheets or cells using the parameters described in this article will not prevent your viewers from being able to view them if they want to. They could simply remove your parameters from the URL and enter it into a different window.
Is there an easy way to change the background color on a case-by-case basis? White is fine for some of my spreadsheets I’m bringing in, but I need other colors on a select few others. Right now I’m getting around it by tightening up the height and width to fit perfectly to the spreadsheet, but that’s kind of a pain. Is there an easier way?
Thank you for your comment. I don’t know a way to change the background color in the same manner as those attributes described above unfortunately.
I guess the most reliable thing would be to change the background of the cells in the sheet itself, or wrap your embed codes in other HTML elements that can provide borders and background colors at the edges.
It could also be possible to target the iframe itself via CSS rules.
I hope that helps – you might need your designer/developer to look at these suggestions if you don’t happen to know much about those topics.
I have changed the colors of the cells to match and used the code in this post to capture just those, the issue is when the frame uses 100% width or otherwise and the cells don’t fit that – the rest is filled with empty white space.
I’ll post any solutions I find here.
Comments are closed.