Tie::Google::Sheets - Tie perl variables to Google Sheets
version 0.02
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};
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.
tie my %doc, 'Tie::Google::Sheets', %options;
%options may contain:
The id of the spreadsheet, taken from its URL (https://docs.google.com/spreadsheets/d/<id>/edit). Required unless "spreadsheet_url" is given instead.
The full URL of the spreadsheet, from which the spreadsheet id will be extracted. Ignored if "spreadsheet_id" is also given.
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.
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).
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.
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".
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".
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.
This class implements the standard perltie TIEHASH protocol; see perltie for the full semantics of each method.
Constructor, called via tie; see "CONSTRUCTOR" above.
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.
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.
Deletes a worksheet.
Returns true if a worksheet with the given title exists.
Always croaks: a spreadsheet must keep at least one worksheet, so the tied hash cannot be emptied wholesale.
Together implement iteration (keys, values, each) over the spreadsheet's worksheet titles.
These are ordinary (non-tie) methods available on the underlying object via tied %doc.
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.
tied(%doc)->delete_worksheet($title);
Deletes the worksheet named $title. Equivalent to delete $doc{$title}.
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}.
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.
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.
%doc cannot be emptied with %doc = (); delete worksheets individually instead.$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}.Graham Ollis <plicease@cpan.org>
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.