PrePAN

Sign in to PrePAN

Google::RestApi An API to the latest Google APIs.

Good

Synopsis

         use aliased Google::RestApi;
         use aliased Google::RestApi::SheetsApi4;

         $rest = RestApi->new(%config);
         $sheets = SheetsApi4->new(api => $rest);
         $sheet = $sheets->create_spreadsheet(title => 'my_name');
         $ws0 = $sheet->open_worksheet(id => 0);

         # sub worksheet->cell/col/cols/row/rows immediately get/set
         # values. this is less efficient but the simplest way to
         # interface with the api. you don't deal with any intermediate
         # api objects.
 
         # add some data to the worksheet:
         @values = (
           [ 1001, "Herb Ellis", "100", "10000" ],
           [ 1002, "Bela Fleck", "200", "20000" ],
           [ 1003, "Freddie Mercury", "999", "99999" ],
         );
         $ws0->rows([1, 2, 3], \@values);
         $values = $ws0->rows([1, 2, 3]);

         # use and manipulate 'range' objects to do more complex work.
         # ranges can be specified in many ways, use whatever way is most convenient.
         $range = $ws0->range("A1:B2");
         $range = $ws0->range([[1,1],[2,2]]);
         $range = $ws0->range([{col => 1, row => 1}, {col => 2, row => 2}]);

         $cell = $ws0->range_cell("A1");
         $cell = $ws0->range_cell([1,1]);
         $cell = $ws0->range_cell({col => 1, row => 1});

         $col = $ws0->range_col(1);
         $col = $ws0->range_col("A3:A");
         $col = $ws0->range_col([1]);
         $col = $ws0->range_col([[1, 3], [1]]);
         $col = $ws0->range_col({col => 1});

         $row = $ws0->range_row(1);
         $row = $ws0->range_row("C1:1");
         $row = $ws0->range_row([<false>, 1]);
         $row = $ws0->range_row({row => 1});
         $row = $ws0->range_row([col => 3, row => 1 }, {row => 1}]);

         # add a header:
         $row = $ws0->range_row(1);
         $row->insert_d()->freeze()->bold()->italic()->center()->middle()->submit_requests();
         # sends the values to the api directly, not using batch (less efficient):
         $row->values(values => [qw(Id Name Tax Salary)]);

         # bold the names:
         $col = $ws0->range_col("B2:B");
         $col->bold()->submit_requests();

         # add some tax info:
         $tax = $ws0->range_cell([ 3, 5 ]);   # or 'C5' or [ 'C', 5 ] or { col => 3, row => 5 }...
         $salary = $ws0->range_cell({ col => "D", row => 5 }); # same as "D5"
         # set up batch update with staged values:
         $tax->batch_values(values => "=SUM(C2:C4)");
         $salary->batch_values(values => "=SUM(D2:D4)");
         # now collect the ranges into a group and send the values via batch:
         $rg = $sheet->range_group($tax, $salary);
         $rg->submit_values();
         # bold and italicize both cells:
         $rg->bold()->italic()->submit_requests();

         # tie ranges to a hash:
         $row = $worksheet->tie_cells({id => 'A2'}, {name => 'B2'});
         $row->{id} = '1001';
         $row->{name} = 'Herb Ellis';
         tied(%$row)->submit_values();

         # or use a hash slice:
         $ranges = $worksheet->tie_ranges();
         @$ranges{ 'A2', 'B2', 'C2', 'D4:E5' } =
           (1001, "Herb Ellis", "123 Some Street", [["Halifax"]]);
         tied(%$ranges)->submit_values();

         # use simple header column/row values as a source for tied keys:
         $cols = $worksheet->tie_cols('Id', 'Name');
         $cols->{Id} = [1001, 1002, 1003];
         $cols->{Name} = ['Herb Ellis', 'Bela Fleck', 'Freddie Mercury'];
         tied(%$cols)->submit_values();

         # format tied values by requesting that the tied hash returns the
         # underlying range objects on fetch:
         tied(%$rows)->fetch_range(1);
         $rows->{Id}->bold()->center();
         $rows->{Name}->red();
         # turn off fetch range and submit the formatting:
         tied(%$rows)->fetch_range()->submit_requests();

         # iterators can be used to step through ranges:
         # a basic iterator on a column:
         $col = $worksheet->range_col(1);
         $i = $col->iterator();
         while(1) {
           $cell = $i->next();
           last if !defined $cell->values();
         }

         # a basic iterator on an arbitrary range, iterating by col or row:
         $range = $worksheet->range("A1:C3");
         $i = $range->iterator(dim => 'col');
         $cell = $i->next();  # A1
         $cell = $i->next();  # A2
         $i = $range->iterator(dim => 'row');
         $cell = $i->next();  # A1
         $cell = $i->next();  # B1

         # an iterator on a range group:
         $col = $worksheet->range_col(1);
         $row = $worksheet->range_row(1);
         $rg = $spreadsheet->range_group($col, $row);
         $i = $rg->iterator();
         $rg2 = $i->next();  # another range group of cells A1, A1
         $rg2 = $i->next();  # another range group of cells A2, B1

         # an iterator on a tied range group:
         $cols = $worksheet->tie_cols(qw(Id Name));
         $i = tied(%$cols)->iterator();
         $row = $i->next();
         $row->{Id} = '1001';
         $row->{Name} = 'Herb Ellis';
         tied(%$row)->submit_values();

Description

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 that should be fine for most normal needs, but those behaviours can be overridden when necessary.

It is assumed that you are familiar with the Google Sheets API: https://developers.google.com/sheets/api

Comments

anonymouse
Anonymous
Have you seen Net::Google::Spreadsheets::V4? If not, maybe you can contribute to it or extend it or takeover maintenance.
I did look at that package briefly when I was trying to find support for V4 sheets. It does close nothing for you - get_sheet, clear_sheet, to_csv is all it has.

Please sign up to post a review.