Embedding Google Spreadsheets with more control over appearance

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.

3 Comments

  • Nate Phillips December 17, 2014 at 8:23 pm

    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?

    • Dan December 18, 2014 at 2:57 pm

      Nate,

      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.

      Regards,

      Dan

      • Nate Phillips December 19, 2014 at 3:35 pm

        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.

Google Apps Login is trusted by thousands of organizations from small businesses to NASDAQ-listed companies and government organizations.
Users click their way through Login via Google (just one click after the first time)

Users click their way through Login via Google (just one click after the first time)

You can try it out by clicking below to auto-create a test account on this site! This will show you the basic Google login process.
Try out Google login
 

Logging in is only the start – Google Apps Login takes care of all your WordPress user management. Find out more here.

[user_show_avatar]

See [user_first_name] – that was easy!

Your account has been created, and you are now logged in. On your own site, you will specify your Google Apps domain – e.g. mycompany.com – and only users on that domain will be allowed to auto-create accounts.
Logging in is only the start – Google Apps Login takes care of all your WordPress user management.
Find out more

×