Class: GoogleDrive::Worksheet

Inherits:
Object
  • Object
show all
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

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Util

concat_url, construct_and_query, construct_query, convert_params, delegate_api_methods, encode_query, get_singleton_class, h, new_upload_io

Constructor Details

#initialize(session, spreadsheet, worksheet_feed_entry) ⇒ Worksheet

:nodoc:



21
22
23
24
25
26
27
28
29
30
31
32
33
 
# File 'lib/google_drive/worksheet.rb', line 21

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



39
40
41
 
# File 'lib/google_drive/worksheet.rb', line 39

def title
  @title
end

#updated ⇒ Object (readonly)

Time object which represents the time the worksheet was last updated.



42
43
44
 
# File 'lib/google_drive/worksheet.rb', line 42

def updated
  @updated
end

#worksheet_feed_entry ⇒ Object (readonly)

Nokogiri::XML::Element object of the <entry> element in a worksheets feed.



36
37
38
 
# File 'lib/google_drive/worksheet.rb', line 36

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"



102
103
104
105
 
# File 'lib/google_drive/worksheet.rb', line 102

def [](*args)
  (row, col) = parse_cell_args(args)
  return self.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"



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
# File 'lib/google_drive/worksheet.rb', line 116

def []=(*args)
  (row, col) = parse_cell_args(args[0...-1])
  value = args[-1].to_s()
  validate_cell_value(value)
  reload_cells() if !@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]



459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
 
# File 'lib/google_drive/worksheet.rb', line 459

def cell_name_to_row_col(cell_name)
  if !cell_name.is_a?(String)
    raise(ArgumentError, "Cell name must be a string: %p" % cell_name)
  end
  if !(cell_name.upcase =~ /^([A-Z]+)(\d+)$/)
    raise(ArgumentError,
        "Cell name must be only letters followed by digits with no spaces in between: %p" %
            cell_name)
  end
  col = 0
  $1.each_byte() do |b|
    # 0x41: "A"
    col = col * 26 + (b - 0x41 + 1)
  end
  row = $2.to_i()
  return [row, col]
end

#cells ⇒ Object

:nodoc:



231
232
233
234
 
# File 'lib/google_drive/worksheet.rb', line 231

def cells #:nodoc:
  reload_cells() if !@cells
  return @cells
end

#cells_feed_url ⇒ Object

URL of cell-based feed of the worksheet.



45
46
47
48
 
# File 'lib/google_drive/worksheet.rb', line 45

def cells_feed_url
  return @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.



56
57
58
59
 
# File 'lib/google_drive/worksheet.rb', line 56

def csv_export_url
  return @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().



423
424
425
426
427
 
# File 'lib/google_drive/worksheet.rb', line 423

def delete()
  ws_doc = @session.request(:get, self.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.



294
295
296
297
298
299
300
301
302
303
304
 
# File 'lib/google_drive/worksheet.rb', line 294

def delete_rows(row_num, rows)
  if row_num + rows - 1 > self.max_rows
    raise(ArgumentError, "The row number is out of range")
  end
  for r in row_num..(self.max_rows - rows)
    for c in 1..self.num_cols
      self[r, c] = self[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.

Returns:

  • (Boolean) 


430
431
432
 
# File 'lib/google_drive/worksheet.rb', line 430

def dirty?
  return !@modified.empty?
end

#export_as_file(path) ⇒ Object

Exports the worksheet to path in CSV format.



68
69
70
71
 
# File 'lib/google_drive/worksheet.rb', line 68

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.



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

def export_as_string()
  api_result = @session.execute!(:uri => self.csv_export_url)
  return api_result.body
end

#gid ⇒ Object

gid of the worksheet.



74
75
76
77
 
# File 'lib/google_drive/worksheet.rb', line 74

def gid
  # A bit tricky but couldn't find a better way.
  return CGI.parse(URI.parse(self.csv_export_url).query)["gid"].last
end

#human_url ⇒ Object

URL to view/edit the worksheet in a Web browser.



80
81
82
 
# File 'lib/google_drive/worksheet.rb', line 80

def human_url
  return "%s\#gid=%s" % [self.spreadsheet.human_url, self.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]"



155
156
157
158
159
 
# File 'lib/google_drive/worksheet.rb', line 155

def input_value(*args)
  (row, col) = parse_cell_args(args)
  reload_cells() if !@cells
  return @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.



259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
 
# File 'lib/google_drive/worksheet.rb', line 259

def insert_rows(row_num, rows)

  if rows.is_a?(Integer)
    rows = Array.new(rows, [])
  end

  # Shifts all cells below the row.
  self.max_rows += rows.size
  r = self.num_rows
  while r >= row_num
    for c in 1..self.num_cols
      self[r + rows.size, c] = self[r, c]
    end
    r -= 1
  end

  # Fills in the inserted rows.
  num_cols = self.num_cols
  rows.each_with_index() do |row, r|
    for c in 0...[row.size, num_cols].max
      self[row_num + r, 1 + c] = row[c] || ""
    end
  end

end

#inspect ⇒ Object 



477
478
479
480
481
 
# File 'lib/google_drive/worksheet.rb', line 477

def inspect
  fields = {:worksheet_feed_url => self.worksheet_feed_url}
  fields[:title] = @title if @title
  return "\#<%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().



452
453
454
 
# File 'lib/google_drive/worksheet.rb', line 452

def list
  return @list ||= List.new(self)
end

#list_feed_url ⇒ Object

List feed URL of the worksheet.



435
436
437
438
 
# File 'lib/google_drive/worksheet.rb', line 435

def list_feed_url
  return @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.



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

def max_cols
  reload_cells() if !@cells
  return @max_cols
end

#max_cols=(cols) ⇒ Object

Updates number of columns. Note that update is not sent to the server until you call save().



218
219
220
221
222
 
# File 'lib/google_drive/worksheet.rb', line 218

def max_cols=(cols)
  reload_cells() if !@cells
  @max_cols = cols
  @meta_modified = true
end

#max_rows ⇒ Object

Number of rows including empty rows.



197
198
199
200
 
# File 'lib/google_drive/worksheet.rb', line 197

def max_rows
  reload_cells() if !@cells
  return @max_rows
end

#max_rows=(rows) ⇒ Object

Updates number of rows. Note that update is not sent to the server until you call save().



204
205
206
207
208
 
# File 'lib/google_drive/worksheet.rb', line 204

def max_rows=(rows)
  reload_cells() if !@cells
  @max_rows = rows
  @meta_modified = true
end

#num_cols ⇒ Object

Column number of the right-most non-empty column.



189
190
191
192
193
194
 
# File 'lib/google_drive/worksheet.rb', line 189

def num_cols
  reload_cells() if !@cells
  # Memoizes it because this can be bottle-neck.
  # https://github.com/gimite/google-drive-ruby/pull/49
  return @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.



181
182
183
184
185
186
 
# File 'lib/google_drive/worksheet.rb', line 181

def num_rows
  reload_cells() if !@cells
  # Memoizes it because this can be bottle-neck.
  # https://github.com/gimite/google-drive-ruby/pull/49
  return @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



174
175
176
177
178
 
# File 'lib/google_drive/worksheet.rb', line 174

def numeric_value(*args)
  (row, col) = parse_cell_args(args)
  reload_cells() if !@cells
  return @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().



308
309
310
311
312
 
# File 'lib/google_drive/worksheet.rb', line 308

def reload()
  set_worksheet_feed_entry(@session.request(:get, self.worksheet_feed_url).root)
  reload_cells()
  return 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]



240
241
242
243
244
245
246
 
# File 'lib/google_drive/worksheet.rb', line 240

def rows(skip = 0)
  nc = self.num_cols
  result = ((1 + skip)..self.num_rows).map() do |row|
    (1..nc).map(){ |col| self[row, col] }.freeze()
  end
  return result.freeze()
end

#save ⇒ Object

Saves your changes made by []=, etc. to the server.



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
401
402
403
404
405
406
407
408
409
410
411
412
413
414
 
# File 'lib/google_drive/worksheet.rb', line 315

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(self.title)}</title>
        <gs:rowCount>#{h(self.max_rows)}</gs:rowCount>
        <gs:colCount>#{h(self.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

  if !@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(self.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)

    for entry in doc.css("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(self.cells_feed_url)}</id>
    EOS
    for row, col in @modified
      value = @cells[[row, col]]
      entry = cell_entries[[row, col]]
      id = entry.css("id").text
      edit_link = entry.css("link[rel='edit']")[0]
      if !edit_link
        raise(GoogleDrive::Error,
            "The user doesn't have write permission to the spreadsheet: %p" % self.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(self.cells_feed_url, "/batch")
    result = @session.request(
        :post,
        batch_url,
        :data => xml,
        :header => {"Content-Type" => "application/atom+xml;charset=utf-8", "If-Match" => "*"})
    for entry in result.css("entry")
      interrupted = entry.css("batch|interrupted")[0]
      if interrupted
        raise(GoogleDrive::Error, "Update has failed: %s" %
          interrupted["reason"])
      end
      if !(entry.css("batch|status").first["code"] =~ /^2/)
        raise(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
  
  return sent
  
end

#spreadsheet ⇒ Object

GoogleDrive::Spreadsheet which this worksheet belongs to.



85
86
87
88
89
90
91
92
93
94
 
# File 'lib/google_drive/worksheet.rb', line 85

def spreadsheet
  if !@spreadsheet
    if !(self.worksheet_feed_url =~ %r{https?://spreadsheets\.google\.com/feeds/worksheets/(.*)/(.*)$})
      raise(GoogleDrive::Error,
          "Worksheet feed URL is in unknown format: #{self.worksheet_feed_url}")
    end
    @spreadsheet = @session.file_by_id($1)
  end
  return @spreadsheet
end

#synchronize ⇒ Object

Calls save() and reload().



417
418
419
420
 
# File 'lib/google_drive/worksheet.rb', line 417

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"]])



141
142
143
144
145
146
147
 
# File 'lib/google_drive/worksheet.rb', line 141

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.



51
52
53
 
# File 'lib/google_drive/worksheet.rb', line 51

def worksheet_feed_url
  return @worksheet_feed_entry.css("link[rel='self']")[0]["href"]
end