Class: GoogleSpreadsheet::Worksheet

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

Overview

Use GoogleSpreadsheet::Spreadsheet#worksheets to get GoogleSpreadsheet::Worksheet object.

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Util

as_utf8, encode_query, h, http_request, uri_encode

Constructor Details

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

:nodoc:



402
403
404
405
406
407
408
409
410
411
 
# File 'lib/google_spreadsheet.rb', line 402

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
  @modified = Set.new()
end

Instance Attribute Details

#cells_feed_url ⇒ Object (readonly)

URL of cell-based feed of the worksheet.



414
415
416
 
# File 'lib/google_spreadsheet.rb', line 414

def cells_feed_url
  @cells_feed_url
end

Instance Method Details

#[](row, col) ⇒ Object

Returns content of the cell as String. Top-left cell is [1, 1].



443
444
445
 
# File 'lib/google_spreadsheet.rb', line 443

def [](row, col)
  return self.cells[[row, col]] || ""
end

#[]=(row, col, value) ⇒ Object

Updates content of the cell. 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[1, 3] = "=A1+B1"



454
455
456
457
458
459
460
461
 
# File 'lib/google_spreadsheet.rb', line 454

def []=(row, col, value)
  reload() if !@cells
  @cells[[row, col]] = value
  @input_values[[row, col]] = value
  @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) ⇒ Object

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



669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
 
# File 'lib/google_spreadsheet.rb', line 669

def add_table(table_title, summary, columns)
  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='1' />
      <gs:data numRows='0' startRow='2'>
        #{column_xml}
      </gs:data>
    </entry>
  EOS

  result = @session.post(self.spreadsheet.tables_feed_url, xml)
  return Table.new(@session, result)
end

#cells ⇒ Object

:nodoc:



523
524
525
526
 
# File 'lib/google_spreadsheet.rb', line 523

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

#delete ⇒ Object

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



655
656
657
658
659
 
# File 'lib/google_spreadsheet.rb', line 655

def delete
  ws_doc = @session.get(self.worksheet_feed_url)
  edit_url = ws_doc.search("link[@rel='edit']")[0]["href"]
  @session.delete(edit_url)
end

#dirty? ⇒ Boolean

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

Returns:

  • (Boolean) 


662
663
664
 
# File 'lib/google_spreadsheet.rb', line 662

def dirty?
  return !@modified.empty?
end

#input_value(row, col) ⇒ Object

Returns the value or the formula of the cell. Top-left cell is [1, 1].

If user input “=A1+B1” to cell [1, 3], worksheet[1, 3] is “3” for example and worksheet.input_value(1, 3) is “=RC+RC”.



467
468
469
470
 
# File 'lib/google_spreadsheet.rb', line 467

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

#max_cols ⇒ Object

Number of columns including empty columns.



498
499
500
501
 
# File 'lib/google_spreadsheet.rb', line 498

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



505
506
507
508
 
# File 'lib/google_spreadsheet.rb', line 505

def max_cols=(cols)
  @max_cols = cols
  @meta_modified = true
end

#max_rows ⇒ Object

Number of rows including empty rows.



485
486
487
488
 
# File 'lib/google_spreadsheet.rb', line 485

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



492
493
494
495
 
# File 'lib/google_spreadsheet.rb', line 492

def max_rows=(rows)
  @max_rows = rows
  @meta_modified = true
end

#num_cols ⇒ Object

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



479
480
481
482
 
# File 'lib/google_spreadsheet.rb', line 479

def num_cols
  reload() if !@cells
  return @cells.keys.map(){ |r, c| c }.max || 0
end

#num_rows ⇒ Object

Row number of the bottom-most non-empty row.



473
474
475
476
 
# File 'lib/google_spreadsheet.rb', line 473

def num_rows
  reload() if !@cells
  return @cells.keys.map(){ |r, c| r }.max || 0
end

#reload ⇒ Object

Reloads content of the worksheets from the server. Note that changes you made by []= is discarded if you haven’t called save().



541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
 
# File 'lib/google_spreadsheet.rb', line 541

def reload()
  doc = @session.get(@cells_feed_url)
  @max_rows = doc.search("gs:rowCount").text.to_i()
  @max_cols = doc.search("gs:colCount").text.to_i()
  @title = as_utf8(doc.search("title").text)
  
  @cells = {}
  @input_values = {}
  for entry in doc.search("entry")
    cell = entry.search("gs:cell")[0]
    row = cell["row"].to_i()
    col = cell["col"].to_i()
    @cells[[row, col]] = as_utf8(cell.inner_text)
    @input_values[[row, col]] = as_utf8(cell["inputValue"])
  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] == worksheet[1, 1].



531
532
533
534
535
536
537
 
# File 'lib/google_spreadsheet.rb', line 531

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.



562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
 
# File 'lib/google_spreadsheet.rb', line 562

def save()
  sent = false
  
  if @meta_modified
    
    ws_doc = @session.get(self.worksheet_feed_url)
    edit_url = ws_doc.search("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.put(edit_url, 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 = "#{@cells_feed_url}?return-empty=true&min-row=#{rows.min}&max-row=#{rows.max}" +
      "&min-col=#{cols.min}&max-col=#{cols.max}"
    doc = @session.get(url)
    for entry in doc.search("entry")
      row = entry.search("gs:cell")[0]["row"].to_i()
      col = entry.search("gs:cell")[0]["col"].to_i()
      cell_entries[[row, col]] = entry
    end
    
    # Updates cell values using batch operation.
    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 @modified
      value = @cells[[row, col]]
      entry = cell_entries[[row, col]]
      id = entry.search("id").text
      edit_url = entry.search("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
    
    result = @session.post("#{@cells_feed_url}/batch", xml)
    for entry in result.search("atom:entry")
      interrupted = entry.search("batch:interrupted")[0]
      if interrupted
        raise(GoogleSpreadsheet::Error, "Update has failed: %s" %
          interrupted["reason"])
      end
      if !(entry.search("batch:status")[0]["code"] =~ /^2/)
        raise(GoogleSpreadsheet::Error, "Updating cell %s has failed: %s" %
          [entry.search("atom:id").text, entry.search("batch:status")[0]["reason"]])
      end
    end
    
    @modified.clear()
    sent = true
    
  end
  return sent
end

#spreadsheet ⇒ Object

GoogleSpreadsheet::Spreadsheet which this worksheet belongs to.



430
431
432
433
434
435
436
437
438
439
440
 
# File 'lib/google_spreadsheet.rb', line 430

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



649
650
651
652
 
# File 'lib/google_spreadsheet.rb', line 649

def synchronize()
  save()
  reload()
end

#tables ⇒ Object

Returns list of tables for the workwheet.



693
694
695
 
# File 'lib/google_spreadsheet.rb', line 693

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



511
512
513
514
 
# File 'lib/google_spreadsheet.rb', line 511

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



518
519
520
521
 
# File 'lib/google_spreadsheet.rb', line 518

def title=(title)
  @title = title
  @meta_modified = true
end

#worksheet_feed_url ⇒ Object

URL of worksheet feed URL of the worksheet.



417
418
419
420
421
422
423
424
425
426
427
 
# File 'lib/google_spreadsheet.rb', line 417

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{^http://spreadsheets.google.com/feeds/cells/(.*)/(.*)/private/full$})
    raise(GoogleSpreadsheet::Error,
      "cells feed URL is in unknown format: #{@cells_feed_url}")
  end
  return "http://spreadsheets.google.com/feeds/worksheets/#{$1}/private/full/#{$2}"
end