Class: GoogleDrive::Worksheet
- Inherits:
-
Object
- Object
- GoogleDrive::Worksheet
- Includes:
- Util
- Defined in:
- lib/google_drive/worksheet.rb
Overview
A worksheet (i.e. a tab) in a spreadsheet. Use GoogleDrive::Spreadsheet#worksheets to get GoogleDrive::Worksheet object.
Constant Summary
Constants included from Util
Util::EXT_TO_CONTENT_TYPE, Util::IMPORTABLE_CONTENT_TYPE_MAP
Instance Attribute Summary collapse
-
#title ⇒ Object
Title of the worksheet (shown as tab label in Web interface).
-
#updated ⇒ Object
readonly
Time object which represents the time the worksheet was last updated.
-
#worksheet_feed_entry ⇒ Object
readonly
Nokogiri::XML::Element object of the <entry> element in a worksheets feed.
Instance Method Summary collapse
-
#[](*args) ⇒ Object
Returns content of the cell as String.
-
#[]=(*args) ⇒ Object
Updates content of the cell.
-
#cell_name_to_row_col(cell_name) ⇒ Object
Returns a [row, col] pair for a cell name string.
-
#cells ⇒ Object
:nodoc:.
-
#cells_feed_url ⇒ Object
URL of cell-based feed of the worksheet.
-
#csv_export_url ⇒ Object
URL to export the worksheet as CSV.
-
#delete ⇒ Object
Deletes this worksheet.
-
#delete_rows(row_num, rows) ⇒ Object
Deletes rows.
-
#dirty? ⇒ Boolean
Returns true if you have changes made by []= which haven’t been saved.
-
#export_as_file(path) ⇒ Object
Exports the worksheet to
path
in CSV format. -
#export_as_string ⇒ Object
Exports the worksheet as String in CSV format.
-
#gid ⇒ Object
gid of the worksheet.
-
#human_url ⇒ Object
URL to view/edit the worksheet in a Web browser.
-
#initialize(session, spreadsheet, worksheet_feed_entry) ⇒ Worksheet
constructor
:nodoc:.
-
#input_value(*args) ⇒ Object
Returns the value or the formula of the cell.
-
#insert_rows(row_num, rows) ⇒ Object
Inserts rows.
- #inspect ⇒ Object
-
#list ⇒ Object
Provides access to cells using column names, assuming the first row contains column names.
-
#list_feed_url ⇒ Object
List feed URL of the worksheet.
-
#max_cols ⇒ Object
Number of columns including empty columns.
-
#max_cols=(cols) ⇒ Object
Updates number of columns.
-
#max_rows ⇒ Object
Number of rows including empty rows.
-
#max_rows=(rows) ⇒ Object
Updates number of rows.
-
#num_cols ⇒ Object
Column number of the right-most non-empty column.
-
#num_rows ⇒ Object
Row number of the bottom-most non-empty row.
-
#numeric_value(*args) ⇒ Object
Returns the numeric value of the cell.
-
#reload ⇒ Object
Reloads content of the worksheets from the server.
-
#rows(skip = 0) ⇒ Object
An array of spreadsheet rows.
-
#save ⇒ Object
Saves your changes made by []=, etc.
-
#spreadsheet ⇒ Object
GoogleDrive::Spreadsheet which this worksheet belongs to.
-
#synchronize ⇒ Object
Calls save() and reload().
-
#update_cells(top_row, left_col, darray) ⇒ Object
Updates cells in a rectangle area by a two-dimensional Array.
-
#worksheet_feed_url ⇒ Object
URL of worksheet feed URL of the worksheet.
Methods included from Util
concat_url, construct_and_query, construct_query, convert_params, delegate_api_methods, encode_query, get_singleton_class, h
Constructor Details
#initialize(session, spreadsheet, worksheet_feed_entry) ⇒ Worksheet
:nodoc:
18 19 20 21 22 23 24 25 26 27 28 |
# File 'lib/google_drive/worksheet.rb', line 18 def initialize(session, spreadsheet, worksheet_feed_entry) #:nodoc: @session = session @spreadsheet = spreadsheet set_worksheet_feed_entry(worksheet_feed_entry) @cells = nil @input_values = nil @numeric_values = nil @modified = Set.new @list = nil end |
Instance Attribute Details
#title ⇒ Object
Title of the worksheet (shown as tab label in Web interface).
34 35 36 |
# File 'lib/google_drive/worksheet.rb', line 34 def title @title end |
#updated ⇒ Object (readonly)
Time object which represents the time the worksheet was last updated.
37 38 39 |
# File 'lib/google_drive/worksheet.rb', line 37 def updated @updated end |
#worksheet_feed_entry ⇒ Object (readonly)
Nokogiri::XML::Element object of the <entry> element in a worksheets feed.
31 32 33 |
# File 'lib/google_drive/worksheet.rb', line 31 def worksheet_feed_entry @worksheet_feed_entry end |
Instance Method Details
#[](*args) ⇒ Object
Returns content of the cell as String. Arguments must be either (row number, column number) or cell name. Top-left cell is [1, 1].
e.g.
worksheet[2, 1] #=> "hoge"
worksheet["A2"] #=> "hoge"
96 97 98 99 |
# File 'lib/google_drive/worksheet.rb', line 96 def [](*args) (row, col) = parse_cell_args(args) cells[[row, col]] || '' end |
#[]=(*args) ⇒ Object
Updates content of the cell. Arguments in the bracket must be either (row number, column number) or cell name. Note that update is not sent to the server until you call save(). Top-left cell is [1, 1].
e.g.
worksheet[2, 1] = "hoge"
worksheet["A2"] = "hoge"
worksheet[1, 3] = "=A1+B1"
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 |
# File 'lib/google_drive/worksheet.rb', line 110 def []=(*args) (row, col) = parse_cell_args(args[0...-1]) value = args[-1].to_s validate_cell_value(value) reload_cells unless @cells @cells[[row, col]] = value @input_values[[row, col]] = value @numeric_values[[row, col]] = nil @modified.add([row, col]) self.max_rows = row if row > @max_rows self.max_cols = col if col > @max_cols if value.empty? @num_rows = nil @num_cols = nil else @num_rows = row if row > num_rows @num_cols = col if col > num_cols end end |
#cell_name_to_row_col(cell_name) ⇒ Object
Returns a [row, col] pair for a cell name string. e.g.
worksheet.cell_name_to_row_col("C2") #=> [2, 3]
445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 |
# File 'lib/google_drive/worksheet.rb', line 445 def cell_name_to_row_col(cell_name) unless cell_name.is_a?(String) fail(ArgumentError, 'Cell name must be a string: %p' % cell_name) end unless cell_name.upcase =~ /^([A-Z]+)(\d+)$/ fail(ArgumentError, 'Cell name must be only letters followed by digits with no spaces in between: %p' % cell_name) end col = 0 Regexp.last_match(1).each_byte do |b| # 0x41: "A" col = col * 26 + (b - 0x41 + 1) end row = Regexp.last_match(2).to_i [row, col] end |
#cells ⇒ Object
:nodoc:
225 226 227 228 |
# File 'lib/google_drive/worksheet.rb', line 225 def cells #:nodoc: reload_cells unless @cells @cells end |
#cells_feed_url ⇒ Object
URL of cell-based feed of the worksheet.
40 41 42 43 |
# File 'lib/google_drive/worksheet.rb', line 40 def cells_feed_url @worksheet_feed_entry.css( "link[rel='http://schemas.google.com/spreadsheets/2006#cellsfeed']")[0]['href'] end |
#csv_export_url ⇒ Object
URL to export the worksheet as CSV.
51 52 53 54 |
# File 'lib/google_drive/worksheet.rb', line 51 def csv_export_url @worksheet_feed_entry.css( "link[rel='http://schemas.google.com/spreadsheets/2006#exportcsv']")[0]['href'] end |
#delete ⇒ Object
Deletes this worksheet. Deletion takes effect right away without calling save().
409 410 411 412 413 |
# File 'lib/google_drive/worksheet.rb', line 409 def delete ws_doc = @session.request(:get, worksheet_feed_url) edit_url = ws_doc.css("link[rel='edit']")[0]['href'] @session.request(:delete, edit_url) end |
#delete_rows(row_num, rows) ⇒ Object
Deletes rows.
e.g.
# Deletes 2 rows starting from row 3 (i.e., deletes row 3 and 4).
worksheet.delete_rows(3, 2)
Note that this method is implemented by shifting all cells below the row. Its behavior is different from deleting rows on the web interface if the worksheet contains inter-cell reference.
282 283 284 285 286 287 288 289 290 291 292 |
# File 'lib/google_drive/worksheet.rb', line 282 def delete_rows(row_num, rows) if row_num + rows - 1 > self.max_rows fail(ArgumentError, 'The row number is out of range') end for r in row_num..(self.max_rows - rows) for c in 1..num_cols self[r, c] = self.input_value(r + rows, c) end end self.max_rows -= rows end |
#dirty? ⇒ Boolean
Returns true if you have changes made by []= which haven’t been saved.
416 417 418 |
# File 'lib/google_drive/worksheet.rb', line 416 def dirty? !@modified.empty? end |
#export_as_file(path) ⇒ Object
Exports the worksheet to path
in CSV format.
62 63 64 65 |
# File 'lib/google_drive/worksheet.rb', line 62 def export_as_file(path) data = export_as_string open(path, 'wb') { |f| f.write(data) } end |
#export_as_string ⇒ Object
Exports the worksheet as String in CSV format.
57 58 59 |
# File 'lib/google_drive/worksheet.rb', line 57 def export_as_string @session.request(:get, csv_export_url, response_type: :raw) end |
#gid ⇒ Object
gid of the worksheet.
68 69 70 71 |
# File 'lib/google_drive/worksheet.rb', line 68 def gid # A bit tricky but couldn't find a better way. CGI.parse(URI.parse(csv_export_url).query)['gid'].last end |
#human_url ⇒ Object
URL to view/edit the worksheet in a Web browser.
74 75 76 |
# File 'lib/google_drive/worksheet.rb', line 74 def human_url "%s\#gid=%s" % [spreadsheet.human_url, gid] end |
#input_value(*args) ⇒ Object
Returns the value or the formula of the cell. Arguments must be either (row number, column number) or cell name. Top-left cell is [1, 1].
If user input “=A1+B1” to cell [1, 3]:
worksheet[1, 3] #=> "3" for example
worksheet.input_value(1, 3) #=> "=RC[-2]+RC[-1]"
149 150 151 152 153 |
# File 'lib/google_drive/worksheet.rb', line 149 def input_value(*args) (row, col) = parse_cell_args(args) reload_cells unless @cells @input_values[[row, col]] || '' end |
#insert_rows(row_num, rows) ⇒ Object
Inserts rows.
e.g.
# Inserts 2 empty rows before row 3.
worksheet.insert_rows(3, 2)
# Inserts 2 rows with values before row 3.
worksheet.insert_rows(3, [["a, "b"], ["c, "d"]])
Note that this method is implemented by shifting all cells below the row. Its behavior is different from inserting rows on the web interface if the worksheet contains inter-cell reference.
253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 |
# File 'lib/google_drive/worksheet.rb', line 253 def insert_rows(row_num, rows) rows = Array.new(rows, []) if rows.is_a?(Integer) # Shifts all cells below the row. self.max_rows += rows.size num_rows.downto(row_num) do |r| (1..num_cols).each do |c| self[r + rows.size, c] = self.input_value(r, c) end end # Fills in the inserted rows. num_cols = self.num_cols rows.each_with_index do |row, r| (0...[row.size, num_cols].max).each do |c| self[row_num + r, 1 + c] = row[c] || '' end end end |
#inspect ⇒ Object
463 464 465 466 467 |
# File 'lib/google_drive/worksheet.rb', line 463 def inspect fields = { worksheet_feed_url: worksheet_feed_url } fields[:title] = @title if @title "\#<%p %s>" % [self.class, fields.map { |k, v| '%s=%p' % [k, v] }.join(', ')] end |
#list ⇒ Object
Provides access to cells using column names, assuming the first row contains column names. Returned object is GoogleDrive::List which you can use mostly as Array of Hash.
e.g. Assuming the first row is [“x”, “y”]:
worksheet.list[0]["x"] #=> "1" # i.e. worksheet[2, 1]
worksheet.list[0]["y"] #=> "2" # i.e. worksheet[2, 2]
worksheet.list[1]["x"] = "3" # i.e. worksheet[3, 1] = "3"
worksheet.list[1]["y"] = "4" # i.e. worksheet[3, 2] = "4"
worksheet.list.push({"x" => "5", "y" => "6"})
Note that update is not sent to the server until you call save().
438 439 440 |
# File 'lib/google_drive/worksheet.rb', line 438 def list @list ||= List.new(self) end |
#list_feed_url ⇒ Object
List feed URL of the worksheet.
421 422 423 424 |
# File 'lib/google_drive/worksheet.rb', line 421 def list_feed_url @worksheet_feed_entry.css( "link[rel='http://schemas.google.com/spreadsheets/2006#listfeed']")[0]['href'] end |
#max_cols ⇒ Object
Number of columns including empty columns.
205 206 207 208 |
# File 'lib/google_drive/worksheet.rb', line 205 def max_cols reload_cells unless @cells @max_cols end |
#max_cols=(cols) ⇒ Object
Updates number of columns. Note that update is not sent to the server until you call save().
212 213 214 215 216 |
# File 'lib/google_drive/worksheet.rb', line 212 def max_cols=(cols) reload_cells unless @cells @max_cols = cols @meta_modified = true end |
#max_rows ⇒ Object
Number of rows including empty rows.
191 192 193 194 |
# File 'lib/google_drive/worksheet.rb', line 191 def max_rows reload_cells unless @cells @max_rows end |
#max_rows=(rows) ⇒ Object
Updates number of rows. Note that update is not sent to the server until you call save().
198 199 200 201 202 |
# File 'lib/google_drive/worksheet.rb', line 198 def max_rows=(rows) reload_cells unless @cells @max_rows = rows @meta_modified = true end |
#num_cols ⇒ Object
Column number of the right-most non-empty column.
183 184 185 186 187 188 |
# File 'lib/google_drive/worksheet.rb', line 183 def num_cols reload_cells unless @cells # Memoizes it because this can be bottle-neck. # https://github.com/gimite/google-drive-ruby/pull/49 @num_cols ||= @input_values.select { |(_r, _c), v| !v.empty? }.map { |(_r, c), _v| c }.max || 0 end |
#num_rows ⇒ Object
Row number of the bottom-most non-empty row.
175 176 177 178 179 180 |
# File 'lib/google_drive/worksheet.rb', line 175 def num_rows reload_cells unless @cells # Memoizes it because this can be bottle-neck. # https://github.com/gimite/google-drive-ruby/pull/49 @num_rows ||= @input_values.select { |(_r, _c), v| !v.empty? }.map { |(r, _c), _v| r }.max || 0 end |
#numeric_value(*args) ⇒ Object
Returns the numeric value of the cell. Arguments must be either (row number, column number) or cell name. Top-left cell is [1, 1].
e.g.
worksheet[1, 3] #=> "3,0" # it depends on locale, currency...
worksheet.numeric_value(1, 3) #=> 3.0
Returns nil if the cell is empty or contains non-number.
If you modify the cell, its numeric_value is nil until you call save() and reload().
For details, see: developers.google.com/google-apps/spreadsheets/#working_with_cell-based_feeds
168 169 170 171 172 |
# File 'lib/google_drive/worksheet.rb', line 168 def numeric_value(*args) (row, col) = parse_cell_args(args) reload_cells unless @cells @numeric_values[[row, col]] end |
#reload ⇒ Object
Reloads content of the worksheets from the server. Note that changes you made by []= etc. is discarded if you haven’t called save().
296 297 298 299 300 |
# File 'lib/google_drive/worksheet.rb', line 296 def reload set_worksheet_feed_entry(@session.request(:get, worksheet_feed_url).root) reload_cells true end |
#rows(skip = 0) ⇒ Object
An array of spreadsheet rows. Each row contains an array of columns. Note that resulting array is 0-origin so:
worksheet.rows[0][0] == worksheet[1, 1]
234 235 236 237 238 239 240 |
# File 'lib/google_drive/worksheet.rb', line 234 def rows(skip = 0) nc = num_cols result = ((1 + skip)..num_rows).map do |row| (1..nc).map { |col| self[row, col] }.freeze end result.freeze end |
#save ⇒ Object
Saves your changes made by []=, etc. to the server.
303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 |
# File 'lib/google_drive/worksheet.rb', line 303 def save sent = false if @meta_modified edit_url = @worksheet_feed_entry.css("link[rel='edit']")[0]['href'] 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(self.max_rows)}</gs:rowCount> <gs:colCount>#{h(max_cols)}</gs:colCount> </entry> EOS result = @session.request( :put, edit_url, data: xml, header: { 'Content-Type' => 'application/atom+xml;charset=utf-8', 'If-Match' => '*' }) set_worksheet_feed_entry(result.root) sent = true end unless @modified.empty? # Gets id and edit URL for each cell. # Note that return-empty=true is required to get those info for empty cells. cell_entries = {} rows = @modified.map { |r, _c| r } cols = @modified.map { |_r, c| c } url = concat_url(cells_feed_url, "?return-empty=true&min-row=#{rows.min}&max-row=#{rows.max}" \ "&min-col=#{cols.min}&max-col=#{cols.max}") doc = @session.request(:get, url) doc.css('entry').each do |entry| row = entry.css('gs|cell')[0]['row'].to_i col = entry.css('gs|cell')[0]['col'].to_i cell_entries[[row, col]] = entry end xml = <<-EOS <feed xmlns="http://www.w3.org/2005/Atom" xmlns:batch="http://schemas.google.com/gdata/batch" xmlns:gs="http://schemas.google.com/spreadsheets/2006"> <id>#{h(cells_feed_url)}</id> EOS @modified.each do |row, col| value = @cells[[row, col]] entry = cell_entries[[row, col]] id = entry.css('id').text edit_link = entry.css("link[rel='edit']")[0] unless edit_link fail(GoogleDrive::Error, "The user doesn't have write permission to the spreadsheet: %p" % spreadsheet) end edit_url = edit_link['href'] xml << <<-EOS <entry> <batch:id>#{h(row)},#{h(col)}</batch:id> <batch:operation type="update"/> <id>#{h(id)}</id> <link rel="edit" type="application/atom+xml" href="#{h(edit_url)}"/> <gs:cell row="#{h(row)}" col="#{h(col)}" inputValue="#{h(value)}"/> </entry> EOS end xml << <<-"EOS" </feed> EOS batch_url = concat_url(cells_feed_url, '/batch') result = @session.request( :post, batch_url, data: xml, header: { 'Content-Type' => 'application/atom+xml;charset=utf-8', 'If-Match' => '*' }) result.css('entry').each do |entry| interrupted = entry.css('batch|interrupted')[0] if interrupted fail(GoogleDrive::Error, 'Update has failed: %s' % interrupted['reason']) end unless entry.css('batch|status').first['code'] =~ /^2/ fail(GoogleDrive::Error, 'Updating cell %s has failed: %s' % [entry.css('id').text, entry.css('batch|status')[0]['reason']]) end end @modified.clear sent = true end sent end |
#spreadsheet ⇒ Object
GoogleDrive::Spreadsheet which this worksheet belongs to.
79 80 81 82 83 84 85 86 87 88 |
# File 'lib/google_drive/worksheet.rb', line 79 def spreadsheet unless @spreadsheet unless worksheet_feed_url =~ %r{https?://spreadsheets\.google\.com/feeds/worksheets/(.*)/(.*)$} fail(GoogleDrive::Error, "Worksheet feed URL is in unknown format: #{worksheet_feed_url}") end @spreadsheet = @session.file_by_id(Regexp.last_match(1)) end @spreadsheet end |
#synchronize ⇒ Object
Calls save() and reload().
403 404 405 406 |
# File 'lib/google_drive/worksheet.rb', line 403 def synchronize save reload end |
#update_cells(top_row, left_col, darray) ⇒ Object
Updates cells in a rectangle area by a two-dimensional Array. top_row
and left_col
specifies the top-left corner of the area.
e.g.
worksheet.update_cells(2, 3, [["1", "2"], ["3", "4"]])
135 136 137 138 139 140 141 |
# File 'lib/google_drive/worksheet.rb', line 135 def update_cells(top_row, left_col, darray) darray.each_with_index do |array, y| array.each_with_index do |value, x| self[top_row + y, left_col + x] = value end end end |
#worksheet_feed_url ⇒ Object
URL of worksheet feed URL of the worksheet.
46 47 48 |
# File 'lib/google_drive/worksheet.rb', line 46 def worksheet_feed_url @worksheet_feed_entry.css("link[rel='self']")[0]['href'] end |