GAS(Google Apps Script)とスプレッドシート Part1(基本編)

今回は、Google Apps Script、通称GASでスプレッドシートを利用する方法についてです。前回、Cloud FunctionsとGAS、スプレッドシートを利用することでほぼ無料かつ自動でスクレイピングプログラムを実行する方法について記事を書きました。

スプレッドシート+GAS(Google Apps Script)+Cloud Functionsで自動実行スクレイピング

スクレイピングプログラムは様々な書籍やブログで紹介されており、勉強環境は非常に充実しています。一方でそのプログラムをどのように運用するかについては、情報量ははるかに少なくなります。この記事ではその1つの解としてGASとスプレッドシート、Cloud Functionsを使った自動実行システム構築の方法を解説しています。

その時、GASからスプレッドシートを利用する方法が既にわかっているとの前提で書きましたが、別件でGASの奥深さを知る機会があったので、この機会に基本から応用を改めてまとめようと考え、記事にすることにしました。当記事では、GASとスプレッドシートの連携が基本となり、Google Forms等についてはフレいません。今回は基本編ですが、今後公式ドキュメントのクイックスタート、工場向けのシフト管理表システムについて記事にしようと考えています。

なお、1つ前の記事でも書きましたが、Google Apps ScriptはJavaScriptをベースにしていますが、最新のJavaScriptには対応していません。そのため、アロー関数や一部関数については利用できない場合があるので注意頂ければと思います。

この記事が一人でも多くの方の参考になれば幸いです。

Google Apps Script(GAS)のエラーに対処する

Google Apps Scriptはスプレッドシートやグーグルフォームなどと簡単に連携できる非常に使い勝手の良いグーグルアプリの機能です。JavaScriptを踏襲してはいますが、完全には同じでないためエラーが発生することがあります。今回はGASで遭遇したエラーとその原因、解決策についてまとめました。

GASの公式ドキュメントは以下になります。

公式ドキュメント: https://developers.google.com/apps-script/

※ 当記事は2019年8月現在の情報に基づいており、今後GASのアップグレード等で下記内容に齟齬が生じる場合もありますので、あらかじめご了承頂ければと思います。

1. Google Apps Scriptとスプレッドシートとは

まずはGASとは何か説明したいと思います。公式ドキュメントでGASは次のように説明されています。

Google Apps Script is a rapid application development platform that makes it fast and easy to create business applications that integrate with G Suite. You write code in JavaScript and have access to built-in libraries for favorite G Suite applications like Gmail, Calendar, Drive, and more. There’s nothing to install—we give you a code editor right in your browser, and your scripts run on Google’s servers.
reference: https://developers.google.com/apps-script/overview

内容としては、「GASはG Suiteと連携するビジネス用のアプリケーションを早く、そして簡単に作れる開発プラットフォームであり、JavaScriptで記述でき、また利用者はGmailやカレンダー、GoogleドライブなどのG Suiteアプリケーション向けのビルトイン(最初から用意された組み込みの)ライブラリを利用できる」。そして、「利用者は何もインストールする必要はなく、ブラウザ上のエディターを利用でき、グーグルサーバーでスクリプトを実行できる」と説明されています。つまり、JavaScriptとグーグルが用意したライブラリで、グーグルの様々なアプリケーションを利用できるのがGoogle Apps Scriptであり、利用環境も特別に構築することなくブラウザで完結するということです。ライブラリというのは、Googleの様々なアプリケーションを利用するためのGAS専用のコードの集まり(パッケージ集)です。G SuiteとはGmailやGoogleカレンダーなどのグーグルアプリを独自ドメインで会社などビジネス用途で利用できるサービスのことですが、GASは通常のグーグルアカウントでも利用でき、会社のような組織でビジネス用途で使うわけでないのであれば、無料で使える通常のグーグルアカウントで十分です。

2. GASからスプレッドシートを使う基本構造

まずはGASの利用イメージを説明したいと思います。

2-1. GASコードの基本構造

GASでスプレッドシートを利用する基本的な構造は次のようになります。

オブジェクトの生成
スプレッドシートへの実行処理

オブジェクトを生成し、そのオブジェクトを使ってスプレッドシートへの実行処理を記述するのが、GASコードの基本構造となります。ここでいう「オブジェクト」とは後述するようなスプレッドシートやその中のシート、書き込みやデータを取得するセル情報などを格納したものです。オブジェクト指向に添えば、インスタンスという言葉の方が適当な気がするのですが、GASでは「オブジェクト」という呼称が一般的な様です。

2-2. 代表的なオブジェクトの種類

このオブジェクトには様々な種類がありますが、スプレッドシートで利用する代表的なオブジェクトは次の3つです。

スプレッドシートオブジェクト
シートオブジェクト
レンジオブジェクト

3つとも名前からなんとなくイメージしやすいと思いますが、スプレッドシートオブジェクトはスプレッドシートに関する情報をもつオブジェクトで、シートオブジェクトはスプレッドシートの中のシートに関する情報を持ち、レンジオブジェクトはセルの情報をもちます。

3. GASからスプレッドシートを操作する

ここからは具体的にGASでスプレッドシートを操作する基本的な方法について説明していきます。黒塗り部分については個人情報等の秘匿したい情報ですので、特段GASの利用に関わるものではありません。

3-1. GASファイルの作成(スプレッドシートからGASを開く)

まず、スプレッドシートからGASファイルを作成する方法です。まずは、スプレッドシートを新規に作成しますが、今後オブジェクトの生成時に対象がどれか確認するために下記のスクリーンショットの様にスプレッドシートとシートの名称を変更しておきます。スプレッドシートのIDについては後ほど説明します。

次にGASファイルを作成します。次の様に「ツール」から「スクリプトエディタ」を選択します。このスクリプトエディタがこの記事内でGASファイルと読んでいるもので、GASの説明部分で書かれていたブラウザ上で使えるエディタのことです。なお、今回はGASとスプレッドシートの連携を軸にしているためスプレッドシートからGASファイルを作成する方法について記述していますが、グーグルドライブから直接GASファイルを作成することもできます。

作成したスクリプトエディタの画面が下記になります。ここでもGASファイルの名前を変更しています。またコードの上側には、実行ボタンや実行する関数を指定するボタンが設置されています。関数の名前は適宜変更することができます。

3-2. GASの実行承認

GASにコードを記述する準備ができましたが、コードを記述する前に、GASコードの初回実行時に必要となる実行承認について説明します。GASコードを初めて実行するとき次のスクリーンショットの様に「承認が必要」とメッセージが出ます。これは作成したGASファイルからスプレッドシートにアクセスし、操作するできる権限を付与する手続きです。知らない第三者が勝手にスプレッドシートのデータを利用できない様にするためのセキュリティ措置になります。初めてGASコードを実行する際は、下記のスクリーンショットに沿って順次承認手続きを行えば大丈夫です。

「詳細」をクリック

「GAS-Intro（安全でないページ）に移動」をクリック

「許可」をクリック

これでGASファイルへのスプレッドシートへのアクセスと編集権限の付与が完了します。

3-3. 基本的なGASコード

ここからは具体的なGASコードの書き方を説明していきます。なお各メソッドやプロパティを参照する公式リファレンスは下記になりますので、英語にはなりますが適宜そちらも参照して頂ければと思います。

公式リファレンス: Spreadsheet Service

funcOne(基本)

最初の関数funcOneです。GASでは関数名を自由に設定できます。コードは以下になります。

function funcOne() {
  // 1. SpreadsheetOBJの生成(ssはSpreadSheetの略語。スプレッドシートのIDは破線で一部非表示にしている)
  var ssA = SpreadsheetApp.getActiveSpreadsheet();
  var ssB = SpreadsheetApp.openById("1c-DkvQNRAVUuS~~~~~~HbNH7CU-H32Iy9lvBgEP-lI");
  
  // 2. SheetOBJの生成
  var sheetA = ssA.getActiveSheet();
  var sheetB = ssB.getSheetByName('test');
  
  // 3. RangeOBJの生成
  var rangeA = sheetA.getRange('A1');
  var rangeB = sheetB.getRange(1, 2, 1, 1);
  
  // 4. 値の挿入
  rangeA.setValue('testA');
  rangeB.setValue('testB');
};

基本的な流れを整理すると次の様になります。(OBJはオブジェクトの略)

SpreadsheetOBJの生成…どのスプレッドシートを利用するか決まります。
SheetOBJの生成…指定したスプレッドシートの中のどのシートを活用するかを決まります。
RangeOBJの生成…値の入力や取得を行うセルをどのセルにするか決まります。
値の挿入…RangeOBJで指定したセルに値を挿入します。

上記のコードでは各オブジェクトをそれぞれAとBという名称で分けていますが、これはオブジェクトを生成する方法が複数あるためです。SpreadsheetOBJとSheetOBJの生成方法は主に2つで、アクティブなスプレッドシート、シートを指定するgetActive~~メソッドを利用する方法と、IDや名前を明示的に指定してオブジェクトを生成する方法です。アクティブな状態とは、その名の通りアクティブな状態にあるもので、通常今回の様にスプレッドシートからGASファイルを作成した場合はデフォルトのアクティブスプレッドシートが元のスプレッドシートになります。またシートの場合はスプレッドシート上で選択されている(すぐに文字の入力などの編集ができる状態の)シートになります。なお、スプレッドシートを明示的に指定する場合は、スプレッドシートのIDを使いますが、これが最初のスプレッドシートのスクリーンショットで記載していたIDです。

RangeOBJではセルの座標で選択するものと行と列を数字で表現して選択する方法の2つがあります(この場合の4つの引数のそれぞれの意味は下記に記載しています)。ここではRangeAはそのまま”A1″セルを、一方RangeBは”B1″セルを選択しています。そして最後にsetValueメソッドで値を入力しています。実行結果は次のスクリーンショットの様になります。初回実行時にだけ、3-2の実行承認の画面が出ると思うので、承認作業をして下さい。

ここでのポイントは以下になります。 * スプレッドシート、シート、セル(Range)のオブジェクトを作成する

スプレッドシートからシートオブジェクトを、シートオブジェクトからレンジオブジェクトを作る様に、上位階層から下位階層のオブジェクトを生成する
オブジェクトを作成するときにはアクティブな状態のものを対象とする方法と、IDや名称を指定して作成する方法がある
セルを指定する方法は、セルの座標を指定する方法と行と列をそれぞれ番号で表現する方法の2つがある

なお、今回、オブジェクトをスプレッドシートから順に作成していますが、メソッドをつなぎ合わせて(チェーンメソッド)一気にオブジェクトを作成でき、またスプレッドシートオブジェクトから直接レンジオブジェクトを生成するメソッドなど、ユーザーが柔軟に使用できるメソッドがGASライブラリに多数用意されています。

ここで出てきたメソッドを下記になります(<>内は戻り値の型です)。

SpreadsheetApp.getActiveSpreadsheet()…アクティブ状態のスプレッドシートを対象とするスプレッドシートオブジェクトを生成
SpreadsheetApp.openById(スプレッドシートのID)…IDからスプレッドシートオブジェクトを生成
SpreadsheetOBJ.getActiveSheet()…アクティブ状態にあるシートを対象とするシートオブジェクトを生成
SpreadsheetOBJ.getSheetByName(シート名)…シート名で指定したシートオブジェクトを生成
SheetOBJ.getRange(a1notation)…引数で指定した座標(a1notation)のセルを対象とするレンジオブジェクトを生成
SheetOBJ.getRange(row, column, numRows, numColumns)…行、列の番号からレンジオブジェクトを生成。row、columnはそれぞれA1セルから何個目のセルを表現し、(row, column)=(1,1)であればA1であり、(3,2)であればB3セルを表す。そのセルを基準にnumRowsとnumColumnsを使って対象のセル範囲を指定する。(3,2,4,3)であれば、”B3:D6″となる(1つ目のそれ自体のセルを指す)。
RangeOBJ.setValue(value)…レンジオブジェクトで対象としているセルにvalueを入力する(setValueが単数形であることに注意)

これが一番基本となるGASからスプレッドシートを利用する方法です。

funcTwo(複数のセルにそれぞれ値を入力する)

次は複数の値を挿入する方法です。GASファイルでは複数の関数を1つのファイルに記述できるので、下記コードをfuncOneの下にコピペし、関数の選択部分で”funcTwo”を選択すればこの関数を実行することができます。

function funcTwo() {
  var sheetA = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var sheetB = SpreadsheetApp.openById("1c-DkvQNRAVUuS~~~~~~HbNH7CU-H32Iy9lvBgEP-lI").getSheetByName('test');
  // 5. 複数の値の挿入
  cellA = [
    ['A3'],
    ['A4'],
    ['A5']
  ]
  cellB = [
    ['B3'],
    ['B4'],
    ['B5']
  ]
  rangeA = sheetA.getRange('A3:A5');
  rangeB = sheetA.getRange(3, 2, 3, 1);
  rangeA.setValues(cellA);
  rangeB.setValues(cellB);
};

ここでは、メソッドを繋げることでシートオブジェクトを生成しています。また、rangeAとrangeBにそれぞれ別の値を挿入しています。複数のセルに値を入力する場合は二次元の配列(配列を要素とする配列)で値を設定する必要があります。1次元目の配列(配列の要素となっているもの)は行を表します。そして2次元目の配列(配列の要素の配列の値)は列の値を表します。この場合、cellAの[‘A3’]は3行目を、[‘A4’]は4行目を表しています。一方で’A3’も’A4’も2次元目の配列の1つ目の要素なので1列目となります。また、複数のセルに値を挿入する場合はRangeOBJ.setValuesメソッドを利用します。行と列を間違えた場合など、レンジオブジェクトと入力する値の範囲が異なる場合エラーが出ますので、注意して下さい。このコードの実行結果は次のスクリーンショットになると思います。

ここでのポイントは次になります。

メソッドをつなぐことで一気に下位のオブジェクトまで生成できる(この場合はSpreadsheetAppオブジェクトからシートオブジェクトを生成)
複数のセルを指定する場合、一次元目の配列は「行(row)」を二次元目の配列は「列(column)」を表す
入力する値の配列構造がセル範囲と整合しない場合エラーが発生する
複数のセルに値を入力する場合はRangeOBJ.setValuesメソッドを使う

ここで使用したメソッドは以下になります。

RangeOBJ.setValues(入力する値)…複数のセルに値を入力する(setValuesが複数形であることに注意)

funcThree(スプレッドシートに入力されているデータを取得する)

3つ目の関数funcThreeではスプレッドシートに入力されているデータを取得します。スプレッドシートの入力内容はfuncOneとfuncTwoを実行した結果が入力されたものを利用します(funcTwoの実行結果を写したスクリーンショットです)。

function funcThree() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange()
  values = range.getValues();
  Logger.log(values)
}

データが入力されたセル情報を取得するSheetOBJ.getDataRangeメソッドを指定しています。そのセルから入力データをSheetOBJ.getValuesメソッドで取得し、変数valuesに格納しています。最後はログ表示させるLogger.logメソッドを使っています。GASファイルの画面で”command+Enter”を押すことでログを確認できます。ログ画面には次のように出力されると思います。データの見方はレンジオブジェクトの配列と同じになります。

ここで使用したメソッドは以下になります。

SheetOBJ.getDataRange()…データが入力されているセル範囲を取得する
RangeOBJ.getValues()…セルに入力された値を取得する(複数形であることに注意)
Logger.log(表示する値)…ログで表示する

funcFour

最後がfuncFourです。これは今まで入力した値を全て削除しまっさらなシートに戻す関数です。シートはfuncOneとfuncTwoを実行した状態のシートを仮定します。

function funcFour() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();
}

この関数を実行するとclearメソッドでシートの入力内容が全て削除されます。一部のセルの値だけを消したり、フォーマットだけを初期化する兄弟メソッドが多数ありますので、公式リファレンスで確認してみてください。実行結果は次の画像のようになります。

ここで紹介したメソッドは以下になります。

SheetOBJ.clear()…シートの入力内容を全削除する

4. まとめ(参考サイト付き)

以上がGoogle Apps Script、通称GASの基本的な使い方です。オブジェクトを生成し、メソッドで指定した処理を行っていくという非常に簡単な使い方ができます。またライブラリがかなり充実しており、痒いところに手がとどくメソッドも多数ありますので、オリジナルコードを作る際には英語を恐れず公式リファレンスを積極的に参照して頂ければと思います。なお、下記のサイトはGASやGoogleアプリのAPIなどの様々な使い方を基本から応用まで幅広くわかりやすく紹介していますので、参考にして頂ければと思います(私も一番最初はそのサイトをみてGASを勉強をしました)。

【保存版】初心者向け実務で使えるGoogle Apps Script完全マニュアル

Googleが提供しているJavaScriptベースの開発環境Google Apps Script（GAS）。このページは初心者がGASを実務に活用できるようになるまでを支援するまとめ記事です。

読んで頂きありがとうございました。この記事が参考になりましたら幸いです。