Class: GoogleDrive::Spreadsheet

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

Overview

A spreadsheet.

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

Constant Summary collapse

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

Constants included from Util

Util::DOCS_BASE_URL, Util::EXT_TO_CONTENT_TYPE

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Util

concat_url, encode_query, h, to_v3_url

Methods inherited from File

#acl, #acl_feed_url, #available_content_types, #delete, #download_to_file, #download_to_string, #rename, #resource_id, #resource_type, #update_from_file, #update_from_io, #update_from_string

Constructor Details

#initialize(session, worksheets_feed_url, title = nil) ⇒ Spreadsheet

:nodoc:



25
26
27
28
29
 
# File 'lib/google_drive/spreadsheet.rb', line 25

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

Instance Attribute Details

#worksheets_feed_url ⇒ Object (readonly)

URL of worksheet-based feed of the spreadsheet.



32
33
34
 
# File 'lib/google_drive/spreadsheet.rb', line 32

def worksheets_feed_url
  @worksheets_feed_url
end

Instance Method Details

#add_worksheet(title, max_rows = 100, max_cols = 20) ⇒ Object

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



195
196
197
198
199
200
201
202
203
204
205
206
207
208
 
# File 'lib/google_drive/spreadsheet.rb', line 195

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

#document_feed_entry(params = {}) ⇒ Object

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

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



97
98
99
100
101
102
103
 
# File 'lib/google_drive/spreadsheet.rb', line 97

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

#document_feed_url ⇒ Object

URL of feed used in document list feed API.



79
80
81
 
# File 'lib/google_drive/spreadsheet.rb', line 79

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

#download_to_io(io, params = {}) ⇒ Object

Raises:

  • (NotImplementedError) 


159
160
161
162
163
164
165
 
# File 'lib/google_drive/spreadsheet.rb', line 159

def download_to_io(io, params = {})
  # General downloading API doesn't work for spreadsheets because it requires a different
  # authorization token, and it has a bug that it downloads PDF when text/html is
  # requested.
  raise(NotImplementedError,
      "Use export_as_file or export_as_string instead for GoogleDrive::Spreadsheet.")
end

#duplicate(new_title = nil) ⇒ Object

Creates copy of this spreadsheet with the given title.



106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'lib/google_drive/spreadsheet.rb', line 106

def duplicate(new_title = nil)
  new_title ||= (self.title ? "Copy of " + self.title : "Untitled")
  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, DOCS_BASE_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

#export_as_file(local_path, format = nil, worksheet_index = nil) ⇒ Object

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)



144
145
146
147
148
149
150
151
152
153
154
155
156
157
 
# File 'lib/google_drive/spreadsheet.rb', line 144

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

#export_as_string(format, worksheet_index = nil) ⇒ Object

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.



127
128
129
130
131
132
133
 
# File 'lib/google_drive/spreadsheet.rb', line 127

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

#human_url ⇒ Object

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

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



62
63
64
65
 
# File 'lib/google_drive/spreadsheet.rb', line 62

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

#inspect ⇒ Object 



222
223
224
225
226
 
# File 'lib/google_drive/spreadsheet.rb', line 222

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

#key ⇒ Object

Key of the spreadsheet.



45
46
47
48
49
50
51
52
 
# File 'lib/google_drive/spreadsheet.rb', line 45

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

#spreadsheet_feed_entry(params = {}) ⇒ Object

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

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



86
87
88
89
90
91
92
 
# File 'lib/google_drive/spreadsheet.rb', line 86

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

#spreadsheet_feed_url ⇒ Object

Spreadsheet feed URL of the spreadsheet.



55
56
57
 
# File 'lib/google_drive/spreadsheet.rb', line 55

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

#tables ⇒ Object

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

Returns list of tables in the spreadsheet.



214
215
216
217
218
219
220
 
# File 'lib/google_drive/spreadsheet.rb', line 214

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

#tables_feed_url ⇒ Object

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

Tables feed URL of the spreadsheet.



71
72
73
74
75
76
 
# File 'lib/google_drive/spreadsheet.rb', line 71

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

#title(params = {}) ⇒ Object

Title of the spreadsheet.

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



37
38
39
40
41
42
 
# File 'lib/google_drive/spreadsheet.rb', line 37

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

#worksheet_by_title(title) ⇒ Object

Returns a GoogleDrive::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.



190
191
192
 
# File 'lib/google_drive/spreadsheet.rb', line 190

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

#worksheets ⇒ Object

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



168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
 
# File 'lib/google_drive/spreadsheet.rb', line 168

def worksheets
  doc = @session.request(:get, @worksheets_feed_url)
  if doc.root.name != "feed"
    raise(GoogleDrive::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
    updated = Time.parse(entry.css("updated").text)
    url = entry.css(
      "link[rel='http://schemas.google.com/spreadsheets/2006#cellsfeed']")[0]["href"]
    result.push(Worksheet.new(@session, self, url, title, updated))
  end
  return result.freeze()
end