Perl で Google Sheet API をつかってセルの値を削除する

あれー？

Google Sheet API を使って Google Sheet に金融機関コードや支店ごとの情報を反映させる、ってなコードを書いてました。

以下が完成するとこうなるって画像です。

テストのシートへの反映を確認し、いざ本番のシートに反映！

・・・で、やってみたところ確かに反映されるのですが、一部の値が重複していることがわかりました。

確認したところ、こういうことでした。

• 支店の統廃合の結果、全金融機関の支店数は日々減少している

• 更新に使ったGoogle Sheet API の values:batchUpdate は、引数で渡された範囲のセルの値を更新する

• 対象範囲外のセルは残る

つまり、反映前に 1000 件のリストがあり、更新時のリストが 800 件しかなければ、更新されるのは 800 件のみとなります。

そして残りの 200 件はそのまま残るわけですね。

なるほど。

シートの値だけを消す

ということで、情報を反映させる前に一旦シートの中身を綺麗にすることで対応しました。

今回は値のみを削除し、書式は残します。

これの Perl で書いてみた版です。

developers.google.com

あと自分のブログのこの辺り参考 にしました。半年も前だと完全に忘れてますね。

sironekotoro.hateblo.jp

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

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

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

sheet_clear(
    bearer           => $bearer,
    SHEET_ID         => '123456789',    # 更新したいシートのID（gid= の後の数字）
    SPREADSHEET_ID   => '', # スプレッドシートのID
    GOOGLE_SHEET_API => 'https://sheets.googleapis.com/v4/spreadsheets/',
);

sub sheet_clear {
    my %hash = @_;

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

    my $SHEET_URI
        = $hash{GOOGLE_SHEET_API} . $hash{SPREADSHEET_ID} . ':batchUpdate';
    my $URI = URI->new($SHEET_URI);

    my $response = $ht->request(
        'POST', $URI,
        {   content => encode_json(
                {   requests => [
                        {   updateCells => {
                                range  => { sheetId => $hash{SHEET_ID} },
                                fields => "userEnteredValue",
                            }
                        }
                    ]
                }
            )
        }
    );
}


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};
}

困って解決したらブログに残しておこう

困った時にすぐググる人っていますよね？

自分のことです。

自分が解決したり、困ったり、一度やったことを書いておくと、ググる様が検索して見つけてきてくれます。

ありがたい。

今回も過去の自分の記事に救われました。書いててよかったわー

この記事もきっと未来の自分と、もしかしたら Google 経由で来た誰かの助けになるでしょう。