The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
Google-RestApi-1.0.0
River stage zero No dependents
4 ++
/ 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>)

This turns on/off the header row so that column headings can be used as keys to tied hashes. See header_row.

header_row(refresh<boolean>)

Returns an array of values in the first row that act as simple headers for the columns. The values are cached in the worksheet so that multiple calls will only invoke the API once, unless you pass a true value to the routine to refresh them.

This is used internally to check for indexed column names such as 'Id', 'Name' or 'Address' etc. It may be of limited use externally.

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

enable_header_col(enable<boolean>)

This turns on/off the header row so that column headings can be used as keys to tied hashes. See header_col.

You must pass i really want to do this to turn it on. This is because you may have a worksheet with thousands of rows that end up being 'headers'. This is less of an issue with header row.

header_col(refresh<boolean>)

A less practical version of header_row, uses the first column to label each row. Since this is cached, if the spreadsheet is large, this can potentially use a lot of memory. Therefore, you must call enable_header_col first, with i really want to do this value, to obtain these column values.

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.

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<array<range>>...);

Same as tie_ranges (above), but ties Range::Col objects. Specify range strings, or column headings to represent the columns.

 $tied = $ws->tie_cols({id => 'A'}, 'Name');
 $tied->{id} = [1001, 1002, 1003];
 $tied->{Name} = ['Herb Ellis', 'Bela Fleck', 'Freddie Mercury'];
 tied(%$tied)->submit_values();
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) 2021, 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.