How to embed specific cells range when embedding a Google spreadsheet

If you’re suffering through spreadsheets, you may also like this how-to guide about creating a new Google doc (or sheet) inside a specific Google Drive folder.

Update April 14, 2014: With the help of a Good Samaritan reaching out to me via email, a solution has been found for embedding specific ranges of the new Google Spreadsheets (they were updated in early 2014) onto your site. Here’s the new trick:

1. Publish your sheet to the web by going to File > Publish to the web.

2. Grab the Embed code (it uses an iframe).

3. Modify the iframe code so it reads like so. You’ll need to change different pieces of the code to match your own spreadsheet:

<iframe src="https://docs.google.com/spreadsheets/d/1B56....this will change according to your spreadsheet/pubhtml/sheet?headers=false&gid=0&range=A1:C10" width="400" height="350"></iframe>

For comparison, here is what my example’s original embed code looks like:

<iframe src="https://docs.google.com/spreadsheets/d/1B56....this will change according to your spreadsheet/pubhtml?widget=true&amp;headers=false"></iframe>

4. To explain the differences, this new, working embed code differs starting with that “/” placed after the “pubhtml.” The original embed code actually has a question mark.

5. This new embed code also differs in how everything follows after “pubhtml/”. You don’t use the word “widget=true.” And that “gid=0”? You get that from the URL of the spreadsheet you are editing in Google Docs.

6. You’ll see that you can’t call out named ranges, but you can manually specify the range. This is the “&range=A1:C10” that follows “&gid=0”.

And here’s an example of the working embed code:

Below is an embed of the second sheet within the same test project. To embed this specific sheet, I had to click on its tab within the project to make it active, then inspect the URL that gets loaded. That URL contains the gid number. It’s not something simple or logical like “2” or “1.” In my case, it was 2106886376. So all you do is change the functioning embed code to reflect that.

So here’s what the embed code for the 2nd sheet in my project looks like:

<iframe src="https://docs.google.com/spreadsheets/d/1B56Rf_LvXx-meBDrk8Ouz18uwHCEATHpdCwLgS3qPnQ/pubhtml/sheet?headers=false&gid=2106886376&range=A1:C2" width="400" height="120"></iframe>

And below is the result:

34 Comments

    1. Hello, and thanks for writing. You’re not using an iframe with your code (you are using a regular “a href” tag, which is wrong). There might be some other oddities in it. Try using this embed code:

      <iframe src="https://docs.google.com/a/atomsupplies.com/spreadsheets/d/YOUR-SHEET-CODE/pubhtml/sheet?headers=false&gid=1333836645&range=B3:I8" width="300" height="350"></iframe>

      1. Great job, this trick really work in any kind of website. However, it does not work in a new Google Site. Do you know how?

        1. Thanks for writing. Well, it looks like the new Google Sites does not allow you to edit the HTML. Without that feature, you can’t use my embed trick. However, the new Google Sites does indeed make it easy to add an entire Google spreadsheet. How to use it? In Sites, check out the right column, and at the top make sure you have “Insert” selected. Then scroll to the bottom of the same right column. There you will see an option to insert a Google spreadsheet. It’s pretty rudimentary; you can’t select a cell range, so if you want to embed very specific pieces, you may need to make a custom spreadsheet just for that.

  1. I am not sure whether this has been covered in the comments but I’ll ask anyway. I have a google spreadsheet that is just a fun “football tipping” competition. I have just run it with made up players but I would like buttons on a website to display sections of this main spreadsheet to show current progress etc where players can go to site and tap on a button to show what they wish to see. I probably haven’t explained things too well but to have on a web page buttons that will direct a participant to the section they wish to view would be great(Like leaderboard). If anyone could explain to me in simple terms that’d be great.

    1. It looks like my previous reply was lost. In short, yes, you should be able to embed as many iterations of your spreadsheet as you like, and you can make each embed different by choosing a different range of cells to display. To then display each of those via a button: Well, this is a web design question. You could use anchor links on a single page to autoscroll down to a particular embed of your spreadsheet (a list of links up top to the various subsections, then all the subsections are embedded below that). You could also do a popup, though I don’t recommend that, since it’s a poor user experience, and you need to think how it plays on mobile devices. Hope that helps.

  2. Hi, would you mind uploading the images again, they are not coming through so I can’t see how to amend the code :( Many thanks in advance!

    1. Hi Jerry – This is fixed; I had disabled a plugin that facilitated presenting code, and it knocked out the explanatory text. Hope you get what you need.

  3. Hello,
    I am trying to achieve what Heather asked further up these comments: Show column headers in cell A2:K2 and then define the actual data to display in A5:K5 for person A. A different person would have the same headers (A2:K2) but the code should reference e.g. A10:K10.

    Possible? I cant work it out in HTML and Google has let me down!

    1. I’m sorry but I know of no way to do what you ask. The best I’ve gotten is just a straight-forward display of columns and rows.

  4. This is very helpful, thanks!
    Is there a way to make the range pull from 2 different areas of the spreadsheet (ie. from the top two header rows, and then a different range of rows)?
    Example: I want to display A3:AO4 and A11:AO11 together (kind of like hiding rows when you are looking at them in sheets).

  5. I don’t know where else to ask, so I am asking here… With the old Google Spreadsheets we had an option to publish part of a spreadsheet as in RSS format. Is there a workaround for this in the new ones?

    Thanks in advance.

    1. Not sure what you’re looking for. Do you mean to use Google Spreadsheets as an RSS reader? Someone wrote about that here ==> http://www.makeuseof.com/tag/create-an-rss-feed-reader-google-spreadsheet/

      Or did you mean embedding part of a sheet on a page, and using that sheetpart as a way to display an RSS feed? Hmmmmm. Sounds like a fun trick. I don’t know off the top of my head, but I imagine the RSS technique described by makeuseof.com could be worked into some sort of working shape.

      1. Up until the previous version of Google Spreadsheets, when you published on the web, you had three choices with what went published:
        a. specific sheet
        b. a specific range of cells
        c. the output format. HTML was the default, but alternatively you could choose atom/rss, csv, pdf, etc.
        With atom/rss I used to be able to export an RSS feed with content taken from a specific range on a sheet. For instance, I could keep the number of people who completed a form on a cell and export this number as a live feed.

        This is not there in the new Google Spreadsheets; or has been removed from the UI.

  6. Thanks for this, I’m trying to do this but also making the spreadsheet interactive so people can enter data in one of the cells – is this possible?

  7. Thanks very much for this. You’ve filled one of the big gaps in the new sheets.

    Have you found a way to remove the header containing the file name and sheet name from appearing at the top of the iframe? That’s my one remaining issue with the new sheets.

    1. Hi, Colin. You’re welcome. Glad it helped. I believe that as long as you include the bit I’ve indicated in the tutorial that reads … sheet?headers=false …. that you will remove the headers. It seems to work in the example I’ve embedded in this post.

  8. The “New” Google Sheets has removed the cell range input window from the “Publish to Web” input window. Do you know how to input the desired cell range into the html code manually? Thanks.

    1. Heyah – I’ve updated the post to address your question. There is a way to update the HTML manually. Google isn’t making this easy. All the steps are the same in terms of preparing content for embedding, but the embed Spreadsheets provides is different. You’ll need to adjust it as I explain in the update to this blog post. Happy spreadsheetin’!

      1. Thanks Amedeo. I tried your recommended method in New Google Sheets but it seems to ignore my reference to the name range. Also, I still haven’t found a way to remove the ‘file name/sheet name’ header that comes over. I think changing ‘widget=true’ to ‘widget=false’ took care of it in Old Google Spreadsheets, but changing this in New Google Sheets doesn’t seem to make a difference.

Leave a Comment

Your email address will not be published. Required fields are marked *

 

%d bloggers like this: