File Coverage

lib/Google/RestApi/SheetsApi4/Spreadsheet.pm
Criterion Covered Total %
statement 175 184 95.1
branch 31 44 70.4
condition 15 20 75.0
subroutine 33 37 89.1
pod 21 25 84.0
total 275 310 88.7


line stmt bran cond sub pod time code
1             package Google::RestApi::SheetsApi4::Spreadsheet;
2              
3             our $VERSION = '1.0.4';
4              
5 1     1   624 use Google::RestApi::Setup;
  1         3  
  1         6  
6              
7 1     1   14064 use Cache::Memory::Simple ();
  1         692  
  1         26  
8              
9 1     1   13 use aliased 'Google::RestApi::SheetsApi4';
  1         2  
  1         10  
10 1     1   257 use aliased 'Google::RestApi::SheetsApi4::Worksheet';
  1         8  
  1         20  
11 1     1   117 use aliased 'Google::RestApi::SheetsApi4::RangeGroup';
  1         2  
  1         5  
12 1     1   212 use aliased 'Google::RestApi::SheetsApi4::RangeGroup::Tie';
  1         3  
  1         5  
13              
14 1     1   172 use parent "Google::RestApi::SheetsApi4::Request::Spreadsheet";
  1         3  
  1         5  
15              
16             sub new {
17 151     151 1 10056 my $class = shift;
18              
19 151         649 my $qr_id = SheetsApi4->Spreadsheet_Id;
20 151         563 my $qr_uri = SheetsApi4->Spreadsheet_Uri;
21             # pass one of id/name/title/uri and this will work out the others.
22 151         373 state $check = compile_named(
23             sheets_api => HasApi,
24             # https://developers.google.com/sheets/api/guides/concepts
25             id => StrMatch[qr/^$qr_id$/], { optional => 1 },
26             name => Str, { optional => 1 },
27             title => Str, { optional => 1 },
28             uri => StrMatch[qr|^$qr_uri/$qr_id/?|], { optional => 1 },
29             cache_seconds => PositiveOrZeroNum, { default => 5 },
30             );
31 151         10003 my $self = $check->(@_);
32              
33 151         10099 $self = bless $self, $class;
34 151   66     1241 $self->{name} ||= $self->{title};
35 151         485 delete $self->{title};
36              
37 151 100 100     702 $self->{id} || $self->{name} || $self->{uri} or LOGDIE "At least one of id, name, or uri must be specified";
      100        
38              
39 150         975 return $self;
40             }
41              
42             # take the passed uri from worksheet/range/rangegroup etc, and tack on the spreadsheet id,
43             # then pass it up to G::R::SheetsApi4 which will tack on the endpoint.
44             sub api {
45 216     216 1 435 my $self = shift;
46 216         414 state $check = compile_named(
47             uri => Str, { default => '' },
48             _extra_ => slurpy Any, # we'll just pass the params/content etc up for processing.
49             );
50 216         4877 my $p = named_extra($check->(@_));
51 216         990 $p->{uri} = $self->spreadsheet_id() . $p->{uri};
52 216         728 return $self->sheets_api()->api(%$p);
53             }
54              
55             # work out the id from the uri or the name/title.
56             sub spreadsheet_id {
57 225     225 1 14285 my $self = shift;
58              
59 225 100       836 if (!$self->{id}) {
60 6 100       24 if ($self->{uri}) {
61 2         9 my $qr_id = SheetsApi4->Spreadsheet_Id;
62 2         7 my $qr_uri = SheetsApi4->Spreadsheet_Uri;
63 2         62 ($self->{id}) = $self->{uri} =~ m|^$qr_uri/($qr_id)|; # can end with '/edit'
64 2 50       9 LOGDIE "Unable to extract a sheet id from uri" if !$self->{id};
65 2         20 DEBUG("Got sheet ID '$self->{id}' via URI '$self->{uri}'.");
66             } else {
67 4         183 my @spreadsheets = grep { $_->{name} eq $self->{name}; } $self->sheets_api()->spreadsheets();
  12         38  
68 4 100       18 LOGDIE "Sheet '$self->{name}' not found on Google Drive" if !@spreadsheets;
69 3 100       17 LOGDIE "More than one spreadsheet found with name '$self->{name}'. Specify 'id' or 'uri' instead."
70             if scalar @spreadsheets > 1;
71 2         10 $self->{id} = $spreadsheets[0]->{id};
72 2         15 DEBUG("Got sheet id '$self->{id}' via spreadsheet list.");
73             }
74             }
75              
76 223         1191 return $self->{id};
77             }
78              
79             # when 'api' is eventually called, id will be worked out if we don't already have it.
80             # the resolving of id/name/title/uri is deferred until the first api call.
81             sub spreadsheet_name {
82 5     5 1 13123 my $self = shift;
83             $self->{name} ||= $self->properties('title')->{title}
84 5 50 66     29 or LOGDIE "No properties title present in properties";
85 5         101 return $self->{name};
86             }
87 1     1 1 267 sub spreadsheet_title { spreadsheet_name(@_); }
88              
89             # see above routine.
90             sub spreadsheet_uri {
91 3     3 1 12351 my $self = shift;
92             $self->{uri} ||= $self->attrs('spreadsheetUrl')->{spreadsheetUrl}
93 3 50 66     19 or LOGDIE "No spreadsheet URI found from get results";
94 3         66 $self->{uri} =~ s[/(edit|copy)$][]; # this isn't necessary but keeps things cleaner.
95 3         24 return $self->{uri};
96             }
97              
98             # return one of the attributes of the spreadsheet.
99             sub attrs {
100 137     137 1 1377 my $self = shift;
101 137         310 my $fields = shift;
102             return $self->_cache($fields, sub {
103 134     134   2704 $self->api(params => { fields => $fields })
104 137         910 });
105             }
106              
107             # return one of the property attributes of the spreadsheet.
108             sub properties {
109 13     13 1 2548 my $self = shift;
110 13         35 state $check = compile(Str);
111 13         2688 my ($what) = $check->(@_);
112 13         202 my $fields = _fields('properties', $what);
113 13         48 return $self->attrs($fields)->{properties};
114             }
115              
116             # GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId?&fields=sheets.properties
117             # returns properties for each worksheet in the spreadsheet.
118             sub worksheet_properties {
119 119     119 1 280 my $self = shift;
120 119         217 state $check = compile(Str);
121 119         2452 my ($what) = $check->(@_);
122 119         1473 my $fields = _fields('sheets.properties', $what);
123 119         457 my $properties = $self->attrs($fields)->{sheets};
124 119         2836 my @properties = map { $_->{properties}; } @$properties;
  119         584  
125 119         500 return \@properties;
126             }
127              
128             sub _fields {
129 132     132   429 my ($fields, $what) = @_;
130 132 100       732 if ($what =~ /^\(/) {
131 117         328 $fields .= $what;
132             } else {
133 15         56 $fields .= ".$what";
134             }
135 132         327 return $fields;
136             }
137              
138             # if multiple attributes are called for, it can hammer the api unnecessarily, so cache the results
139             # for a few seconds (default 5).
140             sub _cache {
141 137     137   342 my $self = shift;
142              
143 137         297 state $check = compile(Str, CodeRef);
144 137         3246 my ($key, $code) = $check->(@_);
145 137 100       2264 return $code->() if !$self->{cache_seconds};
146              
147 136   66     1329 $self->{_cache} ||= Cache::Memory::Simple->new();
148             # will run the code and store the result for x seconds.
149             return $self->{_cache}->get_or_set(
150             $key, $code, $self->{cache_seconds}
151 136         1445 );
152             }
153              
154             sub _cache_delete {
155 8     8   20 my $self = shift;
156 8         47 delete $self->{_cache};
157 8         20 return;
158             }
159              
160             # sets the number of seconds that attrs will be cached. this can be adjusted
161             # to suit your use-case. if there are lots of people updating things, then a
162             # lower cache value would suit. if you know you are the only one using it, then
163             # a higher cache value will prevent unnecessary calls to the api.
164             sub cache_seconds {
165 2     2 1 118 my $self = shift;
166              
167 2         9 state $check = compile(PositiveOrZeroNum);
168 2         2741 my ($cache_seconds) = $check->(@_);
169              
170 2 100       36 $self->{_cache}->delete_all() if $self->{_cache};
171              
172 2 100       18 if (!$cache_seconds) {
173 1         9 $self->_cache_delete();
174 1         3 delete $self->{cache_seconds};
175             } else {
176 1         4 $self->{cache_seconds} = $cache_seconds;
177             }
178              
179 2         10 return;
180             }
181              
182             sub copy_spreadsheet {
183 3     3 1 22 my $self = shift;
184 3         11 return $self->sheets_api()->copy_spreadsheet(
185             spreadsheet_id => $self->spreadsheet_id(), @_,
186             );
187             }
188              
189             sub delete_spreadsheet {
190 1     1 1 303 my $self = shift;
191 1         27 return $self->sheets_api()->delete_spreadsheet($self->spreadsheet_id());
192             }
193              
194             sub range_group {
195 24     24 1 60 my $self = shift;
196 24         48 state $check = compile(slurpy ArrayRef[HasRange]);
197 24         5881 my ($ranges) = $check->(@_);
198 24         681 return RangeGroup->new(
199             spreadsheet => $self,
200             ranges => $ranges,
201             );
202             }
203              
204             # ties a key => range set of pairs to this spreadsheet.
205             sub tie {
206 16     16 1 55 my $self = shift;
207 16         44 my %ranges = @_;
208 16         202 tie my %tie, Tie, $self;
209 16         95 tied(%tie)->add_ranges(%ranges);
210 16         101 return \%tie;
211             }
212              
213             # this is done simply to allow open_worksheet to return the same worksheet instance
214             # each time it's called for the same remote worksheet. this is to avoid working on
215             # multiple local copies of the same remote worksheet.
216             # TODO: if worksheet is renamed, registration should be updated too.
217             sub _register_worksheet {
218 117     117   242 my $self = shift;
219 117         212 state $check = compile(HasApi);
220 117         3038 my ($worksheet) = $check->(@_);
221 117         1691 my $name = $worksheet->worksheet_name();
222 117 50       674 return $self->{registered_worksheet}->{$name} if $self->{registered_worksheet}->{$name};
223 117         488 $self->{registered_worksheet}->{$name} = $worksheet;
224 117         704 return $worksheet;
225             }
226              
227             # sends batch values to the api.
228             sub submit_values {
229 10     10 1 25 my $self = shift;
230              
231 10         37 state $check = compile_named(
232             ranges => ArrayRef[HasMethods[qw(has_values batch_values values_response_from_api)]],
233             content => HashRef, { default => {} },
234             );
235 10         4308 my $p = $check->(@_);
236              
237             # find out which ranges have something to send.
238 10         463 my @ranges = grep { $_->has_values(); } @{ delete $p->{ranges} };
  10         35  
  10         51  
239 10         30 my @values = map { $_->batch_values(); } @ranges;
  10         44  
240 10 50       36 return if !@values;
241              
242 10         49 $p->{content}->{data} = \@values;
243 10   50     91 $p->{content}->{valueInputOption} //= 'USER_ENTERED';
244 10         27 $p->{method} = 'post';
245 10         32 $p->{uri} = "/values:batchUpdate";
246 10         54 my $api = $self->api(%$p); # this is where the rubber hits the road.
247              
248             # each range that had values should strip off the response from the api's
249             # responses array. if everything is in sync, there should be no responses left.
250 10         67 my $responses = delete $api->{responses};
251 10         65 $_->values_response_from_api($responses) foreach @ranges;
252 10 50       46 LOGDIE "Returned batch values update responses were not consumed" if @$responses;
253              
254             # return whatever's left over.
255 10         49 return $api;
256             }
257              
258             # sends batch requests (formatting etc) to the api.
259             sub submit_requests {
260 7     7 1 17 my $self = shift;
261              
262 7         19 state $check = compile_named(
263             ranges => ArrayRef[HasMethods[qw(batch_requests requests_response_from_api)]], { default => [] }, # might just be self.
264             content => HashRef, { default => {} },
265             );
266 7         4333 my $p = $check->(@_);
267              
268 7         282 my @all_requests = (@{ delete $p->{ranges} }, $self); # add myself to the list.
  7         34  
269              
270             # for each object that has requests to submit, store them so that
271             # they can process the responses that come back.
272             my @ranges = map {
273 7 100       22 $_->batch_requests() ? $_ : ();
  14         58  
274             } @all_requests;
275 7 50       31 return if !@ranges;
276              
277             # pull out the requests hashes to be sent to the rest api.
278             my @batch_requests = map {
279 7         21 $_->batch_requests();
  14         32  
280             } @all_requests;
281 7 50       25 return if !@batch_requests;
282              
283             # we're about to do a bunch of requests that could affect what's in the cache.
284             # TODO: selectively invalidate the cache based on what's being submitted.
285 7         31 $self->_cache_delete();
286              
287             # call the batch request api.
288 7         19 $p->{content}->{requests} = \@batch_requests;
289 7         22 $p->{method} = 'post';
290 7         21 $p->{uri} = ':batchUpdate';
291 7         33 my $api = $self->api(%$p);
292              
293             # grab the json decoded replies from the response.
294 7         41 my $responses = delete $api->{replies};
295             # present the responses back to those who are waiting, each will strip off the ones they requested.
296 7         47 $_->requests_response_from_api($responses) foreach @ranges;
297             # if there are any left over, it sux. we are out of sync somewhere. all requestors should
298             # process their corresponding response.
299 7 50       61 LOGDIE "Returned batch request responses were not consumed" if @$responses;
300              
301 7         57 return $api;
302             }
303              
304             sub named_ranges {
305 1     1 1 4 my $self = shift;
306              
307 1         5 state $check = compile(RangeNamed, { optional => 1 });
308 1         2552 my ($named_range_name) = $check->(@_);
309              
310 1         25 my $named_ranges = $self->attrs('namedRanges')->{namedRanges};
311 1 50       26 return $named_ranges if !$named_range_name;
312              
313 1         5 my ($named_range) = grep { $_->{name} eq $named_range_name; } @$named_ranges;
  1         6  
314 1         4 return $named_range;
315             }
316              
317             # takes a named range and works out the actual range it represents.
318             sub normalize_named {
319 1     1 0 3 my $self = shift;
320              
321 1         4 state $check = compile(RangeNamed);
322 1         2551 my ($named_range_name) = $check->(@_);
323              
324 1 50       29 my $named_range = $self->named_ranges($named_range_name) or return;
325 1         4 $named_range = $named_range->{range};
326             my $range = [
327             [ $named_range->{startColumnIndex} + 1, $named_range->{startRowIndex} + 1 ],
328 1         8 [ $named_range->{endColumnIndex}, $named_range->{endRowIndex} ],
329             ];
330              
331 1         4 return ($named_range->{sheetId}, $range);
332             }
333              
334 0     0 1 0 sub protected_ranges { shift->attrs('sheets.protectedRanges')->{sheets}; }
335              
336             # each worksheet has an entry:
337             # ---
338             # - protectedRanges:
339             # - editors:
340             # users:
341             # - xxx@gmail.com
342             # - yyy@gmail.com
343             # protectedRangeId: 1161285259
344             # range: {}
345             # requestingUserCanEdit: !!perl/scalar:JSON::PP::Boolean 1
346             # warningOnly: !!perl/scalar:JSON::PP::Boolean 1
347             # - {}
348             # - {}
349             # submit_requests needs to be called by the caller after this.
350             sub delete_all_protected_ranges {
351 0     0 1 0 my $self = shift;
352 0         0 foreach my $worksheet (@{ $self->protected_ranges() }) {
  0         0  
353 0 0       0 my $ranges = $worksheet->{protectedRanges} or next;
354 0         0 $self->delete_protected_range($_->{protectedRangeId}) foreach (@$ranges);
355             }
356 0         0 return $self;
357             }
358              
359 0     0 1 0 sub open_worksheet { Worksheet->new(spreadsheet => shift, @_); }
360 238     238 1 1272 sub sheets_api { shift->{sheets_api}; }
361 8     8 0 597 sub rest_api { shift->sheets_api()->rest_api(); }
362 0     0 0 0 sub transaction { shift->sheets_api()->transaction(); }
363 6     6 0 47 sub stats { shift->sheets_api()->stats(); }
364              
365             1;
366              
367             __END__
368              
369             =head1 NAME
370              
371             Google::RestApi::SheetsApi4::Spreadsheet - Represents a Google Spreadsheet.
372              
373             =head1 DESCRIPTION
374              
375             See the description and synopsis at L<Google::RestApi::SheetsApi4>.
376              
377             =head1 NAVIGATION
378              
379             =over
380              
381             =item * L<Google::RestApi::SheetsApi4>
382              
383             =item * L<Google::RestApi::SheetsApi4::Spreadsheet>
384              
385             =item * L<Google::RestApi::SheetsApi4::Worksheet>
386              
387             =item * L<Google::RestApi::SheetsApi4::Range>
388              
389             =item * L<Google::RestApi::SheetsApi4::Range::All>
390              
391             =item * L<Google::RestApi::SheetsApi4::Range::Col>
392              
393             =item * L<Google::RestApi::SheetsApi4::Range::Row>
394              
395             =item * L<Google::RestApi::SheetsApi4::Range::Cell>
396              
397             =item * L<Google::RestApi::SheetsApi4::RangeGroup>
398              
399             =item * L<Google::RestApi::SheetsApi4::RangeGroup::Iterator>
400              
401             =item * L<Google::RestApi::SheetsApi4::RangeGroup::Tie>
402              
403             =item * L<Google::RestApi::SheetsApi4::RangeGroup::Tie::Iterator>
404              
405             =item * L<Google::RestApi::SheetsApi4::Request::Spreadsheet>
406              
407             =item * L<Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet>
408              
409             =item * L<Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet::Range>
410              
411             =back
412              
413             =head1 SUBROUTINES
414              
415             =over
416              
417             =item new(%args);
418              
419             Creates a new instance of a Spreadsheet object.
420              
421             %args consists of:
422              
423             =over
424              
425             =item * C<sheets_api> <SheetsApi4>: The parent SheetsApi4 object.
426              
427             =item * C<id> <string>: The id of the spreadsheet (Google Drive file ID).
428              
429             =item * C<name> <string>: The name of the spreadsheet (as shown in Google Drive).
430              
431             =item * C<title> <string>: An alias for name.
432              
433             =item * C<uri> <string>: The spreadsheet ID extracted from the overall URI.
434              
435             =item * C<cache_seconds> <int>: Cache information for this many seconds (default to 5, 0 disables).
436              
437             =back
438              
439             You would not normally call this directly, you would obtain it from the SheetsApi4::open_spreadsheet routine.
440              
441             Only one of id/name/title/uri should be specified and this API will derive the others as necessary.
442              
443             The cache exists so that repeated calls for the same attributes or worksheet properties doesn't keep hammering the Google API over and over.
444             The default is 5 seconds. See 'cache_seconds' below.
445              
446             =item api(%args);
447              
448             Calls the parent SheetsApi4's 'api' routine adding the sheet file ID, and passing through any args such as content, params, headers, etc.
449              
450             You would not normally call this directly unless you were making a Google API call not currently supported by this API framework.
451              
452             Returns the response hash from Google API.
453              
454             =item spreadsheet_id();
455              
456             Returns the spreadsheet id (the Google Drive file id).
457              
458             =item spreadsheet_uri();
459              
460             Returns the URI of this spreadsheet.
461              
462             =item spreadsheet_name();
463              
464             Returns the name of the spreadsheet.
465              
466             =item spreadsheet_title();
467              
468             An alias for 'spreadsheet_name'.
469              
470             =item attrs(fields<string>);
471              
472             Returns the spreadsheet attributes of the specified fields.
473              
474             =item properties(properties<string>);
475              
476             Returns the spreadsheet property attributes of the specified fields.
477              
478             =item worksheet_properties(what<string>);
479              
480             Returns an array ref of the properties of the worksheets owned by this spreadsheet.
481              
482             =item cache_seconds(seconds<int>)
483              
484             Sets the caching time in seconds. Calling will always delete the existing cache. 0 disables the cache (not recommended).
485              
486             =item delete_all_protected_ranges();
487              
488             Deletes all the protected ranges from all the worksheets owned by this spreadsheet.
489              
490             =item named_ranges(name<string>);
491              
492             Returns the properties of the named range passed, or if false is passed, all the named ranges for this spreadsheet.
493              
494             =item copy_spreadsheet(%args);
495              
496             Creates a copy of this spreadsheet and passes any args to the Google Drive file copy routine.
497              
498             =item delete_spreadsheet();
499              
500             Deletes this spreadsheet from Google Drive.
501              
502             =item range_group(range<array>...);
503              
504             Creates a range group with the contained ranges. See the RangeGroup object.
505              
506             =item tie(%args);
507              
508             %args is a hash of key => Range object pairs.
509              
510             Ties the given 'key => range' pairs into a tied range group. The range group can be used to send batch values (API batchUpdate) and
511             batch requests (API batchRequests) as a single call once all the changes have been made to the overall hash.
512              
513             Turning on the 'fetch_range' property will return the underlying ranges on fetch so that formatting for the ranges can be set. You
514             would normally only turn this on for a short time, and turn it off when the underlying batch requests have been submitted.
515              
516             $tied = $ss->tie(id => $range_cell);
517             tied(%$tied)->fetch_range(1);
518             $tied->{id}->bold()->red()->background_blue();
519             tied(%$tied)->fetch_range(0)->submit_requests();
520              
521             See also Worksheet::tie.
522              
523             =item submit_values(%args);
524              
525             %args consists of:
526              
527             =over
528              
529             =item * C<ranges> <arrayref>: The ranges that have requests to be sent in one batchUpdate Google API call.
530              
531             =item * C<content> <hashref>: Any additional content to be sent to the Google API for this batch submit, for any customized content that needs to be passed.
532              
533             =back
534              
535             Submits the batch values (Google API's batchUpdate) for the specified ranges. Content is passed to the SheetsApi4's 'api' call for any customized
536             content you may need to pass.
537              
538             =item submit_requests(%args);
539              
540             %args consists of:
541              
542             =over
543              
544             =item * C<ranges> <arrayref>: The ranges that have requests to be sent in one batchRequest Google API call.
545              
546             =item * C<content> <hashref>: Any additional content to be sent to the Google API for this batch submit, for any customized content that needs to be passed.
547              
548             =back
549              
550             Submits the batch requests (Google API's batchRequest) for the specified ranges. Content is passed to the SheetsApi4's 'api' call for any customized
551             content you may need to pass.
552              
553             =item protected_ranges();
554              
555             Returns all the protected ranges for this spreadsheet.
556              
557             =item open_worksheet(%args);
558              
559             Creates a new Worksheet object, passing the args to that object's 'new' routine (which see).
560              
561             =item sheets_api();
562              
563             Returns the SheetsApi4 object.
564              
565             =back
566              
567             =head1 AUTHORS
568              
569             =over
570              
571             =item
572              
573             Robin Murray mvsjes@cpan.org
574              
575             =back
576              
577             =head1 COPYRIGHT
578              
579             Copyright (c) 2021, Robin Murray. All rights reserved.
580              
581             This program is free software; you may redistribute it and/or modify it under the same terms as Perl itself.