Google Apps Scriptでスプレッドシートへチェックボックスを作成する

概要

最近導入されたスプレッドシートのチェックボックスについて、スクリプトで使用する方法についてまとめました。スプレッドシートの挿入を選択するとチェックボックスの項目が追加されており、今の時点では下の図のようにNEWが付いています。

チェックボックスの作成

SpreadsheetAppの場合

ドキュメントを見てみると、SpreadsheetAppを使って下記のようなスクリプトでチェックボックスを作成するのだと思われますが、これを実行すると「サーバー エラーが発生しました。しばらくしてからもう一度試してください。」とのエラーが発生してしまいます。まだ未完成？なのかもしれません。

var rule = SpreadsheetApp.newDataValidation().withCriteria(SpreadsheetApp.DataValidationCriteria.CHECKBOX, ["TRUE", "FALSE"]).build();
SpreadsheetApp.getActiveSheet().getRange("A1").setDataValidation(rule);

Sheets APIの場合

SpreadsheetAppではまだ使用できそうになかったため、Sheets APIならどうかと試してみたところ、無事に作成することができましたので、その方法について下記に記載させていただきます。

このサンプルスクリプトを使用する際は、Googleの拡張サービスとAPIコンソールでSheets APIを有効にしてください。

• Googleの拡張サービス
  • スクリプトエディタから リソース -> Googleの拡張サービス を開き、Sheets APIを有効にしてください。
• APIコンソール
  • スクリプトエディタから リソース -> Cloud Platform プロジェクト を開き、右下の 「APIコンソールを表示」 をクリックします。
  • スタートガイドの中にある「APIを有効化し、鍵などの認証情報を取得」をクリックします。
  • 左側の「ライブラリ」を開いて「APIとサービスを検索」のテキストボックスへ「Sheet API」と入力して検索し、「Google Sheets API」をクリックして「有効にする」ボタンを押して有効にします。

これで準備は完了です。

チェックボックスはいろいろな状況で使用されると思われますので、ここではサンプルとして、今開いているシートの"A1:C3"の9つのセルへチェックボックスを作成してみます。スクリプトは次の通りです。dataValidationについてのドキュメントはこちらをご覧ください。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetId = ss.getActiveSheet().getSheetId();
var resource = {"requests": [
  {
    "repeatCell": {
      "cell": {"dataValidation": {"condition": {"type": "BOOLEAN"}}},
      "range": {"sheetId": sheetId, "startRowIndex": 0, "endRowIndex": 3, "startColumnIndex": 0, "endColumnIndex": 3},
      "fields": "dataValidation",
    },
  },
]};
Sheets.Spreadsheets.batchUpdate(resource, ss.getId());

"A1:C3"のセルが空の場合は次のよう全てのチェックボックスがチェック無しの状態（FALSE）で作成されます。それぞれのチェックボックスは、マウスでクリックするとチェックが付き、TRUEに変化します。

次に、"A1:C3"のセルが全てTRUEと書かれている場合は全てのチェックボックスがチェック有りの状態（TRUE）で作成されます。

TRUE, FALSEを判定する文字列を変えてみる

Sheets APIのdataValidationリクエストのBooleanConditionには、typeとvaluesのプロパティがあり、typeにはチェックボックスを作成するために"BOOLEAN"の値を使用するのですが、valuesには2つの値を設定することができます。0番目の要素にはTRUEと判定する文字列、1番目の要素にはFALSEと判定する文字列を使用します。これを上記のサンプルスクリプトへ適応させると次のようになります。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetId = ss.getActiveSheet().getSheetId();
var resource = {"requests": [
  {
    "repeatCell": {
      "cell": {"dataValidation": {"condition": {"type": "BOOLEAN", "values": [{"userEnteredValue": "hoge"}, {"userEnteredValue": "fuga"}]}}},
      "range": {"sheetId": sheetId, "startRowIndex": 0, "endRowIndex": 3, "startColumnIndex": 0, "endColumnIndex": 3},
      "fields": "dataValidation",
    },
  },
]};
Sheets.Spreadsheets.batchUpdate(resource, ss.getId());

修正したスクリプトを実行すると下記のようになります。

実行前 :

実行後 :

チェックボックスの値取得

チェックボックスの値を取得する場合は、getValue()やgetValues()を使って取得することができます。例えば、スプレッドシートのバウンドスクリプトを使ってセルA1のチェックボックスの値を取得する場合は下記のようなサンプルスクリプトを使用します。getDisplayValues()でも問題ありません。

var value = SpreadsheetApp.getActiveSheet().getRange("A1").getValue();

取得される値は、チェックされている場合、されていない場合でそれぞれ設定したTRUE, FALSEに対応した文字列が返されます。デフォルト値はTRUE, FALSEです。