This new feature lets you get information from filetypes such as xml, html, csv, tsv, as well as RSS and Atom feeds that you might read today in Google Reader.
Please note: The functions listed here can only read publicly available files by default. Data in other spreadsheets can only be referenced after the relevant spreadsheet has been published. If a file is behind a corporate firewall, the firewall administrator will need to provide Google with access using Secure Data Connector.
Additionally, the limit on functions per spreadsheet is 50.
Functions:
=importXML("URL";"query")
- URL - the URL of the XML or HTML file
- query - the XPath query to run on the data given at the URL. For example, "//a/@href" returns a list of the href attributes of all <a> tags in the document (i.e. all of the URLs the document links to). Each result from the XPath query is placed in its own row of the spreadsheet. For more information about XPath, please visit http://www.w3schools.com/xpath/
- Example: =importXml("www.google.com"; "//a/@href"). This returns all of the href attributes (the link URLs) in all the <a> tags on www.google.com homepage
=importData("URL")
- URL = the URL of the CSV or TSV file. This imports a comma- or tab-separated file.
=ImportHtml(URL; "list" | "table"; index). This imports the data in a particular table or list from an HTML page. The arguments to the function are as follows:
- URL - the url of the HTML page
- either "list" or "table" to indicate what type of structure to pull in from the webpage. If it's "list," the function looks for the contents of <UL>, <OL>, or <DL> tags; if it's "table," it just looks for <TABLE> tags.
- index - the 1-based index of the table or the list on the source web page. The indices are maintained separately so there might be both a list #1 and a table #1.
- Example: =ImportHtml("http://en.wikipedia.org/wiki/Demographics_of_India"; "table";4). This function returns demographic information for the population of India.
=ImportFeed(URL; [feedQuery | itemQuery]; [headers]; [numItems]). This function imports an RSS or ATOM feed, just as you can in Google Reader. The arguments to the function are as follows:
- URL - the url of the RSS or ATOM feed
- feedQuery/itemQuery - one of the following query strings: "feed", "feed title", "feed author", "feed description", "feed url", "items", "items author", "items title", "items summary", "items url", or "items created". The feed queries return properties of the feed as a whole: the feed's title, the feed's author, etc. Note: To get the data included in the feed, you need to do an "items" request.
- the "feed" query returns a single row with all of the feed information.
- the "feed <type>" query returns a single cell with the requested feed information.
- the "items" query returns a full table, with all of the item information about each item in the feed.
- the "items <type>" query returns a single column with the requested information about each item.
- if a query is given that begins with "feed", the numItems parameter isn't necessary and is replaced by the option headers param.
- if a query is given that begins with "items", the numItems parameter is expected as the 3rd param, and headers as the 4th.
- headers - "true" if column headers is desired. This will add an extra row to the top of the output labeling each column of the output.
- Example: =ImportFeed("http://news.google.com/?output=atom")