Perl で Google Sheet API を使（って|わなくても） csv でダウンロードする

休日の方が仕事がらみのプログラムが捗る

ってことないですかね？

うちはそうです。

つまり逆は・・・この話やめますか

こんな業務

以下のフローで処理している業務があります。

他部署から限定公開の Google Sheet の URL がこちらに伝えられてくる
Google Sheet を csv ファイルでダウンロードする
- ファイル -> ダウンロード -> カンマ区切りの値
ダウンロードした csv ファイルを特定のファイル名にリネームする
別なプログラムを起動して csv ファイルを読み込ませる
生成された諸々のファイルをサーバーにあげてなんたらかんたら

めんどさの水位を超えた

まず、この業務の時にいちいち Google Sheet みなきゃいけないのがめんどいです。

ダウンロードするためだけにブラウザで開くのめんどい。

コマンド一発でファイルをダウンロードできたらいいのに・・・という思いを持ってきたのですが、書きだした通りたいした手間でもない（15秒もかからない）のでずっと「めんどい」と思いながら放置してきたわけです。

しかし、めんどい値が水位を超えて溢れる日がやってきました。先週です。

というわけで、いつものように Perl で片付けます。

Perl で Google Sheet API を使って csv でダウンロードする

まず、限定公開の Google Sheet なので、プログラムからアクセスするには OAuth による認可とったうえで API でアクセスします。

当初は Google Drive API で持ってきたら楽なのでは？と考えたのですが、シート単位でのダウンロードはできない模様。

できるという情報ある方、お待ちしてます・・・

developers.google.com

で、Google Sheet API 使いました。

ほとんど過去のコードのコピペです。

Google Sheet API で Google Sheet のデータを取得して、csv に書き出すってやつです。

sironekotoro.hateblo.jp

#!/usr/bin/env perl
use strict;
use warnings;
use Encode;
use HTTP::Tiny;
use JSON;
use Text::CSV_XS;
use URI;

my $ACCESS_TOKEN = access_token(
    CLIENT_ID     => '',
    CLIENT_SECRET => '',
    REFRESH_TOKEN => '',
);

my $bearer = join " ", ( 'Bearer', $ACCESS_TOKEN );

my $rows = values_get(
    bearer           => $bearer,
    SPREADSHEET_ID   => '',
    GOOGLE_SHEET_API => 'https://sheets.googleapis.com/v4/spreadsheets/',
    SHEET_NAME       => 'シート1',
);

my $csv = Text::CSV_XS->new( { eol => "\012"} );
open my $FH, '>:utf8', './test.csv';
while ( my $row = shift @${rows} ) {
    $csv->print( $FH, $row );
}
close $FH;

sub values_get {
    my %hash = @_;

    my $ht
        = HTTP::Tiny->new( default_headers => { Authorization => $bearer, } );

    my $SHEET_URI
        = $hash{GOOGLE_SHEET_API}
        . $hash{SPREADSHEET_ID}
        . '/values/'
        . "$hash{SHEET_NAME}!A1:BV";

    my $URI = URI->new($SHEET_URI);

    my $res  = $ht->get($URI);
    my $data = decode_json( $res->{content} );

    return $data->{values};
}

sub access_token {
    my %hash = @_;

    my $URI = URI->new('https://oauth2.googleapis.com/token');

    my $ht = HTTP::Tiny->new();

    my $response = $ht->request(
        'POST', $URI,

        {   content => encode_json(
                {   client_id     => $hash{CLIENT_ID},
                    client_secret => $hash{CLIENT_SECRET},
                    grant_type    => 'refresh_token',
                    refresh_token => $hash{REFRESH_TOKEN},
                }
            )
        }
    );

    my $json = decode_json( $response->{content} );

    return $json->{access_token};
}

しかし、出来上がった csv ファイルは Google Sheet の API からダウンロードした csv ファイルと内容が異なっています。

どうやら、こんな感じ

Web からダウンロード：セルに日本語が入っており一定の長さ以上だったり , カンマやスペースが入っている時は " ダブルクオーテーションで囲んでいる（？）、そうでない時は " ダブルクオーテーションで囲んでいない
今回作ったのでダウンロード：機械的にフィールドの値を " ダブルクオーテーションで囲んでいる

うーん。

いや、csv ファイルがちょっと異なっていようが、今回 Perl でおとしてきた csv ファイルが、手順上の「その上で別なプログラムを起動して csv ファイルを読み込ませる」でも読めればいいわけです。

・・・だめでした。無念。

このまま、Google Sheet 上での csv ダウンロードの仕様に沿った csv つくる・・・？

俺が、俺こそが Google になって Google Sheet を csv にパースする？

それってもっとすんごく面倒そうだなー、となります。

Perl で Google Sheet API を使わなくても csv でダウンロードする

色々とぐぐっている中で、こういう記事がありました。

qiita.com

あら、curl で csv にしてダウンロードできるの・・・？

この記事では公開されている Google Sheet ですが、非公開でも OAuth で通ったやつをつけてあげれば良いのでは？と考えました。

#!/usr/bin/env perl
use strict;
use warnings;
use HTTP::Tiny;
use URI;
use Encode;
use JSON;

my $ACCESS_TOKEN = access_token(
    CLIENT_ID     => '',
    CLIENT_SECRET => '',
    REFRESH_TOKEN => '',
);

my $bearer = join " ", ( 'Bearer', $ACCESS_TOKEN );

my $http = HTTP::Tiny->new( default_headers => { Authorization => $bearer, } );

my $url = 'https://docs.google.com/spreadsheets/d/xxxxxx/export?gid=0123456789&format=csv';
my $file = './test.csv';
my %options = ();
my $response = $http->mirror($url, $file, \%options);
if ( $response->{success} ) {
    print "$file is up to date\n";
}

sub access_token {
    my %hash = @_;

    my $URI = URI->new('https://oauth2.googleapis.com/token');

    my $ht = HTTP::Tiny->new();

    my $response = $ht->request(
        'POST', $URI,

        {   content => encode_json(
                {   client_id     => $hash{CLIENT_ID},
                    client_secret => $hash{CLIENT_SECRET},
                    grant_type    => 'refresh_token',
                    refresh_token => $hash{REFRESH_TOKEN},
                }
            )
        }
    );

    my $json = decode_json( $response->{content} );

    return $json->{access_token};
}

あっさりダウンロードできました。csv ファイルも Google シートからダウンロードしたものと diff をとっても違いなし。

これでまた一つ、仕事が楽になりました。

sironekotoroの日記

Perl で楽をしたい