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