/ 
Google-RestApi-2.2.3
/ Google::RestApi::SheetsApi4::Worksheet

NAME

Google::RestApi::SheetsApi4::Worksheet - Represents a Worksheet within a Google Spreadsheet.

DESCRIPTION

See the description and synopsis at Google::RestApi::SheetsApi4.

NAVIGATION

SUBROUTINES

new(spreadsheet => <object>, (id => <string> | name => <string> | uri => <string>));

Creates a new instance of a Worksheet object. You would not normally call this directly, you would obtain it from the Spreadsheet->open_worksheet routine.

spreadsheet: The parent object that represents the collection of worksheets.
id: The id of the worksheet (0, 1, 2 etc).
name: The name of the worksheet (as shown on the tab).
uri: The worksheet ID extracted from the overall URI.

Only one of id/name/uri should be specified and this API will derive the others as necessary.

worksheet_id()

Returns the worksheet id.

worksheet_name()

Returns the worksheet name or title.

worksheet_uri()

Returns the worksheet URL (URL);

properties(what<string>);

Returns the specific properties of this worksheet, such as the title or sheet id (sheetId).

col(range<range>, values<arrayref>);

Positional args consist of:

  • <range>: A range representing a column.

  • <arrayref<str>>: An array of strings of column values.

Gets or sets the column values.

$ws->col('A', [1, 2, 3]);
$values = $ws->col('A');

Note: the Google API is called immediately, so this is the easiest but least efficient way of getting/setting spreadsheet values.

Returns the values for the specified column.

cols(cols, values); Positional args consist of:
<arrayref<range>>: An array of ranges that represent columns.
<arrayref<arrayref<string>>>: An optional array of values to set the columns to.

Gets or sets a group of columns, see note for 'col' above.

$ws->cols(['A', 2, 'Id'], [[1, 2, 3, 4], [5], [6, 7]]);
$values = $ws->cols(['A', 2, 'Id']);

Returns the values for the specified columns.

row(range<range>, values<arrayref<string>>);

Same as 'col' above, but operates on a row.

rows(rows<arrayref<range>>, values<arrayref<arrayref<string>>>)

Same as 'cols' above, but operates on rows.

cell(col<range>, row<range>|range<range>), value<string>);

Same as above, but operates on a cell.

enable_header_row(enable<boolean>)

Enables or disables use of the first row as column headers. Call this before calling header_row or tie_cols. Pass a true value (or no argument) to enable; pass a false value to disable and clear any cached header data.

$ws->enable_header_row();     # enable
$ws->enable_header_row(0);    # disable and clear cache
header_row(refresh<boolean>)

Returns an arrayref of values from the first row, which are treated as column headers. The result is cached so that subsequent calls do not hit the API again. Pass a true value to force a refresh.

$ws->enable_header_row();
my $headers = $ws->header_row();   # ['Id', 'Name', 'Address']
$ws->row(1, ['Id', 'Name', 'Address']);
my $fresh = $ws->header_row(1);    # force re-fetch after update

You must call enable_header_row before calling this, otherwise it returns undef.

This will only work on simple headers that don't use fancy formatting spread over multiple merged cells or rows.

enable_header_col(enable<boolean>)

Same concept as enable_header_row, but for the first column. You must pass i really want to do this to enable it, because a worksheet with thousands of rows would load all of them into memory as headers.

$ws->enable_header_col('i really want to do this');
$ws->enable_header_col(0);   # disable and clear cache
header_col(refresh<boolean>)

Same as header_row but reads the first column instead of the first row. See enable_header_col for the reason this requires an explicit opt-in.

name_value_pairs(name_col<range>, value_col<range>);

A utility to convert two columns into a simple hash.

name_col: A range pointing to the keys of the hash.
value_col: A range pointing to the values of the hash.

A spreadsheet with the values:

Name    Value
Fred    1
Charlie 2

...will return the hash:

Fred    => 1,
Charlie => 2,

This allows you to store and retrieve a hash with little muss or fuss. If enable_header_row is set, the first row is skipped automatically.

tie_ranges(ranges<array<hash|<string>>>...);

Ties the given ranges into a tied range group. Specify either a 'key => range<range>' or a plain <range<string>>.

$tied = $ws->tie_ranges({id => 'A2'}, 'B2', 'A5:B6');
$tied->{id} = [['1001']];
$tied->{B2} = [['Herb Ellis']];
$tied->{A5:B6} = [[1, 2], [3, 4]];
tied(%$tied)->submit_values();

If you need to represent the range as anything but a string, you must specify the key=>range format ({id => [1, 2]} or {id => {col => 1, row => 1}}).

tie_cols(ranges<hash>...);

Ties Range::Col objects into a hash, keyed by the names you supply.

$tied = $ws->tie_cols(id => 'A', name => 'B', address => 'C');
$tied->{id}      = [1001, 1002, 1003];
$tied->{name}    = ['Herb Ellis', 'Bela Fleck', 'Freddie Mercury'];
$tied->{address} = ['123 Main St', '456 Oak Ave', '789 Pine Rd'];
tied(%$tied)->submit_values();

If enable_header_row has been called and tie_cols is called with no arguments, the header row values are read from row 1 and used automatically as the hash keys, with each key tied to the corresponding column:

$ws->row(1, ['Id', 'Name', 'Address']);
$ws->enable_header_row();
my $tied = $ws->tie_cols();   # keys: Id => col A, Name => col B, Address => col C
$tied->{Id}      = [1001, 1002, 1003];
$tied->{Name}    = ['Herb Ellis', 'Bela Fleck', 'Freddie Mercury'];
$tied->{Address} = ['123 Main St', '456 Oak Ave', '789 Pine Rd'];
tied(%$tied)->submit_values();

Columns with an undefined header value are skipped.

tie_rows(ranges<array<range>>...);

Same as tie_cols (above), but ties Range::Row objects.

$tied = $ws->tie_rows({herb => 2}, {bela => 3});
$tied->{herb} = ['Herb Ellis'];
$tied->{bela} = ['Bela Fleck'];
tied(%$tied)->submit_values();

To use row 'headings' as ranges (assuming 'Herb Ellis' is in column 1), you must call 'enable_header_col' to enable the row headers first (see below).

tie_cells(ranges<array<range>>...);

Same as above, but ties Range::Cell objects.

$tied = $ws->tie_cells(qw({id => A2}, B2));
$tied->{id} = 1001;
$tied->{B2} = 'Herb Ellis';
tied(%$tied)->submit_values();
tie(ranges<hash>);

Ties the given 'key => range' pairs into a tied range group, and sets the default worksheet, for any new keys later added, to this worksheet.

$tied = $ws->tie(id => $range_cell);
$tied->{id} = 1001;
$teid->{B2} = 'Herb Ellis'; # autocreated for this worksheet.
tied(%$tied)->submit_values();

New keys that are added later are assumed to address cells if there is no ':' (A1), or a general range if a ':' is found (A1:B2). It is better to explicitly set all the ranges you expect to use on the call to 'tie' rather than auto-creating the ranges later to avoid unexpected behaviour.

See also Google::RestApi::SheetsApi4::Spreadsheet tie.

submit_requests(%args)

Submits any outstanding requests (API batchRequests) for this worksheet. %args are any args to be passed to the RestApi's 'api' routine (content, params etc).

range(range<range>);

Returns a Range object or one of its subclasses (Col, Row etc) representing the passed range. If you specify a range that represents a column (A:A), you will get back a Range::Col object. To guaranty a particular subclass returned, use the below routines (range_col, range_row, etc).

range_col(range<range>);

Returns a Google::RestApi::SheetsApi4::Range::Col object representing the passed range. If you pass a non-column range, your script will die.

range_row(range<range>);

Returns a Google::RestApi::SheetsApi4::Range::Row object representing the passed range.

range_cell(range<range>);

Returns a Google::RestApi::SheetsApi4::Range::Cell object representing the passed range.

range_all();

Returns a Google::RestApi::SheetsApi4::Range::All object that represents the whole worksheet. Caution: this class has not been fully tested.

range_group_cols

Returns a Google::RestApi::SheetsApi4::RangeGroup object that represents a group of columns.

range_group_rows(ranges<arrayref>);

Returns a Google::RestApi::SheetsApi4::RangeGroup object that represents a group of rows.

range_group_cells(ranges<arrayref>);

Returns a Google::RestApi::SheetsApi4::RangeGroup object that represents a group of cells.

range_group(ranges<arrayref>);

Returns a Google::RestApi::SheetsApi4::RangeGroup object that represents a group of arbitrary ranges.

api(%args);

A passthrough to the parent Spreadsheet object's 'api' routine.

sheets_api();

Returns the SheetsApi4 object.

spreadsheet();

Returns the parent Spreadsheet object.

spreadsheet_id();

Returns the parent Spreadsheet id.

AUTHORS

  • Robin Murray mvsjes@cpan.org

COPYRIGHT

Copyright (c) 2019-2026 Robin Murray. All rights reserved.

This program is free software; you may redistribute it and/or modify it under the same terms as Perl itself.