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::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) ⇒ Worksheet

:nodoc:



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

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

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



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

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"



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

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"



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

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
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 GoogleDrive::Table. See this document for details: code.google.com/intl/en/apis/spreadsheets/docs/3.0/developers_guide_protocol.html#TableFeeds



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

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]



431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
 
# File 'lib/google_drive/worksheet.rb', line 431

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:



194
195
196
197
 
# File 'lib/google_drive/worksheet.rb', line 194

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

#delete ⇒ Object

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



343
344
345
346
347
 
# File 'lib/google_drive/worksheet.rb', line 343

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) 


350
351
352
 
# File 'lib/google_drive/worksheet.rb', line 350

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



115
116
117
118
119
 
# File 'lib/google_drive/worksheet.rb', line 115

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

#inspect ⇒ Object 



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

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



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

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

#list_feed_url ⇒ Object

List feed URL of the worksheet.



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

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.



167
168
169
170
 
# File 'lib/google_drive/worksheet.rb', line 167

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



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

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

#max_rows ⇒ Object

Number of rows including empty rows.



153
154
155
156
 
# File 'lib/google_drive/worksheet.rb', line 153

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



160
161
162
163
164
 
# File 'lib/google_drive/worksheet.rb', line 160

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.



147
148
149
150
 
# File 'lib/google_drive/worksheet.rb', line 147

def num_cols
  reload() if !@cells
  return @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.



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

def num_rows
  reload() if !@cells
  return @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



134
135
136
137
138
 
# File 'lib/google_drive/worksheet.rb', line 134

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



213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
 
# File 'lib/google_drive/worksheet.rb', line 213

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

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



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

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.



239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
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
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
 
# File 'lib/google_drive/worksheet.rb', line 239

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)

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

    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

    # Updates cell values using batch operation.
    # If the data is large, we split it into multiple operations, otherwise batch may fail.
    @modified.each_slice(250) 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)
      result.css("atom|entry").each() do |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("atom|id").text, entry.css("batch|status")[0]["reason"]])
        end
      end

    end

    @modified.clear()
    sent = true

  end
  
  return sent
  
end

#spreadsheet ⇒ Object

GoogleDrive::Spreadsheet which this worksheet belongs to.



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

def spreadsheet
  if !@spreadsheet
    if !(@cells_feed_url =~
        %r{^https?://spreadsheets.google.com/feeds/cells/(.*)/(.*)/private/full(\?.*)?$})
      raise(GoogleDrive::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().



337
338
339
340
 
# File 'lib/google_drive/worksheet.rb', line 337

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.



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

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



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

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



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

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



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

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.



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

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(GoogleDrive::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