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

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



400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
 
# File 'lib/google_drive/worksheet.rb', line 400

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:



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

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



364
365
366
367
368
 
# File 'lib/google_drive/worksheet.rb', line 364

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) 


371
372
373
 
# File 'lib/google_drive/worksheet.rb', line 371

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



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

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

#inspect ⇒ Object 



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

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



393
394
395
 
# File 'lib/google_drive/worksheet.rb', line 393

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

#list_feed_url ⇒ Object

List feed URL of the worksheet.



376
377
378
379
 
# File 'lib/google_drive/worksheet.rb', line 376

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.



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

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



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

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

#max_rows ⇒ Object

Number of rows including empty rows.



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

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



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

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.



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

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.



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

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



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

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



249
250
251
252
253
 
# File 'lib/google_drive/worksheet.rb', line 249

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]



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

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.



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
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/google_drive/worksheet.rb', line 256

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



358
359
360
361
 
# File 'lib/google_drive/worksheet.rb', line 358

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



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

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