システムアイ Advent Calendar 2022の記事となります。

概要

Google Apps Scriptとは、Google スプレッドシート等のGoogleのサービスで様々な自動化を行えるJavaScriptプラットフォームです。

使用例に挙げられているものは以下のようなものです。

Google ドキュメント、スプレッドシート、フォームにカスタムメニュー、ダイアログ、サイドバーを追加する。

Google スプレッドシートのカスタム関数とマクロを作成します。

ウェブアプリを公開（スタンドアロン、または Google サイトに埋め込んだ）

AdSense、アナリティクス、カレンダー、ドライブ、Gmail、マップなど、他の Google サービスを利用する。

アドオンを作成してGoogle Workspace Marketplace に公開します。

どうやらその気になればウェブアプリのようなものも作れるようですが、一番出番のありそうなGoogleスプレッドシートでの利用についてフォーカスしてみたいと思います。

実際にマクロとして使ってみる(Googleスプレッドシート)

実際にGoogleスプレッドシートでApps Scriptを利用してみようと思います。
おおよその使用の流れは以下の通りです。

スプレッドシートを開く
サブメニューの「拡張機能」→「Apps Script」を選択
別なタブにApps Scriptのエディタが開く
必要な処理(JavaScript)を書く
関数を実行する

1.スプレッドシートを開く

今回はサンプルとして新規のスプレッドシートを作成し、そのシートに対しての処理を作っていきます。

赤枠で囲われた サンプル というスプレッドシートで作業していきます。

2.サブメニューの「拡張機能」→「Apps Script」を選択

開いたスプレッドシートからApps Scriptのエディタを開きます。

3.別なタブにApps Scriptのエディタが開く

以下のようなエディタの画面が表示されます。

4.必要な処理(JavaScript)を書く

処理を書いてみます。
今回は以下のような処理を書いてみます。

function myFunction() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activeSheet.getSheetByName("シート1");

  sheet.getRange("A1").setValue("Hello World!");
}

5.関数を実行する

処理を書いたら実際に動かしてみます。

Apps Scriptはクラウド上(GCP)で実行されることになります。そのため一度保存する必要があります。
保存は実行のボタン左にあるフロッピーのボタンで保存ができます。

保存ができたら実行ボタンを押します。

実行が完了すると 実行ログ に処理が完了したことが表示されます。

すると実行結果がスプレッドシートに反映されます。

※初回実行の場合、一度Apps Scriptがアクセス権限の確認がされます。

権限を確認 ボタンを押します。

別なウィンドウが開くので、権限確認をするGoogleアカウントを選択します。

未承認の関数のため、警告が表示されます。
詳細を押し、一番下にある無題のプロジェクト（安全ではないページ）に移動を押します。(無題のプロジェクトとなってはいますが、別途プロジェクト名を設定した場合はその名称となります。)

Googleアカウントへのアクセスの許可を求める表示が出てきます。許可を押すことで処理が実行されます。

自作の関数を作ってみる(Googleスプレッドシート)

Googleスプレッドシートで利用できる関数も自作できます。

今回はopenDBと呼ばれる書籍情報を取得できるAPIを利用した関数を作ってみます。
openDBの利用規約等については公式サイトを参照してください。

以下の流れで自作関数を追加します。

コードの追加
(今回は追加の権限が必要なため)Apps Scriptの権限の追加
実際にスプレッドシートに作った関数を使ってみる

1.コードの追加

以下の関数を先程作ったコードに追加します。

/**
 * ダミーの呼び出し関数
 */
function callBookTitle() {
  console.log(getBookTitle("9784048686051")); // ダミーとして実際の書籍のISBNを設定
}

/**
 * openDBからISBNをキーに書籍のタイトルを取得
 */
function getBookTitle(isbnVal) {
  if (isbnVal == "") return ;

  const apiBaseUrl = "https://api.openbd.jp/v1/get?";
  const apiParamIsbn = "isbn=";

  var response = UrlFetchApp.fetch(apiBaseUrl + apiParamIsbn + isbnVal);
  var responseJson = JSON.parse(response.getContentText());

  return responseJson[0]["onix"]["DescriptiveDetail"]["TitleDetail"]["TitleElement"]["TitleText"]["content"];
}

コードを書き終わったら保存してください。

2.(今回は追加の権限が必要なため)Apps Scriptの権限の追加

今回はopenDB APIを利用します。外部のAPIを利用するために予めApps Scriptで権限を追加する必要があります。

関数呼び出し用のダミー関数を実行します。
複数の関数が定義されている場合、デバッグボタン右にあるプルダウンから実行する関数を選択し、実行ボタンを押します。
(複数の関数を定義するような状態になっていくと、実行する関数の選択を忘れている場合もあります。よく確認しましょう)

再度権限が必要である旨のメッセージが表示されます。権限を確認ボタンを押します。

別なウィンドウが開き、権限確認をするGoogleアカウントを選択します。
選択後にアクセス許可を求める表示が出てきます。

青枠で囲っているものが追加を要求されている権限です。許可ボタンを押して権限を付与します。

付与が完了すると関数が実行され、実行ログ部分に結果が表示されます。

3.実際にスプレッドシートに作った関数を使ってみる

作った関数を実際にスプレッドシートで使ってみます。
適当なセルに以下の数式を入力します。(引数のISBNはお好みのものをご利用ください。)

=getBookTitle("978-4-04-868605-1")

APIの結果を解析され、以下のように表示されれば自作関数が動いています。

まとめ

GoogleスプレッドシートでのApps Scriptの利用について、簡単にですがまとめてみました。
マクロは表計算ソフトとしては拡張性のキモとなる機能です。このような形でGoogleスプレッドシートを有効活用ができることを知っていただければと思います。

Google Apps Scriptを使う

概要