sironekotoroの日記

Perl と Mac の初心者の備忘録

Perl で Gogle Sheet API をつかってセルに値を書き込む

さくっとやりました!と言いたいところなのですが

3 ヶ月じゃないな 2 ヶ月くらいだな。

Google Sheet API でセルの内容を書き換えるメソッドは 3 つあるんですが、自分の用途に合致した values/batchUpdate を使いました。

developers.google.com

後、Qiita のこの記事がとても参考になりました。

qiita.com

こんな感じでセルを埋めることができます。

f:id:sironekotoro:20201123121228p:plain

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

use Data::Dumper;
use HTTP::Tiny;
use JSON;
use URI;
use utf8;

my $ACCESS_TOKEN = '';

my $SPREADSHEET_ID = '1sVNSpvtWPPkv5Qnb0tifYHpKMxaehxj5ChAeQ1G1kgA';

my $GOOGLE_SHEET_API = "https://sheets.googleapis.com/v4/spreadsheets/";

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

# Method: spreadsheets.values.batchUpdate  |  Sheets API
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate
values_batchUpdate();

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

    my $SHEET_URI
        = $GOOGLE_SHEET_API . $SPREADSHEET_ID . '/values:batchUpdate';
    my $URI = URI->new($SHEET_URI);

    my $response = $ht->request(
        'POST', $URI,
        {   content => encode_json(
                {   valueInputOption => 'USER_ENTERED',
                    data             => [
                        {   range  => 'シート1!A1',
                            values => [
                                [ 200,    100, '=A1+B1' ],
                                [ 'hoge', '',  'fuga' ]
                                ]
                        }
                    ],
                    includeValuesInResponse      => 'true',
                    responseValueRenderOption    => 'UNFORMATTED_VALUE',
                    responseDateTimeRenderOption => 'FORMATTED_STRING',
                }
            )
        },
    );
    # print Dumper $response;
}

Perl で Gogle Sheet API をつかってセルの値を取得する

これも GET だけでできるのでお手軽ー

説明よりもコードを読んでもらったほうが早い気がする。

配列リファレンスの中の配列リファレンス、てな形でデータが取得できるので、csv にするなりなんなり後は自由自在ですね(力量による)

ちょっと詰まったところ

この API は取得するセルの範囲を指定する必要があります。

シート1!A1:C2 こんな感じで。

もう一段楽をするためには、値が入っているセルの範囲を取得する必要があります。

値が入っているセルの行と列の最大値が欲しい・・・!

Google Apps Script だと getLastRow() とか getLastColumn() みたいなやつ。

developers.google.com

となると、API でアクセスして、情報が入っている最後の Row(行)と Column(列)を把握して・・・あれー、そんな関数見当たらないぞー

困ったときは Google 先生、そして stack overflow。

stackoverflow.com

You can set the range to "A2:D" and this would fetch as far as the last data row in your sheet.

あ、そういう・・・列さえ把握しておけばいい的な・・・

で解決したのでした。

Perl で Gogle Sheet API をつかってセルの値を取得する

以下がコードです。

情報をとるときには 2 つの方法 values.get, values.batchGet があるので、太っ腹に両方やってみました。

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

use Data::Dumper;
use HTTP::Tiny;
use JSON;
use URI::QueryParam;
use URI;
use utf8;

my $SPREADSHEET_ID = '1sVNSpvtWPPkv5Qnb0tifYHpKMxaehxj5ChAeQ1G1kgA';

my $ACCESS_TOKEN  = '';

my $GOOGLE_SHEET_API = "https://sheets.googleapis.com/v4/spreadsheets/";

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

values_get();
values_batchGet();


# Method: spreadsheets.values.get
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get
sub values_get {
    my $ht
        = HTTP::Tiny->new( default_headers => { Authorization => $bearer, } );

    my $SHEET_URI
        = $GOOGLE_SHEET_API . $SPREADSHEET_ID . '/values/シート1!A1:C';
    my $URI = URI->new($SHEET_URI);

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

    print Dumper $data->{values};

}

# Method: spreadsheets.values.batchGet
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet
sub values_batchGet {
    my $ht
        = HTTP::Tiny->new( default_headers => { Authorization => $bearer, } );

    my $URI
        = URI->new( 'https://sheets.googleapis.com/v4/spreadsheets/'
            . $SPREADSHEET_ID
            . '/values:batchGet' );
    $URI->query_param( ranges => 'シート1!A1:C');

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

    print Dumper $data->{valueRanges}->[0]->{values};

}

Perl で Gogle Sheet API をつかってタイトルを変更する

なんか、適当に文言を入れ替えて自動生成したみたいなタイトルになってきたなぁ・・・

それはそれとして、Google Drive API に引き続き、Google Sheet API を使ってホゲホゲするってやつです。

以前、モジュールを使ってやってみたんですが、せっかく Web API 投げる方法もわかったことだし、この方法でやってみよう!ってことで。

使う都度 Any::Moose の警告が出るのが気に食わないってのもあります。

sironekotoro.hateblo.jp

Google Sheet API の許可とアクセストークン が必要です

かつての記事で書いてます・・・なんか、遠い昔の気がする

sironekotoro.hateblo.jp

sironekotoro.hateblo.jp

タイトルを変更する

まずはジャブから・・・早速、公式のリファレンスを確認します。

developers.google.com

おぉ、わかりやすい。もっと他の言語での実装例を参考にして回るかと思ってた。あっさり。

リファレンスにあるリクエストボディ は json にしておく必要があります。

これは先のページには書いてないんですが、正しい構造のJSON 送らないとエラーで「まともな JSON で送って〜」と言われます。

今回は好みで HTTP::Tiny 使いました。

リクエストボディを設定するときは公式リファレンスの \%options のところで content をキーにする必要があるので注意です。

metacpan.org

エラーメッセージを見て直しつつ完成したのが以下スクリプトです。

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

use HTTP::Tiny;
use JSON;
use URI;
use utf8;

my $SPREADSHEET_ID = '1sVNSpvtWPPkv5Qnb0tifYHpKMxaehxj5ChAeQ1G1kgA';

my $ACCESS_TOKEN = '';

my $GOOGLE_SHEET_API = "https://sheets.googleapis.com/v4/spreadsheets/";

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

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

my $url = URI->new( join "",
    ( $GOOGLE_SHEET_API . $SPREADSHEET_ID . ':batchUpdate' ) );

my $response = $ht->request(
    'POST',
    $url,
    {   content => encode_json {
            requests => [
                {   updateSpreadsheetProperties => {
                        properties =>
                            { title => "Perl から API 叩いて変更したタイトル" },
                        fields => "title"
                    }
                }
            ]
        }
    },
);

print $response->{status} . "\n";
print $response->{reason} . "\n";

f:id:sironekotoro:20201121164441p:plain

ちなみに、タイトルを変えても更新日は変わらない模様。

Perl で Slack API を使ってユーザー一覧を取得する(追記あり)

Slack のユーザー一覧を取得せよ!

ってなお仕事が降ってきたものの、Slack のメニューとか探したど見当たらず。

これは API 使えってことなのかなぁ・・・使うんはいいけど、普通の非テックな会社だと厳しいんでは。

もっと高い料金プランだと出てくるのかな?

それとも、非テックでも Slack 使うような会社ならそれくらいいけるんだろうか。

API を使うそのまえに

ってわけで、いつも通り Perl でやります・・・が、やる前に API を利用できるようにする準備から。

こちらの記事が大変役に立つというか、そのまま進んでそのまま OAuth Token を入手することができました。

ありがたい。

qiita.com

WebService::Slack::WebApi

あとは、Perl の便利モジュールがうまいことやってくれます。

metacpan.org

#!/usr/bin/env perl
use strict;
use warnings;
binmode STDOUT, ":utf8";

use WebService::Slack::WebApi;

my $ACCESS_TOKEN = 'xoxp-hogehogefugafuga';

my $slack = WebService::Slack::WebApi->new( token => $ACCESS_TOKEN );

my $users = $slack->users->list;

# $users の中にユーザーの情報が入っているので、
# とりたい情報が気になる人は Data::Dumper で中を覗いてみる

for my $member ( @{ $users->{members} } ) {

   # 削除済みユーザーや、bot も含まれているので除外する
    next if ( $member->{deleted} == 1 ) || ( $member->{is_bot} == 1 );

    print "$member->{name}\t$member->{profile}->{display_name}\n";
}

あっさり。

2020-11-20 追記

依頼主もメンバー一覧とるのに API しかなさそうー、って言ってはいたんですが、slack のワークスペースの管理者であれば、確認方法があることを教えていただきました。

そりゃあるよなぁ!(テノヒラクルー

ありがとうございます・・・って、bio 見たらこの方、slack のAPI SDK 作っておられるのですね・・・助かりました。

画面左上のワークスペース -> 設定と管理 -> メンバーを管理する、から確認することができました。

f:id:sironekotoro:20201120102113p:plain

Perl で Gogle Drive API をつかって特定のファイルを削除する

ファイルの上書き終わったところでこのシリーズ(シリーズ?)終わらせようと思っておりました。

しかし CRAD:Create, Read, Add, Delete の一角たる Delete やらないっていうのはないんじゃないか?

どうせ後で必要になりそうだし・・・あぁ、でも面倒〜。

ってことでやりました。

いざ手をつけるとすぐでしたが、普段は色々なモジュールの裏に隠れている HTTP の DELETE メソッドを明示的に初めて使ったように思います。

情報処理試験の教本や問題集でしか見たことのなかった(そして問題には出そうにない) DELETE メソッド、本当にあって本当に使えるんや・・・という謎の感動がちょっとあります。

そして、削除が成功した時の HTTP ステータスコード 204 これも初見でした。

先輩と覚える HTTP ステータスコード · GitHub

削除したら応答も何もないよねというのわかる。

はー!これで CRAD 全部揃ったーはずー!!

・・・しかし、なんか my 宣言が並んでてアレな感じのコードですね・・・

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

binmode STDOUT, ":utf8";

use URI;
use HTTP::Tiny;

my $ACCESS_TOKEN  = "";

my $GOOGLE_DRIVE_API = "https://www.googleapis.com/drive/v3/files/";

my $delete_fileid = "1SNmgEpX2xnFCbeRQWtGg4PdU0ic6h92IO-VuynVGli8";

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

my $uri = URI->new( $GOOGLE_DRIVE_API . $delete_fileid );

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

my $response = $ht->delete($uri);

print $response->{status} . "\n";    # 204

Perl入学式 オンライン 2020 第2回お疲れ様でした

受講された方、サポーターの方、お疲れ様でした。 講師をやったジャージの人です。

講義に利用したスライド・動画類は以下 Perl 入学式の公式サイトで公開しています。参加された方も、参加できなかった方も、ぜひ復習に使ってください。

www.perl-entrance.org

また、復習問題を用意しています。
四則演算、文字列連結の問題にチャレンジしてみてください。私の回答例もこちらおいておきます。

問題の意味がわからない、とか、このような解答例はどうだろう?という方は Discord のPerl入学式チャンネル(招待コード)や、twitterハッシュタグ #Perl入学式 をつけて聞いてみてください。

Perl入学式 オンライン 2020 第2回

今回は「四則演算」「文字列連結」「コマンドライン引数」を学習しました。次回は条件分岐、IF 文からとなります。

IF 文は変数に続くプログラミング独特、プログラミングらしい学習内容です。おたのしみに!

もちろん、待ち切れない方は資料やスライドツールから先に学習を進めていただいて大丈夫です。その上で、不明な点があれば Discord や twitter で聞いてください。

コマンドライン引数

昨年までの Perl 入学式 ではコマンドライン引数ではなく、標準入力を学習していました。

標準入力はコマンドライン引数と同様に、プログラムの外部から入力を与え、プログラムの挙動や表示を変える方法の一つです。

コマンドライン引数」と「標準入力」、何が違うかというと、入力を行うタイミングです。

  • コマンドライン引数:実行前にあらかじめ引数という形で入力を与えておく。

  • 標準入力:プログラムの実行中に、入力する。対話型のプログラムを作ることができる。

標準入力に興味のある方は昨年度のカリキュラムを見てみてください。

github.com

なお、Wandbox で標準入力の問題を実行してみる場合、対話型のプログラムとはならず、コマンドライン引数と同じような形であらかじめ入力しておく、という形になります。

標準入力の入力欄はコマンドライン引数のものとは異なります。

コードを書いているエディタ欄の下に Stdin というリンクがあります。このリンクをクリックして開くところにあらかじめ入力しておきます

f:id:sironekotoro:20201115132329p:plain

・・・あらかじめ?

となると、Wandbox 上ではコマンドライン引数と同じ様な感じだよなぁ、ってことで、Wandbox を使った学習ではコマンドライン引数に変更しました。

以下に Wandbox で標準入力を使った場合のサンプルを置いておきます。

wandbox.org

コマンドライン引数の実例

自作のツールでコマンドライン引数を使っています。

github.com

最初の引数に銀行名、次の引数に支店名を入力すると、その条件に該当する銀行コードと支店コードを表示してくれます。

もし、コマンドライン引数がないと、検索する都度、コードの一部を書き換えてから実行という手順になります。

それは面倒ですよね。

こういう時、コマンドライン引数でちゃちゃっと条件を変えて実行結果を変えていけると、とても楽です。

f:id:sironekotoro:20201115134054p:plain

第3回の開催について

次回は12月を予定しています。年末にかからないところ、初旬から中旬での開催を予定しています。

次回の参加、お待ちしております!

Perl で Gogle Drive API をつかってフォルダーを作成する

はい、慣れてきました。

API の URL と mimeType 以外はそのまんまです。

my $GOOGLE_DRIVE_API
    = "https://www.googleapis.com/upload/drive/v3/files?create";

# (中略)

          `mimeType => 'application/vnd.google-apps.folder',`

developers.google.com

To create a folder, use the files.create method with the application/vnd.google-apps.folder MIME type and a title.

(DeepL翻訳)フォルダを作成するには、files.create メソッドを使用して、application/vnd.google-apps.folder MIME タイプとタイトルを指定します。

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

binmode STDOUT, ":utf8";

use HTTP::Request::Common;
use JSON qw/encode_json/;
use LWP::UserAgent;

my $GOOGLE_DRIVE_API
    = "https://www.googleapis.com/upload/drive/v3/files?create";
my $ACCESS_TOKEN    = "";

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

my $ua = LWP::UserAgent->new;
my $res = $ua->request(
    POST $GOOGLE_DRIVE_API,
    'Content-Type' => 'multipart/form-data',
    Authorization => $bearer,
    Content => [

        metadata => [
            undef,
            undef,
            'Content-Type' => 'application/json;charset=UTF-8',
            'Content' => encode_json(
                {
                    name     => 'create_folder_test',
                    mimeType => 'application/vnd.google-apps.folder',
                    parents  => ['10kCqEUmWsWlqMdP_vF9pDGrQXFVZ-Lvr'],
                },
            ),
        ],

    ],
);

print  $res->code . "\n";
print  $res->content . "\n";

# 200
# {
#  "kind": "drive#file",
#  "id": "1cJ1jQOQ9KAqxVPJwIWNhGuXH1BBA9k2M",
#  "name": "create_folder_test",
#  "mimeType": "application/vnd.google-apps.folder"
# }