/ 
Tie-Google-Sheets-0.02
/ Tie::Google::Sheets

NAME

Tie::Google::Sheets - Tie perl variables to Google Sheets

VERSION

version 0.02

SYNOPSIS

use Tie::Google::Sheets;

tie my %doc, 'Tie::Google::Sheets',
    spreadsheet_id  => '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
    service_account => '/path/to/service-account-key.json';

# read and write individual cells
my $name = $doc{Employees}{A1};
$doc{Employees}{A2} = 'Grace Hopper';

# create a new worksheet, optionally pre-populated
tied(%doc)->add_worksheet('Report', { A1 => 'Total', B1 => 42 });

# iterate over worksheet tabs
for my $title (keys %doc) {
    print "$title\n";
}

# remove a worksheet
delete $doc{Report};

DESCRIPTION

This module ties a Perl hash to a Google Sheets spreadsheet document. The outer hash is keyed by worksheet (tab) title; each value is itself a hash (implemented by Tie::Google::Sheets::Worksheet) keyed by cell reference in A1 notation (A1, B12, and so on), so that a spreadsheet can be read and written using ordinary Perl hash syntax:

$doc{'Sheet1'}{'A1'} = 'hello';
print $doc{'Sheet1'}{'A1'};

Authentication is via a Google service account. Remember to share the spreadsheet (or its containing folder) with the service account's client_email address, the same way you would share it with any other Google account, otherwise API calls will fail with a permission error.

CONSTRUCTOR

tie my %doc, 'Tie::Google::Sheets', %options;

%options may contain:

spreadsheet_id

The id of the spreadsheet, taken from its URL (https://docs.google.com/spreadsheets/d/<id>/edit). Required unless "spreadsheet_url" is given instead.

spreadsheet_url

The full URL of the spreadsheet, from which the spreadsheet id will be extracted. Ignored if "spreadsheet_id" is also given.

service_account

Either a hash reference containing the decoded contents of a Google service account JSON key file, or a path to the key file itself. Required unless "access_token" is given instead.

access_token

An OAuth2 bearer token (or a code reference which returns one) to use instead of authenticating with a service account. Useful when the caller already has its own way of obtaining and refreshing tokens (or, in tests, for supplying a fake token).

ua

A user agent object (for example an HTTP::Tiny or LWP::UserAgent instance) to be wrapped in an HTTP::AnyUA. Defaults to a plain HTTP::Tiny instance.

any_ua

An already constructed HTTP::AnyUA compatible object (that is, anything providing a request($method, $url, \%options) method with the same contract as HTTP::Tiny). Used as-is instead of wrapping "ua". Mutually exclusive with "ua".

batch_size

Enables write batching. When set to a positive integer, cell writes (that is, $doc{$title}{$cell} = $value) are queued instead of being sent immediately, and are combined into a single API call once the queue reaches batch_size pending writes. Defaults to undef, meaning every write is sent immediately as its own API call.

Regardless of batch_size, queued writes are always flushed before they could otherwise be observed out of order: immediately before any read, before any worksheet is added or deleted, and when %doc (or the last reference to it) goes out of scope. Queued writes can also be flushed explicitly at any time; see "flush".

backoff_retry

Enables automatic retry when Google rate limits a request (HTTP status 429). When set to a positive integer, a rate limited API call is retried that many times, with exponential backoff (1, 2, 4, ... seconds) between attempts, before giving up and croaking. Defaults to undef, meaning a rate limited request fails immediately.

TIE METHODS

This class implements the standard perltie TIEHASH protocol; see perltie for the full semantics of each method.

TIEHASH

Constructor, called via tie; see "CONSTRUCTOR" above.

FETCH

Returns the worksheet named by the given key, as a hashref implemented by Tie::Google::Sheets::Worksheet, or undef if no worksheet with that title exists on the server.

STORE

Creates a new worksheet. The key is the new worksheet's title; the value must be undef or a hashref of cell reference / value pairs to populate it with. Croaks if a worksheet with that title already exists.

DELETE

Deletes a worksheet.

EXISTS

Returns true if a worksheet with the given title exists.

CLEAR

Always croaks: a spreadsheet must keep at least one worksheet, so the tied hash cannot be emptied wholesale.

FIRSTKEY, NEXTKEY

Together implement iteration (keys, values, each) over the spreadsheet's worksheet titles.

METHODS

These are ordinary (non-tie) methods available on the underlying object via tied %doc.

add_worksheet

tied(%doc)->add_worksheet($title);
tied(%doc)->add_worksheet($title, \%cells);

Creates a new worksheet named $title. If \%cells is given, its key/value pairs are written into the new worksheet as cell reference / value pairs. Returns the new worksheet hashref, the same as $doc{$title}.

This is equivalent to $doc{$title} = \%cells.

delete_worksheet

tied(%doc)->delete_worksheet($title);

Deletes the worksheet named $title. Equivalent to delete $doc{$title}.

copy_worksheet

tied(%doc)->copy_worksheet($from_title, $to_title);

Copies the worksheet named $from_title to a new worksheet named $to_title, including its formatting, data validation, and other properties, not just its cell values. Croaks if $from_title doesn't exist, or if $to_title already exists. Returns the new worksheet hashref, the same as $doc{$to_title}.

worksheet_titles

my @titles = tied(%doc)->worksheet_titles;

Returns the titles of all worksheets in the spreadsheet, in the order Google Sheets returns them. Equivalent to keys %doc.

flush

tied(%doc)->flush;

Sends any cell writes queued by the "batch_size" constructor option as a single API call. A no-op if batch_size wasn't given, or if there is nothing queued. See "batch_size" for when this happens automatically.

CAVEATS

  • Every cell access is a separate Google Sheets API call; there is no local caching, and writes are not batched unless "batch_size" is given. Be mindful of Google's API quotas if you access many cells.

  • A spreadsheet must always have at least one worksheet, so %doc cannot be emptied with %doc = (); delete worksheets individually instead.

  • Only cell values are read and written; formatting, formulas results vs formula text, and other cell metadata are not exposed.

  • Because of how Perl autovivifies nested data structures, reading or writing a cell of a worksheet that doesn't yet exist (for example $doc{NewSheet}{A1}) creates that worksheet as a side effect, the same way $doc{NewSheet} = undef would. To check whether a worksheet exists without creating it, use exists $doc{$title} or "worksheet_titles", not a truth test on $doc{$title}.

SEE ALSO

Tie::Google::Sheets::Worksheet

AUTHOR

Graham Ollis <plicease@cpan.org>

COPYRIGHT AND LICENSE

This software is copyright (c) 2026 by Graham Ollis.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.