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 collapse

XML_INVAILD_CHAR_REGEXP =

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

A regexp which matches an invalid character in XML 1.0: en.wikipedia.org/wiki/Valid_characters_in_XML#XML_1.0

/[^\u0009\u000a\u000d\u0020-\ud7ff\ue000-\ufffd\u{10000}-\u{10ffff}]/

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.



26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/google_drive/worksheet.rb', line 26

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.



227
228
229
 
# File 'lib/google_drive/worksheet.rb', line 227

def max_cols
  @max_cols
end

#max_rows ⇒ Object

Number of rows including empty rows.



217
218
219
 
# File 'lib/google_drive/worksheet.rb', line 217

def max_rows
  @max_rows
end

#title ⇒ Object

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



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

def title
  @title
end

#updated ⇒ Object (readonly)

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



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

def updated
  @updated
end

#worksheet_feed_entry ⇒ Object (readonly)

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



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

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"



108
109
110
111
 
# File 'lib/google_drive/worksheet.rb', line 108

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"



123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
 
# File 'lib/google_drive/worksheet.rb', line 123

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 @num_rows && row > @num_rows
    @num_cols = col if @num_cols && 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]



494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
 
# File 'lib/google_drive/worksheet.rb', line 494

def cell_name_to_row_col(cell_name)
  unless cell_name.is_a?(String)
    raise(
      ArgumentError, format('Cell name must be a string: %p', cell_name)
    )
  end
  unless cell_name.upcase =~ /^([A-Z]+)(\d+)$/
    raise(
      ArgumentError,
      format(
        '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. 



244
245
246
247
 
# File 'lib/google_drive/worksheet.rb', line 244

def cells
  reload_cells unless @cells
  @cells
end

#cells_feed_url ⇒ Object

URL of cell-based feed of the worksheet.



48
49
50
51
52
 
# File 'lib/google_drive/worksheet.rb', line 48

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.



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

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



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

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.



301
302
303
304
305
306
307
308
309
310
311
 
# File 'lib/google_drive/worksheet.rb', line 301

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..num_cols
      self[r, c] = 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) 


463
464
465
 
# File 'lib/google_drive/worksheet.rb', line 463

def dirty?
  !@modified.empty?
end

#export_as_file(path) ⇒ Object

Exports the worksheet to path in CSV format.



72
73
74
75
 
# File 'lib/google_drive/worksheet.rb', line 72

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.



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

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

#gid ⇒ Object

gid of the worksheet.



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

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.



84
85
86
 
# File 'lib/google_drive/worksheet.rb', line 84

def human_url
  format("%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]"



162
163
164
165
166
 
# File 'lib/google_drive/worksheet.rb', line 162

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.



272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
 
# File 'lib/google_drive/worksheet.rb', line 272

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



519
520
521
522
523
524
525
526
527
 
# File 'lib/google_drive/worksheet.rb', line 519

def inspect
  fields = { worksheet_feed_url: worksheet_feed_url }
  fields[:title] = @title if @title
  format(
    "\#<%p %s>",
    self.class,
    fields.map { |k, v| format('%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().



487
488
489
 
# File 'lib/google_drive/worksheet.rb', line 487

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

#list_feed_url ⇒ Object

List feed URL of the worksheet.



468
469
470
471
472
 
# File 'lib/google_drive/worksheet.rb', line 468

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.



204
205
206
207
208
209
210
211
212
213
214
 
# File 'lib/google_drive/worksheet.rb', line 204

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
    .reject { |(_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.



191
192
193
194
195
196
197
198
199
200
201
 
# File 'lib/google_drive/worksheet.rb', line 191

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
    .reject { |(_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



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

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



316
317
318
319
320
 
# File 'lib/google_drive/worksheet.rb', line 316

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]



253
254
255
256
257
258
259
 
# File 'lib/google_drive/worksheet.rb', line 253

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.



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
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
 
# File 'lib/google_drive/worksheet.rb', line 323

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
        raise(
          GoogleDrive::Error,
          format(
            "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
        raise(
          GoogleDrive::Error,
          format('Update has failed: %s', interrupted['reason'])
        )
      end
      next if entry.css('batch|status').first['code'] =~ /^2/
      raise(
        GoogleDrive::Error,
        format(
          'Updating cell %s has failed: %s',
          entry.css('id').text, entry.css('batch|status')[0]['reason']
        )
      )
    end

    @modified.clear
    sent = true

  end

  sent
end

#spreadsheet ⇒ Object

GoogleDrive::Spreadsheet which this worksheet belongs to.



89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/google_drive/worksheet.rb', line 89

def spreadsheet
  unless @spreadsheet
    unless worksheet_feed_url =~
           %r{https?://spreadsheets\.google\.com/feeds/worksheets/(.*)/(.*)$}
      raise(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().



449
450
451
452
 
# File 'lib/google_drive/worksheet.rb', line 449

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



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

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.



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

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