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_io, #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:



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

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.



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

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.



184
185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/google_drive/spreadsheet.rb', line 184

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.



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

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.



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

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

#duplicate(new_title = nil) ⇒ Object

Creates copy of this spreadsheet with the given title.



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

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)



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

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.



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

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”



60
61
62
63
 
# File 'lib/google_drive/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

#inspect ⇒ Object 



211
212
213
214
215
 
# File 'lib/google_drive/spreadsheet.rb', line 211

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.



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

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.



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

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.



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

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.



203
204
205
206
207
208
209
 
# File 'lib/google_drive/spreadsheet.rb', line 203

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.



69
70
71
72
73
74
 
# File 'lib/google_drive/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

#title(params = {}) ⇒ Object

Title of the spreadsheet.

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



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

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.



179
180
181
 
# File 'lib/google_drive/spreadsheet.rb', line 179

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.



158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
 
# File 'lib/google_drive/spreadsheet.rb', line 158

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
    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