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:



511
512
513
514
515
516
517
518
519
520
 
# File 'lib/google_spreadsheet.rb', line 511

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.



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

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



552
553
554
 
# File 'lib/google_spreadsheet.rb', line 552

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"



563
564
565
566
567
568
569
570
 
# File 'lib/google_spreadsheet.rb', line 563

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



786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
 
# File 'lib/google_spreadsheet.rb', line 786

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:



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

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

#delete ⇒ Object

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



772
773
774
775
776
 
# File 'lib/google_spreadsheet.rb', line 772

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) 


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

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



576
577
578
579
 
# File 'lib/google_spreadsheet.rb', line 576

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

#max_cols ⇒ Object

Number of columns including empty columns.



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

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



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

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

#max_rows ⇒ Object

Number of rows including empty rows.



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

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



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

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.



588
589
590
591
 
# File 'lib/google_spreadsheet.rb', line 588

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.



582
583
584
585
 
# File 'lib/google_spreadsheet.rb', line 582

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



653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
 
# File 'lib/google_spreadsheet.rb', line 653

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



643
644
645
646
647
648
649
 
# File 'lib/google_spreadsheet.rb', line 643

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.



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
756
757
758
759
760
761
762
763
 
# File 'lib/google_spreadsheet.rb', line 674

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.



539
540
541
542
543
544
545
546
547
548
549
 
# File 'lib/google_spreadsheet.rb', line 539

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



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

def synchronize()
  save()
  reload()
end

#tables ⇒ Object

Returns list of tables for the workwheet.



810
811
812
 
# File 'lib/google_spreadsheet.rb', line 810

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



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

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



629
630
631
632
633
 
# File 'lib/google_spreadsheet.rb', line 629

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

#worksheet_feed_url ⇒ Object

URL of worksheet feed URL of the worksheet.



526
527
528
529
530
531
532
533
534
535
536
 
# File 'lib/google_spreadsheet.rb', line 526

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