読者です 読者をやめる 読者になる 読者になる

satoshi_komyのブログ

気になったことを書き連ねていきます

Google Apps ScriptでGoogleスプレッドシートを操作する

概略

Googleの各種システムにはGoogle Apps Script(以下GAS)と呼ばれるスクリプト実行環境が用意されていて、例えばMicrosoft ExcelにおけるマクロやVBAのように開発・実行することができる。GASで操作するのはWebサイトであったりスプレッドシートであったりといった場合が多いようだけども、文書ファイルでも動かせるのかもしれない。あるいはGoogleドライブのファイル操作といったAPIも用意されているので、ファイルのアップロードみたいなことも可能なようだ。そしてお約束の基本的な操作部分はだいたいどのパターンでも変わらないので、サンプルコードとして載せておくことにする。

基本的なやり方を覚えてしまえばそう難しくはない

まずはコードの記述方法から。
スプレッドシート → ツール → スクリプトエディタ でGoogle Apps Scriptのコード記述画面が出てくる。そこでJavaScriptベースのコードを記述していけばOK。デフォルトでmyFunctionというサンプル関数が用意されているが、この関数に記述していってもいいし、自分で関数を作ってもいい。このブログではmyFunctionを流用している。

とにかくなんでもいいからスプレッドシートを操作してみる

function myFunction() {
  //今開いているスプレッドシートをsheet_sampleへ格納
  var spread_sheet_sample = SpreadsheetApp.getActiveSpreadsheet();

  //「シート1」をアクティブなシートとして設定する
  var sheet_sample = spread_sheet_sample.getSheetByName("シート1");

  //1~100行目まで値を入れてみる。ループ用の変数iとjを宣言
  var i;
  var j;
  for(i=1;i<=100;i++){
    for(j=1;j<=100;j++){
      //(i,j)セルにi*jを入れる
      sheet_sample.getRange(i, j).setValue(i*j);
    }
  }
}

厳密には違うけども、スプレッドシートスクリプトエディタで↑のコードを書いて、myFunctionを選択して実行*1すると、元のスプレッドシートの(1,1)セル~(100,1)に変数i*jの値が代入される。*2
f:id:satoshi_komy:20170417222351p:plain

↓ちなみに実際のシートはこちら↓
docs.google.com

内容としては昔BASICとかC言語の授業でやったようなよくあるアレだ、九九の表を表示したり、みたいな。GASはJavaScriptと文法は基本的に同じようなので、あとはJavaScriptがわかる人なら適当にいじることもできると思う。
コメントで何やってるか?を書いているけども、SpreadsheetAppとかgetActiveSpreadsheetなんかはお約束だと思えば割とシンプルに考えられるかも。

作った関数/機能を自動実行する

せっかく作った関数/機能もある程度自動で動いてくれないと意味がない。*3 幸いなことにGASにはトリガーという概念があって、Windowsのタスクスケジューラーのように決まった時間に動かすことができる。
やり方はものすごく簡単で、編集メニュー→すべてのトリガーの実行を選択するだけ。f:id:satoshi_komy:20170417221309p:plain
トリガーはプルダウンメニューから選択するだけなので迷うことはないと思う。注意点がひとつあって、時間をトリガーに設定しても正確には動作しないという点。たとえば午前0~1時というトリガーを設定すると午前0時ちょうどには動かず、本当に午前0~1時のどこかのタイミングで実行されるといった感じ。この仕組みはものすごく便利で、言ってみればサーバー/PCが1台無料で用意されているようなもの。通常ならばなんらかのマシンを1台用意して、cron的なものを走らせる必要があるからね。

といったところでここまで。GASのAPIは種類が結構あるのでまだなにも覚えられていないし、そもそも使う機会もあるのかどうかわからないけども、便利なものがあったら簡単にまとめていこうと思う。

*1:実行メニュー→myFunctionを選択

*2:最初の1回のみ「このスクリプトを承認しますか?」的な画面が出てくるので、承認すると実行できる。

*3:日時で処理や結果が変わったりするようなものだと、自動で動いてくれると大きな恩恵に預かれる。