「Perl から Google Sheet を読み書きする・・・ための3種の TOKEN を手に入れる」の続きです。
編集対象の Google Sheets を用意する
API で書き込みを行う Google Sheet を一つ用意します。
用意した Sheet の URL から、以下の場所にあるスプレッドシートの ID を控えておきましょう。
スプレッドシートの ID は URL の中のここです。
例:https://docs.google.com/spreadsheets/d/{スプレッドシートID}/edit#gid=0
テスト用なので、こんな内容にしてみます。
Perl のモジュールを用意する
Perl のモジュールはこちらを利用します。
息を吸うように $ 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 なのかはこちらを参考
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" } }, ] } );
あからさま〜!
というわけで
ここまでくるのに苦労したんですが、いやー、大変でした。
多分絶対もっと楽な方法があるはずなんだよなぁ・・・
最終的には、 CSV ファイルや Perl のデータ構造をこの API を使って Google Sheets にあげるところまで行きたいですね。
(できそう、と思うと途端に筆が鈍る人)
それではー