sironekotoroの日記

Perl と Mac の初心者の備忘録

Perl から Google Sheet を読み書きする

Perl から Google Sheet を読み書きする・・・ための3種の TOKEN を手に入れる」の続きです。

sironekotoro.hateblo.jp

編集対象の Google Sheets を用意する

API で書き込みを行う Google Sheet を一つ用意します。

用意した Sheet の URL から、以下の場所にあるスプレッドシートの ID を控えておきましょう。

スプレッドシートの ID は URL の中のここです。

例:https://docs.google.com/spreadsheets/d/{スプレッドシートID}/edit#gid=0

テスト用なので、こんな内容にしてみます。

f:id:sironekotoro:20201018145759p:plain

Perl のモジュールを用意する

Perl のモジュールはこちらを利用します。

metacpan.org

息を吸うように $ cpanm Net::Google::Spreadsheets::V4 ・・・すると、関連するモジュールのインストールに失敗してインストールできません。

自分の環境では Net::Google::DataAPI がインストール後のテストに失敗しているようでした。

個別でテストなしでのインストールを行います。-n をつける事でテストなしでのインストールを試みます。

$ cpanm -n Net::Google::DataAPI

無事インストールできたので、再度 $ cpanm Net::Google::Spreadsheets::V4 ・・・インストール完了しました。

ちなみに、インストール後に以下のスクリプトで確認できるのですが・・・

#!/usr/bin/env perl
use strict;
use warnings;

use Net::Google::Spreadsheets::V4;

モジュールを呼び出した「だけ」のスクリプトですが、以下の警告が出ます。

Any::Moose is deprecated. Please use Moo instead

これは先ほどのインストールエラーの原因でもあったのですが、このモジュールが利用しているモジュールの一つである Any::Moose が deprecated 非推奨である旨のメッセージです。

無視をしてもスクリプトは動くので続けます。

Any::Moose が何者であり、なぜ deprecated なのかはこちらを参考

gihyo.jp

blogs.perl.org

Google Sheets を読んでみる(シート自体の情報)

以下のスクリプトを手元の環境で実行します。

#!/usr/bin/env perl
use strict;
use warnings;

use Net::Google::Spreadsheets::V4;

my $CLIENT_ID    = "";
my $CLIENT_SECRET = "";
my $REFRESH_TOKEN    = "";
my $SPREADSHEET_ID = "";

my $gs = Net::Google::Spreadsheets::V4->new(
    client_id      => $CLIENT_ID,
    client_secret  => $CLIENT_SECRET,
    refresh_token  => $REFRESH_TOKEN,
    spreadsheet_id => $SPREADSHEET_ID,
);

 # シートのプロパティを取得
my $sheet = $gs->get_sheet( sheet_id => '0' );

# シートのプロパティを表示
for my $key ( sort keys %{ $sheet->{properties} } ) {
    printf( "%s:%s\n", $key, $sheet->{properties}->{$key} );
}
Any::Moose is deprecated. Please use Moo instead at /Users/sironekotoro/.plenv/versions/5.30.1/lib/perl5/site_perl/5.30.1/Net/Google/DataAPI/Auth/OAuth2.pm line 2.
gridProperties:HASH(0x7fd1f5eb9f18)
index:0
sheetId:0
sheetType:GRID
title:シート1

シートのプロパティであるシートのタイトル名の取得ができたと思います。

Google Sheets を読んでみる(セルの内容の取得)

おまちかね、セルの取得です。

#!/usr/bin/env perl
use strict;
use warnings;
use Data::Dumper;

use Net::Google::Spreadsheets::V4;

my $CLIENT_ID      = "";
my $CLIENT_SECRET  = "";
my $REFRESH_TOKEN  = "";
my $SPREADSHEET_ID = "";

my $gs = Net::Google::Spreadsheets::V4->new(
    client_id      => $CLIENT_ID,
    client_secret  => $CLIENT_SECRET,
    refresh_token  => $REFRESH_TOKEN,
    spreadsheet_id => $SPREADSHEET_ID,
);

my ( $content, $res, ) = $gs->request( GET => '?includeGridData=true' );

print Dumper $res;

これでセルの情報を表示できます・・・が、セルの背景色や文字色といったすべての情報が降ってくるので、莫大な量です。

うちの環境では3386行の表示(Dumper)でした。ひゃー

今回はセルの入力内容だけが欲しいので、出力を絞ります。先のスクリプトを1行変更します。

# my ( $content, $res, ) = $gs->request( GET => '?includeGridData=true' );

# fields パラメータで欲しいプロパティだけ書いておく
my ( $content, $res, ) = $gs->request( GET => '?includeGridData=true&fields=sheets.data.rowData.values(formattedValue)' );

これで Dumper での出力が151行程度になりました。ギガに優しいですね。

セルの内容だけ出力するスクリプトはこうなります。

Dumper の結果を頑張ってパースしたんですが、多分どっかに絶対楽な方法があると思うんですよね・・・うちが知らないだけで・・・

#!/usr/bin/env perl
use strict;
use warnings;

use Net::Google::Spreadsheets::V4;

my $CLIENT_ID      = "";
my $CLIENT_SECRET  = "";
my $REFRESH_TOKEN  = "";
my $SPREADSHEET_ID = "";

my $gs = Net::Google::Spreadsheets::V4->new(
    client_id      => $CLIENT_ID,
    client_secret  => $CLIENT_SECRET,
    refresh_token  => $REFRESH_TOKEN,
    spreadsheet_id => $SPREADSHEET_ID,
);

use Data::Dumper;

my ( $content, $res, $hoge )
    = $gs->request( GET =>
        '?includeGridData=true&fields=sheets.data.rowData.values(formattedValue)'
    );

my $cells = $content->{sheets}->[0]->{data}->[0]->{rowData};

my $count_row = 1;
my @col       = ( 'A' .. 'Z' );

for my $rows ( @{$cells} ) {

    my $count_col = 0;

    # 行の処理
    my $values = $rows->{values};
    for my $value ( @{$values} ) {

        # 列の処理
        print $col[$count_col]
            . $count_row . ':'
            . $value->{formattedValue} . "\n";

        $count_col++;

    }
    $count_row++;

}
Any::Moose is deprecated. Please use Moo instead at /Users/sironekotoro/.plenv/versions/5.30.1/lib/perl5/site_perl/5.30.1/Net/Google/DataAPI/Auth/OAuth2.pm line 2.
A1:hoge
B1:fuga
A2:piyo
B2:Perl

Google Sheets に書いてみる

途中にあるあからさまなデータ構造のおかげで、雰囲気は掴めるのではないかと・・・

あ、途中にある use utf8; 忘れると文字化けするので注意。

#!/usr/bin/env perl
use strict;
use warnings;

use Net::Google::Spreadsheets::V4;

my $CLIENT_ID      = "";
my $CLIENT_SECRET  = "";
my $REFRESH_TOKEN  = "";
my $SPREADSHEET_ID = "";

my $gs = Net::Google::Spreadsheets::V4->new(
    client_id      => $CLIENT_ID,
    client_secret  => $CLIENT_SECRET,
    refresh_token  => $REFRESH_TOKEN,
    spreadsheet_id => $SPREADSHEET_ID,
);

use utf8;

my ( $content, $res ) = $gs->request(
    POST => ':batchUpdate',
    {   requests => [
            {   updateCells => {
                    start => {
                        sheetId     => 0,
                        rowIndex    => 0,
                        columnIndex => 0
                    },
                    rows => [
                        {   values => [
                                {   userEnteredValue => {
                                        stringValue =>
                                            'Perl入学式'
                                    }
                                },
                                {   userEnteredValue => {
                                        stringValue =>
                                            '始まりました!'
                                    }
                                },
                            ],
                        },
                        {   values => [
                                {   userEnteredValue => {
                                        stringValue => '詳細はconnpassで'
                                    }
                                },
                                {   userEnteredValue => {
                                        stringValue =>
                                            'Perl入学式オンラインで検索!'
                                    }
                                },
                            ]
                        }
                    ],
                    fields => "userEnteredValue"
                }
            },

        ]
    }
);

f:id:sironekotoro:20201018152620p:plain

あからさま〜!

というわけで

ここまでくるのに苦労したんですが、いやー、大変でした。

多分絶対もっと楽な方法があるはずなんだよなぁ・・・

最終的には、 CSV ファイルや Perl のデータ構造をこの API を使って Google Sheets にあげるところまで行きたいですね。

(できそう、と思うと途端に筆が鈍る人)

それではー