NAME

Google::RestApi::SheetsApi4 - API to Google Sheets API V4.

SYNOPSIS

use aliased Google::RestApi;
use aliased Google::RestApi::SheetsApi4;

$rest_api = RestApi->new(%config);
$sheets_api = SheetsApi4->new(api => $rest_api);
$sheet = $sheets_api->create_spreadsheet(title => 'my_name');
$ws0 = $sheet->open_worksheet(id => 0);
$sw1 = $sheet->add_worksheet(name => 'Fred');

# sub Worksheet::cell/col/cols/row/rows immediately get/set
# values. this is less efficient but the simplest way to
# interface with the api. you don't deal with any intermediate
# api objects.

# add some data to the worksheet:
@values = (
  [ 1001, "Herb Ellis", "100", "10000" ],
  [ 1002, "Bela Fleck", "200", "20000" ],
  [ 1003, "Freddie Mercury", "999", "99999" ],
);
$ws0->rows([1, 2, 3], \@values);
$values = $ws0->rows([1, 2, 3]);

# use and manipulate 'range' objects to do more complex work.
# ranges can be specified in many ways, use whatever way is most convenient.
$range = $ws0->range("A1:B2");
$range = $ws0->range([[1,1],[2,2]]);
$range = $ws0->range([{col => 1, row => 1}, {col => 2, row => 2}]);

$cell = $ws0->range_cell("A1");
$cell = $ws0->range_cell([1,1]);
$cell = $ws0->range_cell({col => 1, row => 1});

$col = $ws0->range_col(1);
$col = $ws0->range_col("A3:A");
$col = $ws0->range_col([1]);
$col = $ws0->range_col([[1, 3], [1]]);
$col = $ws0->range_col({col => 1});

$row = $ws0->range_row(1);
$row = $ws0->range_row("C1:1");
$row = $ws0->range_row([<false>, 1]);
$row = $ws0->range_row({row => 1});
$row = $ws0->range_row([{col => 3, row => 1 }, {row => 1}]);

# add a header:
$row = $ws0->range_row(1);
$row->insert_d()->freeze()->bold()->italic()->center()->middle()->submit_requests();
# sends the values to the api directly, not using batch (less efficient):
$row->values(values => [qw(Id Name Tax Salary)]);

# bold the names:
$col = $ws0->range_col("B2:B");
$col->bold()->submit_requests();

# add some tax info:
$tax = $ws0->range_cell([ 3, 5 ]);   # or 'C5' or [ 'C', 5 ] or { col => 3, row => 5 }...
$salary = $ws0->range_cell({ col => "D", row => 5 }); # same as "D5"
# set up batch update with staged values:
$tax->batch_values(values => "=SUM(C2:C4)");
$salary->batch_values(values => "=SUM(D2:D4)");
# now collect the ranges into a group and send the values via batch:
$rg = $sheet->range_group($tax, $salary);
$rg->submit_values();
# bold and italicize both cells, and put a solid border around each one:
$rg->bold()->italic()->bd_solid()->submit_requests();

# tie ranges to a hash:
$row = $ws0->tie_cells({id => 'A2'}, {name => 'B2'});
$row->{id} = '1001';
$row->{name} = 'Herb Ellis';
tied(%$row)->submit_values();

# or use a hash slice:
$ranges = $ws0->tie_ranges();
@$ranges{ 'A2', 'B2', 'C2', 'D4:E5' } =
  (1001, "Herb Ellis", "123 Some Street", [["Halifax"]]);
tied(%$ranges)->submit_values();

# use simple header column/row values as a source for tied keys:
$cols = $ws0->tie_cols('Id', 'Name');
$cols->{Id} = [1001, 1002, 1003];
$cols->{Name} = ['Herb Ellis', 'Bela Fleck', 'Freddie Mercury'];
tied(%$cols)->submit_values();

# format tied values by requesting that the tied hash returns the
# underlying range objects on fetch:
tied(%$rows)->fetch_range(1);
$rows->{Id}->bold()->center();
$rows->{Name}->red();
# turn off fetch range and submit the formatting:
tied(%$rows)->fetch_range(0)->submit_requests();

# iterators can be used to step through ranges:
# a basic iterator on a column:
$col = $ws0->range_col(1);
$i = $col->iterator();
while(1) {
  $cell = $i->next();
  last if !defined $cell->values();
}

# a basic iterator on an arbitrary range, iterating by col or row:
$range = $ws0->range("A1:C3");
$i = $range->iterator(dim => 'col');
$cell = $i->next();  # A1
$cell = $i->next();  # A2
$i = $range->iterator(dim => 'row');
$cell = $i->next();  # A1
$cell = $i->next();  # B1

# an iterator on a range group:
$col = $ws0->range_col(1);
$row = $ws0->range_row(1);
$rg = $sheet->range_group($col, $row);
$i = $rg->iterator();
$rg2 = $i->next();  # another range group of cells A1, A1
$rg2 = $i->next();  # another range group of cells A2, B1

# an iterator on a tied range group:
$cols = $ws0->tie_cols(qw(Id Name));
$i = tied(%$cols)->iterator();
$row = $i->next();
$row->{Id} = '1001';
$row->{Name} = 'Herb Ellis';
tied(%$row)->submit_values();

DESCRIPTION

SheetsApi4 is an API to Google Sheets. It is very perl-ish in that there is usually "more than one way to do it". It provides default behaviours that should be fine for most normal needs, but those behaviours can be overridden when necessary.

It is assumed that you are familiar with the Google Sheets API: https://developers.google.com/sheets/api

tutorial/sheets/* also has a step-by-step tutorial of creating and updating a spreadsheet, showing you the API calls and return values for each step.

SUBROUTINES

new(%args);

Creates a new instance of a SheetsApi object.

%args consists of:

api <Google::RestApi>: A reference to a configured Google::RestApi instance.

api(%args);

%args consists of:

uri <path_segments_string>: Adds this path segment to the Sheets endpoint and calls the Google::RestApi's api subroutine.
%args: Passes any extra arguments to the Google::RestApi's api subroutine (content, params, method etc).

This is essentially a pass-through method between lower-level Worksheet/Range objects and Google::RestApi, where this method adds in the Sheets endpoint. See <Google::RestApi::SheetsApi4::Worksheet>'s api routine for how this is called. You would not normally call this directly unless you were making a Google API call not currently supported by this API framework.

Returns the response hash from Google API.

create_spreadsheet(%args);

Creates a new spreadsheet.

%args consists of:

title|name <string>: The title (or name) of the new spreadsheet.
%args: Passes through any extra arguments to Google Drive's create file routine.

Args title and name are synonymous, you can use either. Note that Sheets allows multiple spreadsheets with the same name.

Normally this would be called via the Spreadsheet object, which would fill in the Drive file ID for you.

Returns the object instance of the new spreadsheet object.

copy_spreadsheet(%args);

Creates a copy of a spreadsheet.

%args consists of:

spreadsheet_id <string>: The file ID in Google Drive of the spreadsheet you want to make a copy of.
%args: Additional arguments passed through to Google Drive file copy subroutine.

Returns the object instance of the new spreadsheet object.

delete_spreadsheet(spreadsheet_id<string>);

Deletes the spreadsheet from Google Drive.

%args consists of:

spreadsheet_id is the file ID in Google Drive of the spreadsheet you want to delete.

Returns the Google API response.

delete_all_spreadsheets_by_filters([spreadsheet_name<string>]);

Deletes all spreadsheets with the given names from Google Drive.

Returns the number of spreadsheets deleted.

spreadsheets();

Returns a list of spreadsheets in Google Drive.

drive();

Returns an instance of Google Drive that shares the same RestApi as this SheetsApi object. You would not normally need to use this directly.

open_spreadsheet(%args);

Opens a new spreadsheet from the given id, uri, or name.

%args consists of any args passed to Spreadsheet->new routine (which see).

REQUEST CLASSES

The Request classes provide methods for building Google Sheets API batchUpdate requests. These are typically called on Range, Worksheet, or Spreadsheet objects which inherit from the appropriate Request class. The requests are staged and then submitted via submit_requests().

Google::RestApi::SheetsApi4::Request::Spreadsheet

Spreadsheet-level requests (inherited by Spreadsheet objects):

add_worksheet(%args): Add a new worksheet. Args: name or title, grid_properties (hashref with rows/cols), tab_color (hashref with red/blue/green).
update_spreadsheet_properties(properties => \%props, fields => $fields): Update spreadsheet properties directly.
ss_title($title): Set the spreadsheet title.
ss_locale($locale): Set the spreadsheet locale (e.g., 'en_US').
ss_time_zone($tz): Set the spreadsheet time zone (e.g., 'America/New_York').
ss_auto_recalc($mode): Set auto recalculation mode ('ON_CHANGE', 'MINUTE', 'HOUR').
ss_iteration_count($count): Set maximum iterations for circular references.
ss_iteration_threshold($threshold): Set convergence threshold for iterative calculations.
ss_default_format($format): Set the default cell format for the spreadsheet.
add_protected_range(%args): Add a protected range. Args: range (required hashref), description, warning_only, requesting_user, editors.
update_protected_range(%args): Update a protected range. Args: id (required), range, description, warning_only, requesting_user, editors, fields.
delete_protected_range($id): Delete a protected range by ID.

Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet

Worksheet-level requests (inherited by Worksheet objects):

Worksheet Properties

ws_rename($name): Rename the worksheet.
ws_index($index): Set the worksheet tab position (0-indexed).
ws_hide($bool) / ws_hidden($bool): Hide or show the worksheet.
ws_right_to_left($bool) / ws_left_to_right($bool): Set the text direction for the worksheet.
ws_tab_color($color_hashref): Set the tab color. Color is a hashref with red/blue/green/alpha keys (0-1 values).
ws_tab_red($value) / ws_tab_blue($value) / ws_tab_green($value) / ws_tab_alpha($value): Set individual tab color components (0-1 values).
ws_tab_black() / ws_tab_white(): Set tab color to black or white.
update_worksheet_properties(properties => \%props, fields => $fields): Update worksheet properties directly.

Freezing

freeze_rows($count) / freeze_cols($count): Freeze the specified number of rows or columns.
unfreeze_rows() / unfreeze_cols(): Unfreeze rows or columns.

Clearing

clear_values(): Clear all cell values in the worksheet.
clear_formatting(): Clear all formatting in the worksheet.
reset(): Clear values, formatting, and unfreeze rows/columns.

Worksheet Management

delete_worksheet(): Delete the worksheet.
duplicate_worksheet(%args): Duplicate the worksheet. Args: new_name, insert_index, new_sheet_id.

Dimensions

update_dimension_properties(%args): Update dimension properties. Args: dimension ('row'/'col'), start, end, properties, fields.
row_height($start, $end, $height): Set the height (in pixels) for rows from start to end index.
col_width($start, $end, $width): Set the width (in pixels) for columns from start to end index.
hide_rows($start, $end) / hide_cols($start, $end): Hide rows or columns.
show_rows($start, $end) / show_cols($start, $end): Show hidden rows or columns.
append_dimension(dimension => $dim, length => $len): Append rows or columns.
append_rows($length) / append_cols($length): Convenience methods to append rows or columns.
auto_resize_dimensions(dimension => $dim, start => $start, end => $end): Auto-resize rows or columns to fit content.
auto_resize_rows($start, $end) / auto_resize_cols($start, $end): Convenience methods to auto-resize rows or columns.

Filters

set_basic_filter(%args): Set a basic filter. Args: range (hashref, defaults to entire sheet), criteria (hashref), sort_specs (arrayref).
clear_basic_filter(): Clear the basic filter.

Conditional Formatting

add_conditional_format_rule(%args): Add a conditional format rule. Args: ranges (required arrayref), rule (required hashref), index (default 0).
update_conditional_format_rule(%args): Update a conditional format rule. Args: index (required), rule, new_index.
delete_conditional_format_rule($index): Delete a conditional format rule by index.

Banding (Alternating Colors)

add_banding(%args): Add banded (alternating) colors. Args: range (required hashref), row_properties, column_properties.
update_banding(%args): Update banded range. Args: id (required), range, row_properties, column_properties, fields.
delete_banding($id): Delete a banded range by ID.

Row/Column Grouping

add_dimension_group(dimension => $dim, start => $start, end => $end): Add a row or column group (for expand/collapse).
delete_dimension_group(dimension => $dim, start => $start, end => $end): Delete a dimension group.
update_dimension_group(%args): Update a dimension group. Args: dimension, start, end, depth, collapsed.
group_rows($start, $end) / group_cols($start, $end): Convenience methods to group rows or columns.
ungroup_rows($start, $end) / ungroup_cols($start, $end): Convenience methods to ungroup rows or columns.

Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet::Range

Range-level requests (inherited by Range objects):

Text Alignment

left() / center() / right(): Set horizontal alignment.
top() / middle() / bottom(): Set vertical alignment.
horizontal_alignment($alignment): Set horizontal alignment directly ('LEFT', 'CENTER', 'RIGHT').
vertical_alignment($alignment): Set vertical alignment directly ('TOP', 'MIDDLE', 'BOTTOM').

Text Formatting

font_family($family): Set the font family (e.g., 'Arial', 'Times New Roman').
font_size($size): Set the font size in points.
bold($bool) / italic($bool) / strikethrough($bool) / underline($bool): Toggle text formatting. Pass 0 or no argument to enable, pass explicit false to disable.
heading(): Apply a preset heading style (centered, bold, white on black, 12pt).

Text Color

red($value) / blue($value) / green($value) / alpha($value): Set text color components (0-1 values).
black() / white(): Set text color to black or white.
color($color_hashref): Set text color directly with a hashref.

Background Color

bk_red($value) / bk_blue($value) / bk_green($value) / bk_alpha($value): Set background color components (0-1 values).
bk_black() / bk_white(): Set background to black or white.
bk_color($color_hashref): Set background color directly with a hashref.

Number Formats

text($pattern) / number($pattern) / percent($pattern) / currency($pattern): Set number format type with optional pattern.
date($pattern) / time($pattern) / date_time($pattern) / scientific($pattern): Set date/time or scientific format with optional pattern.
number_format($type, $pattern): Set number format directly.

Text Wrapping and Direction

overflow() / clip() / wrap(): Set wrap strategy for cell content.
wrap_strategy($strategy): Set wrap strategy directly ('OVERFLOW_CELL', 'CLIP', 'WRAP').
padding(%args): Set cell padding. Args: top, bottom, left, right.
left_to_right() / right_to_left(): Set text direction.
rotate($angle): Rotate text by angle (-90 to 90 degrees).
vertical($bool): Stack text vertically.
hyper_linked() / hyper_plain(): Set hyperlink display type.

Borders

bd_solid($border) / bd_dotted($border) / bd_dashed($border): Set border style. $border can be 'top', 'bottom', 'left', 'right', 'around', 'vertical', 'horizontal', 'inner', 'all', or an arrayref of these.
bd_medium($border) / bd_thick($border) / bd_double($border) / bd_none($border): Additional border styles.
bd_red($value, $border) / bd_blue($value, $border) / bd_green($value, $border) / bd_alpha($value, $border): Set border color components.
bd_black($border) / bd_white($border): Set border color to black or white.
bd_color($color_hashref, $border): Set border color directly.
borders(properties => \%props, border => $border): Full border control.
bd_repeat_cell($bool): Toggle whether borders apply to each cell individually (1) or to the range edges (0).

Merging

merge_all() / merge_both(): Merge all cells in the range into one.
merge_rows(): Merge cells in each row.
merge_cols(): Merge cells in each column.
merge_cells(merge_type => $type): Merge cells with specified type ('all', 'row', 'col').
unmerge() / unmerge_cells(): Unmerge cells.

Insert/Delete Operations

insert_dimension(dimension => $dim, inherit => $bool): Insert rows or columns. dimension is 'row' or 'col'.
insert_d($dimension, $inherit): Shorthand for insert_dimension.
insert_range(dimension => $dim): Insert range, shifting existing cells.
insert_r($dimension): Shorthand for insert_range.
delete_dimension(dimension => $dim): Delete rows or columns.
delete_d($dimension): Shorthand for delete_dimension.
delete_range(dimension => $dim): Delete range, shifting remaining cells.
delete_r($dimension): Shorthand for delete_range.
move_dimension(dimension => $dim, destination => $range): Move rows or columns to a new position.
move($dimension, $destination): Shorthand for move_dimension.

Copy/Paste

copy_paste(destination => $range, type => $type, orientation => $orient): Copy and paste. type: 'normal', 'values', 'format', 'no_borders', 'formula', 'data_validation', 'conditional_formatting'. orientation: 'normal', 'transpose'.
cut_paste(destination => $range, type => $type): Cut and paste.

Named Ranges

add_named(name => $name) / named_a($name): Create a named range.
delete_named() / named_d(): Delete the named range.
update_named(%args) / named_u(%args): Update a named range. Args: name, range, fields.

Data Operations

auto_fill(source => $range, use_template => $bool): Auto-fill based on a source range pattern.
append_cells(rows => \@rows, fields => $fields): Append rows of cell data.
paste_data(data => $data, delimiter => $delim, type => $type, html => $bool): Paste delimited or HTML data.
text_to_columns(delimiter => $delim, delimiter_type => $type): Split text in column to multiple columns.
find_replace(%args): Find and replace. Args: find (required), replacement, match_case, match_entire_cell, search_by_regex, include_formulas.

Data Validation

set_data_validation(rule => \%rule): Set data validation rule.
clear_data_validation(): Clear data validation.
data_validation_list(values => \@values, strict => $bool, show_custom => $bool, input_message => $msg): Create a dropdown list validation.
data_validation_range(source => $range, strict => $bool, show_custom => $bool, input_message => $msg): Create validation from a range of values.

Sorting and Cleanup

sort_range(sort_specs => \@specs): Sort range by specified columns.
sort_asc($col) / sort_desc($col): Sort ascending or descending by column index (default 0).
randomize_range(): Randomize the order of rows in the range.
trim_whitespace(): Trim leading/trailing whitespace from cells.
delete_duplicates(comparison_columns => \@cols): Delete duplicate rows. Optional column indices for comparison.

Clearing (Range)

clear_values(): Clear values in the range.
clear_formatting(): Clear formatting in the range.

STATUS

This api is currently in beta status. It is incomplete. There may be design flaws that need to be addressed in later releases. Later releases may break this release. Not all api calls have been implemented.

AUTHORS

Robin Murray mvsjes@cpan.org

COPYRIGHT

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

To install Google::RestApi, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Google::RestApi

CPAN shell

perl -MCPAN -e shell
install Google::RestApi

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

NAVIGATION

SUBROUTINES

REQUEST CLASSES

Google::RestApi::SheetsApi4::Request::Spreadsheet

Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet

Worksheet Properties

Freezing

Clearing

Worksheet Management

Dimensions

Filters

Conditional Formatting

Banding (Alternating Colors)

Row/Column Grouping

Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet::Range

Text Alignment

Text Formatting

Text Color

Background Color

Number Formats

Text Wrapping and Direction

Borders

Merging

Insert/Delete Operations

Copy/Paste

Named Ranges

Data Operations

Data Validation

Sorting and Cleanup

Clearing (Range)

STATUS

AUTHORS

COPYRIGHT

Module Install Instructions