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
/15B___SOYjsRmU9tiwZly318HZnFHOHeayS6UTHx7Pu2I/edit#gid=419657423

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.

Security

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.