NAME

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

DESCRIPTION

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

SUBROUTINES

new(spreadsheet => <object>, (id => <string> | name => <string> | uri => <string>), config_id => <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.
 config_id: The custom config for this worksheet.

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.

 what: The fields you are interested in (title, sheetId, etc).

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

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.

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

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

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

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.

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.

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 a true value, to obtain these column values.

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

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.
 has_headers: A boolean indicating if the first row should be ignored.

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

config(key<string>)

Returns the custom configuration item with the given key, or the entire configuration for this worksheet if no key is specified.

range(range<range>);

Returns a Range object representing the passed range string, array, or hash.

range_col(range<range>);

Returns a Range::Col object representing the passed range.

range_row(range<range>);

Returns a Range::Row object representing the passed range.

range_cell(range<range>);

Returns a Range::Cell object representing the passed range.

range_all();

Returns a Range::All object that represents the whole worksheet.

api(%args);

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

sheets();

Returns the SheetsApi4 object.

spreadsheet();

Returns the parent Spreadsheet object.

spreadsheet_id();

Returns the parent Spreadsheet id.

spreadsheet_config();

Returns the parent Spreadsheet config.

AUTHORS

• Robin Murray mvsjes@cpan.org

COPYRIGHT

Copyright (c) 2019, 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.