Class: GoogleDrive::Worksheet
- Inherits:
-
Object
- Object
- GoogleDrive::Worksheet
- Includes:
- Util
- Defined in:
- lib/google_drive/worksheet.rb
Overview
A worksheet (i.e. a tab) in a spreadsheet. Use GoogleDrive::Spreadsheet#worksheets to get GoogleDrive::Worksheet object.
Defined Under Namespace
Modules: Colors
Constant Summary collapse
- XML_INVAILD_CHAR_REGEXP =
This constant is part of a private API. You should avoid using this constant if possible, as it may be removed or be changed in the future.
A regexp which matches an invalid character in XML 1.0: en.wikipedia.org/wiki/Valid_characters_in_XML#XML_1.0
/[^\u0009\u000a\u000d\u0020-\ud7ff\ue000-\ufffd\u{10000}-\u{10ffff}]/
Constants included from Util
Util::EXT_TO_CONTENT_TYPE, Util::IMPORTABLE_CONTENT_TYPE_MAP
Instance Attribute Summary collapse
-
#max_cols ⇒ Object
Number of columns including empty columns.
-
#max_rows ⇒ Object
Number of rows including empty rows.
-
#properties ⇒ Object
readonly
Google::Apis::SheetsV4::SheetProperties object for this worksheet.
-
#spreadsheet ⇒ Object
readonly
GoogleDrive::Spreadsheet which this worksheet belongs to.
-
#title ⇒ Object
Title of the worksheet (shown as tab label in Web interface).
Instance Method Summary collapse
-
#[](*args) ⇒ Object
Returns content of the cell as String.
-
#[]=(*args) ⇒ Object
Updates content of the cell.
-
#add_request(request) ⇒ Object
Add an instance of Google::Apis::SheetsV4::Request (or its Hash equivalent) which will be applied on the next call to the save method.
-
#cell_name_to_row_col(cell_name) ⇒ Object
Returns a [row, col] pair for a cell name string.
- #cells ⇒ Object private
-
#cells_feed_url ⇒ Object
URL of cell-based feed of the worksheet.
-
#csv_export_url ⇒ Object
URL to export the worksheet as CSV.
-
#delete ⇒ Object
Deletes this worksheet.
-
#delete_rows(row_num, rows) ⇒ Object
Deletes rows.
-
#dirty? ⇒ Boolean
Returns true if you have changes made by []= etc.
-
#export_as_file(path) ⇒ Object
Exports the worksheet to
path
in CSV format. -
#export_as_string ⇒ Object
Exports the worksheet as String in CSV format.
-
#gid ⇒ Object
Returns sheet_id.to_s.
-
#human_url ⇒ Object
URL to view/edit the worksheet in a Web browser.
-
#initialize(session, spreadsheet, properties) ⇒ Worksheet
constructor
private
A new instance of Worksheet.
-
#input_value(*args) ⇒ Object
Returns the value or the formula of the cell.
-
#insert_rows(row_num, rows) ⇒ Object
Inserts rows.
- #inspect ⇒ Object
-
#list ⇒ Object
Provides access to cells using column names, assuming the first row contains column names.
-
#list_feed_url ⇒ Object
List feed URL of the worksheet.
-
#merge_cells(top_row, left_col, num_rows, num_cols, merge_type: 'MERGE_ALL') ⇒ Object
Merges a range of cells together.
-
#num_cols ⇒ Object
Column number of the right-most non-empty column.
-
#num_rows ⇒ Object
Row number of the bottom-most non-empty row.
-
#numeric_value(*args) ⇒ Object
Returns the numeric value of the cell.
-
#reload ⇒ Object
Reloads content of the worksheets from the server.
-
#rows(skip = 0) ⇒ Object
An array of spreadsheet rows.
-
#save ⇒ Object
Saves your changes made by []=, etc.
-
#set_background_color(top_row, left_col, num_rows, num_cols, background_color) ⇒ Object
Changes the background color on a range of cells.
-
#set_number_format(top_row, left_col, num_rows, num_cols, pattern, type: "NUMBER") ⇒ Object
Changes the formatting of a range of cells to match the given number format.
-
#set_text_alignment(top_row, left_col, num_rows, num_cols, horizontal: nil, vertical: nil) ⇒ Object
Changes text alignment of a range of cells.
-
#set_text_format(top_row, left_col, num_rows, num_cols, bold: false, italic: false, strikethrough: false, font_size: nil, font_family: nil, foreground_color: nil) ⇒ Object
Change the text formatting on a range of cells.
-
#sheet_id ⇒ Object
ID of the worksheet.
-
#synchronize ⇒ Object
Calls save() and reload().
-
#update_borders(top_row, left_col, num_rows, num_cols, borders) ⇒ Object
Update the border styles for a range of cells.
-
#update_cells(top_row, left_col, darray) ⇒ Object
Updates cells in a rectangle area by a two-dimensional Array.
-
#updated ⇒ Object
Time object which represents the time the worksheet was last updated.
-
#worksheet_feed_entry ⇒ Object
Nokogiri::XML::Element object of the <entry> element in a worksheets feed.
- #worksheet_feed_id ⇒ Object private
-
#worksheet_feed_url ⇒ Object
URL of worksheet feed URL of the worksheet.
Methods included from Util
concat_url, construct_and_query, construct_query, convert_params, delegate_api_methods, encode_query, get_singleton_class, h
Constructor Details
#initialize(session, spreadsheet, properties) ⇒ Worksheet
This method is part of a private API. You should avoid using this method if possible, as it may be removed or be changed in the future.
Returns a new instance of Worksheet.
57 58 59 60 61 62 63 64 65 66 67 |
# File 'lib/google_drive/worksheet.rb', line 57 def initialize(session, spreadsheet, properties) @session = session @spreadsheet = spreadsheet set_properties(properties) @cells = nil @input_values = nil @numeric_values = nil @modified = Set.new @list = nil @v4_requests = [] end |
Instance Attribute Details
#max_cols ⇒ Object
Number of columns including empty columns.
267 268 269 |
# File 'lib/google_drive/worksheet.rb', line 267 def max_cols @max_cols end |
#max_rows ⇒ Object
Number of rows including empty rows.
257 258 259 |
# File 'lib/google_drive/worksheet.rb', line 257 def max_rows @max_rows end |
#properties ⇒ Object (readonly)
Google::Apis::SheetsV4::SheetProperties object for this worksheet.
78 79 80 |
# File 'lib/google_drive/worksheet.rb', line 78 def properties @properties end |
#spreadsheet ⇒ Object (readonly)
GoogleDrive::Spreadsheet which this worksheet belongs to.
84 85 86 |
# File 'lib/google_drive/worksheet.rb', line 84 def spreadsheet @spreadsheet end |
#title ⇒ Object
Title of the worksheet (shown as tab label in Web interface).
81 82 83 |
# File 'lib/google_drive/worksheet.rb', line 81 def title @title end |
Instance Method Details
#[](*args) ⇒ Object
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"
148 149 150 151 |
# File 'lib/google_drive/worksheet.rb', line 148 def [](*args) (row, col) = parse_cell_args(args) cells[[row, col]] || '' end |
#[]=(*args) ⇒ Object
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"
163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 |
# File 'lib/google_drive/worksheet.rb', line 163 def []=(*args) (row, col) = parse_cell_args(args[0...-1]) value = args[-1].to_s validate_cell_value(value) reload_cells unless @cells @cells[[row, col]] = value @input_values[[row, col]] = value @numeric_values[[row, col]] = nil @modified.add([row, col]) self.max_rows = row if row > @max_rows self.max_cols = col if col > @max_cols if value.empty? @num_rows = nil @num_cols = nil else @num_rows = row if @num_rows && row > @num_rows @num_cols = col if @num_cols && col > @num_cols end end |
#add_request(request) ⇒ Object
Add an instance of Google::Apis::SheetsV4::Request (or its Hash equivalent) which will be applied on the next call to the save method.
613 614 615 |
# File 'lib/google_drive/worksheet.rb', line 613 def add_request(request) @v4_requests.push(request) end |
#cell_name_to_row_col(cell_name) ⇒ Object
Returns a [row, col] pair for a cell name string. e.g.
worksheet.cell_name_to_row_col("C2") #=> [2, 3]
484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 |
# File 'lib/google_drive/worksheet.rb', line 484 def cell_name_to_row_col(cell_name) unless cell_name.is_a?(String) raise( ArgumentError, format('Cell name must be a string: %p', cell_name) ) end unless cell_name.upcase =~ /^([A-Z]+)(\d+)$/ raise( ArgumentError, format( 'Cell name must be only letters followed by digits with no ' \ 'spaces in between: %p', cell_name ) ) end col = 0 Regexp.last_match(1).each_byte do |b| # 0x41: "A" col = col * 26 + (b - 0x41 + 1) end row = Regexp.last_match(2).to_i [row, col] end |
#cells ⇒ Object
This method is part of a private API. You should avoid using this method if possible, as it may be removed or be changed in the future.
284 285 286 287 |
# File 'lib/google_drive/worksheet.rb', line 284 def cells reload_cells unless @cells @cells end |
#cells_feed_url ⇒ Object
URL of cell-based feed of the worksheet.
DEPRECATED: This method is deprecated, and now requires additional network fetch.
99 100 101 102 103 |
# File 'lib/google_drive/worksheet.rb', line 99 def cells_feed_url worksheet_feed_entry.css( "link[rel='http://schemas.google.com/spreadsheets/2006#cellsfeed']" )[0]['href'] end |
#csv_export_url ⇒ Object
URL to export the worksheet as CSV.
111 112 113 114 |
# File 'lib/google_drive/worksheet.rb', line 111 def csv_export_url 'https://docs.google.com/spreadsheets/d/%s/export?gid=%s&format=csv' % [spreadsheet.id, gid] end |
#delete ⇒ Object
Deletes this worksheet. Deletion takes effect right away without calling save().
443 444 445 446 447 |
# File 'lib/google_drive/worksheet.rb', line 443 def delete spreadsheet.batch_update([{ delete_sheet: Google::Apis::SheetsV4::DeleteSheetRequest.new(sheet_id: sheet_id), }]) end |
#delete_rows(row_num, rows) ⇒ Object
Deletes rows.
e.g.
# Deletes 2 rows starting from row 3 (i.e., deletes row 3 and 4).
worksheet.delete_rows(3, 2)
Note that this method is implemented by shifting all cells below the row. Its behavior is different from deleting rows on the web interface if the worksheet contains inter-cell reference.
341 342 343 344 345 346 347 348 349 350 351 |
# File 'lib/google_drive/worksheet.rb', line 341 def delete_rows(row_num, rows) if row_num + rows - 1 > self.max_rows raise(ArgumentError, 'The row number is out of range') end for r in row_num..(self.max_rows - rows) for c in 1..num_cols self[r, c] = input_value(r + rows, c) end end self.max_rows -= rows end |
#dirty? ⇒ Boolean
Returns true if you have changes made by []= etc. which haven’t been saved.
450 451 452 |
# File 'lib/google_drive/worksheet.rb', line 450 def dirty? !@modified.empty? || !@v4_requests.empty? end |
#export_as_file(path) ⇒ Object
Exports the worksheet to path
in CSV format.
122 123 124 125 |
# File 'lib/google_drive/worksheet.rb', line 122 def export_as_file(path) data = export_as_string open(path, 'wb') { |f| f.write(data) } end |
#export_as_string ⇒ Object
Exports the worksheet as String in CSV format.
117 118 119 |
# File 'lib/google_drive/worksheet.rb', line 117 def export_as_string @session.request(:get, csv_export_url, response_type: :raw) end |
#gid ⇒ Object
Returns sheet_id.to_s.
133 134 135 |
# File 'lib/google_drive/worksheet.rb', line 133 def gid sheet_id.to_s end |
#human_url ⇒ Object
URL to view/edit the worksheet in a Web browser.
138 139 140 |
# File 'lib/google_drive/worksheet.rb', line 138 def human_url format("%s\#gid=%s", spreadsheet.human_url, gid) end |
#input_value(*args) ⇒ Object
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]"
202 203 204 205 206 |
# File 'lib/google_drive/worksheet.rb', line 202 def input_value(*args) (row, col) = parse_cell_args(args) reload_cells unless @cells @input_values[[row, col]] || '' end |
#insert_rows(row_num, rows) ⇒ Object
Inserts rows.
e.g.
# Inserts 2 empty rows before row 3.
worksheet.insert_rows(3, 2)
# Inserts 2 rows with values before row 3.
worksheet.insert_rows(3, [["a, "b"], ["c, "d"]])
Note that this method is implemented by shifting all cells below the row. Its behavior is different from inserting rows on the web interface if the worksheet contains inter-cell reference.
312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 |
# File 'lib/google_drive/worksheet.rb', line 312 def insert_rows(row_num, rows) rows = Array.new(rows, []) if rows.is_a?(Integer) # Shifts all cells below the row. self.max_rows += rows.size num_rows.downto(row_num) do |r| (1..num_cols).each do |c| self[r + rows.size, c] = input_value(r, c) end end # Fills in the inserted rows. num_cols = self.num_cols rows.each_with_index do |row, r| (0...[row.size, num_cols].max).each do |c| self[row_num + r, 1 + c] = row[c] || '' end end end |
#inspect ⇒ Object
509 510 511 512 513 514 515 516 517 |
# File 'lib/google_drive/worksheet.rb', line 509 def inspect fields = { spreadsheet_id: spreadsheet.id, gid: gid } fields[:title] = @title if @title format( "\#<%p %s>", self.class, fields.map { |k, v| format('%s=%p', k, v) }.join(', ') ) end |
#list ⇒ Object
Provides access to cells using column names, assuming the first row contains column names. Returned object is GoogleDrive::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().
477 478 479 |
# File 'lib/google_drive/worksheet.rb', line 477 def list @list ||= List.new(self) end |
#list_feed_url ⇒ Object
List feed URL of the worksheet.
DEPRECATED: This method is deprecated, and now requires additional network fetch.
458 459 460 461 462 |
# File 'lib/google_drive/worksheet.rb', line 458 def list_feed_url @worksheet_feed_entry.css( "link[rel='http://schemas.google.com/spreadsheets/2006#listfeed']" )[0]['href'] end |
#merge_cells(top_row, left_col, num_rows, num_cols, merge_type: 'MERGE_ALL') ⇒ Object
Merges a range of cells together. “MERGE_COLUMNS” is another option for merge_type
520 521 522 523 524 525 526 527 |
# File 'lib/google_drive/worksheet.rb', line 520 def merge_cells(top_row, left_col, num_rows, num_cols, merge_type: 'MERGE_ALL') range = v4_range_object(top_row, left_col, num_rows, num_cols) add_request({ merge_cells: Google::Apis::SheetsV4::MergeCellsRequest.new( range: range, merge_type: merge_type), }) end |
#num_cols ⇒ Object
Column number of the right-most non-empty column.
244 245 246 247 248 249 250 251 252 253 254 |
# File 'lib/google_drive/worksheet.rb', line 244 def num_cols reload_cells unless @cells # Memoizes it because this can be bottle-neck. # https://github.com/gimite/google-drive-ruby/pull/49 @num_cols ||= @input_values .reject { |(_r, _c), v| v.empty? } .map { |(_r, c), _v| c } .max || 0 end |
#num_rows ⇒ Object
Row number of the bottom-most non-empty row.
231 232 233 234 235 236 237 238 239 240 241 |
# File 'lib/google_drive/worksheet.rb', line 231 def num_rows reload_cells unless @cells # Memoizes it because this can be bottle-neck. # https://github.com/gimite/google-drive-ruby/pull/49 @num_rows ||= @input_values .reject { |(_r, _c), v| v.empty? } .map { |(r, _c), _v| r } .max || 0 end |
#numeric_value(*args) ⇒ Object
Returns the numeric value of the cell. Arguments must be either (row number, column number) or cell name. Top-left cell is [1, 1].
e.g.
worksheet[1, 3]
#=> "3,0" # it depends on locale, currency...
worksheet.numeric_value(1, 3)
#=> 3.0
Returns nil if the cell is empty or contains non-number.
If you modify the cell, its numeric_value is nil until you call save() and reload().
For details, see: developers.google.com/google-apps/spreadsheets/#working_with_cell-based_feeds
224 225 226 227 228 |
# File 'lib/google_drive/worksheet.rb', line 224 def numeric_value(*args) (row, col) = parse_cell_args(args) reload_cells unless @cells @numeric_values[[row, col]] end |
#reload ⇒ Object
Reloads content of the worksheets from the server. Note that changes you made by []= etc. is discarded if you haven’t called save().
356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 |
# File 'lib/google_drive/worksheet.rb', line 356 def reload api_spreadsheet = @session.sheets_service.get_spreadsheet( spreadsheet.id, ranges: "'%s'" % @title, fields: 'sheets(properties,data.rowData.values' \ '(formattedValue,userEnteredValue,effectiveValue))' ) api_sheet = api_spreadsheet.sheets[0] set_properties(api_sheet.properties) update_cells_from_api_sheet(api_sheet) @v4_requests = [] @worksheet_feed_entry = nil 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][0] == worksheet[1, 1]
293 294 295 296 297 298 299 |
# File 'lib/google_drive/worksheet.rb', line 293 def rows(skip = 0) nc = num_cols result = ((1 + skip)..num_rows).map do |row| (1..nc).map { |col| self[row, col] }.freeze end result.freeze end |
#save ⇒ Object
Saves your changes made by []=, etc. to the server.
374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 |
# File 'lib/google_drive/worksheet.rb', line 374 def save sent = false if @meta_modified add_request({ update_sheet_properties: { properties: { sheet_id: sheet_id, title: title, grid_properties: {row_count: max_rows, column_count: max_cols}, }, fields: '*', }, }) end if !@v4_requests.empty? self.spreadsheet.batch_update(@v4_requests) @v4_requests = [] sent = true end @remote_title = @title unless @modified.empty? min_modified_row = 1.0 / 0.0 max_modified_row = 0 min_modified_col = 1.0 / 0.0 max_modified_col = 0 @modified.each do |r, c| min_modified_row = r if r < min_modified_row max_modified_row = r if r > max_modified_row min_modified_col = c if c < min_modified_col max_modified_col = c if c > max_modified_col end # Uses update_spreadsheet_value instead batch_update_spreadsheet with # update_cells. batch_update_spreadsheet has benefit that the request # can be batched with other requests. But it has drawback that the # type of the value (string_value, number_value, etc.) must be # explicitly specified in user_entered_value. Since I don't know exact # logic to determine the type from text, I chose to use # update_spreadsheet_value here. range = "'%s'!R%dC%d:R%dC%d" % [@title, min_modified_row, min_modified_col, max_modified_row, max_modified_col] values = (min_modified_row..max_modified_row).map do |r| (min_modified_col..max_modified_col).map do |c| @modified.include?([r, c]) ? (@cells[[r, c]] || '') : nil end end value_range = Google::Apis::SheetsV4::ValueRange.new(values: values) @session.sheets_service.update_spreadsheet_value( spreadsheet.id, range, value_range, value_input_option: 'USER_ENTERED') @modified.clear sent = true end sent end |
#set_background_color(top_row, left_col, num_rows, num_cols, background_color) ⇒ Object
Changes the background color on a range of cells. e.g.:
worksheet.set_background_color(1, 1, 1, 1, GoogleDrive::Worksheet::Colors::DARK_YELLOW_1)
background_color is an instance of Google::Apis::SheetsV4::Color.
563 564 565 566 567 |
# File 'lib/google_drive/worksheet.rb', line 563 def set_background_color(top_row, left_col, num_rows, num_cols, background_color) format = Google::Apis::SheetsV4::CellFormat.new(background_color: background_color) fields = 'userEnteredFormat(backgroundColor)' format_cells(top_row, left_col, num_rows, num_cols, format, fields) end |
#set_number_format(top_row, left_col, num_rows, num_cols, pattern, type: "NUMBER") ⇒ Object
Changes the formatting of a range of cells to match the given number format. For example to change A1 to a percentage with 1 decimal point:
worksheet.set_number_format(1, 1, 1, 1, "##.#%")
Google API reference: developers.google.com/sheets/api/reference/rest/v4/spreadsheets#numberformat
533 534 535 536 537 538 |
# File 'lib/google_drive/worksheet.rb', line 533 def set_number_format(top_row, left_col, num_rows, num_cols, pattern, type: "NUMBER") number_format = Google::Apis::SheetsV4::NumberFormat.new(type: type, pattern: pattern) format = Google::Apis::SheetsV4::CellFormat.new(number_format: number_format) fields = 'userEnteredFormat(numberFormat)' format_cells(top_row, left_col, num_rows, num_cols, format, fields) end |
#set_text_alignment(top_row, left_col, num_rows, num_cols, horizontal: nil, vertical: nil) ⇒ Object
Changes text alignment of a range of cells. Horizontal alignment can be “LEFT”, “CENTER”, or “RIGHT”. Vertical alignment can be “TOP”, “MIDDLE”, or “BOTTOM”. Google API reference: developers.google.com/sheets/api/reference/rest/v4/spreadsheets#HorizontalAlign
544 545 546 547 548 549 550 551 552 553 554 555 556 557 |
# File 'lib/google_drive/worksheet.rb', line 544 def set_text_alignment( top_row, left_col, num_rows, num_cols, horizontal: nil, vertical: nil) return if horizontal.nil? && vertical.nil? format = Google::Apis::SheetsV4::CellFormat.new( horizontal_alignment: horizontal, vertical_alignment: vertical) subfields = (horizontal.nil? ? [] : ['horizontalAlignment']) + (vertical.nil? ? [] : ['verticalAlignment']) fields = 'userEnteredFormat(%s)' % subfields.join(',') format_cells(top_row, left_col, num_rows, num_cols, format, fields) end |
#set_text_format(top_row, left_col, num_rows, num_cols, bold: false, italic: false, strikethrough: false, font_size: nil, font_family: nil, foreground_color: nil) ⇒ Object
Change the text formatting on a range of cells. e.g., To set cell A1 to have red text that is bold and italic:
worksheet.set_text_format(
1, 1, 1, 1,
bold: true,
italic: true,
foreground_color: GoogleDrive::Worksheet::Colors::RED_BERRY)
foreground_color is an instance of Google::Apis::SheetsV4::Color. Google API reference: developers.google.com/sheets/api/reference/rest/v4/spreadsheets#textformat
580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 |
# File 'lib/google_drive/worksheet.rb', line 580 def set_text_format(top_row, left_col, num_rows, num_cols, bold: false, italic: false, strikethrough: false, font_size: nil, font_family: nil, foreground_color: nil) text_format = Google::Apis::SheetsV4::TextFormat.new( bold: bold, italic: italic, strikethrough: strikethrough, font_size: font_size, font_family: font_family, foreground_color: foreground_color ) format = Google::Apis::SheetsV4::CellFormat.new(text_format: text_format) fields = 'userEnteredFormat(textFormat)' format_cells(top_row, left_col, num_rows, num_cols, format, fields) end |
#sheet_id ⇒ Object
ID of the worksheet.
128 129 130 |
# File 'lib/google_drive/worksheet.rb', line 128 def sheet_id @properties.sheet_id end |
#synchronize ⇒ Object
Calls save() and reload().
436 437 438 439 |
# File 'lib/google_drive/worksheet.rb', line 436 def synchronize save reload end |
#update_borders(top_row, left_col, num_rows, num_cols, borders) ⇒ Object
Update the border styles for a range of cells. borders is a Hash of Google::Apis::SheetsV4::Border keyed with the following symbols: :top, :bottom, :left, :right, :innerHorizontal, :innerVertical e.g., To set a black double-line on the bottom of A1:
update_borders(
1, 1, 1, 1,
{bottom: Google::Apis::SheetsV4::Border.new(
style: "DOUBLE", color: GoogleDrive::Worksheet::Colors::BLACK)})
605 606 607 608 609 |
# File 'lib/google_drive/worksheet.rb', line 605 def update_borders(top_row, left_col, num_rows, num_cols, borders) request = Google::Apis::SheetsV4::UpdateBordersRequest.new(borders) request.range = v4_range_object(top_row, left_col, num_rows, num_cols) add_request({update_borders: request}) end |
#update_cells(top_row, left_col, darray) ⇒ Object
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"]])
188 189 190 191 192 193 194 |
# File 'lib/google_drive/worksheet.rb', line 188 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 |
#updated ⇒ Object
Time object which represents the time the worksheet was last updated.
DEPRECATED: From google_drive 3.0.0, it returns the time the spreadsheet was last updated, instead of the worksheet. This is because it looks the information is not available in Sheets v4 API.
91 92 93 |
# File 'lib/google_drive/worksheet.rb', line 91 def updated spreadsheet.modified_time.to_time end |
#worksheet_feed_entry ⇒ Object
Nokogiri::XML::Element object of the <entry> element in a worksheets feed.
DEPRECATED: This method is deprecated, and now requires additional network fetch. Consider using properties instead.
73 74 75 |
# File 'lib/google_drive/worksheet.rb', line 73 def worksheet_feed_entry @worksheet_feed_entry ||= @session.request(:get, worksheet_feed_url).root end |
#worksheet_feed_id ⇒ Object
This method is part of a private API. You should avoid using this method if possible, as it may be removed or be changed in the future.
618 619 620 621 622 623 |
# File 'lib/google_drive/worksheet.rb', line 618 def worksheet_feed_id gid_int = sheet_id xor_val = gid_int > 31578 ? 474 : 31578 letter = gid_int > 31578 ? 'o' : '' letter + (gid_int ^ xor_val).to_s(36) end |
#worksheet_feed_url ⇒ Object
URL of worksheet feed URL of the worksheet.
106 107 108 |
# File 'lib/google_drive/worksheet.rb', line 106 def worksheet_feed_url return '%s/%s' % [spreadsheet.worksheets_feed_url, worksheet_feed_id] end |