This tutorial shows how to import and display data in Google Spreadsheets. The first section uses public data so that you can test this use of Spreadsheets. The second section requires that you first install Secure Data Connector (SDC) as described in Installing, which requires additional time and resources to complete. Thereafter, you can test the use of SDC and Spreadsheets using data that is private to your network and resides on a web server in your corporate network.
Importing Public Data to Google Spreadsheets
You can import a data source to a spreadsheet as follows:
- Create or open Google Spreadsheets and click in any cell.
- Enter an equal sign (
=) and enter the following
importDatacommand to import data from a web site. Google provides test data for this tutorial as a comma-separated value (CSV) file:
The spreadsheet lists the function names as you type:
After you enter the
importDatacommand, Google Spreadsheets requests the data from the file and loads the information into the spreadsheet.
The spreadsheet then lists the contents of the CSV file:
Importing Private Data to Google Spreadsheets
The next section imports private data. Private data means data that resides behind a firewall and is accessed with a URL from a web server in your intranet.
Note: This section assumes you have Installed the SDC. If SDC is not currently installed, you should complete that activity before returning to this tutorial to complete the remaining sections.
After completing an import using a public feed source, create a private data file by installing Secure Data Connector (SDC), copying the CSV file to a web server on your network, and then creating a spreadsheet by importing the data file from your network.
Note: Comma-separated value (CSV) files must have a
.csv file type to work correctly with Google Spreadsheets.
To import private data:
- Download the Google CSV file and put the CSV file on
a web server in your corporate network.
You can also use a CSV file that you create by exporting a Google Spreadsheet as a CSV file:
Create a rule in
resourceRules.xmlfor the location and access of the CSV file (or add the
<rule>code block to the existing
resourceRules.xmlfile). See Configuring for more information. For example, if Polly Hedra wants to access the CSV file, specify the following resource rule:
<resourceRules> <rule repeatable="true"> <ruleNum>1</ruleNum> <agentId>sdc42-agent</agentId> <viewerEmail repeatable="true">firstname.lastname@example.org</viewerEmail> <url>http://corp.example.com/contacts.csv</url> <urlMatch>URLEXACT</urlMatch> <apps> <service>Spreadsheets</service> <allowAnyAppId>true</allowAnyAppId> </apps> </rule> </resourceRules>
SDC uses resource rules to limit which users can access which corporate resources. The
<resourceRules>element defines a source of information that is made available from a web server. The
<rule>element defines what resources or applications that a Google App can access. This rule states that this is the first resource rule to be processed (by the
<ruleNum>1statement), that Polly Hedra may access the
- Perform the import again using Google Spreadsheets and the URL of the CSV file on your
corporate network. In the previous example, the command would be:
The data appears just the same as when you imported public data.