File Coverage

lib/Google/RestApi/SheetsApi4.pm
Criterion Covered Total %
statement 81 82 98.7
branch 7 8 87.5
condition 5 6 83.3
subroutine 19 20 95.0
pod 9 12 75.0
total 121 128 94.5


line stmt bran cond sub pod time code
1             package Google::RestApi::SheetsApi4;
2              
3             our $VERSION = '1.0.3';
4              
5 1     1   98867 use Google::RestApi::Setup;
  1         3  
  1         8  
6              
7 1     1   13608 use Module::Load qw( load );
  1         3  
  1         10  
8 1     1   90 use Try::Tiny ();
  1         25  
  1         17  
9 1     1   5 use YAML::Any ();
  1         2  
  1         19  
10              
11 1     1   5 use aliased 'Google::RestApi::DriveApi3';
  1         1  
  1         8  
12 1     1   100 use aliased 'Google::RestApi::SheetsApi4::Spreadsheet';
  1         2  
  1         5  
13              
14             # TODO: switch to ReadOnly
15             use constant {
16 1         1407 Sheets_Endpoint => "https://sheets.googleapis.com/v4/spreadsheets",
17             Spreadsheet_Filter => "mimeType='application/vnd.google-apps.spreadsheet'",
18             Spreadsheet_Id => DriveApi3->Drive_File_Id,
19             Spreadsheet_Uri => "https://docs.google.com/spreadsheets/d",
20             Worksheet_Id => "[0-9]+",
21             Worksheet_Uri => "[#&]gid=([0-9]+)",
22 1     1   137 };
  1         3  
23              
24             sub new {
25 150     150 1 14587 my $class = shift;
26              
27 150         388 state $check = compile_named(
28             api => HasApi, # the G::RestApi object that will be used to send http calls.
29             drive => HasMethods[qw(filter_files)], { optional => 1 }, # a drive instnace, could be your own, defaults to G::R::DriveApi3.
30             endpoint => Str, { default => Sheets_Endpoint }, # this gets tacked on to the api uri to reach the sheets endpoint.
31             );
32 150         5381 my $self = $check->(@_);
33              
34 150         6677 return bless $self, $class;
35             }
36              
37             # this gets called by lower-level classes like worksheet and range objects. they
38             # will have passed thier own uri with params and possible body, we tack on the
39             # sheets endpoint and pass it up the line to G::RestApi to make the actual call.
40             sub api {
41 220     220 1 13237 my $self = shift;
42 220         372 state $check = compile_named(
43             uri => Str, { default => '' },
44             _extra_ => slurpy Any, # just pass through any extra params to G::RestApi::api call.
45             );
46 220         4715 my $p = named_extra($check->(@_));
47 220         785 my $uri = $self->{endpoint}; # tack on the uri endpoint and pass the buck.
48 220 100       1279 $uri .= "/$p->{uri}" if $p->{uri};
49 220         852 return $self->rest_api()->api(%$p, uri => $uri);
50             }
51              
52             sub create_spreadsheet {
53 3     3 1 376 my $self = shift;
54              
55 3         12 state $check = compile_named(
56             title => Str, { optional => 1 },
57             name => Str, { optional => 1 },
58             _extra_ => slurpy Any,
59             );
60 3         5397 my $p = named_extra($check->(@_));
61             # we allow name and title to be synonymous for convenience. it's actuall title in the google api.
62 3 100 100     27 $p->{title} || $p->{name} or LOGDIE "Either 'title' or 'name' should be supplied";
63 2   66     16 $p->{title} ||= $p->{name};
64 2         7 delete $p->{name};
65              
66 2         12 my $result = $self->api(
67             method => 'post',
68             content => { properties => $p },
69             );
70 2         8 for (qw(spreadsheetId spreadsheetUrl properties)) {
71 6 50       19 $result->{$_} or LOGDIE "No '$_' returned from creating spreadsheet";
72             }
73              
74             return $self->open_spreadsheet(
75             id => $result->{spreadsheetId},
76             uri => $result->{spreadsheetUrl},
77 2         13 );
78             }
79              
80             sub copy_spreadsheet {
81 6     6 1 289 my $self = shift;
82 6         12 my $id = Spreadsheet_Id;
83 6         39 state $check = compile_named(
84             spreadsheet_id => StrMatch[qr/$id/],
85             _extra_ => slurpy Any,
86             );
87 6         6414 my $p = named_extra($check->(@_));
88 6         24 my $file_id = delete $p->{spreadsheet_id};
89 6         25 my $file = $self->drive()->file(id => $file_id);
90 6         342 my $copy = $file->copy(%$p);
91 6         258 return $self->open_spreadsheet(id => $copy->file_id());
92             }
93              
94             sub delete_spreadsheet {
95 5     5 1 289 my $self = shift;
96 5         12 my $id = Spreadsheet_Id;
97 5         39 state $check = compile(StrMatch[qr/$id/]);
98 5         3881 my ($spreadsheet_id) = $check->(@_);
99 5         110 return $self->drive()->file(id => $spreadsheet_id)->delete();
100             }
101              
102             # delete all the spreadsheets by the names passed.
103             sub delete_all_spreadsheets {
104 4     4 1 331 my $self = shift;
105              
106 4     4   15 state $check = compile(ArrayRef->plus_coercions(Str, sub { [$_]; }));
  4         70  
107 4         4343 my ($names) = $check->(@_);
108              
109 4         34 my $count = 0;
110 4         11 foreach my $name (@$names) {
111 4         14 my @spreadsheets = grep { $_->{name} eq $name; } $self->spreadsheets();
  12         32  
112 4         12 $count += scalar @spreadsheets;
113 4         34 DEBUG(sprintf("Deleting %d spreadsheets for name '$name'", scalar @spreadsheets));
114 4         43 $self->delete_spreadsheet($_->{id}) foreach (@spreadsheets);
115             }
116 4         28 return $count;
117             }
118              
119             # list all spreadsheets.
120             sub spreadsheets {
121 9     9 1 282 my $self = shift;
122 9         32 my $drive = $self->drive();
123 9         41 my $spreadsheets = $drive->filter_files(Spreadsheet_Filter);
124 9         22 my @spreadsheets = map { { id => $_->{id}, name => $_->{name} }; } @{ $spreadsheets->{files} };
  27         113  
  9         35  
125 9         40 return @spreadsheets;
126             }
127              
128             sub drive {
129 20     20 1 44 my $self = shift;
130 20 100       88 if (!$self->{drive}) {
131 10         57 load DriveApi3;
132 10         894 $self->{drive} = DriveApi3->new(api => $self->rest_api());
133             }
134 20         467 return $self->{drive};
135             }
136              
137 8     8 1 62 sub open_spreadsheet { Spreadsheet->new(sheets_api => shift, @_); }
138 0     0 0 0 sub transaction { shift->rest_api()->transaction(); }
139 6     6 0 17 sub stats { shift->rest_api()->stats(); }
140 246     246 0 2378 sub rest_api { shift->{api}; }
141              
142             1;
143              
144             __END__
145              
146             =head1 NAME
147              
148             Google::RestApi::SheetsApi4 - API to Google Sheets API V4.
149              
150             =head1 SYNOPSIS
151              
152             =over
153              
154             use aliased Google::RestApi;
155             use aliased Google::RestApi::SheetsApi4;
156              
157             $rest_api = RestApi->new(%config);
158             $sheets_api = SheetsApi4->new(api => $rest_api);
159             $sheet = $sheets_api->create_spreadsheet(title => 'my_name');
160             $ws0 = $sheet->open_worksheet(id => 0);
161             $sw1 = $sheet->add_worksheet(name => 'Fred');
162              
163             # sub Worksheet::cell/col/cols/row/rows immediately get/set
164             # values. this is less efficient but the simplest way to
165             # interface with the api. you don't deal with any intermediate
166             # api objects.
167            
168             # add some data to the worksheet:
169             @values = (
170             [ 1001, "Herb Ellis", "100", "10000" ],
171             [ 1002, "Bela Fleck", "200", "20000" ],
172             [ 1003, "Freddie Mercury", "999", "99999" ],
173             );
174             $ws0->rows([1, 2, 3], \@values);
175             $values = $ws0->rows([1, 2, 3]);
176              
177             # use and manipulate 'range' objects to do more complex work.
178             # ranges can be specified in many ways, use whatever way is most convenient.
179             $range = $ws0->range("A1:B2");
180             $range = $ws0->range([[1,1],[2,2]]);
181             $range = $ws0->range([{col => 1, row => 1}, {col => 2, row => 2}]);
182              
183             $cell = $ws0->range_cell("A1");
184             $cell = $ws0->range_cell([1,1]);
185             $cell = $ws0->range_cell({col => 1, row => 1});
186              
187             $col = $ws0->range_col(1);
188             $col = $ws0->range_col("A3:A");
189             $col = $ws0->range_col([1]);
190             $col = $ws0->range_col([[1, 3], [1]]);
191             $col = $ws0->range_col({col => 1});
192              
193             $row = $ws0->range_row(1);
194             $row = $ws0->range_row("C1:1");
195             $row = $ws0->range_row([<false>, 1]);
196             $row = $ws0->range_row({row => 1});
197             $row = $ws0->range_row([{col => 3, row => 1 }, {row => 1}]);
198              
199             # add a header:
200             $row = $ws0->range_row(1);
201             $row->insert_d()->freeze()->bold()->italic()->center()->middle()->submit_requests();
202             # sends the values to the api directly, not using batch (less efficient):
203             $row->values(values => [qw(Id Name Tax Salary)]);
204              
205             # bold the names:
206             $col = $ws0->range_col("B2:B");
207             $col->bold()->submit_requests();
208              
209             # add some tax info:
210             $tax = $ws0->range_cell([ 3, 5 ]); # or 'C5' or [ 'C', 5 ] or { col => 3, row => 5 }...
211             $salary = $ws0->range_cell({ col => "D", row => 5 }); # same as "D5"
212             # set up batch update with staged values:
213             $tax->batch_values(values => "=SUM(C2:C4)");
214             $salary->batch_values(values => "=SUM(D2:D4)");
215             # now collect the ranges into a group and send the values via batch:
216             $rg = $sheet->range_group($tax, $salary);
217             $rg->submit_values();
218             # bold and italicize both cells, and put a solid border around each one:
219             $rg->bold()->italic()->bd_solid()->submit_requests();
220              
221             # tie ranges to a hash:
222             $row = $ws0->tie_cells({id => 'A2'}, {name => 'B2'});
223             $row->{id} = '1001';
224             $row->{name} = 'Herb Ellis';
225             tied(%$row)->submit_values();
226              
227             # or use a hash slice:
228             $ranges = $ws0->tie_ranges();
229             @$ranges{ 'A2', 'B2', 'C2', 'D4:E5' } =
230             (1001, "Herb Ellis", "123 Some Street", [["Halifax"]]);
231             tied(%$ranges)->submit_values();
232              
233             # use simple header column/row values as a source for tied keys:
234             $cols = $ws0->tie_cols('Id', 'Name');
235             $cols->{Id} = [1001, 1002, 1003];
236             $cols->{Name} = ['Herb Ellis', 'Bela Fleck', 'Freddie Mercury'];
237             tied(%$cols)->submit_values();
238              
239             # format tied values by requesting that the tied hash returns the
240             # underlying range objects on fetch:
241             tied(%$rows)->fetch_range(1);
242             $rows->{Id}->bold()->center();
243             $rows->{Name}->red();
244             # turn off fetch range and submit the formatting:
245             tied(%$rows)->fetch_range(0)->submit_requests();
246              
247             # iterators can be used to step through ranges:
248             # a basic iterator on a column:
249             $col = $ws0->range_col(1);
250             $i = $col->iterator();
251             while(1) {
252             $cell = $i->next();
253             last if !defined $cell->values();
254             }
255              
256             # a basic iterator on an arbitrary range, iterating by col or row:
257             $range = $ws0->range("A1:C3");
258             $i = $range->iterator(dim => 'col');
259             $cell = $i->next(); # A1
260             $cell = $i->next(); # A2
261             $i = $range->iterator(dim => 'row');
262             $cell = $i->next(); # A1
263             $cell = $i->next(); # B1
264              
265             # an iterator on a range group:
266             $col = $ws0->range_col(1);
267             $row = $ws0->range_row(1);
268             $rg = $sheet->range_group($col, $row);
269             $i = $rg->iterator();
270             $rg2 = $i->next(); # another range group of cells A1, A1
271             $rg2 = $i->next(); # another range group of cells A2, B1
272              
273             # an iterator on a tied range group:
274             $cols = $ws0->tie_cols(qw(Id Name));
275             $i = tied(%$cols)->iterator();
276             $row = $i->next();
277             $row->{Id} = '1001';
278             $row->{Name} = 'Herb Ellis';
279             tied(%$row)->submit_values();
280              
281             =back
282              
283             =head1 DESCRIPTION
284              
285             SheetsApi4 is an API to Google Sheets. It is very perl-ish in that there is usually "more than one way to do it". It provides default behaviours
286             that should be fine for most normal needs, but those behaviours can be overridden when necessary.
287              
288             It is assumed that you are familiar with the Google Sheets API: https://developers.google.com/sheets/api
289              
290             C<t/tutorial/sheets/*> also has a step-by-step tutorial of creating and updating a spreadsheet, showing you the API calls and return values for each step.
291              
292             =head1 NAVIGATION
293              
294             =over
295              
296             =item * L<Google::RestApi::SheetsApi4>
297              
298             =item * L<Google::RestApi::SheetsApi4::Spreadsheet>
299              
300             =item * L<Google::RestApi::SheetsApi4::Worksheet>
301              
302             =item * L<Google::RestApi::SheetsApi4::Range>
303              
304             =item * L<Google::RestApi::SheetsApi4::Range::All>
305              
306             =item * L<Google::RestApi::SheetsApi4::Range::Col>
307              
308             =item * L<Google::RestApi::SheetsApi4::Range::Row>
309              
310             =item * L<Google::RestApi::SheetsApi4::Range::Cell>
311              
312             =item * L<Google::RestApi::SheetsApi4::RangeGroup>
313              
314             =item * L<Google::RestApi::SheetsApi4::RangeGroup::Iterator>
315              
316             =item * L<Google::RestApi::SheetsApi4::RangeGroup::Tie>
317              
318             =item * L<Google::RestApi::SheetsApi4::RangeGroup::Tie::Iterator>
319              
320             =item * L<Google::RestApi::SheetsApi4::Request::Spreadsheet>
321              
322             =item * L<Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet>
323              
324             =item * L<Google::RestApi::SheetsApi4::Request::Spreadsheet::Worksheet::Range>
325              
326             =back
327              
328             =head1 SUBROUTINES
329              
330             =over
331              
332             =item new(%args);
333              
334             Creates a new instance of a SheetsApi object.
335              
336             %args consists of:
337              
338             =over
339              
340             =item C<api> L<<Google::RestApi>>: A reference to a configured L<Google::RestApi> instance.
341              
342             =back
343              
344             =item api(%args);
345              
346             %args consists of:
347              
348             =over
349              
350             =item * C<uri> <path_segments_string>: Adds this path segment to the Sheets endpoint and calls the L<Google::RestApi>'s C<api> subroutine.
351              
352             =item * C<%args>: Passes any extra arguments to the L<Google::RestApi>'s C<api> subroutine (content, params, method etc).
353              
354             =back
355              
356             This is essentially a pass-through method between lower-level Worksheet/Range objects and L<Google::RestApi>, where this method adds in the Sheets endpoint.
357             See <Google::RestApi::SheetsApi4::Worksheet>'s C<api> routine for how this is called. You would not normally call this directly unless you were making a Google API call not currently
358             supported by this API framework.
359              
360             Returns the response hash from Google API.
361              
362             =item create_spreadsheet(%args);
363              
364             Creates a new spreadsheet.
365              
366             %args consists of:
367              
368             =over
369              
370             =item * C<title|name> <string>: The title (or name) of the new spreadsheet.
371              
372             =item * C<%args>: Passes through any extra arguments to Google Drive's create file routine.
373              
374             =back
375              
376             Args C<title> and C<name> are synonymous, you can use either. Note that Sheets allows multiple spreadsheets with the same name.
377              
378             Normally this would be called via the Spreadsheet object, which would fill in the Drive file ID for you.
379              
380             Returns the object instance of the new spreadsheet object.
381              
382             =item copy_spreadsheet(%args);
383              
384             Creates a copy of a spreadsheet.
385              
386             %args consists of:
387              
388             =over
389              
390             =item * C<spreadsheet_id> <string>: The file ID in Google Drive of the spreadsheet you want to make a copy of.
391              
392             =item * C<%args>: Additional arguments passed through to Google Drive file copy subroutine.
393              
394             =back
395              
396             Returns the object instance of the new spreadsheet object.
397              
398             =item delete_spreadsheet(spreadsheet_id<string>);
399              
400             Deletes the spreadsheet from Google Drive.
401              
402             %args consists of:
403              
404             spreadsheet_id is the file ID in Google Drive of the spreadsheet you want to delete.
405              
406             Returns the Google API response.
407              
408             =item delete_all_spreadsheets(spreadsheet_name<string>);
409              
410             Deletes all spreadsheets with the given name from Google Drive.
411              
412             Returns the number of spreadsheets deleted.
413              
414             =item spreadsheets();
415              
416             Returns a list of spreadsheets in Google Drive.
417              
418             =item drive();
419              
420             Returns an instance of Google Drive that shares the same RestApi as this SheetsApi object. You would not normally need to use this directly.
421              
422             =item open_spreadsheet(%args);
423              
424             Opens a new spreadsheet from the given id, uri, or name.
425              
426             %args consists of any args passed to Spreadsheet->new routine (which see).
427              
428             =back
429              
430             =head1 STATUS
431              
432             This api is currently in beta status. It is incomplete. There may be design flaws that need to be addressed in later releases. Later
433             releases may break this release. Not all api calls have been implemented.
434              
435             =head1 AUTHORS
436              
437             =over
438              
439             =item
440              
441             Robin Murray mvsjes@cpan.org
442              
443             =back
444              
445             =head1 COPYRIGHT
446              
447             Copyright (c) 2021, Robin Murray. All rights reserved.
448              
449             This program is free software; you may redistribute it and/or modify it under the same terms as Perl itself.