Class: GoogleSpreadsheet::Spreadsheet

Inherits:
Object
  • Object
show all
Includes:
Util
Defined in:
lib/google_spreadsheet/spreadsheet.rb

Overview

A spreadsheet.

Use methods in GoogleSpreadsheet::Session to get GoogleSpreadsheet::Spreadsheet object.

Constant Summary

SUPPORTED_EXPORT_FORMAT = 
Set.new(["xls", "csv", "pdf", "ods", "tsv", "html"])

Instance Attribute Summary (collapse)

Instance Method Summary (collapse)

Methods included from Util

#concat_url, #encode_query, #h

Constructor Details

- (Spreadsheet) initialize(session, worksheets_feed_url, title = nil)

:nodoc:



22
23
24
25
26
27
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 22

def initialize(session, worksheets_feed_url, title = nil) #:nodoc:
  @session = session
  @worksheets_feed_url = worksheets_feed_url
  @title = title
  @acl = nil
end

Instance Attribute Details

- (Object) worksheets_feed_url (readonly)

URL of worksheet-based feed of the spreadsheet.



30
31
32
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 30

def worksheets_feed_url
  @worksheets_feed_url
end

Instance Method Details

- (Object) acl(params = {})

Returns GoogleSpreadsheet::Acl object for the spreadsheet.

With the object, you can see and modify people who can access the spreadsheet. Modifications take effect immediately.

Set params[:reload] to true to force reloading the title.

e.g.

# Dumps people who have access:
for entry in spreadsheet.acl
  p [entry.scope_type, entry.scope, entry.role]
  # => e.g. ["user", "example1@gmail.com", "owner"]
end

# Shares the spreadsheet with new people:
# NOTE: This sends email to the new people.
spreadsheet.acl.push(
    {:scope_type => "user", :scope => "example2@gmail.com", :role => "reader"})
spreadsheet.acl.push(
    {:scope_type => "user", :scope => "example3@gmail.com", :role => "writer"})

# Changes the role of a person:
spreadsheet.acl[1].role = "writer"

# Deletes an ACL entry:
spreadsheet.acl.delete(spreadsheet.acl[1])


271
272
273
274
275
276
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 271

def acl(params = {})
  if !@acl || params[:reload]
    @acl = Acl.new(@session, self.acl_feed_url)
  end
  return @acl
end

- (Object) acl_feed_url

ACL feed URL of the spreadsheet.



82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 82

def acl_feed_url
  orig_acl_feed_url = document_feed_entry.css(
      "gd|feedLink[rel='http://schemas.google.com/acl/2007#accessControlList']")[0]["href"]
  case orig_acl_feed_url
    when %r{^https?://docs.google.com/feeds/default/private/full/.*/acl$}
      return orig_acl_feed_url
    when %r{^https?://docs.google.com/feeds/acl/private/full/([^\?]*)(\?.*)?$}
      # URL of old API version. Converts to v3 URL.
      return "https://docs.google.com/feeds/default/private/full/#{$1}/acl"
    else
      raise(GoogleSpreadsheet::Error,
        "ACL feed URL is in unknown format: #{orig_acl_feed_url}")
  end
end

- (Object) add_worksheet(title, max_rows = 100, max_cols = 20)

Adds a new worksheet to the spreadsheet. Returns added GoogleSpreadsheet::Worksheet.



229
230
231
232
233
234
235
236
237
238
239
240
241
242
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 229

def add_worksheet(title, max_rows = 100, max_cols = 20)
  xml = <<-"EOS"
    <entry xmlns='http://www.w3.org/2005/Atom'
           xmlns:gs='http://schemas.google.com/spreadsheets/2006'>
      <title>#{h(title)}</title>
      <gs:rowCount>#{h(max_rows)}</gs:rowCount>
      <gs:colCount>#{h(max_cols)}</gs:colCount>
    </entry>
  EOS
  doc = @session.request(:post, @worksheets_feed_url, :data => xml)
  url = doc.css(
    "link[rel='http://schemas.google.com/spreadsheets/2006#cellsfeed']")[0]["href"]
  return Worksheet.new(@session, self, url, title)
end

- (Object) delete(permanent = false)

If permanent is false, moves the spreadsheet to the trash. If permanent is true, deletes the spreadsheet permanently.



139
140
141
142
143
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 139

def delete(permanent = false)
  @session.request(:delete,
    self.document_feed_url + (permanent ? "?delete=true" : ""),
    :auth => :writely, :header => {"If-Match" => "*"})
end

- (Object) document_feed_entry(params = {})

<entry> element of document list feed as Nokogiri::XML::Element.

Set params[:reload] to true to force reloading the feed.



111
112
113
114
115
116
117
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 111

def document_feed_entry(params = {})
  if !@document_feed_entry || params[:reload]
    @document_feed_entry =
        @session.request(:get, self.document_feed_url, :auth => :writely).css("entry")[0]
  end
  return @document_feed_entry
end

- (Object) document_feed_url

URL of feed used in document list feed API.



77
78
79
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 77

def document_feed_url
  return "https://docs.google.com/feeds/documents/private/full/spreadsheet%3A#{self.key}"
end

- (Object) duplicate(new_title = nil)

Creates copy of this spreadsheet with the given title.



120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 120

def duplicate(new_title = nil)
  new_title ||= (self.title ? "Copy of " + self.title : "Untitled")
  post_url = "https://docs.google.com/feeds/default/private/full/"
  header = {"GData-Version" => "3.0", "Content-Type" => "application/atom+xml"}
  xml = <<-"EOS"
    <entry xmlns='http://www.w3.org/2005/Atom'>
      <id>#{h(self.document_feed_url)}</id>
      <title>#{h(new_title)}</title>
    </entry>
  EOS
  doc = @session.request(
      :post, post_url, :data => xml, :header => header, :auth => :writely)
  ss_url = doc.css(
      "link[rel='http://schemas.google.com/spreadsheets/2006#worksheetsfeed']")[0]["href"]
  return Spreadsheet.new(@session, ss_url, new_title)
end

- (Object) export_as_file(local_path, format = nil, worksheet_index = nil)

Exports the spreadsheet in format as a local file.

format can be either ???xls???, ???csv???, ???pdf???, ???ods???, ???tsv??? or ???html???. If format is nil, it is guessed from the file name. In format such as ???csv???, only the worksheet specified with worksheet_index is exported.

e.g.

spreadsheet.export_as_file("hoge.ods")
spreadsheet.export_as_file("hoge.csv", nil, 0)


187
188
189
190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 187

def export_as_file(local_path, format = nil, worksheet_index = nil)
  if !format
    format = File.extname(local_path).gsub(/^\./, "")
    if !SUPPORTED_EXPORT_FORMAT.include?(format)
      raise(ArgumentError,
          ("Cannot guess format from the file name: %s\n" +
           "Specify format argument explicitly.") %
          local_path)
    end
  end
  open(local_path, "wb") do |f|
    f.write(export_as_string(format, worksheet_index))
  end
end

- (Object) export_as_string(format, worksheet_index = nil)

Exports the spreadsheet in format and returns it as String.

format can be either ???xls???, ???csv???, ???pdf???, ???ods???, ???tsv??? or ???html???. In format such as ???csv???, only the worksheet specified with worksheet_index is exported.



170
171
172
173
174
175
176
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 170

def export_as_string(format, worksheet_index = nil)
  gid_param = worksheet_index ? "&gid=#{worksheet_index}" : ""
  url =
      "https://spreadsheets.google.com/feeds/download/spreadsheets/Export" +
      "?key=#{key}&exportFormat=#{format}#{gid_param}"
  return @session.request(:get, url, :response_type => :raw)
end

- (Object) human_url

URL which you can open the spreadsheet in a Web browser with.

e.g. "spreadsheets.google.com/ccc?key=pz7XtlQC-PYx-jrVMJErTcg"



60
61
62
63
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 60

def human_url
  # Uses Document feed because Spreadsheet feed returns wrong URL for Apps account.
  return self.document_feed_entry.css("link[rel='alternate']")[0]["href"]
end

- (Object) inspect 



290
291
292
293
294
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 290

def inspect
  fields = {:worksheets_feed_url => self.worksheets_feed_url}
  fields[:title] = @title if @title
  return "\#<%p %s>" % [self.class, fields.map(){ |k, v| "%s=%p" % [k, v] }.join(", ")]
end

- (Object) key

Key of the spreadsheet.



43
44
45
46
47
48
49
50
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 43

def key
  if !(@worksheets_feed_url =~
      %r{^https?://spreadsheets.google.com/feeds/worksheets/(.*)/private/.*$})
    raise(GoogleSpreadsheet::Error,
      "Worksheets feed URL is in unknown format: #{@worksheets_feed_url}")
  end
  return $1
end

- (Object) rename(title) Also known as: title=

Renames title of the spreadsheet.



146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 146

def rename(title)
  doc = @session.request(:get, self.document_feed_url, :auth => :writely)
  edit_url = doc.css("link[rel='edit']").first["href"]
  xml = <<-"EOS"
    <atom:entry
        xmlns:atom="http://www.w3.org/2005/Atom"
        xmlns:docs="http://schemas.google.com/docs/2007">
      <atom:category
        scheme="http://schemas.google.com/g/2005#kind"
        term="http://schemas.google.com/docs/2007#spreadsheet" label="spreadsheet"/>
      <atom:title>#{h(title)}</atom:title>
    </atom:entry>
  EOS

  @session.request(:put, edit_url, :data => xml, :auth => :writely)
end

- (Object) spreadsheet_feed_entry(params = {})

<entry> element of spreadsheet feed as Nokogiri::XML::Element.

Set params[:reload] to true to force reloading the feed.



100
101
102
103
104
105
106
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 100

def spreadsheet_feed_entry(params = {})
  if !@spreadsheet_feed_entry || params[:reload]
    @spreadsheet_feed_entry =
        @session.request(:get, self.spreadsheet_feed_url).css("entry")[0]
  end
  return @spreadsheet_feed_entry
end

- (Object) spreadsheet_feed_url

Spreadsheet feed URL of the spreadsheet.



53
54
55
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 53

def spreadsheet_feed_url
  return "https://spreadsheets.google.com/feeds/spreadsheets/private/full/#{self.key}"
end

- (Object) tables

DEPRECATED: Table and Record feeds are deprecated and they will not be available after March 2012.

Returns list of tables in the spreadsheet.



282
283
284
285
286
287
288
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 282

def tables
  warn(
      "DEPRECATED: Google Spreadsheet Table and Record feeds are deprecated and they " +
      "will not be available after March 2012.")
  doc = @session.request(:get, self.tables_feed_url)
  return doc.css("entry").map(){ |e| Table.new(@session, e) }.freeze()
end

- (Object) tables_feed_url

DEPRECATED: Table and Record feeds are deprecated and they will not be available after March 2012.

Tables feed URL of the spreadsheet.



69
70
71
72
73
74
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 69

def tables_feed_url
  warn(
      "DEPRECATED: Google Spreadsheet Table and Record feeds are deprecated and they " +
      "will not be available after March 2012.")
  return "https://spreadsheets.google.com/feeds/#{self.key}/tables"
end

- (Object) title(params = {})

Title of the spreadsheet.

Set params[:reload] to true to force reloading the title.



35
36
37
38
39
40
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 35

def title(params = {})
  if !@title || params[:reload]
    @title = spreadsheet_feed_entry(params).css("title").text
  end
  return @title
end

- (Object) worksheet_by_title(title)

Returns a GoogleSpreadsheet::Worksheet with the given title in the spreadsheet.

Returns nil if not found. Returns the first one when multiple worksheets with the title are found.



224
225
226
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 224

def worksheet_by_title(title)
  return self.worksheets.find(){ |ws| ws.title == title }
end

- (Object) worksheets

Returns worksheets of the spreadsheet as array of GoogleSpreadsheet::Worksheet.



203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
 
# File 'lib/google_spreadsheet/spreadsheet.rb', line 203

def worksheets
  doc = @session.request(:get, @worksheets_feed_url)
  if doc.root.name != "feed"
    raise(GoogleSpreadsheet::Error,
        "%s doesn't look like a worksheets feed URL because its root is not <feed>." %
        @worksheets_feed_url)
  end
  result = []
  doc.css("entry").each() do |entry|
    title = entry.css("title").text
    url = entry.css(
      "link[rel='http://schemas.google.com/spreadsheets/2006#cellsfeed']")[0]["href"]
    result.push(Worksheet.new(@session, self, url, title))
  end
  return result.freeze()
end