getWorksheets: Access the contents of a spreadsheet

Description

These functions provide access to the contents of a spreadsheet. They allow one to access the different worksheets within a spreadsheet and then access the values of cells within those worksheets. One can convert a worksheet, or a subset of it, into a data frame or matrix in R.

Having obtained a GoogleWorsheetRef, one can query its number of rows and columns directly (i.e. without an additional query to the Google server) and one can coerce the contents to data frame or matrix with as(sheetRef, "data.frame").

Usage

getWorksheets(doc, con = getGoogleDocsConnection(service = "wise"), stringsAsFactors = default.stringsAsFactors())
sheetAsMatrix(sheet, header = FALSE, as.data.frame = TRUE,  trim = TRUE, con = sheet@connection, doc = xmlParse(getCells(sheet, con = con)), stringsAsFactors = default.stringsAsFactors())

Arguments

doc

the name of the spreadsheet document or a GoogleDocument object obtained via a call to getDocs. In sheetAsMatrix this is the parsed XML document containing the cell values. This is typically computed via the default value of the parameter. But one can call getCells separately with non-default values for the parameters.

con

the authenticated connection to the Google Spreadsheets API. This must be for the "wise" service rather than the general "writely" service. So this should be an object of class "GoogleSpreadsheetsConnection" created with a call of the form getGoogleDocsConnection(login, password, "wise").

sheet

the sheet object of class GoogleWorksheetRef.

header

information about the column headers for the worksheet. This is a) a character vector giving the names of the resulting columns (after the empty columns have been discarded), b) a logical value of TRUE indicating whether the first row of the worksheet is to be treated as the header, and c) a logical value of FALSE indicating that there are no column headers available and so use V1, V2, ...

as.data.frame

a logical value indicating whether a matrix (FALSE) or a data frame (TRUE) is to be created

trim

a logical value indicating whether to drop the rows and columns of the worksheet that have only missing values, i.e. those that may be used for pseudo-formatting/layout. This is optional as there may be rows or columns that contain legitmate missing values. The algorithm for converting the cells to a table could be more intelligent about this and explicitly determine the rows and columns which have no values, not which contain NAs.

stringsAsFactors

a logical indicating whether to convert string variables to factors (TRUE), or leave as character vectors (FALSE).

Value

A list of the GoogleWorksheetRef objects. Each of these is an object of class GoogleWorksheetRef-class.The names of the sheets are used as the names of the returned R list object.

References

http://code.google.com/apis/spreadsheets/docs/2.0/developers\_guide\_protocol.html

Examples

Run this code

if(exists("GoogleDocsPassword")) {
      # getGoogleDocsConnection("my login", "my password", "wise")
  con = getGoogleDocsConnection(names(GoogleDocsPassword), GoogleDocsPassword, "wise")

  sheets = getWorksheets("TwoSheets", sheets.con)

  docs = getDocs(sheets.con)
  sheets = getWorksheets(docs[["TwoSheets"]], sheets.con)

  names(sheets)
}