perl-google-sheets-get-all-values

#! /usr/bin/env perl

use feature qw(say);
use strict;
use warnings;
use WWW::Google::Cloud::Auth::ServiceAccount;
use LWP::UserAgent;
use URI;
use URI::Encode;
use HTTP::Request;
use Mojolicious::Lite;
use Data::Dumper;
use JSON;
use experimental qw(declared_refs refaliasing signatures);

{
    my @scopes = ('https://www.googleapis.com/auth/spreadsheets',
                  'https://www.googleapis.com/auth/drive');
    my $auth =  WWW::Google::Cloud::Auth::ServiceAccount->new(
        credentials_path => 'credentials.json',
        scope => join " ", @scopes
    );

    get '/' => sub {
        my $c = shift;
        my $token = $auth->get_token();
        my $sheet_id = '1FPyDuIPPzwUeLNpLbdI-RzfouKcm-2duOJ9Jio-Z-Qw';
        my ($return_code, $matrix) = read_spreadsheet($token, $sheet_id);
        app->log->debug(app->dumper( { matrix => $matrix } ));
        $c->render(template => 'index', return_code => $return_code);
    };
    app->start;
}

sub read_spreadsheet($token, $id) {
    my $encoder = URI::Encode->new();
    my $value_range = $encoder->encode("'Sheet1'");
    my $url = sprintf 'https://sheets.googleapis.com/v4/spreadsheets/%s/values/%s', $id, $value_range;
    my $result = send_google_drive_get_request($url, $token);
    my $status_line = $result->status_line;
    if (!$result->is_success) {
        return $status_line;
    }
    my $result_hash = decode_json( $result->content );
    app->log->debug(app->dumper( $result_hash ));
    return $status_line, $result_hash->{values};
}

sub send_google_drive_get_request( $url, $token ) {
    my @headers = get_headers($token);
    my $req = HTTP::Request->new('GET', $url, \@headers);
    my $ua = LWP::UserAgent->new();
    my $res = $ua->request($req);
    return $res;
}

sub get_headers($token) {
    return
        'User-Agent'      => 'Mozilla/8.0',
        'Accept-Encoding' => 'gzip, deflate',
        'Accept'          => '*/*',
        'Connection'      => 'keep-alive',
        "Authorization"   => "Bearer $token";
}

__DATA__

@@ index.html.ep
<!DOCTYPE html>
<html>
  <head><title>Testing access to google sheets...</title></head>
  <body>
    <h1>Return code = <%= $return_code %></h1>
  </body>
</html>