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, Util::IMPORTABLE_CONTENT_TYPE_MAP

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

Constructor Details

#initialize(session, spreadsheet, worksheet_feed_entry) ⇒ Worksheet

This method is part of a private API. You should avoid using this method if possible, as it may be removed or be changed in the future.

Returns a new instance of Worksheet.



19
20
21
22
23
24
25
26
27
28
29
 
# File 'lib/google_drive/worksheet.rb', line 19

def initialize(session, spreadsheet, worksheet_feed_entry)
  @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

#max_cols ⇒ Object

Number of columns including empty columns.



202
203
204
 
# File 'lib/google_drive/worksheet.rb', line 202

def max_cols
  @max_cols
end

#max_rows ⇒ Object

Number of rows including empty rows.



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

def max_rows
  @max_rows
end

#title ⇒ Object

Title of the worksheet (shown as tab label in Web interface).



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

def title
  @title
end

#updated ⇒ Object (readonly)

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



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

def updated
  @updated
end

#worksheet_feed_entry ⇒ Object (readonly)

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



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

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"



97
98
99
100
 
# File 'lib/google_drive/worksheet.rb', line 97

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"



111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/google_drive/worksheet.rb', line 111

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]



439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
 
# File 'lib/google_drive/worksheet.rb', line 439

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

This method is part of a private API. You should avoid using this method if possible, as it may be removed or be changed in the future. 



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

def cells
  reload_cells unless @cells
  @cells
end

#cells_feed_url ⇒ Object

URL of cell-based feed of the worksheet.



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

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.



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

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



403
404
405
406
407
 
# File 'lib/google_drive/worksheet.rb', line 403

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.



276
277
278
279
280
281
282
283
284
285
286
 
# File 'lib/google_drive/worksheet.rb', line 276

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.

Returns:

  • (Boolean) 


410
411
412
 
# File 'lib/google_drive/worksheet.rb', line 410

def dirty?
  !@modified.empty?
end

#export_as_file(path) ⇒ Object

Exports the worksheet to path in CSV format.



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

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.



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

def export_as_string
  @session.request(:get, csv_export_url, response_type: :raw)
end

#gid ⇒ Object

gid of the worksheet.



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

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.



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

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



150
151
152
153
154
 
# File 'lib/google_drive/worksheet.rb', line 150

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.



247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
 
# File 'lib/google_drive/worksheet.rb', line 247

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 



457
458
459
460
461
 
# File 'lib/google_drive/worksheet.rb', line 457

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



432
433
434
 
# File 'lib/google_drive/worksheet.rb', line 432

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

#list_feed_url ⇒ Object

List feed URL of the worksheet.



415
416
417
418
 
# File 'lib/google_drive/worksheet.rb', line 415

def list_feed_url
  @worksheet_feed_entry.css(
    "link[rel='http://schemas.google.com/spreadsheets/2006#listfeed']")[0]['href']
end

#num_cols ⇒ Object

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



184
185
186
187
188
189
 
# File 'lib/google_drive/worksheet.rb', line 184

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.



176
177
178
179
180
181
 
# File 'lib/google_drive/worksheet.rb', line 176

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



169
170
171
172
173
 
# File 'lib/google_drive/worksheet.rb', line 169

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



290
291
292
293
294
 
# File 'lib/google_drive/worksheet.rb', line 290

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]



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

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.



297
298
299
300
301
302
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
 
# File 'lib/google_drive/worksheet.rb', line 297

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



80
81
82
83
84
85
86
87
88
89
 
# File 'lib/google_drive/worksheet.rb', line 80

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



397
398
399
400
 
# File 'lib/google_drive/worksheet.rb', line 397

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



136
137
138
139
140
141
142
 
# File 'lib/google_drive/worksheet.rb', line 136

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.



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

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