Many of our digital signage Gadgets (such as the Google Spreadsheet and Directory Gadgets), require a Data Source URL setting in order to function. If you've used any of these Gadgets before, you probably already know how to get the value for this setting. Still, let's cover it here in detail and also go over some of the finer points of the Data Source URL's query parameters.
Getting the Data Source URL
The Data Source URL comes from the Google Spreadsheet from which the Gadget will get its data. It is NOT the address that you see in the browser's address bar. Rather, it can be found by doing the following:
- In the spreadsheet, select the specific cells whose data you want to be returned to the Gadget. If all of the data should be returned, then there is no need to highlight specific cells.
- From the spreadsheet toolbar, click Insert and choose Gadget. Choose an existing Gadget from the list. It really doesn't matter which one is selected as all we are doing is getting a URL to pass to the Rise Vision Gadget.
- Open the Gadget's menu by clicking the arrow in the title bar and select Get query data source url... from the drop-down.
- Choose either the Selected range or Entire sheet option, depending on whether you want to include only selected cells or the whole spreadsheet.
- If the data in your spreadsheet has a header, specify the number of rows that are header rows in the Number of header rows drop-down. These rows will be flagged as headers instead of being treated as rows of data.
- Copy the URL from Paste this as gadget data source url and paste it into the Data Source URL Gadget setting of our open source digital signage software.
wp-image-10537" title="Query Data Source" src="http://risevision.com/wp-content/uploads/2012/03/Query-Data-Source.png" alt="" width="424" height="304" />
If Number of header rows is not specified, the Auto option will be used. Auto means that the spreadsheet will try to guess how many rows are header rows. If all of the columns are string data, the spreadsheet might have difficulty in determining which rows are header rows. For that reason, it is considered a best practice to explicitly select a numeric value for Number of header rows, even if there are none (in which case the value would be 0).
You can quickly change the number of header rows directly in the Data Source URL Gadget setting instead of having to open the spreadsheet and repeat the above process. Just look for
headers= near the end of the URL and change the number that appears after
= to the new value.
If you make a mistake and specify an incorrect number of header rows, you will see strange results in the Gadget. This will likely involve data being incorrectly treated as a header, or a header being incorrectly treated as data.
Using a Different Sheet
You can also specify which sheet of a multi-sheet document to pull data from. This can be done by changing
gid=N in the Data Source URL, where
N is the ID number of the sheet. It is one less than the number in the sheet name when it is first created. For example,
gid=0 would reference Sheet1. The ID number can also be found by navigating to the published version of that sheet and looking for the
gid=N parameter in the URL.
Alternatively, you can use the
sheet=sheet_name parameter, where
sheet_name is the display name of the sheet. This would be used in place of
Note that the Google Spreadsheet must be made Public on the web in order for a Gadget to be able to read data from it.
Hopefully these tips help to clear up some of the mystery surrounding Data Source URLs. Use them wisely!