Googleドライブの各種データを自動処理できる「GAS」で作業自動化の第一歩を踏み出してみた

Googleが提供するスクリプト実行環境「Google Apps Script」(以下GAS)を使うと、簡単にJavaScriptを実行することができます。Google ドキュメントやGmailなどのGoogle提供の各種アプリを簡単に操作することができるとのことで、今回は試しにGoogle スプレッドシートを操作してみました。

Apps Script  |  Google Developers
https://developers.google.com/apps-script/

まず、元となるデータを作成します。Google ドライブの画面左上にある「新規」をクリックし、「Google スプレッドシート」を選択します。

今回は、「201708」や「201709」といった月ごとのシートがあり、そのシートの「C2」セルに合計金額の記載があるという「テストデータ」を用意しました。

同様に「テストデータその2」を用意しました。「テストデータ」と「テストデータその2」のそれぞれの合計金額を一つのシートにまとめるスクリプトを書いていきます。

スクリプトファイルを作成するために、「Google Apps Script」アプリを追加します。Google ドライブの画面へ戻り、「新規」から「その他」を選び、「アプリを追加」をクリック。

右上の検索欄に「google apps script」と入力すると「Google Apps Script」が表示されます。右側の「＋接続」をクリック。

「Google Apps ScriptがGoogle ドライブに接続されました。」と表示されるので「OK」をクリックして閉じます。

もう一度「新規」から「その他」を選びます。新たに「Google Apps Script」が追加されているのでクリック。

わかりやすいように名前を「集計スクリプト」に変更しておきました。

スクリプトの書き方を調べていきます。Google Apps Scriptのレファレンスページを開き、「Sheets」アイコンをクリックします。

クラス一覧をスクロールしていくと、説明文に「This class allows users to open Google Sheets files and to create new ones.(Google Sheetsファイルを開いたり、作成したりするためのクラスです)」と書いてあるクラスが見つかりました。「SpreadsheetApp」をクリックして詳細を確認します。

メソッド一覧を眺めていると「openByUrl(url)」という、URLを入力したらスプレッドシートを開いてくれそうな名前のメソッドを発見。クリックして詳細を確認します。

使い方が記載されていました。このコードをお手本にして書いていきます。

コード画面に戻り、

var testdata = SpreadsheetApp.o

まで入力すると候補が出てくるので、カーソルキーで「openByUrl」を選択してエンターキーを押します。

先ほど作成したテストデータを開き、URLをコピーして貼り付けます。お手本通りにURLを「'」で挟みます。

うまく動作しているか確かめるためにログを取りたいので、

Logger.log(testdata);

を下に挿入して実行ボタンをクリックします。

「承認が必要です」とのダイアログが出るので「許可を確認」をクリック。

アカウントを選択し……

「許可」をクリックしてスプレッドシートシートへのアクセスを許可するとスクリプトが実行されます。

「表示」タブの「ログ」をクリックするか、「Ctrl＋Enter」を押してログを確認します。

変数「testdata」に入っているオブジェクトがSpreadsheetになっていればOK。

「テストデータその2」ファイルも同様に書いていきます。

ログにも出力されていたように、「SpreadsheetApp.openByUrl()」の戻り値は「Spreadsheet」オブジェクトです。左のメニューバーから「Spreadsheet」をクリックして詳細を確認します。

「Spreadsheet」クラスのメソッドに「getSheetByName()」というものがあり、名前でシートを開けるのが便利そうだったのでこれを使用してみました。

引数にはシートの名前である「"201709"」を設定。見本と同じように「"」(ダブルクオーテーション)で囲っておきます。先ほどと同様にログを出力するための関数である「Logger.log(testdatasheet)」を設定し、実行ボタンをクリックします。

「Ctrl＋Enter」を押すとログ画面に。変数「testdatasheet」にSheetオブジェクトが入っていれば成功です。

「Sheet」クラスのメソッドに「getSheetValues()」といういかにもスプレッドシートから値を取得してくれそうなものがあったのでこれを使います。

今回読み取りたいセルは「C2」です。

引数に「2行目3列目から1行1列取得する」という意味になるように「2, 3, 1, 1」を設定します。ログの出力をセットして実行ボタンをクリック。

「Ctrl＋Enter」でログ画面に移動し、読み取れているか確認します。ここで数字がただの「8321.0」ではなく「 [[8321.0]] 」と二重に括弧がつく形式になっているのは、この数字が二次元配列の要素であることを示しています。しかしJavaScriptは二次元配列の要素[[8321.0]]を数値「8321.0」に自動で読み替えてくれるので、ここでは違いを無視することにします。

つづいて読み取ったデータを書き込む設定をしていきます。「集計結果」という名前の空のシートを作成しました。

書き込み用のスプレッドシートを用意したら読み込んだときと同じようにスクリプトから操作できるようにしていきます。「テストデータ」の読み込みに使用した部分を使い回しました。

レファレンスによると、「sheet」クラスのオブジェクトから直接書き込むことはできず、「Range」クラスを使うとのこと。「Range」オブジェクトを取得する方法を探してみると「sheet」クラスのメソッドに「getRange()」という直球そのままな名前のものがあったのでこれを使うことに。使用例を参考にします。

左上、つまり1行1列目のセルから2行2列に書き込みたいので引数は「1, 1, 2, 2」に設定します。実行ボタンをクリックすると……

エラーが出てしまいました。「行 17」とあるので17行目を確認します。

エラー文によると、変数「matomesheet」に「null」が入ってしまっているようです。

「matomesheet」を宣言した行を確認すると、「matome.getSheetByName("201709")」の表記。この結果が「null」になるということは、「matome」オブジェクトに「201709」という名前のシートが存在しないということを意味しています。

「集計結果」スプレッドシートを確認すると、シート名が「シート1」のままになっていました。シート名を「201709」に変更してもう一度スクリプトを実行してみます。

「Ctrl＋Enter」を押してログを確認すると、きちんと「Range」オブジェクトが代入されていました。

書き込むための「Range」クラスのメソッドを探していると「setValues()」というものを発見。2次元のセルに入力していくので引数には2次元配列を用意しなければいけません。

入力用の配列「values」を用意し、いざ実行。

「テストデータ」「テストデータその2」からそれぞれの値を読み取り、別のスプレッドシートに書き込むことができました。

GASを使ってスプレッドシートのデータを操作できました。JavaScriptということもあって実行環境の用意が不要なので、作業の自動化を始める第一歩を踏み出すにはぴったりのサービスです。