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:



515
516
517
518
519
520
521
522
523
524
 
# File 'lib/google_spreadsheet.rb', line 515

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.



527
528
529
 
# File 'lib/google_spreadsheet.rb', line 527

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



556
557
558
 
# File 'lib/google_spreadsheet.rb', line 556

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"



567
568
569
570
571
572
573
574
 
# File 'lib/google_spreadsheet.rb', line 567

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



799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
 
# File 'lib/google_spreadsheet.rb', line 799

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:



639
640
641
642
 
# File 'lib/google_spreadsheet.rb', line 639

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

#delete ⇒ Object

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



785
786
787
788
789
 
# File 'lib/google_spreadsheet.rb', line 785

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) 


792
793
794
 
# File 'lib/google_spreadsheet.rb', line 792

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



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

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

#max_cols ⇒ Object

Number of columns including empty columns.



612
613
614
615
 
# File 'lib/google_spreadsheet.rb', line 612

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



619
620
621
622
623
 
# File 'lib/google_spreadsheet.rb', line 619

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

#max_rows ⇒ Object

Number of rows including empty rows.



598
599
600
601
 
# File 'lib/google_spreadsheet.rb', line 598

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



605
606
607
608
609
 
# File 'lib/google_spreadsheet.rb', line 605

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.



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

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.



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

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



657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
 
# File 'lib/google_spreadsheet.rb', line 657

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



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

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.



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
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
 
# File 'lib/google_spreadsheet.rb', line 678

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, :header => {'If-Match' => '*', "Content-Type" => "application/atom+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, :header => {'GData-Version' => '1'})

    #puts doc.to_s
    #puts @cells_feed_url

    for entry in doc.search("entry")
      #p 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"/>
            <title>#{h(row)} #{h(col)}</title>
            <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
      
      #puts xml
    
      result = @session.request(:post, "#{@cells_feed_url}/batch", :data => xml, :header => {'GData-Version' => '1', "Content-Type" => "application/atom+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.



543
544
545
546
547
548
549
550
551
552
553
 
# File 'lib/google_spreadsheet.rb', line 543

def spreadsheet
  if !@spreadsheet
    if !(@cells_feed_url =~
        %r{^https?://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().



779
780
781
782
 
# File 'lib/google_spreadsheet.rb', line 779

def synchronize()
  save()
  reload()
end

#tables ⇒ Object

Returns list of tables for the workwheet.



823
824
825
 
# File 'lib/google_spreadsheet.rb', line 823

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



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

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



633
634
635
636
637
 
# File 'lib/google_spreadsheet.rb', line 633

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

#worksheet_feed_url ⇒ Object

URL of worksheet feed URL of the worksheet.



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

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