class GoogleSpreadsheet::Worksheet

[](*args) click to toggle source

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"

# File lib/google_spreadsheet/worksheet.rb, line 69
def [](*args)
  (row, col) = parse_cell_args(args)
  return self.cells[[row, col]] || ""
end

[]=(*args) click to toggle source

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"

# File lib/google_spreadsheet/worksheet.rb, line 83
def []=(*args)
  (row, col) = parse_cell_args(args[0...-1])
  value = args[-1].to_s()
  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, options) click to toggle source

DEPRECATED: Table and Record feeds are deprecated and they will not be available after March 2012.

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

# File lib/google_spreadsheet/worksheet.rb, line 335
        def add_table(table_title, summary, columns, options)
          
          warn(
              "DEPRECATED: Google Spreadsheet Table and Record feeds are deprecated and they " +
              "will not be available after March 2012.")
          default_options = { :header_row => 1, :num_rows => 0, :start_row => 2}
          options = default_options.merge(options)

          column_xml = ""
          columns.each() do |index, name|
            column_xml += "<gs:column index='#{h(index)}' name='#{h(name)}'/>\n"
          end

          xml = "            <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='#{options[:header_row]}' />
              <gs:data numRows='#{options[:num_rows]}' startRow='#{options[:start_row]}'>
                #{column_xml}
              </gs:data>
            </entry>
"

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

cell_name_to_row_col(cell_name) click to toggle source

Returns a [row, col] pair for a cell name string. e.g.

worksheet.cell_name_to_row_col("C2")  #=> [2, 3]

# File lib/google_spreadsheet/worksheet.rb, line 406
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 =~ %r^([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

delete() click to toggle source

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

# File lib/google_spreadsheet/worksheet.rb, line 318
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?() click to toggle source

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

# File lib/google_spreadsheet/worksheet.rb, line 325
def dirty?
  return !@modified.empty?
end

input_value(*args) click to toggle source

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

# File lib/google_spreadsheet/worksheet.rb, line 113
def input_value(*args)
  (row, col) = parse_cell_args(args)
  reload() if !@cells
  return @input_values[[row, col]] || ""
end

inspect() click to toggle source

# File lib/google_spreadsheet/worksheet.rb, line 424
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() click to toggle source

Provides access to cells using column names, assuming the first row contains column names. Returned object is GoogleSpreadsheet::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().

# File lib/google_spreadsheet/worksheet.rb, line 399
def list
  return @list ||= List.new(self)
end

list_feed_url() click to toggle source

List feed URL of the worksheet.

# File lib/google_spreadsheet/worksheet.rb, line 378
def list_feed_url
  # Gets the worksheets metafeed.
  entry = @session.request(:get, self.worksheet_feed_url)

  # Gets the URL of list-based feed for the given spreadsheet.
  return entry.css(
    "link[rel='http://schemas.google.com/spreadsheets/2006#listfeed']")[0]["href"]
end

max_cols() click to toggle source

Number of columns including empty columns.

# File lib/google_spreadsheet/worksheet.rb, line 146
def max_cols
  reload() if !@cells
  return @max_cols
end

max_cols=(cols) click to toggle source

Updates number of columns. Note that update is not sent to the server until you call save().

# File lib/google_spreadsheet/worksheet.rb, line 153
def max_cols=(cols)
  reload() if !@cells
  @max_cols = cols
  @meta_modified = true
end

max_rows() click to toggle source

Number of rows including empty rows.

# File lib/google_spreadsheet/worksheet.rb, line 132
def max_rows
  reload() if !@cells
  return @max_rows
end

max_rows=(rows) click to toggle source

Updates number of rows. Note that update is not sent to the server until you call save().

# File lib/google_spreadsheet/worksheet.rb, line 139
def max_rows=(rows)
  reload() if !@cells
  @max_rows = rows
  @meta_modified = true
end

num_cols() click to toggle source

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

# File lib/google_spreadsheet/worksheet.rb, line 126
def num_cols
  reload() if !@cells
  return @input_values.select(){ |(r, c), v| !v.empty? }.map(){ |(r, c), v| c }.max || 0
end

num_rows() click to toggle source

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

# File lib/google_spreadsheet/worksheet.rb, line 120
def num_rows
  reload() if !@cells
  return @input_values.select(){ |(r, c), v| !v.empty? }.map(){ |(r, c), v| r }.max || 0
end

reload() click to toggle source

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

# File lib/google_spreadsheet/worksheet.rb, line 191
def reload()
  
  doc = @session.request(:get, @cells_feed_url)
  @max_rows = doc.css("gs|rowCount").text.to_i()
  @max_cols = doc.css("gs|colCount").text.to_i()
  @title = doc.css("feed > title")[0].text

  @cells = {}
  @input_values = {}
  doc.css("feed > entry").each() do |entry|
    cell = entry.css("gs|cell")[0]
    row = cell["row"].to_i()
    col = cell["col"].to_i()
    @cells[[row, col]] = cell.inner_text
    @input_values[[row, col]] = cell["inputValue"]
  end
  @modified.clear()
  @meta_modified = false
  return true
  
end

rows(skip = 0) click to toggle source

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

# File lib/google_spreadsheet/worksheet.rb, line 181
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() click to toggle source

Saves your changes made by []=, etc. to the server.

# File lib/google_spreadsheet/worksheet.rb, line 214
        def save()
          
          sent = false

          if @meta_modified

            ws_doc = @session.request(:get, self.worksheet_feed_url)
            edit_url = ws_doc.css("link[rel='edit']")[0]["href"]
            xml = "              <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>
"

            @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 = concat_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)

            doc.css("entry").each() do |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

            # 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 = "                <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>
"
              for row, col in chunk
                value = @cells[[row, col]]
                entry = cell_entries[[row, col]]
                id = entry.css("id").text
                edit_url = entry.css("link[rel='edit']")[0]["href"]
                xml << "                  <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>
"
              end
              xml << "                </feed>
"

              batch_url = concat_url(@cells_feed_url, "/batch")
              result = @session.request(:post, batch_url, :data => xml)
              result.css("atom|entry").each() do |entry|
                interrupted = entry.css("batch|interrupted")[0]
                if interrupted
                  raise(GoogleSpreadsheet::Error, "Update has failed: %s" %
                    interrupted["reason"])
                end
                if !(entry.css("batch|status").first["code"] =~ %r^2/)
                  raise(GoogleSpreadsheet::Error, "Updating cell %s has failed: %s" %
                    [entry.css("atom|id").text, entry.css("batch|status")[0]["reason"]])
                end
              end

            end

            @modified.clear()
            sent = true

          end
          
          return sent
          
        end

spreadsheet() click to toggle source

GoogleSpreadsheet::Spreadsheet which this worksheet belongs to.

# File lib/google_spreadsheet/worksheet.rb, line 51
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() click to toggle source

Calls save() and reload().

# File lib/google_spreadsheet/worksheet.rb, line 312
def synchronize()
  save()
  reload()
end

tables() click to toggle source

DEPRECATED: Table and Record feeds are deprecated and they will not be available after March 2012.

Returns list of tables for the workwheet.

# File lib/google_spreadsheet/worksheet.rb, line 370
def tables
  warn(
      "DEPRECATED: Google Spreadsheet Table and Record feeds are deprecated and they " +
      "will not be available after March 2012.")
  return self.spreadsheet.tables.select(){ |t| t.worksheet_title == self.title }
end

title() click to toggle source

Title of the worksheet (shown as tab label in Web interface).

# File lib/google_spreadsheet/worksheet.rb, line 160
def title
  reload() if !@title
  return @title
end

title=(title) click to toggle source

Updates title of the worksheet. Note that update is not sent to the server until you call save().

# File lib/google_spreadsheet/worksheet.rb, line 167
def title=(title)
  reload() if !@cells
  @title = title
  @meta_modified = true
end

update_cells(top_row, left_col, darray) click to toggle source

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

# File lib/google_spreadsheet/worksheet.rb, line 99
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() click to toggle source

URL of worksheet feed URL of the worksheet.

# File lib/google_spreadsheet/worksheet.rb, line 38
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}#{$3}"
end

Attributes

Public Instance Methods