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

Constructor Details

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

:nodoc:



503
504
505
506
507
508
509
510
511
512
 
# File 'lib/google_spreadsheet.rb', line 503

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.



515
516
517
 
# File 'lib/google_spreadsheet.rb', line 515

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



544
545
546
 
# File 'lib/google_spreadsheet.rb', line 544

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"



555
556
557
558
559
560
561
562
 
# File 'lib/google_spreadsheet.rb', line 555

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



778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
 
# File 'lib/google_spreadsheet.rb', line 778

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

#cells ⇒ Object

:nodoc:



627
628
629
630
 
# File 'lib/google_spreadsheet.rb', line 627

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

#delete ⇒ Object

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



764
765
766
767
768
 
# File 'lib/google_spreadsheet.rb', line 764

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


771
772
773
 
# File 'lib/google_spreadsheet.rb', line 771

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



568
569
570
571
 
# File 'lib/google_spreadsheet.rb', line 568

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

#max_cols ⇒ Object

Number of columns including empty columns.



600
601
602
603
 
# File 'lib/google_spreadsheet.rb', line 600

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



607
608
609
610
611
 
# File 'lib/google_spreadsheet.rb', line 607

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

#max_rows ⇒ Object

Number of rows including empty rows.



586
587
588
589
 
# File 'lib/google_spreadsheet.rb', line 586

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



593
594
595
596
597
 
# File 'lib/google_spreadsheet.rb', line 593

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.



580
581
582
583
 
# File 'lib/google_spreadsheet.rb', line 580

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.



574
575
576
577
 
# File 'lib/google_spreadsheet.rb', line 574

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



645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
 
# File 'lib/google_spreadsheet.rb', line 645

def reload()
  doc = @session.request(: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("/feed/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].



635
636
637
638
639
640
641
 
# File 'lib/google_spreadsheet.rb', line 635

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.



666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
 
# File 'lib/google_spreadsheet.rb', line 666

def save()
  sent = false
  
  if @meta_modified
    
    ws_doc = @session.request(: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.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 = "#{@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.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.
    # 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.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.request(:post, "#{@cells_feed_url}/batch", :data => 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
      
    end
    
    @modified.clear()
    sent = true
    
  end
  return sent
end

#spreadsheet ⇒ Object

GoogleSpreadsheet::Spreadsheet which this worksheet belongs to.



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

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



758
759
760
761
 
# File 'lib/google_spreadsheet.rb', line 758

def synchronize()
  save()
  reload()
end

#tables ⇒ Object

Returns list of tables for the workwheet.



802
803
804
 
# File 'lib/google_spreadsheet.rb', line 802

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



614
615
616
617
 
# File 'lib/google_spreadsheet.rb', line 614

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



621
622
623
624
625
 
# File 'lib/google_spreadsheet.rb', line 621

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

#worksheet_feed_url ⇒ Object

URL of worksheet feed URL of the worksheet.



518
519
520
521
522
523
524
525
526
527
528
 
# File 'lib/google_spreadsheet.rb', line 518

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