The Google Spreadsheet Widget is used to display the contents of a Google Spreadsheet. It cannot be used to display the contents of another type of spreadsheet. e.g. Microsoft Excel.
To display the contents of a Google Spreadsheet, the Spreadsheet must be shared publicly. Please see the article How Do I Make a Google Spreadsheet Public?
Add and Select a Spreadsheet
Add the Google Spreadsheet Widget to a Placeholder
- Click More(1)
- In the search bar type “spreadsheet”.
- Click Google Spreadsheet, the Google Spreadsheet Settings window opens.
Select the Spreadsheet You Want to Use
- Click Select Spreadsheet. The Select a File window opens.
- In the Select a file search bar, type the name of the Spreadsheet you would like to display.
- Click the Spreadsheet that you would like to use.
- Click Select to return to the Google Spreadsheet Settings.
Select a Worksheet
- In the Google Spreadsheet Window, click the Select Worksheet list.
- Select the Worksheet you would like to display. All Worksheets in your spreadsheet appear in the list. If the Spreadsheet only has one Worksheet, only that Worksheet will appear in the list.
Format the Worksheet Content to Display
Use the First Row as a Header
Use this option if you want to use the first row of content in the worksheet as a header.
- Click Use First Row as Header.
- The Header Format section is added to the Google Spreadsheet Settings window.
Format the Header
If you chose to use the first row of the worksheet as a header, you can format the header here. The formatting specified here will affect only the content in the first row of your Spreadsheet.
- Scroll down in the Google Spreadsheet Settings window to the Header Format section.
- Use the format toolbar to select the font family, size, alignment, and style, in this example, the formatting specified is the Tahoma font at 36 pixel font size, and bold format.
After saving the changes and your Presentation, the Spreadsheet will appear in Preview and on a Display like the example below, notice how only the Header row has custom formatting, and all following rows use default formatting.
Show an Entire Sheet vs. Show a Range
You have flexibility to choose what content from the Worksheet you display. You can display an entire sheet, or you can show a specific range from a sheet.
- To show entire sheet: Click Show Entire Sheet.
- To specify a range on a sheet: Click Show Range.
If you select Show Range, and set that range to A1 - C5, after saving your Presentation the Spreadsheet will appear in Preview and on a Display like the example below. Notice how only the cell range displays.
Customize the Data Refresh Interval
The Data Refresh Interval controls how often the data from the spreadsheet is refreshed in your Presentation and on the Display it appears on.
A custom Data Refresh Interval can be configured if your Spreadsheet is updated often and you need your Presentation to reflect those updates more often than the 60 minute default. To change the default refresh interval of 60 seconds, you will need an API key. To generate an API key see this article.
- Paste or type the API Key you generated it into the API key field.
- In the Refresh Interval field, type the refresh interval you would like to use.
If you have more content in your Spreadsheet than can be shown at one time on the Display, you can select from multiple scrolling options to display all of your Spreadsheet content.
By default, the Spreadsheet Widget will not scroll the content in a Spreadsheet. You must set up the scrolling options.
- Selecting Continuously will scroll through your content stopping only when is reaches the bottom of your Spreadsheet content.
Example of Continuously scrolling.
- Selecting By Page will scroll through your content stopping at each new page of cells that have not been displayed.
Example of By Page scrolling.
Set the Scrolling Properties
When either Continuously or By Page is selected, the scrolling properties menu will appear.
- Speed controls the scrolling speed. Options available are Slow, Medium, or Fast.
- Pause affects Continuously and By page differently.
- If a Continuous scrolling Spreadsheet is a part of an Interactive Presentation, when the Spreadsheet is touched, it will stop scrolling for the amount of time entered in the Pause field.
- Defining Pause on a Spreadsheet that is scrolling By Page determines the amount of time that the scrolling stops on each new page.
- PUD (Play Until Done) Failover determines the amount of time the Spreadsheet will appear on your Display when there are other items in the Presentation Playlist or Schedule and there is also not enough content in the Spreadsheet to trigger scrolling. See our article Play Until Done for more information on how PUD works.
Format the Data Cells
In the Format section, you customize how the data cells and separators are displayed.
- Row Height specifies the pixel height of the cells displayed in the Presentation are. This is useful if a larger font size is being used.
- Odd and Even Row Color specify the background color of cells on an alternating basis. Background color and transparency can be specified by either entering the rgba color code, or using the color palette and transparency selector.
- Checking or unchecking Show Separator Between Rows and Columns will either show or hide the separators between rows and columns. Color and transparency can be specified by either entering the rgba color code, or using the color palette and transparency selector.
See this example demonstrating a 90 pixel row height, even and odd row colors, and separator color.
Format Cell Content
The Body Format section allows you to specify the font family, size, alignment, and style for displaying the spreadsheet content (minus the Header row).
- You can set font, pixel size, alignment, font color, bold, italic, and underline options.
- The Data Sample Text field will preview the font styles that you select.
Select Individual Columns to Format
The Per Column Format section allows you to to specify the font styles and alignment for individual columns. Individual column formatting overrides any formatting that is specified in the Header Format and Body Format sections.
- Click the column list to select which columns to format.
- Once you select columns to format, a list of the columns you selected will appear.
- Select the arrow next to the first column you would like to format and the formatting options will open.
Format Individual Columns
In the formatting options you can select the font, pixel size, alignment, font color, bold, italic, and underline of the text in the column.
- In the formatting options you can select the font, pixel size, alignment, font color, bold, italic, and underline of the text in the column.
- In Header Text you can specify a unique column header message.
- In Width you can specify a unique column width.
See this example of column A set to display a 14 pixel bold font with a blue background, and the column width is 100 pixels.
Format Numeric Columns Using Color Conditions
Conditional color formatting allows you to change the color of numeric data based on a condition. For example, if a data value increases, display it in green and if it decreases display it in red.
In order for the Spreadsheet Widget to recognize conditional numerical data in your Spreadsheet, the cells will need to be formatted as numbers.
- If you are displaying Numeric data in your Spreadsheet, and you would like colors to change conditionally, click the box next to Numeric data column.
- Once selected, the Color Conditions field will be accessible.
- Change up/down allows conditional coloring of text based on the status of the current and last number in the cell.
- If you select Change up green change down red, the text in a cell that has a last value of 16 and a current value of 22 would appear in green
- The Positive/Negative selection allows conditional coloring of text in cells based on the value of the cell.
- If you select Positive value green negative red and a cell contains the number 101, it would appear in green, while -62 would appear in red
See this example. Positive values display in green, while negative values display in red.