Inline Google Spreadsheet Viewer

Screenshots

Easily turn data stored in a Google Spreadsheet, CSV file, MySQL database, or the output of a Google Apps Script into a beautiful interactive chart or graph, a sortable and searchable table, or both. Embed live previews of PDF, XLS, DOC, and other file formats supported by the Google Docs Viewer. A built-in cache provides extra speed. Update your blog post or page whenever a Google Spreadsheet or CSV file changes. Create beautiful interactive graphs and charts from your Google Spreadsheet or CSV files with ease. Customize the table’s or chart’s look and feel using a powerful and flexible query language and a plethora of configuration options. Use data from a variety of different sources: Google Spreadsheets, Google Apps Scripts, CSV files, your WordPress database, or a remote MySQL database. Embed almost any online document to view without leaving your blog. Donations for this plugin make up a chunk of my income. If you continue to enjoy this plugin, please consider making a donation. ðŸ™? Thank you for your support! Quick start Paste the URL of your public Google Spreadsheet or Google Apps Script Web App on its own line in your WordPress post or page, then save your post. That’s it. ðŸ™? Your data will appear in a sorted, searchable HTML table. Web App output will be displayed using the HTML defined by the Web App. See the screenshots for an example. If using a Google Spreadsheet, the spreadsheet must be shared using either the “Public on the web” or “Anyone with the link” options (learn how to share your spreadsheet). Currently, private Google Spreadsheets or Spreadsheets shared with “Specific people” are not supported. Web Apps must be deployed with the “Anyone, even anonymous” access permissions. CSV files must be available to the public, without the need to log in to the site where they’re hosted. User guide You can transform your spreadsheet into an interactive chart or graph, embed documents other than spreadsheets, and customize the HTML of your table using a [gdoc key=""] WordPress shortcode. The only required parameter is key, which specifies the document you’d like to retrieve. All additional attributes are optional. Google Spreadsheets After saving the appropriate Sharing setting, copy the URL you use to view the Google 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#gid=123456"] CSV files Using a CSV file works the same way as Google Spreadsheets. Set the key to the URL of the file to display it as an HTML table: [gdoc key="http://example.com/research_data.csv"] HTML Tables Customizing the HTML tables that are produced is easy. For instance, to supply 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> By 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"] 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"] Each table can be customized per-table, using shortcode attributes, or globally for your entire site, using the plugin’s settings screen. You can freeze the table header, columns, control pagination length, and more. Refer to the Other Notes section for a full listing of supported customization attributes. Charts Data from Google Spreadsheets or CSV files can be graphed in interactive charts. To visualize your data as a chart, add the chart attribute to your shortcode and supply a supported chart type. You can make: AnnotatedTimeLine charts Annotation charts Area charts Bar charts Bubble charts Candlestick charts Column charts Combo charts Gauge charts Geo charts Histogram charts Line charts Pie charts Scatter charts Stepped area charts Timeline charts For example, if you have data 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"] You can customize your chart with a number of options, such as colors. For example, to create a 3D red and green pie chart whose slices are labelled with your data’s values: [gdoc key="ABCDEFG" chart="Pie" chart_colors="red green" chart_dimensions="3" chart_pie_slice_text="value"] Pre-processing data with Google Queries You can pre-process your Google Spreadsheets or CSV files before retrieving data from them 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 or CSV file as though it 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, where the team name is the first column (column A) and that team’s score is the second column (column B): [gdoc key="ABCDEFG" query="select A where max(B)"] 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. Using a MySQL Database After an administrator enables the SQL queries option in the plugin’s settings screen, privileged users can also retrieve data from the WordPress database by supplying the keyword wordpress to the key attribute of your shortcode along with a valid MySQL SELECT statement. This can be useful for displaying information that other plugins save in your website’s database or that WordPress itself maintains for you. For example, to show a table of user registration dates from the current blog: [gdoc key="wordpress" query="SELECT display_name AS Name, user_registered AS 'Registration Date' FROM wp_users"] Remote MySQL databases are also accessible by supplying a MySQL connection URL with valid access credentials. For example, to show the prices from an inventory database hosted by a MySQL server at server.example.com by logging in as user with the password password and querying for items that are in stock: [gdoc key="mysql://user:password@server.example.com/inventory" query="SELECT sku AS 'Item No.', product_name AS Product, price AS Price WHERE in_stock=TRUE"] Using Google Apps Script Web Apps You can also supply the URL endpoint of any Google Apps Script Web App to retrieve the output from that app and insert it directly into your WordPress post or page. This works exactly the same way as Google Spreadsheets do, so you can use this feature to display arbitrary data on your WordPress site. For example, suppose you maintain a GMail account for fans of your podcast to write you questions, and you want to automatically display some information from these emails on your website. Using GMail filters and labels, you can access these emails through a Google Apps Script that reads your email, counts the number of mail messages in your different labels, and returns that count as an HTML list fragment. Deploy that Google Apps Script as a Web App and supply its URL to the gdoc shortcode: [gdoc key="https://script.google.com/macros/s/ABCDEFG/exec"] Now your website is automatically updated whenever you receive a new question in email from your listeners. Embedding other documents You can also supply the URL of any file online to load a preview of that file on your blog. To do so, supply the file’s URL as your key: [gdoc key="http://example.com/my_final_paper.pdf"] To tweak the way your preview looks, you can use the width, height, or style attributes: [gdoc key="http://example.com/my_final_paper.pdf" style="min-height:780px;border:none;"] If you like this plugin, please consider making a donation for your use of the plugin, purchasing one of Meitar’s web development books or, better yet, contributing directly to Meitar’s Cyberbusking fund. (Publishing royalties ain’t exactly the lucrative income it used to be, y’know?) Your support is appreciated! Shortcode attribute documentation This plugin provides one shortcode (gdoc) that can do many things through a combination of shortcode attributes. Every attribute must have a value. These attributes and their recognized values are documented here. key – Specifies the document to retrieve. required Every gdoc shortcode must have one and only one key attribute. (All other attributes are optional.) key can be one of six types: The fully-qualified URL of a Google Spreadsheet that has been publicly shared, like [gdoc key="https://docs.google.com/spreadsheets/d/ABCDEFG/htmlview#gid=123456"] The fully-qualified URL of a Google Apps Script Web App, like [gdoc key="https://script.google.com/macros/s/ABCDEFG/exec"] The fully-qualified URL of a CSV file or a web service endpoint that produces CSV data, like [gdoc key="http://viewportsizes.com/devices.csv"] The fully-qualified URL of a document on the Web. PDF, DOC, XLS, and other file formats supported by the Google Docs Viewer will be rendered using the Viewer, like [gdoc key="http://example.com/my_final_paper.pdf"] The keyword wordpress to make a SQL query against the current blog’s database, like [gdoc key=”wordpress” query=”SELECT * FROM custom_table”]` A MySQL connection URL to make a SQL query against an arbitrary MySQL server, like [gdoc key="mysql://user:password@server.example.com:12345/database" query="SELECT * FROM custom_table"] chart – Displays Google Sheet data as a chart instead of a table. Valid values are: Area Bar Bubble Candlestick Column Combo Gauge Histogram Line Pie Scatter Stepped class – An optional custom HTML class value or space-separated list of values. The following class names are treated specially: no-datatables deactivates all DataTables features. no-responsive deactivates only DataTables’ Responsive features. FixedHeader or its synonym, FixedHeader-top freezes the table header (its <thead> content) to the top of the window while scrolling vertically. FixedHeader-footer freezes the table footer (its <tfoot> content) to the bottom of the window while scrolling vertically. FixedHeader-left or FixedHeader-right freezes the left- or right-most column of the table while scrolling horizontally. (You will also need to set datatables_scroll_x="true" in your shortcode to enable horizontal scrolling.) FixedColumns-left-N or FixedColumns-right-N freezes the left- or right-most N columns in the table, respectively. expire_in – How long to cache responses from Google for, in seconds. Set to 0 to cache forever. (Default: 600, which is ten minutes.) footer_rows – A number specifying how many trailing rows to place in the output’s <tfoot> element. (Default: 0.) header_cols – A number specifying how many column cells should be written with <th> elements. (Default: 0.) header_rows – A number specifying how many leading rows to place in the output’s <thead> element. (Default: 1.) height – Height of the containing HTML element. Tables ignore this, use style instead. (Default: automatically calculated.) http_opts – A JSON string representing options to pass to the WordPress HTTP API, like [gdoc key="ABCDEFG" http_opts='{"method": "POST", "blocking": false, "user-agent": "My Custom User Agent String"}']. lang – The ISO 639 language code declaring the human language of the spreadsheet’s contents. For instance, use nl-NL to declare that content is in Dutch. (Default: your site’s global language setting.) linkify – Whether or not to automatically turn URLs, email addresses, and so on, into clickable links. Set to no to disable this behavior. (Default: true.) query – A Google Query Language query if the data source is a Google Spreadsheet or CSV file, or a SQL SELECT statement if the data source is a MySQL database. Note: Arrow bracktets (< and >) in queries must be URL-encoded (%3C and %3E, respectively) to avoid confusing the WordPress HTML parser. (Default: none.) strip – The number of leading rows to omit from the resulting HTML table. (Default: 0.) style – An inline CSS rule applied to the containing HTML element. For example, to set a fixed height on a table, use [gdoc key="ABCDEFG" style="height: 480px;"]. (Default: none.) summary – A brief description of the information displayed for the summary attribute of the resulting HTML <table>. (Default: Google Spreadsheet.) title – An optional title for your data visualization or table. This is usually displayed in Web browsers as a tooltip when a user hovers over the table or is shown as the headline of a chart. (Default: none.) use_cache – Whether or not to cache spreadsheet data. Set this to no to disable caching for that shortcode invocation. (Default: true.) width – Width of the containing HTML element. Tables ignore this, use style instead. (Default: 100%.) Chart customization options To use chart customization options, you must also choose a chart type by including the chart attribute. The complete list of attributes for configurable chart options is below. Refer to Google’s Chart Gallery documentation for the type of chart you are using to learn more about which chart types support which chart options. chart_aggregation_target chart_all_values_suffix chart_allow_html chart_allow_redraw chart_animation chart_annotations chart_annotations_width chart_area_opacity chart_avoid_overlapping_grid_lines chart_axis_titles_position chart_background_color chart_bars chart_bubble chart_candlestick chart_chart_area chart_color_axis chart_colors chart_crosshair chart_curve_type chart_data_opacity chart_dataless_region_color chart_date_format chart_default_color chart_dimensions chart_display_annotations chart_display_annotations_filter chart_display_date_bar_separator chart_display_exact_values chart_display_legend_dots chart_display_legend_values chart_display_mode chart_display_range_selector chart_display_zoom_buttons chart_domain chart_enable_interactivity chart_enable_region_interactivity chart_explorer chart_fill chart_focus_target chart_font_name chart_font_size chart_force_i_frame chart_green_color chart_green_from chart_green_to chart_h_axes chart_h_axis chart_height chart_highlight_dot chart_interpolate_nulls chart_is_stacked chart_keep_aspect_ratio chart_legend chart_line_width chart_magnifying_glass chart_major_ticks chart_marker_opacity chart_max chart_min chart_minor_ticks chart_number_formats chart_orientation chart_pie_hole chart_pie_residue_slice_color chart_pie_residue_slice_label chart_pie_slice_border_color chart_pie_slice_text chart_pie_slice_text_style chart_pie_start_angle chart_point_shape chart_point_size chart_red_color chart_red_from chart_red_to chart_region chart_resolution chart_reverse_categories chart_scale_columns chart_scale_format chart_scale_type chart_selection_mode chart_series chart_size_axis chart_slice_visibility_threshold chart_slices chart_table chart_theme chart_thickness chart_timeline chart_title_position chart_title_text_style chart_tooltip chart_trendlines chart_v_axes chart_v_axis chart_width chart_wmode chart_yellow_color chart_yellow_from chart_yellow_to chart_zoom_end_time chart_zoom_start_time DataTables customization options To use DataTables customization options, you must not supply the no-datatables class. The complete list of core DataTables customization attributes is below. Please refer to the DataTables Options reference for more information about each particular option. datatables_auto_width datatables_defer_render datatables_info datatables_j_query_UI datatables_length_change datatables_ordering datatables_paging datatables_processing datatables_scroll_x datatables_scroll_y datatables_searching datatables_server_side datatables_state_save datatables_ajax datatables_data datatables_defer_loading datatables_destroy datatables_display_start datatables_dom datatables_length_menu datatables_order_cells_top datatables_order_classes datatables_order datatables_order_fixed datatables_order_multi datatables_page_length datatables_paging_type datatables_renderer datatables_retrieve datatables_scroll_collapse datatables_search_cols datatables_search_delay datatables_search datatables_state_duration datatables_stripe_classes datatables_select datatables_tab_index datatables_column_defs datatables_columns In addition to the above, the following included DataTables extensions can be customized through these additional shortcode attributes: datatables_buttons for customizing the DataTables Buttons extension Plugin hooks This section documents hooks that the plugin implements. Developers of other plugins or themes can use these in their code to customize the way this plugin works. Filters gdoc_table_html – Filters the Google Spreadsheet data after it has been converted to an HTML <table> element. Some notes about this filter: The most common use for this filter is to use html_entity_decode() to allow the data source to include raw HTML that will be displayed. This is considered a potential security risk and so is not recommended unless you are absolutely sure you need this functionality. In the majority of cases where users assume they need this functionality, it turns out there are other, more preferable alternatives, despite its convenience. Another related use case for this filter is to allow WordPress shortcodes that are present in the data source to be evaluated at runtime. See this thread for a brief discussion of that use case. However, this can also be problematic and is not recommended unless you are certain the shortcodes being used will not cause issues like invalid and broken markup, since most shortcode functions do not expect to be inside of an HTML <table>. This filter runs immediately after HTML conversion is complete, but before that HTML is processed through the make_clickable() function. This means that the value of the linkify shortcode attribute will affect the ultimate output of the shortcode invocation regardless of your filter function, and also means you should not call make_clickable() yourself. gdoc_viewer_html – Same as above, but applied to the <iframe> that loads the Google Docs Viewer. Use this filter to, for intance, customize the fallback content in the case that the user’s browser does not support <iframe> elements. gdoc_webapp_html – Same as above, but applied to the HTTP response body of the Google Apps Script Web App. Use this filter to, for intance, customize the content returned by your GAS Web App similarly to how you might filter the_content of a WordPress post. The first argument is the HTTP response body of the request. The second argument is an array of all the attributes and their values passed to the current invocation of the shortcode. gdoc_query – Filters the Google Visualization API query language query. The first argument is the string supplied to the query attribute, or false if no query was supplied. The second argument is an array of all the attributes and their values passed to the current invocation of the shortcode. A common use case for this filter is to query a Google Spreadsheet using dynamically generated content, such as the email address or username of a logged-in user. gdoc_enqueued_front_end_styles – An array in the form $handle => array(...) representing parameters to pass to wp_enqueue_style(). This filter lets you unset() stylesheets to prevent the plugin from enqueueing them. Use this to tweak your site’s performance by removing any stylesheets you know you will not need. gdoc_enqueued_front_end_scripts – An array in the form $handle => array(...) representing parameters to pass to wp_enqueue_script(). This filter lets you unset() JavaScript scripts to prevent the plugin from enqueuing them. Use this to tweak your site’s performance by removing any scripts you know you will not need. Registered script and stylesheet handles You can selectively dequeue any script or stylesheet this plugin adds by using the gdoc_enqueued_front_end_* filters to remove the scripts with the associated handle. The registered handles are listed here. Scripts jquery-datatables datatables-buttons datatables-buttons-colvis datatables-buttons-print pdfmake pdfmake-fonts jszip datatables-buttons-html5 datatables-select datatables-fixedheader datatables-fixedcolumns datatables-responsive igsv-datatables google-ajax-api igsv-gvizcharts Stylesheets jquery-datatables datatables-buttons datatables-select datatables-fixedheader datatables-fixedcolumns datatables-responsive