Class: GoogleDriveV0::Worksheet

Inherits:
Object
  • Object
show all
Includes:
Util
Defined in:
lib/google_drive_v0/worksheet.rb

Overview

A worksheet (i.e. a tab) in a spreadsheet. Use GoogleDriveV0::Spreadsheet#worksheets to get GoogleDriveV0::Worksheet object.

Constant Summary

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

Constructor Details

#initialize(session, spreadsheet, cells_feed_url, title = nil, updated = nil) ⇒ Worksheet

:nodoc:



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

def initialize(session, spreadsheet, cells_feed_url, title = nil, updated = nil) #:nodoc:
  
  @session = session
  @spreadsheet = spreadsheet
  @cells_feed_url = cells_feed_url
  @title = title
  @updated = updated

  @cells = nil
  @input_values = nil
  @numeric_values = nil
  @modified = Set.new()
  @list = nil
  
end

Instance Attribute Details

#cells_feed_url ⇒ Object (readonly)

URL of cell-based feed of the worksheet.



37
38
39
 
# File 'lib/google_drive_v0/worksheet.rb', line 37

def cells_feed_url
  @cells_feed_url
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"



71
72
73
74
 
# File 'lib/google_drive_v0/worksheet.rb', line 71

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"



85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/google_drive_v0/worksheet.rb', line 85

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

#add_table(table_title, summary, columns, options) ⇒ Object

DEPRECATED: Table and Record feeds are deprecated and they will not be available after March 2012.

Creates table for the worksheet and returns GoogleDriveV0::Table. See this document for details: code.google.com/intl/en/apis/spreadsheets/docs/3.0/developers_guide_protocol.html#TableFeeds



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
 
# File 'lib/google_drive_v0/worksheet.rb', line 383

def add_table(table_title, summary, columns, options)
  
  warn(
      "DEPRECATED: Google Spreadsheet Table and Record feeds are deprecated and they " +
      "will not be available after March 2012.")
  default_options = { :header_row => 1, :num_rows => 0, :start_row => 2}
  options = default_options.merge(options)

  column_xml = ""
  columns.each() do |index, name|
    column_xml += "<gs:column index='#{h(index)}' name='#{h(name)}'/>\n"
  end

  xml = <<-"EOS"
    <entry xmlns="http://www.w3.org/2005/Atom"
      xmlns:gs="http://schemas.google.com/spreadsheets/2006">
      <title type='text'>#{h(table_title)}</title>
      <summary type='text'>#{h(summary)}</summary>
      <gs:worksheet name='#{h(self.title)}' />
      <gs:header row='#{options[:header_row]}' />
      <gs:data numRows='#{options[:num_rows]}' startRow='#{options[:start_row]}'>
        #{column_xml}
      </gs:data>
    </entry>
  EOS

  result = @session.request(:post, self.spreadsheet.tables_feed_url, :data => xml)
  return Table.new(@session, result)
  
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]



454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
 
# File 'lib/google_drive_v0/worksheet.rb', line 454

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:



212
213
214
215
 
# File 'lib/google_drive_v0/worksheet.rb', line 212

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

#delete ⇒ Object

Deletes this worksheet. Deletion takes effect right away without calling save().



366
367
368
369
370
 
# File 'lib/google_drive_v0/worksheet.rb', line 366

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

#dirty? ⇒ Boolean

Returns true if you have changes made by []= which haven’t been saved.

Returns:

  • (Boolean) 


373
374
375
 
# File 'lib/google_drive_v0/worksheet.rb', line 373

def dirty?
  return !@modified.empty?
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]"



123
124
125
126
127
 
# File 'lib/google_drive_v0/worksheet.rb', line 123

def input_value(*args)
  (row, col) = parse_cell_args(args)
  reload() if !@cells
  return @input_values[[row, col]] || ""
end

#inspect ⇒ Object 



472
473
474
475
476
 
# File 'lib/google_drive_v0/worksheet.rb', line 472

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



447
448
449
 
# File 'lib/google_drive_v0/worksheet.rb', line 447

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

#list_feed_url ⇒ Object

List feed URL of the worksheet.



426
427
428
429
430
431
432
433
 
# File 'lib/google_drive_v0/worksheet.rb', line 426

def list_feed_url
  # Gets the worksheets metafeed.
  entry = @session.request(:get, self.worksheet_feed_url)

  # Gets the URL of list-based feed for the given spreadsheet.
  return entry.css(
    "link[rel='http://schemas.google.com/spreadsheets/2006#listfeed']")[0]["href"]
end

#max_cols ⇒ Object

Number of columns including empty columns.



179
180
181
182
 
# File 'lib/google_drive_v0/worksheet.rb', line 179

def max_cols
  reload() 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().



186
187
188
189
190
 
# File 'lib/google_drive_v0/worksheet.rb', line 186

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

#max_rows ⇒ Object

Number of rows including empty rows.



165
166
167
168
 
# File 'lib/google_drive_v0/worksheet.rb', line 165

def max_rows
  reload() 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().



172
173
174
175
176
 
# File 'lib/google_drive_v0/worksheet.rb', line 172

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

#num_cols ⇒ Object

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



157
158
159
160
161
162
 
# File 'lib/google_drive_v0/worksheet.rb', line 157

def num_cols
  reload() 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.



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

def num_rows
  reload() 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



142
143
144
145
146
 
# File 'lib/google_drive_v0/worksheet.rb', line 142

def numeric_value(*args)
  (row, col) = parse_cell_args(args)
  reload() 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().



231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
 
# File 'lib/google_drive_v0/worksheet.rb', line 231

def reload()
  
  doc = @session.request(:get, @cells_feed_url)
  @max_rows = doc.css("gs|rowCount").text.to_i()
  @max_cols = doc.css("gs|colCount").text.to_i()
  @title = doc.css("feed > title")[0].text

  @num_cols = nil
  @num_rows = nil

  @cells = {}
  @input_values = {}
  @numeric_values = {}
  doc.css("feed > entry").each() do |entry|
    cell = entry.css("gs|cell")[0]
    row = cell["row"].to_i()
    col = cell["col"].to_i()
    @cells[[row, col]] = cell.inner_text
    @input_values[[row, col]] = cell["inputValue"] || cell.inner_text
    numeric_value = cell["numericValue"]
    @numeric_values[[row, col]] = numeric_value ? numeric_value.to_f() : nil
  end
  @modified.clear()
  @meta_modified = false
  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]



221
222
223
224
225
226
227
 
# File 'lib/google_drive_v0/worksheet.rb', line 221

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.



260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
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
 
# File 'lib/google_drive_v0/worksheet.rb', line 260

def save()
  
  sent = false

  if @meta_modified

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

    @session.request(
        :put, edit_url, :data => xml,
        :header => {"Content-Type" => "application/atom+xml;charset=utf-8", "If-Match" => "*"})

    @meta_modified = false
    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(@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

    # Updates cell values using batch operation.
    # If the data is large, we split it into multiple operations, otherwise batch may fail.
    @modified.each_slice(25) do |chunk|

      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
      for row, col in chunk
        value = @cells[[row, col]]
        entry = cell_entries[[row, col]]
        id = entry.css("id").text
        edit_url = entry.css("link[rel='edit']")[0]["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" => "*"})
      for entry in result.css("atom|entry")
        interrupted = entry.css("batch|interrupted")[0]
        if interrupted
          raise(GoogleDriveV0::Error, "Update has failed: %s" %
            interrupted["reason"])
        end
        if !(entry.css("batch|status").first["code"] =~ /^2/)
          raise(GoogleDriveV0::Error, "Updating cell %s has failed: %s" %
            [entry.css("atom|id").text, entry.css("batch|status")[0]["reason"]])
        end
      end

    end

    @modified.clear()
    sent = true

  end
  
  return sent
  
end

#spreadsheet ⇒ Object

GoogleDriveV0::Spreadsheet which this worksheet belongs to.



53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/google_drive_v0/worksheet.rb', line 53

def spreadsheet
  if !@spreadsheet
    if !(@cells_feed_url =~
        %r{^https?://spreadsheets.google.com/feeds/cells/(.*)/(.*)/private/full(\?.*)?$})
      raise(GoogleDriveV0::Error,
        "Cells feed URL is in unknown format: #{@cells_feed_url}")
    end
    @spreadsheet = @session.spreadsheet_by_key($1)
  end
  return @spreadsheet
end

#synchronize ⇒ Object

Calls save() and reload().



360
361
362
363
 
# File 'lib/google_drive_v0/worksheet.rb', line 360

def synchronize()
  save()
  reload()
end

#tables ⇒ Object

DEPRECATED: Table and Record feeds are deprecated and they will not be available after March 2012.

Returns list of tables for the workwheet.



418
419
420
421
422
423
 
# File 'lib/google_drive_v0/worksheet.rb', line 418

def tables
  warn(
      "DEPRECATED: Google Spreadsheet Table and Record feeds are deprecated and they " +
      "will not be available after March 2012.")
  return self.spreadsheet.tables.select(){ |t| t.worksheet_title == self.title }
end

#title ⇒ Object

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



193
194
195
196
 
# File 'lib/google_drive_v0/worksheet.rb', line 193

def title
  reload() if !@title
  return @title
end

#title=(title) ⇒ Object

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



206
207
208
209
210
 
# File 'lib/google_drive_v0/worksheet.rb', line 206

def title=(title)
  reload() if !@cells
  @title = title
  @meta_modified = true
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"]])



109
110
111
112
113
114
115
 
# File 'lib/google_drive_v0/worksheet.rb', line 109

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

#updated ⇒ Object

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



199
200
201
202
 
# File 'lib/google_drive_v0/worksheet.rb', line 199

def updated
  reload() if !@updated
  return @updated
end

#worksheet_feed_url ⇒ Object

URL of worksheet feed URL of the worksheet.



40
41
42
43
44
45
46
47
48
49
50
 
# File 'lib/google_drive_v0/worksheet.rb', line 40

def worksheet_feed_url
  # I don't know good way to get worksheet feed URL from cells feed URL.
  # Probably it would be cleaner to keep worksheet feed URL and get cells feed URL
  # from it.
  if !(@cells_feed_url =~
      %r{^https?://spreadsheets.google.com/feeds/cells/(.*)/(.*)/private/full((\?.*)?)$})
    raise(GoogleDriveV0::Error,
      "Cells feed URL is in unknown format: #{@cells_feed_url}")
  end
  return "https://spreadsheets.google.com/feeds/worksheets/#{$1}/private/full/#{$2}#{$3}"
end