Inline Google Spreadsheet Viewer

Need technical support? Please visit the WordPress Plugin directory for up-to-date information about this plugin. Posting to the Support Forum for Inline Google Spreadsheet Viewer is where you’re more likely to get more help faster, rather than leaving a comment here.

Here are some live examples showing charts, graphs, and tables made with the Inline Google Spreadsheet Viewer that all use this Google Spreadsheet:

The default HTML table displayed by Inline Google Spreadsheet Viewer:

Player
Team
Points
Games
John SmithPirates33
Andrea MillerNinjas62
Nancy GlasskillAliens33
José RodriguezRobots25
Keith McGrawPirates63
Lily RiverrunNinjas26
Kim SettlebackRobots53
Judy DemeterAliens82

A Column chart comparing individual players:

The above column chart is created using the shortcode:

[gdoc
    key="https://docs.google.com/spreadsheets/d/16SrlMkySbxfpxD1zi7ssvZAYkpS1gC91fk2wAl4ngVg/edit"
    title="Points earned and games played, by player"
    chart="Column"
    query="select A, C, D"
]

A Bar chart showing the relative ranking of each team:

The above bar chart is created using the shortcode:

[gdoc
    key="https://docs.google.com/spreadsheets/d/16SrlMkySbxfpxD1zi7ssvZAYkpS1gC91fk2wAl4ngVg/edit"
    title="Team ranking, by total score"
    chart="Bar"
    query="select B, sum(C) group by B order by sum(C)"
]

A Pie chart showing the relative breakdown of points earned by players on the Aliens team:

The above pie chart is created using the shortcode:

[gdoc
    key="https://docs.google.com/spreadsheets/d/16SrlMkySbxfpxD1zi7ssvZAYkpS1gC91fk2wAl4ngVg/edit"
    title="Points scored by Aliens team members, by player"
    chart="Pie"
    query="select A, C where B='Aliens'"
]

You can also set an arbitrary CSV file (or web service endpoint that produces CSV data) to be your datasource. These CSV files can also be query‘ed just like Google Spreadsheets. Here’s what the default DataTables-enhanced view looks like, using an arbitrary CSV file, with a custom query:

The above table was created using the shortcode:

[gdoc
    key="http://viewportsizes.com/devices.csv"
    query="select `Device Name`,`OS Version`,`Release Date` where `Platform`='Android'"
]

Overview of Inline Google Spreadsheet Viewer features

The Inline Google Spreadsheet Viewer readme file contains a thorough overview of its features. A brief summary of those features are below. Please read the readme file for a complete description of all included features.

Easily turn data stored in a Google Spreadsheet into a beautiful interactive chart or graph, a sortable and searchable table, or both!

The Inline Google Spreadsheet Viewer fetches a publicly shared Google Spreadsheet using a [gdoc key=""] WordPress shortcode, then renders it as an HTML table or interactive chart, embedded in your blog post or page. The only required parameter is key, which specifies the document you’d like to retrieve and will render a feature-rich table. Additional parameters let you customize how you display your data in the table, or transforms the table into an interactive bar chart, pie chart, or other information visualization.

Your spreadsheet must be shared using either the “Public on the web” or “Anyone with the link” options. Currently, private Google Spreadsheets or Spreadsheets shared with “Specific people” are not supported.

After setting the appropriate Sharing setting, copy the URL you use to view the Spreadsheet from your browser’s address bar into the shortcode. For example, to display the spreadsheet at https://docs.google.com/spreadsheets/d/ABCDEFG/edit#gid=123456, use the following shortcode in your WordPress post or page:

[gdoc key="https://docs.google.com/spreadsheets/d/ABCDEFG/edit#123456"]

If your spreadsheet uses the “old” Google Spreadsheets, you need to ensure that your spreadsheet is “Published to the Web” and you need to copy only the “key” out of the URL. For instance, if the URL of your old Google Spreadsheet is https://docs.google.com/spreadsheets/pub?key=ABCDEFG, then your shortcode should look like this:

[gdoc key="ABCDEFG"]

To create an interactive chart from your Spreadsheet’s data, use the chart attribute to a supported chart type. These include:

  • Area charts
  • Bar charts
  • Bubble charts
  • Candlestick charts
  • Column charts
  • Combo charts
  • Histogram charts
  • Line charts
  • Pie charts
  • Scatter charts
  • Stepped area charts

For example, if you have a Google Spreadsheet for a sports league that records the goals each team has scored (where the first column is the team name and the second column is their total goals), you can create a bar chart, with an optional title, from that data using a shortcode like this:

[gdoc key="ABCDEFG" chart="Bar" title="Total goals per team"]

To render an HTML table with additional metadata, such as supplying the table’s title, summary, <caption>, and a customized class value, you can do the following:

[gdoc key="ABCDEFG" class="my-sheet" title="Tooltip text displayed on hover" summary="An example spreadsheet, with a summary."]This is the table's caption.[/gdoc]

The above shortcode will produce HTML that looks something like the following:

<table id="igsv-ABCDEFG" class="igsv-table my-sheet" title="Tooltip text displayed on hover" summary="An example spreadsheet, with a summary.">
    <caption>This is the table's caption.</caption>
    <!-- ...rest of table code using spreadsheet data here... -->
</table>

You can also strip a certain number of rows (e.g., strip="3" omits the top 3 rows of the spreadsheet).

For “old” Google Spreadsheets, you can use the gid attribute to fetch data from a worksheet other than the first one (the one on the far left). For example, to display a worksheet published at https://spreadsheets.google.com/pub?key=ABCDEFG&gid=4, use the following shortcode in your WordPress post or page:

[gdoc key="ABCDEFG" gid="4"]

The header_rows attribute lets you specify how many rows should be rendered as the table header. For example, to render a worksheet’s top 3 rows inside the <thead> element, use:

[gdoc key="ABCDEFG" header_rows="3"]

Be default, all tables are progressively enhanced with jQuery DataTables to provide sorting, searching, and pagination functions on the table display itself. If you’d like a specific table not to include this functionality, use the no-datatables class in your shortcode. For instance:

[gdoc key="ABCDEFG" class="no-datatables"]

For DataTables-enhanced tables, you can also specify columns that you’d like to “freeze” when the user scrolls large tables horizontally. To do so, use the FixedColumns-left-N and FixedColumns-right-N classes, where N is the number of columns you’d like to freeze. For instance, to display the three left-most columns and the right-most column in a fixed (frozen) position, use the following in your shortcode:

[gdoc key="ABCDEFG" class="FixedColumns-left-3 FixedColumns-right-1"]

Web addresses and email addresses in your data are turned into links. If this causes problems, you can disable this behavior by specifying no to the linkify attribute in your shortcode. For instance:

[gdoc key="ABCDEFG" linkify="no"]

You can pre-process your Google Spreadsheet before retrieving data from it by passing a Google Charts API Query Language query to the shortcode’s query attribute. This lets you interact with the data in your Google Spreadsheet as though the spreadsheet were a relational database table. For instance, if you wish to display the team that scored the most goals on your website, you might use a shortcode like this to query your Google Spreadsheet and display the highest-scoring team:

[gdoc key="ABCDEFG" query="SELECT team WHERE max(goals)"]

Queries are also useful if your spreadsheet contains complex data from which many different charts can be created, allowing you to select only the parts of your spreadsheet that you’d like to use to compose the interactive chart.

Join the Conversation

107 Comments

  1. How should I use strip=”3″ ?

    Is it [gdoc key="ABCDEFG" strip="3"] ?

    And is there a way to show only the top rows, e.g. show the first two rows?

  2. I tried strip as in :
    [gdoc key="ABCDEFG" strip="3"] but that doesn’s strip anything? Using the latest WordPress (multisite config).

  3. I tried strip as in :
    [gdoc key="ABCDEFG" strip="3"] but that doesn’s strip anything? Using the latest WordPress (multisite config).

    I’m sure that it does, as I’m using that parameter on my sites, but I believe that you’re having trouble with it. You may consider double-checking that there aren’t any empty rows in your Google Spreadsheet that are being stripped out.

    Unfortunately, I can’t troubleshoot your issue for you. If you’d like to report a bug, feel free to submit an issue.

  4. LS
    Any reason why this key wouldn’t work?
    [gdoc key="tKGnpi2W1kHafUIQOwRsfMQ&hl=nl&authkey=CNvkgKMB#gid=0"]
    that’s how the spreadsheet named in my browser. Language should be omitted? Wrong configuration of WordPress?

    Thanks,
    Pim.

  5. Thanks Meitar.
    I didn’t know I had to publish the document to the web: that button is in the right corner of the documentscreen in pull downmenu sharing. I usually share document via another links just besides the title.
    In the end I used “TGN embed everything” plug in because it also allows me to embed privatie gDocs, and to embed youtube en other pages via iFrame.

    Thanks,
    Pim.

  6. I am trying to edit the column spacing. Currently there is no spacing between the columns. could you please tell me how to do this?

    Try using left and right padding on the cells themselves. To space the second column out from the first and third, for instance, this might work:

    .col-2 { padding-left: 3em; padding-right: 3em; }
  7. Hi Steen. :)

    I…hope that you will make another plugin for Google Calender. Do you think it would be possible ?

    Well, Google Calendar is pretty easily embed-able within any HTML page. See the Google help page about embedding a Calendar view in a web page. I’m unsure what a plugin would do that isn’t already possible with the free Google Calendar embed code. Can you let me know what you were hoping to accomplish that you can’t accomplish simply embedding the Calendar into your HTML pages?

    Thanks. :)

  8. is it possible to strip out a column like you can do with a row? If so, how do you do it?

    The plugin doesn’t have an option for it, but you can use CSS to achieve that effect. For instance, the following CSS rule will remove the fourth column from the page.

    .col-4 { display: none; }

    Hope that helps.

  9. Html links in my spreadsheet are not appearing. Is this a know error or is something wrong. I can see the links when viewing the google docs but nothing appears in website view

  10. Html links in my spreadsheet are not appearing. Is this a know error or is something wrong. I can see the links when viewing the google docs but nothing appears in website view

    This plugin doesn’t operate on any of the data inside the cells, Steven, which is why you aren’t seeing the URL’s appear as hyperlinks. That’s a good feature request though, because it’s a pretty safe operation, so I’d appreciate it if you can submit an issue to this effect on the issue tracker. :)

  11. Hi Meitar. Love the plugin, thanks. Would like also to have URLs working, as above. I’ve added it to the issue tracker as requested. Thanks, Stuart

  12. Any reason why an update to a published sheet would not display?

    Nope, Brian, no reason as far as the plugin is concerned. The plugin merely fetches whatever the most recent version is, as published by Google. Sometimes Google Docs does take a few minutes to refresh the published-to-the-web version of a “published” document, though. That may be what’s happening.

    In the future, I would like to see the plugin itself keep a cache of that “most recent” version, but as that’s not yet implemented, that can’t be the explanation for why you’re not seeing the most recent version of your spreadsheet when using the plugin.

  13. Great plug-in!

    Is there a way to select which colummns are displayed? I hid columns using css, but the data is still visible in the source code.

    Could I alter the php to only select columns I specify?

  14. I’m trying to display one sheet, and using
    key=”axxxxxxxx” gid=2], but I’m not getting the spreadsheet (it’s actually a lot of junk from google’s website that shows up). I’ve also tried gid=”2″ – doesn’t make a difference. How do I display a specific sheet? I must be missing something here. Thanks.

  15. Is it possible to display clickable multiple sheets?

    Not with this plugin yet. Patches welcome. :)

    Could I alter the php to only select columns I specify? Is there a way to select which colummns are displayed? I hid columns using css, but the data is still visible in the source code.

    If you’d like to change the PHP yourself, sure, go ahead. For now, CSS is what one’s got, and arguably all one should have.

    I’m not getting the spreadsheet (it’s actually a lot of junk from google’s website that shows up)

    Make sure the spreadsheet has been published.

  16. Hi Chris. You described an issue like this:

    when displaying on the webpage it appears without a currency symbol […] it seems to like dollar signs but not pound signs ??

    This is a frustrating problem because the problem is part of PHP’s mishandling of certain characters when they are in the first position in a new CSV cell and there is a mismatch between the current locale setting on your server and the character encoding in the file being read. If your server is running in, say, en_US.iso88591 but your file is encoded in, say, en_GB.utf8, then fgetcsv() (the native PHP function this plugin uses) will read local character symbols (like the GBP symbol, £) incorrectly when it is the first character in a new cell. See, for instance, this report of the problem as well as this one on a PHP user’s board.

    Now, one way to solve the problem is to find out which character encoding your spreadsheet uses (I looked for you; it’s currently en_GB.utf8), then find out which locale your PHP is currently running in (I can’t check this for you, but you can invoke setlocale(LC_CTYPE, “0”) to find out for yourself), and then make sure they match. This is a bit more work than I’m willing to put into the plugin right now, although you’re more than welcome to submit a feature request on the plugin issue tracker to this effect.

    Another workaround might be to surround the values in your spreadsheet in quotes so that the currency symbol isn’t the first character in the cells, and then strip or hide those quotes from your actual web page.

  17. Hi! I would like to use my table with only the 1,2,3 columns. Can I do it with this plugin? Thanks your help! The plugin is great! :)

  18. Hello,

    I have been using your plugin for development of a site locally and it works great but once I moved the site live I now get this error.

    Warning: fopen() [function.fopen]: URL file-access is disabled in the server configuration in /***/inline-gdocs-viewer.php on line 44

    Warning: fopen(https://spreadsheets.google.com/pub?key=0AhCN3DNjYldMdC1HTDVEZXZxemI1X1FXcTRWb1pyZXc&output=csv) [function.fopen]: failed to open stream: no suitable wrapper could be found in /***/inline-google-spreadsheet-viewer/inline-gdocs-viewer.php on line 44

    Any ideas on how to fix would be greatly appreciated.

    Thanks,
    Todd

  19. I would like to use my table with only the 1,2,3 columns. Can I do it with this plugin?

    Adri, use CSS to hide any columns you don’t want visible.

    I have been using your plugin for development of a site locally and it works great but once I moved the site live I now get this error.

    Warning: fopen() [function.fopen]: URL file-access is disabled in the server configuration in /***/inline-gdocs-viewer.php on line 44

    Warning: fopen(https://spreadsheets.google.com/pub?key=0AhCN3DNjYldMdC1HTDVEZXZxemI1X1FXcTRWb1pyZXc&output=csv) [function.fopen]: failed to open stream: no suitable wrapper could be found in /***/inline-google-spreadsheet-viewer/inline-gdocs-viewer.php on line 44

    Any ideas on how to fix would be greatly appreciated.

    You need to change the configuration of allow_url_fopen in your php.ini file, Todd.

  20. Got it working on my site, thanks a lot! It does what it is supposed to do. Even if your doc is public you need to share it as a webpage or it won’t work. But after I did that it worked fine and looks good with the CSS too. Awesome.

  21. I need to be able to use one big google spreadsheet (one column and 1000++ rows). Is there a way to narrow the displayed table to only cells containing specific words? What if I need to display results for a few different words?

  22. I love your plug-in. Our school just switched over to Google Apps for all staff/students. I am integrating it into the school website. Our athletics secretary has a multi-sheet spreadsheet with the schedules for every Fall Sport. I have a separate page for each sport schedule and tried including the gid to display only one sheet.

    For example, Girls Tennis: http://ehs.hbuhsd.edu/athletics/athletic-schedules/girls-tennis-schedule-all-levels looks like: [gdoc key="0Asfo0q9AvVdtdE9OekV3cFM0UW8tYkFqQlRBTmJDamc&single=true&gid=4"].

    and

    Field Hockey: http://ehs.hbuhsd.edu/athletics/athletic-schedules/fall-athletic-schedules/field-hockey looks like: [gdoc key="0Asfo0q9AvVdtdE9OekV3cFM0UW8tYkFqQlRBTmJDamc&single=true&gid=3"].

    Tennis has GID=4 and Field Hockey has GID=3. I published Field Hockey first and then when I published tennis, it changed the Field Hockey (GID=3) to the Tennis schedule (GID=4).

    How can I show separate sheets from the same spreadsheet without messing the other one up?

  23. doh. I got it. User error.

    I am having trouble with CSS. I want to style the tables a bit so every other row has a bg color and there are borders around the the table/cells. I tried “.igsv-table tr.odd { background-color: #xxxxxx; }” to no avail. Also, I couldn’t figure out what element (s) to add the border property to.

  24. Is there a way to give a spreadhseet/table a unique identifier for CSS?

    For example, we have Sport Schedules with several columns that we want to hide. All of our sports schedules have the same columns in the spreadsheet, so it was easy to .igsv .col-1, .col-8, .col-9, etc. {display: none; } to hide all of the columns that we didn’t need.

    However, we also have sports rosters, but they don’t have the same number of columns. So, my CSS is hiding columns I want to show for the rosters.

    Long story short, is there a way to designate .igsv#schedules and .igsv#rosters so that I can apply CSS only to certain ones?

  25. Hi Meitar,

    A great google spreadsheet importing plugin, thank you for your time and effort. I did have a question, but you’ve been kind enough to many users already!

    All the best,
    Chris, UK

  26. Is there a way to give a spreadhseet/table a unique identifier for CSS? […] Long story short, is there a way to designate .igsv#schedules and .igsv#rosters so that I can apply CSS only to certain ones?

    Each spreadsheet already has a unique identifier, haze: the Google Doc “key” used to access it. These aren’t very human-friendly names, but they do distinguish one spreadsheet from another. So you might do #igsv-ABDCEF for “schedules” and #igsv-GHIJKL for “rosters”. Re-read the plugin description, above, to refresh your memory about how this works.

    A great google spreadsheet importing plugin, thank you for your time and effort. I did have a question, but you’ve been kind enough to many users already!

    You’re welcome, Chris. If you have a question, though, do ask. I can’t be kind and try to answer it if you don’t give me the chance to. ;)

  27. Hello Meitar,

    I installed this plugin on my fresh installation of WordPress (v3.2.1). Is this version not supported (Plugin page WordPress –> Compatible v.3.0.1)?

    I get the same problem as several other people mention. However, I published the sheet over the web.
    I implemented this code: [gdoc key="0AksW9-Ch9KqxdHFUS25EamtEd0kxRG1VbjB6d1pfY1E"]
    The link to the sheet is this: https://docs.google.com/spreadsheet/ccc?key=0AksW9-Ch9KqxdHFUS25EamtEd0kxRG1VbjB6d1pfY1E&hl=nl#gid=0

    What I am doing wrong?

    Thanks in advance!!

  28. Hi Meitar,

    I am getting the same error Todd was getting. Where can I find this php.ini page? I am not sure where to find it or how to fix it.

    Cheers

    Nadine

  29. I added on the page:
    [gdoc key="0AqnOsk5qBibQdEZ6RXFKZmpTMFJZWDBkYUNXMFNkWFE" gid="0"]

    but nothing gets displayed

    Dunno what to tell you, Marko. That’s not a lot to go on. At the very least, make sure you’re not hitting a cache of your website instead of the live site.

    Pedro, the URL you’re mentioning isn’t a public webpage. Be certain you’ve published your spreadsheet to the Web using Google Spreadsheets first.

    I am getting the same error Todd was getting. Where can I find this php.ini page? I am not sure where to find it or how to fix it.

    Nadine, the php.ini file is part of PHP itself. Read the manual to learn how it works and where it is likely to be on your system.

Leave a comment

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

You can encrypt your comment so that only Meitar can read it.