File Coverage

lib/Google/RestApi/SheetsApi4/Range.pm
Criterion Covered Total %
statement 226 313 72.2
branch 47 72 65.2
condition 26 37 70.2
subroutine 38 52 73.0
pod 25 34 73.5
total 362 508 71.2


line stmt bran cond sub pod time code
1             package Google::RestApi::SheetsApi4::Range;
2              
3             # some private subroutines here are called by RangeGroup, so think of RangeGroup as
4             # a friend of Range. the routines RangeGroup calls are commented thusly:
5             # "private range routine called here!"
6              
7             # there are different ways to specify ranges. this came about by merging different
8             # ideas from different spreadsheet implementations.
9              
10             # column:
11             # A ====> A:A
12             # A:A ====> A:A
13             # [A] ====> A:A
14             # [[A]] ====> A:A
15             # [1] ====> A:A
16             # [[1]] ====> A:A
17             # {col => A} ====> A:A
18             # [{col => A}] ====> A:A
19             # {col => 1} ====> A:A
20             # [{col => 1}] ====> A:A
21             # a partial column is still considered a column
22             # A5:A10 ====> A5:A10
23              
24             # row:
25             # 1 ====> 1:1
26             # 1:1 ====> 1:1
27             # [undef, 1] ====> 1:1
28             # [[undef, 1]] ====> 1:1
29             # 0's are 'valid' in that they are ignored.
30             # [0, 1] ====> 1:1
31             # [[0, 1]] ====> 1:1
32             # {row => 1} ====> 1:1
33             # [{row => 1}] ====> 1:1
34             # a partial row is still considered a row
35             # D1:1 ====> D1:1
36              
37             # partial rows/columns:
38             # [[A, 5], [A]] ====> A5:A
39             # [{col => A, row => 5}, {col => A}] ====> A5:A
40             # [[A], [A, 5]] ====> A:A5
41             # [{col => A}, {col => A, row => 5}] ====> A:A5
42             # [[5, 1], [undef, 1]] ====> E1:1
43             # [[5, 1], [0, 1]] ====> E1:1
44             # [{col => 5, row => 1}, {row => 1}] ====> E1:1
45              
46             # single cell:
47             # A1 ====> A1
48             # [A, 1] ====> A1
49             # [[A, 1]] ====> A1
50             # [1, 1] ====> A1
51             # [[1, 1]] ====> A1
52             # {col => A, row => 1} ====> A1
53             # [{col => A, row => 1}] ====> A1
54             # {col => 1, row => 1} ====> A1
55             # [{col => 1, row => 1}] ====> A1
56              
57             # same cell twice gets reduced to single cell:
58             # A1:A1 ====> A1
59             # these only get reduced using the factory method.
60             # [[A, 1], [A, 1]] ====> A1
61             # [[1, 1], [1, 1]] ====> A1
62             # [{col => A, row => 1}, {col => A, row => 1}] ====> A1
63             # [{col => 1, row => 1}, {col => 1, row => 1}] ====> A1
64              
65             # general ranges:
66             # A1:B2 ====> A1:B2
67             # [[1,1],[2,2]] ====> A1:B2
68             # [[A,1],[B,2]] ====> A1:B2
69             # [{col => 1, row => 1}, {col => 2, row => 2}] ====> A1:B2
70             # [{col => A, row => 1}, {col => B, row => 2}] ====> A1:B2
71              
72             # mixing is ok:
73             # [A1, [2, 2]] ====> A1:B2
74             # [{col => 1, row => 1}, [2, 2]] ====> A1:B2
75             # [{col => 1. row => 1}, B2] ====> A1:B2
76              
77             # bad ranges:
78             # should be able to support this but makes range routine
79             # too complex, and the simple workaround is to just A1:B2.
80             # [A1, B2]
81              
82             our $VERSION = '1.0.3';
83              
84 1     1   708 use Google::RestApi::Setup;
  1         3  
  1         9  
85              
86 1     1   13871 use Carp qw( confess );
  1         3  
  1         57  
87 1     1   7 use List::Util qw( max );
  1         2  
  1         53  
88 1     1   635 use Readonly;
  1         4168  
  1         58  
89 1     1   8 use Scalar::Util qw( looks_like_number );
  1         3  
  1         51  
90 1     1   6 use Try::Tiny qw( try catch );
  1         3  
  1         50  
91              
92 1     1   592 use experimental qw( switch );
  1         3766  
  1         5  
93              
94 1     1   104 use aliased 'Google::RestApi::SheetsApi4::Range::Col';
  1         3  
  1         9  
95 1     1   130 use aliased 'Google::RestApi::SheetsApi4::Range::Row';
  1         3  
  1         4  
96 1     1   97 use aliased 'Google::RestApi::SheetsApi4::Range::Cell';
  1         3  
  1         4  
97 1     1   177 use aliased 'Google::RestApi::SheetsApi4::Range::Iterator';
  1         3  
  1         4  
98              
99 1     1   128 use parent 'Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet::Range';
  1         2  
  1         9  
100              
101             Readonly::Scalar our $RANGE_EXPANDED => 1;
102              
103             # this routine returns the best fitting object for the range specified.
104             # A5:A10 will return a Col object. A5:J5 will return a Row object. Etc.
105             # this grew organically into something more complicated that it was worth,
106             # but i got it working reliably so, whatever...
107             sub factory {
108 90     90 0 403 my %original_range_args = @_;
109 90         253 my $original_range = $original_range_args{range};
110              
111 90         177 state $check = multisig(
112             compile_named(
113             worksheet => HasApi,
114             range => RangeCol,
115             dim => DimColRow, { optional => 1 }, # is switched to 'col'.
116             ),
117             compile_named(
118             worksheet => HasApi,
119             range => RangeRow,
120             dim => DimColRow, { optional => 1 }, # is switched to 'row'
121             ),
122             compile_named(
123             worksheet => HasApi,
124             range => RangeCell,
125             dim => DimColRow, { optional => 1 }, # doesn't matter for cells.
126             ),
127             compile_named(
128             worksheet => HasApi,
129             range => RangeAny,
130             dim => DimColRow, { default => 'row' },
131             ),
132             compile_named(
133             worksheet => HasApi,
134             range => RangeNamed,
135             dim => DimColRow, { default => 'row' },
136             ),
137             );
138 90         52169 my @range_args = $check->(@_);
139 90         12365 my $range_args = $range_args[0]; # no idea why it returns an arrayref pointer to a hashref.
140 90         360 my $range = $range_args->{range};
141            
142             # be careful here, recursive.
143 90         197 given (${^_TYPE_PARAMS_MULTISIG}) {
144 90         301 when (0) { return Col->new(%$range_args); }
  11         137  
145 79         176 when (1) { return Row->new(%$range_args); }
  12         114  
146 67         153 when (2) { return Cell->new(%$range_args); }
  43         543  
147              
148             # if we've translated a range, it now may be a better fit for one of the above.
149 24         68 when (3) {
150             # convert the range to A1:A1 format and redrive the factory routine to see
151             # if it ends up being a col, row, or cell range.
152 23 100       98 if ($range ne $original_range) {
153 9         49 DEBUG(sprintf("Range '%s' converted to '$range'", flatten_range($original_range)));
154             # resolve cells by collapsing A1:A1 to just A1. also A:A and 1:1 will be
155             # properly resolved to cols/rows.
156 9         102 my ($start, $end) = split(':', $range);
157 9 50 66     55 $range = $start if $end && $start eq $end;
158 9         48 return factory(%$range_args); ##### recursion
159             }
160             # we're already in A1:A1 format so just create a new range object.
161 14         122 return __PACKAGE__->new(%$range_args);
162             }
163              
164             # range could be a named range or a column/row header. we have to resolve the
165             # range first, then see what the best fit will be above.
166 1         3 when (4) {
167 1         5 my $worksheet = $range_args->{worksheet};
168              
169 1         3 my $named = $range;
170 1         8 $range = $worksheet->resolve_header_range($named);
171 1 50       4 if ($range) {
172             # remove original range so it doesn't screw up Type::Params.
173 0         0 delete $range_args->{range};
174 0         0 $range = factory(%$range_args, range => $range); ##### recursion
175 0         0 $range->{header_name} = $named;
176 0         0 return $range;
177             }
178              
179 1 50       5 $range = $worksheet->normalize_named($named)
180             or LOGDIE("Unable to resolve named range '$named'");
181             # we've resolved the name to A1 format, so redrive factory routine to
182             # generate a range object with the resolved range.
183             # remove original range so it doesn't screw up Type::Params.
184 1         5 delete $range_args->{range};
185 1         11 $range = factory(%$range_args, range => $range); ##### recursion
186 1         5 $range->{named} = $named;
187 1         11 return $range;
188             }
189             }
190              
191 0         0 LOGDIE("Unable to resolve '$range_args->{range}' to a range object");
192             }
193              
194             # this should not normally be called directly, everything should be created via
195             # the factory method above or more commonly via the 'worksheet::range*' methods.
196             sub new {
197 220     220 1 668 my $class = shift;
198              
199             # TODO: sort start range and end range before storing.
200              
201 220         522 state $check = compile_named(
202             worksheet => HasMethods[qw(api worksheet_name)],
203             range => RangeAny,
204             dim => DimColRow, { default => 'row' },
205             );
206 220         6606 my $self = $check->(@_);
207 220         16250 $self->{dim} = dims_any($self->{dim}); # convert internally to COLUMN | ROW
208 220         1013 DEBUG("New range " . flatten_range($self->{range}) . " has been created");
209              
210 220         3232 return bless $self, $class;
211             }
212              
213             sub clear {
214 0     0 1 0 my $self = shift;
215 0         0 my $range = $self->range();
216 0         0 DEBUG("Clearing range '$range'");
217 0         0 my %p = (
218             uri => "/values/$range:clear",
219             method => 'post',
220             );
221 0         0 my $response = $self->api(%p);
222 0         0 $self->clear_cached_values();
223 0         0 return $response;
224             }
225              
226 0     0 0 0 sub clear_cached_values { delete shift->{cache_range_values}; }
227              
228             sub refresh_values {
229 0     0 1 0 my $self = shift;
230 0         0 $self->clear_cached_values();
231 0         0 return $self->values();
232             }
233              
234             # this gets or sets the values of a range immediately, no batch.
235             # if no values passed, gets them. if values passed, sets them.
236             sub values {
237 143     143 1 280 my $self = shift;
238 143         255 my %p = @_;
239 143 100       382 $self->_send_values(%p) if defined $p{values};
240 143         356 return $self->_cache_range_values(%p)->{values};
241             }
242              
243             # immediately update the values for a range (no batch).
244             sub _send_values {
245 3     3   7 my $self = shift;
246              
247 3         14 state $check = compile_named(
248             values => ArrayRef[ArrayRef[Str]], { optional => 1 },
249             params => HashRef, { default => {} },
250             content => HashRef, { default => {} },
251             _extra_ => slurpy Any,
252             );
253 3         7159 my $p = named_extra($check->(@_));
254            
255 3         17 my $range = $self->range();
256              
257             # since we're sending these values, fake a response from the
258             # api to store them in our cache.
259             # if includeValuesInResponse is sent in the params, then the
260             # response will replace the cache again.
261             $self->_cache_range_values(
262             range => $range,
263             majorDimension => $self->dimension(),
264             values => $p->{values},
265 3         15 );
266              
267 3         11 $p->{content}->{range} = $range;
268 3         15 $p->{content}->{values} = delete $p->{values};
269 3         11 $p->{content}->{majorDimension} = $self->dimension();
270 3   50     22 $p->{params}->{valueInputOption} //= 'USER_ENTERED';
271             # $p->{params}->{includeValuesInResponse} = 1;
272 3         13 $p->{uri} = "/values/$range";
273 3         10 $p->{method} = 'put';
274              
275 3         16 my $update = $self->api(%$p);
276              
277             # this fakes out the batch response with this immediate response.
278             # batch response would be in an arrayref, so wrap it thusly.
279 3         26 return $self->values_response_from_api([ $update ]);
280             }
281              
282             # this stores the response to an update request, either batch or immediate:
283             # ---
284             # spreadsheetId: 1ky2czjhPArP71a6woeo_dxRr8gBOZZxGAPjOCXJvCwA
285             # updatedCells: 6
286             # updatedColumns: 3
287             # updatedRange: Sheet1!B3:D4
288             # updatedRows: 2
289             # if includeValuesInResponse was requested, the values are stashed
290             # from the updatedData response key.
291             # spreadsheet object will call this on a batch update response.
292             sub values_response_from_api {
293 34     34 0 66 my $self = shift;
294              
295 34         62 state $check = compile(ArrayRef, { optional => 1 });
296 34         2054 my ($updates) = $check->(@_);
297 34 50       430 return if !$updates;
298              
299             # shift off the next update from the batch api response. if this is
300             # getting called from the spreadsheet object, it means we have an
301             # update response to process.
302 34         70 my $update = shift @$updates;
303              
304             # updatedData is included if includeValuesInResponse query param was sent.
305             # updatedData:
306             # majorDimension: ROWS
307             # range: Sheet1!A1
308             # values:
309             # - - Fred
310             # if the data is included, then replace any cached values with this latest
311             # updated set of values.
312 0         0 $self->_cache_range_values(%{ delete $update->{updatedData} })
313 34 50       119 if $update->{updatedData};
314 34         124 $self->{values_response_from_api} = $update;
315              
316 34         138 return $self->values();
317             }
318              
319             # this returns the values and major dimension for a given range. it will store
320             # the values returned from an api fetch. it will store the values from a staged
321             # 'batch_values' call for later update from the api. it will store the values
322             # from an update if includeValuesInResponse was included.
323             # cache is in the format:
324             # majorDimension: ROWS
325             # range: Sheet1!A1 # only on an api return value.
326             # values:
327             # - - Fred
328             # if a range is included it's a flag that the dim and values are present from
329             # the returned api call.
330             sub _cache_range_values {
331 200     200   341 my $self = shift;
332              
333 200         422 my %p = @_;
334             # if a range is included, assume this cache is coming from the api as a reply.
335             # this is to store the values for this range when includeValuesInResponse is
336             # added to the url or content on the original values call. you can replace the
337             # original values using the valueRenderOption to replace, say, formulas with their
338             # calculated value.
339 200 100       547 if ($p{range}) {
340 57         118 state $check = compile_named(
341             majorDimension => DimColRow,
342             range => StrMatch[qr/.+!/],
343             values => ArrayRef, { optional => 1 } # will not exist if values aren't set in the ss.
344             );
345 57         5759 my $p = $check->(@_);
346              
347             # remove all quotes for comparison.
348 57         3418 my $self_range = $self->range();
349 57         287 $self_range =~ s/'//g;
350 57         143 my $range = $p->{range};
351 57         152 $range =~ s/'//g;
352 57         251 my ($worksheet_name) = $range =~ /^(.+)!/;
353              
354 57 50       146 LOGDIE "Setting range data to worksheet name '$worksheet_name' that doesn't belong to this range: " . $self->worksheet_name()
355             if $worksheet_name ne $self->worksheet_name();
356             # TODO: sometimes the api returns a range that is different from the one we sent to the api.
357             # a column A:A can be returned as A1:A1000 by the api, so have to come up with a way
358             # of identifying when a returned range is a close enough match to the one we have.
359             #LOGDIE "Setting range data to '$range' which is not this range: " . $self_range
360             # if $range ne $self_range;
361             LOGDIE "Setting major dimention to '$p->{majorDimension}' that doesn't belong to this range: " . $self->dimention()
362 57 50       237 if $p->{majorDimension} ne $self->dimension();
363              
364 57         159 delete $p->{range}; # we only cache the values and dimensions.
365 57         138 $self->{cache_range_values} = $p;
366 57         140 DEBUG("Saved cached values for range " . $self->range());
367             }
368              
369             # if the value range was just stored (above) or was previously stored, return it.
370 200 100       1149 return $self->{cache_range_values} if $self->{cache_range_values};
371 52         168 DEBUG("No local cache values exist for range " . $self->range() . ", checking for shared values");
372              
373             # used by iterators to use a 'parent' range to query the values for this 'child'
374             # range. this is to reduce network calls when iterating through a range.
375 52         375 my $shared = $self->{shared};
376 52 50 33     191 if ($shared && $shared->has_values()) {
377 0         0 my $dim = $shared->{cache_range_values}->{majorDimension};
378 0         0 my $values = $shared->{cache_range_values}->{values};
379              
380 0         0 my ($top, $left, $bottom, $right) = $self->offsets($shared);
381 0         0 my $data;
382 0 0       0 if ($dim =~ /^col/i) {
383 0         0 my @cols = @$values[$left..$right];
384 0         0 $_ = [ @$_[$top..$bottom] ] foreach (@cols);
385 0         0 $data = \@cols;
386             } else {
387 0         0 my @rows = @$values[$top..$bottom];
388 0         0 $_ = [ @$_[$left..$right] ] foreach (@rows);
389 0         0 $data = \@rows;
390             }
391              
392             # return a subsection of the cached value for the iterator.
393 0         0 DEBUG("Returning shared values for range " . $self->range());
394             return {
395 0         0 majorDimension => $dim,
396             values => $data,
397             };
398             }
399 52         146 DEBUG("No shared values exist for range " . $self->range());
400              
401             # no values are found for this range, go get them from the api immediately.
402 52         420 $p{uri} = sprintf("/values/%s", $self->range());
403 52         201 $p{params}->{majorDimension} = $self->dimension();
404 52         203 $self->{cache_range_values} = $self->api(%p);
405 52         231 DEBUG("Cached values loaded from api for range " . $self->range());
406              
407 52   100     511 $self->{cache_range_values}->{values} //= []; # in case there's nothing set in the ss.
408              
409 52         282 return $self->{cache_range_values};
410             }
411              
412             sub has_values {
413 134     134 1 226 my $self = shift;
414 134   66     833 return $self->{cache_range_values} || ($self->{shared} && $self->{shared}->has_values());
415             }
416              
417             # for a given range, calculate the offsets from this range.
418             sub offsets {
419 0     0 1 0 my $self = shift;
420              
421 0         0 state $check = compile(HasRange);
422 0         0 my ($other_range) = $check->(@_);
423              
424 0         0 my $range = $self->range_to_hash($RANGE_EXPANDED);
425 0         0 $other_range = $other_range->range_to_hash($RANGE_EXPANDED);
426              
427 0         0 my $top = $range->[0]->{row} - $other_range->[0]->{row};
428 0         0 my $left = $range->[0]->{col} - $other_range->[0]->{col};
429 0         0 my $bottom = $range->[1]->{row} - $other_range->[0]->{row};
430 0         0 my $right = $range->[1]->{col} - $other_range->[0]->{col};
431              
432 0         0 return ( $top, $left, $bottom, $right );
433             }
434              
435             sub share_values {
436 22     22 1 55 my $self = shift;
437              
438 22         50 state $check = compile(HasRange);
439 22         2526 my ($shared_range) = $check->(@_);
440 22 50       307 return if !$shared_range->is_other_inside($self);
441              
442 22         75 DEBUG(flatten_range($self) . " is sharing values with " . flatten_range($shared_range));
443 22         177 $self->{shared} = $shared_range;
444 22         58 return $shared_range;
445             }
446              
447             sub is_other_inside {
448 10     10 1 48 my $self = shift;
449              
450 10         24 state $check = compile(HasRange);
451 10         2407 my ($inside_range) = $check->(@_);
452              
453 10         137 my $range = $self->range_to_hash($RANGE_EXPANDED);
454 10         41 $inside_range = $inside_range->range_to_hash($RANGE_EXPANDED);
455              
456             return 1 if
457             $range->[0]->{col} <= $inside_range->[0]->{col} &&
458             $range->[0]->{row} <= $inside_range->[0]->{row} &&
459             $range->[1]->{col} >= $inside_range->[1]->{col} &&
460             $range->[1]->{row} >= $inside_range->[1]->{row}
461 10 100 33     246 ;
      66        
      100        
462 2         16 return;
463             }
464              
465             # stage batch values get/set for later use by submit_values
466             sub batch_values {
467 62     62 1 125 my $self = shift;
468              
469 62         97 state $check = compile_named(
470             values => ArrayRef, { optional => 1 },
471             );
472 62         2107 my $p = $check->(@_);
473              
474 62 100       1186 if (defined $p->{values}) {
475             # since we're sending these values, fake a response from the
476             # api to store them in our cache.
477             # if includeValuesInResponse is sent in the params, then the
478             # response will replace the cache again.
479             $self->_cache_range_values(
480             range => $self->range(),
481             majorDimension => $self->dimension(),
482             values => $p->{values},
483 31         84 );
484             }
485              
486 62 50       174 return if !$self->{cache_range_values};
487              
488             return {
489             range => $self->range(),
490 62         148 %{ $self->{cache_range_values} },
  62         433  
491             };
492             }
493              
494             # tell the api to submit the batch values. the api will call back
495             # values_response_from_api with the results of the update.
496             sub submit_values {
497 0     0 1 0 my $self = shift;
498 0         0 $self->spreadsheet()->submit_values(ranges => [ $self ], @_);
499 0         0 return $self->values();
500             }
501              
502             sub submit_requests {
503 6     6 1 10474 my $self = shift;
504 6         27 return $self->spreadsheet()->submit_requests(ranges => [ $self ], @_);
505             }
506              
507             sub append {
508 0     0 0 0 my $self = shift;
509              
510             state $check = compile_named(
511 0     0   0 values => ArrayRef->plus_coercions(Str, sub { [ $_ ] } ),
  0         0  
512             params => HashRef, { default => {} },
513             content => HashRef, { default => {} },
514             _extra_ => slurpy Any,
515             );
516 0         0 my $p = named_extra($check->(@_));
517              
518 0         0 my $range = $self->range();
519 0         0 $p->{content}->{range} = $range;
520 0         0 $p->{content}->{values} = delete $p->{values};
521 0         0 $p->{content}->{majorDimension} = $self->dimension();
522 0   0     0 $p->{params}->{valueInputOption} //= 'USER_ENTERED';
523 0         0 $p->{uri} = "/values/$range:append";
524 0         0 $p->{method} = 'post';
525              
526 0         0 return $self->api(%$p);
527             }
528              
529             sub range {
530 842     842 1 1705 my $self = shift;
531 842         2074 TRACE("Range external caller: " . $self->_caller_external());
532 842         7107 TRACE("Range internal caller: " . $self->_caller_internal());
533 842         5863 my $name = $self->worksheet_name();
534 842         3807 return "'$name'!$self->{range}";
535             }
536              
537             # some staic calls...
538              
539             sub _caller_internal {
540 842     842   1893 my ($package, $subroutine, $line, $i) = ('', '', 0);
541 842         1250 do {
542 1724         19163 ($package, undef, $line, $subroutine) = caller(++$i);
543             } while($subroutine =~ m|range$|);
544             # not usually going to happen, but during testing we call
545             # range directly, so have to backtrack.
546 842 50       15201 ($package, undef, $line, $subroutine) = caller(--$i)
547             if !$package;
548 842         3545 return "$package:$line => $subroutine";
549             }
550              
551             sub _caller_external {
552 842     842   2588 my ($package, $subroutine, $line, $i) = ('', '', 0);
553 842   66     1360 do {
554 4143         72930 ($package, undef, $line, $subroutine) = caller(++$i);
555             } while($package && $package =~ m[^(Google::RestApi)]);
556 842         19474 return "$package:$line => $subroutine";
557             }
558              
559             # taken from https://metacpan.org/source/DOUGW/Spreadsheet-ParseExcel-0.65/lib/Spreadsheet/ParseExcel/Utility.pm
560             sub _col_a2i {
561 337     337   585 state $check = compile(StrMatch[qr/^[A-Z]+$/]);
562 337         4375 my ($a) = $check->(@_);
563              
564 337         5085 my $result = 0;
565 337         543 my $incr = 0;
566 337         1005 for (my $i = length($a); $i > 0 ; $i--) {
567 337         776 my $char = substr($a, $i - 1);
568 337         726 my $curr += ord(lc($char)) - ord('a') + 1;
569 337 50       761 $curr *= $incr if ($incr);
570 337         468 $result += $curr;
571 337         783 $incr += 26;
572             }
573              
574 337         654 return $result;
575             }
576              
577             sub cell_to_array {
578 365     365 0 611 my $cell = shift;
579              
580 365         1467 my ($col, $row) = $cell =~ /^([A-Z]*)(\d*)$/;
581 365 100       1053 $col = _col_a2i($col) if $col;
582              
583 365   100     846 $col ||= 0;
584 365   100     779 $row ||= 0;
585              
586 365         1031 return [$col, $row];
587             }
588              
589             # back to object calls...
590              
591             # returns [[col, row], [col, row]] for a full range.
592             # returns [col, row] for a col or col(a2:a) or row(a2:2).
593             # passing $RANGE_EXPANDED will always return the former.
594             sub range_to_array {
595 306     306 1 518 my $self = shift;
596              
597 306         467 state $check = compile(Bool, { optional => 1 });
598 306         2960 my ($expand_range) = $check->(@_);
599              
600 306         3767 my $range = $self->range();
601 306         1329 ($range) = $range =~ /!(.+)/;
602 306 50       772 $range or LOGDIE "Unable to convert range to array: ", $self->range();
603              
604 306         895 my ($start_cell, $end_cell) = split(':', $range);
605 306 100 100     985 $end_cell = undef if defined $end_cell && $start_cell eq $end_cell;
606 306         639 $start_cell = cell_to_array($start_cell);
607 306 100       728 $end_cell = cell_to_array($end_cell) if $end_cell;
608            
609 306 100 100     1131 $end_cell = $start_cell if !$end_cell && $expand_range;
610              
611 306 100       934 return $end_cell ? [$start_cell, $end_cell] : $start_cell;
612             }
613              
614             # returns [{col =>, row =>}, {col =>, row => }] for an expanded range.
615             # returns {col =>, row =>} for a cell or col(a2:a) or row(a2:2).
616             # passing $RANGE_EXPANDED will always return the former.
617             sub range_to_hash {
618 190     190 1 357 my $self = shift;
619              
620 190         471 my $range = $self->range_to_array(@_);
621 190 100       708 $range = [ $range ] if !ref($range->[1]);
622 190         426 my @ranges = map { { col => $_->[0], row => $_->[1] } } @$range;
  312         1319  
623              
624 190 100       867 return $ranges[1] ? \@ranges : $ranges[0];
625             }
626              
627             sub range_to_index {
628 98     98 1 216 my $self = shift;
629              
630 98         290 my $array = $self->range_to_array();
631 98 100       351 $array = [$array, $array] if !ref($array->[1]);
632              
633 98         258 my %range = (
634             sheetId => $self->worksheet()->worksheet_id(),
635             startColumnIndex => max($array->[0]->[0] - 1, 0),
636             startRowIndex => max($array->[0]->[1] - 1, 0),
637             endColumnIndex => $array->[1]->[0],
638             endRowIndex => $array->[1]->[1],
639             );
640             delete $range{endColumnIndex}
641 98 50       309 if $range{endColumnIndex} < $range{startColumnIndex};
642             delete $range{endRowIndex}
643 98 50       233 if $range{endRowIndex} < $range{startRowIndex};
644            
645 98         783 return \%range;
646             }
647              
648             sub range_to_dimension {
649 0     0 1 0 my $self = shift;
650              
651 0         0 state $check = compile(DimColRow);
652 0         0 my ($dims) = $check->(@_);
653 0         0 $dims = dims_any($dims);
654              
655 0         0 my $array = $self->range_to_array();
656 0 0       0 $array = [$array, $array] if !ref($array->[1]);
657 0 0       0 my $start = $dims =~ /^col/i ? $array->[0]->[0] : $array->[0]->[1];
658 0 0       0 my $end = $dims =~ /^col/i ? $array->[1]->[0] : $array->[1]->[1];
659              
660 0         0 my %range = (
661             sheetId => $self->worksheet()->worksheet_id(),
662             startIndex => max($start - 1, 0),
663             endIndex => $end,
664             dimension => $dims,
665             );
666              
667 0         0 return \%range;
668             }
669              
670             sub cell_at_offset {
671 6     6 0 458 my $self = shift;
672              
673 6         14 state $check = compile(Int, DimColRow);
674 6         4537 my ($offset, $dim) = $check->(@_);
675              
676             # it's an a1:b2 range. col/row/cell handle this in the subclass.
677 6         165 my $range = $self->range_to_hash($RANGE_EXPANDED);
678 6 100       26 my $other_dim = $dim =~ /col/i ? 'row' : 'col';
679             # TODO: this is really brain dead, figure this out with a bit of arithmetic...
680             my @ranges = map {
681 12         16 my $outside = $_;
682             map {
683 24         40 my $inside = $_;
684 24         47 my $r = { $dim => $inside, $other_dim => $outside }; $r; # for some reason have to stage the ref to a scalar.
  24         51  
685 12         38 } ($range->[0]->{$other_dim} .. $range->[1]->{$other_dim});
686 6         30 } ($range->[0]->{$dim} .. $range->[1]->{$dim});
687              
688 6 100       28 my $offset_range = $ranges[$offset] or return;
689 4         12 my $new_cell = $self->worksheet()->range_cell($offset_range);
690 4         17 $new_cell->share_values($self);
691              
692 4         37 return $new_cell;
693             }
694              
695             # doesn't seem to be used anywhere internally.
696             sub range_at_offset {
697 0     0 0 0 my $self = shift;
698              
699 0         0 state $check = compile_named(
700             col => Int, { default => 0 },
701             row => Int, { default => 0 },
702             top => Int, { default => 0 },
703             left => Int, { default => 0 },
704             bottom => Int, { default => 0 },
705             right => Int, { default => 0 },
706             );
707 0         0 my $p = $check->(@_);
708              
709 0         0 my $range = $self->range_to_hash();
710 0 0       0 if (ref($range) eq 'ARRAY') {
711 0         0 foreach my $dim (@$range) {
712 0         0 $dim->{$_} += $p->{$_} foreach (qw(col row));
713             }
714 0         0 $range->[0]->{row} += $p->{top};
715 0         0 $range->[0]->{col} += $p->{left};
716 0         0 $range->[1]->{row} += $p->{bottom};
717 0         0 $range->[1]->{col} += $p->{right};
718             } else {
719 0         0 $range->{$_} += $p->{$_} foreach (qw(col row));
720 0         0 $range->{row} += $p->{top};
721 0         0 $range->{col} += $p->{left};
722 0         0 $range->{row} += $p->{bottom};
723 0         0 $range->{col} += $p->{right};
724             }
725              
726 0         0 return (ref($self))->new(worksheet => $self->worksheet(), range => $range);
727             }
728              
729 0     0 0 0 sub header_name { shift->{header_name}; }
730 2     2 1 45 sub named { shift->{named}; }
731 55     55 1 119 sub api { shift->worksheet()->api(@_); }
732 169     169 1 591 sub dimension { shift->{dim}; }
733 1080     1080 1 3668 sub worksheet { shift->{worksheet}; }
734 899     899 1 2125 sub worksheet_name { shift->worksheet()->worksheet_name(@_); }
735 0     0 1 0 sub worksheet_id { shift->worksheet()->worksheet_id(@_); }
736 6     6 1 33 sub spreadsheet { shift->worksheet()->spreadsheet(@_); }
737 0     0 1   sub spreadsheet_id { shift->spreadsheet()->spreadsheet_id(@_); }
738 0     0 0   sub transaction { shift->spreadsheet()->transaction(); }
739 0     0 1   sub iterator { Iterator->new(range => shift, @_); }
740              
741             1;
742              
743             __END__
744              
745             =head1 NAME
746              
747             Google::RestApi::SheetsApi4::Range - Represents a range in a Worksheet.
748              
749             =head1 DESCRIPTION
750              
751             A Range object that represents a range in a remote spreadsheet. These are
752             normally short-lived objects used to set up and execute a remote action.
753             Keep in mind that the remote spreadsheet can be concurrently updated by
754             many people, so a compromise must always be reached between holding a copy
755             of the local cell values versus the number of network calls to the Google
756             API to keep the values current.
757              
758             A range can be specified in whatever way is most convenient.
759              
760             For ranges:
761              
762             * A1 notation: A1:B2
763             * Hash: [ { col => (A|1), row => 1 }, { col => (B|2), row => 2 } ]
764             * Array: [ [ (A|1), 1 ], [ (B|2), 2 ] ]
765              
766             For columns:
767              
768             * A1 notation: A or A:A
769             * Hash: { col => (A|1) }
770             * Array: [ A|1 ]
771              
772             For rows:
773              
774             * A1 notation: 1 or 1:1
775             * Hash: { row => 1 }
776             * Array: [ <false>, 1 ]
777              
778             For cells:
779              
780             * A1 notation: A1
781             * Hash: { col => (A|1), row => 1 }
782             * Array: [ (A|1), 1 ]
783              
784             See the description and synopsis at L<Google::RestApi::SheetsApi4>.
785              
786             =head1 NAVIGATION
787              
788             =over
789              
790             =item * L<Google::RestApi::SheetsApi4>
791              
792             =item * L<Google::RestApi::SheetsApi4::Spreadsheet>
793              
794             =item * L<Google::RestApi::SheetsApi4::Worksheet>
795              
796             =item * L<Google::RestApi::SheetsApi4::Range>
797              
798             =item * L<Google::RestApi::SheetsApi4::Range::All>
799              
800             =item * L<Google::RestApi::SheetsApi4::Range::Col>
801              
802             =item * L<Google::RestApi::SheetsApi4::Range::Row>
803              
804             =item * L<Google::RestApi::SheetsApi4::Range::Cell>
805              
806             =item * L<Google::RestApi::SheetsApi4::RangeGroup>
807              
808             =item * L<Google::RestApi::SheetsApi4::RangeGroup::Iterator>
809              
810             =item * L<Google::RestApi::SheetsApi4::RangeGroup::Tie>
811              
812             =item * L<Google::RestApi::SheetsApi4::RangeGroup::Tie::Iterator>
813              
814             =item * L<Google::RestApi::SheetsApi4::Request::Spreadsheet>
815              
816             =item * L<Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet>
817              
818             =item * L<Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet::Range>
819              
820             =back
821              
822             =head1 SUBROUTINES
823              
824             =over
825              
826             =item new(%args);
827              
828             Creates a new range object for the given worksheet.
829              
830             %args consists of:
831              
832             =over
833              
834             =item * C<worksheet> <Worksheet>: The parent Worksheet object for this range.
835              
836             =item * C<range> <range>: The range for this object, either A1 notation, hash {col => x, row => x} or array [col, row].
837              
838             =item * C<dim> <col|row>: The major dimension for this range, either 'col' or 'row', defaults to 'row'.
839              
840             =back
841              
842             You would not normally call this directly, you'd use Worksheet::range* methods to create the range object for you. It is recommended and safer to use
843             the Worksheet's methods to create ranges.
844              
845             =item api(%args);
846              
847             Calls the parent Worksheet's 'api' routine with the range added into the URI or content appropriately. This then get's passed to the
848             Spreadsheet's C<api> routine where the spreadsheet ID is tacked on to the URI. This then gets passed to the SheetsApi4's C<api>
849             routine where the Sheets endpoint is tacked on to the URI. This then gets passed to RestApi's api routine for actual execution.
850              
851             You would not normally call this directly unless you were making a Google API call not currently supported by this API framework.
852              
853             =item clear();
854              
855             Clears the values using Google API's 'A1:clear' call.
856              
857             =item refresh_values();
858              
859             Immediately refreshes and returns the values from the spreadsheet.
860              
861             =item values(values => <arrayref>, %args);
862              
863             Immediately gets or sets the values using Google API's 'get' or 'update'.
864              
865             values: The array ref of cells to update.
866              
867             'args' are passed to the SheetsApi4's 'api' routine so you may add
868             extra arguments to the 'params' (such as C<valueInputOption>) or 'content' as necessary.
869              
870             =item batch_values(values => <arrayref>);
871              
872             Gets or sets the queued batch values that will be sent to Google API
873             at a later time. Call 'submit_values' to send them later.
874              
875             values: The array ref of cells to update.
876              
877             =item submit_values(%args);
878              
879             Sends the previously queued batch values to Google API, if any.
880              
881             'args' are passed to the SheetsApi4's 'api' routine so you may add
882             extra arguments to the 'params' or 'content' as necessary.
883              
884             =item submit_requests(%args);
885              
886             Sends the previously queued requests (formatting, sheet properties etc)
887             to Google API, if any.
888              
889             'args' are passed to the SheetsApi4's 'api' routine so you may add
890             extra arguments to the 'params' or 'content' as necessary.
891              
892             =item normalize_named();
893              
894             If this is a 'named range', get the named range property from the
895             spreadsheet and set this object's real range that the named range
896             represents.
897              
898             =item named();
899              
900             Returns the 'named range' for this range, if any.
901              
902             =item range();
903              
904             Returns the A1 notation for this range.
905              
906             =item range_to_hash();
907              
908             Returns the hash representation for this range (e.g. {col => 1, row => 1}). Passing $RANGE_EXPANDED
909             will always return a double cell notations: [{col => 1, row => 1}, {col => 1, row => 1}].
910              
911             =item range_to_array();
912              
913             Returns the array representation for this range (e.g. [1, 1]). Passing $RANGE_EXPANDED
914             will always return a double cell notations: [[ 1, 1 ], [ 1, 1]].
915              
916             =item range_to_index();
917              
918             Returns the index hash representation for this range, used for formatting
919             requests etc. You would not normally need to call this yourself.
920              
921             =item range_to_dimension();
922              
923             Returns the dimension hash representation for this range, used for
924             insert requests etc. You would not normally need to call this yourself.
925              
926             =item offset(col => <int>, row => <int>, top => <int>, left => <int>, bottom => <int>, right => <int>);
927              
928             Returns a new range object offset from this range.
929              
930             col: Optionally offset the new range by this many columns.
931             row: Optionally offset the new range by this many rows.
932             top: Optionally offset the new range with the new top.
933             left: Optionally offset the new range with the new left.
934             bottom: Optionally offset the new range with the new bottom.
935             right: Optionally offset the new range with the new right.
936              
937             =item offsets(range<Range>);
938              
939             Returns the offsets of this range from the given range in an
940             array (top, left, bottom, right).
941              
942             =item iterator(%args);
943              
944             Returns an iterator for this range. Any 'args' are passed to the
945             'new' routine for the iterator.
946              
947             =item is_other_inside(range<Range>);
948              
949             Returns a true value if the given range fits entirely inside this range.
950              
951             =item share_values(range<Range>);
952              
953             Share the cell values between this range and the passed range.
954              
955             Normally ranges are short-lived, throw-away objects used to interact
956             with the Google API. Range objects work independently and don't
957             share any common storage of cells. No attempt is made to share a
958             common, local grid that mirrors what's in the remote spreadsheet.
959             The spreadsheet is a shared resource that can be updated concurrently
960             by multiple users, so attempting to mirror it locally is a waste of
961             time unless some kind of listener is provided that can be used to
962             syncronize the remote updates with the local copy (which Google doesn't).
963              
964             However, for some cases, like iterators, we can set up ranges to
965             share values between them for a short time in order to reduce the
966             number of network calls to the Google API while you iterate over
967             the cells. When the iterated cell values are updated, the parent
968             range's value is also updated.
969              
970             So far, this is the only use-case I can see for sharing the cell
971             grid between ranges.
972              
973             =item dimension();
974              
975             Returns this range's major dimension (col|row).
976              
977             =item has_values();
978              
979             Returns a true value if this range's values have been set either via
980             'values' or 'batch_values' routines.
981              
982             =item worksheet();
983              
984             Returns the parent Worksheet object.
985              
986             =item worksheet_name();
987              
988             Returns the parent Worksheet name.
989              
990             =item worksheet_id();
991              
992             Returns the parent Worksheet ID.
993              
994             =item spreadsheet();
995              
996             Returns the parent Spreadsheet object.
997              
998             =item spreadsheet_id();
999              
1000             Returns the parent Spreadsheet ID.
1001              
1002             =back
1003              
1004             =head1 AUTHORS
1005              
1006             =over
1007              
1008             =item
1009              
1010             Robin Murray mvsjes@cpan.org
1011              
1012             =back
1013              
1014             =head1 COPYRIGHT
1015              
1016             Copyright (c) 2021, Robin Murray. All rights reserved.
1017              
1018             This program is free software; you may redistribute it and/or modify it under the same terms as Perl itself.