Google スプレッドシートを拡張する

Google Apps Script では、Google スプレッドシートを使用してさまざまな操作を行えます。Apps Script を使用して、Google スプレッドシートにカスタム メニューダイアログ、サイドバーを追加できます。また、スプレッドシートのカスタム関数を作成するだけでなく、カレンダー、ドライブ、Gmail などの他の Google サービスと統合することもできます。

Google スプレッドシート用に設計されたほとんどのスクリプトは、スプレッドシート内のセル、行、列を操作するために配列を操作します。JavaScript の配列に慣れていない方向けに、Codecademy では配列向けの優れたトレーニング モジュールを提供しています。(なお、このコースは Google が開発したものではなく、Google とは関連付けていません。)

Google スプレッドシートで Apps Script を使用する方法については、5 分間のクイックスタート ガイドでマクロ、メニュー、カスタム関数をご覧ください。

始める

Apps Script には、Google スプレッドシートをプログラムで作成、読み取り、編集できる特別な API が含まれています。Apps Script は、主に次の 2 つの方法で Google スプレッドシートとやり取りできます。スクリプトに対するユーザーの権限が適切な場合は、どのスクリプトでもスプレッドシートを作成または変更できます。また、スクリプトをスプレッドシートにバインドすることもできます。これにより、ユーザー インターフェースを変更したり、スプレッドシートを開いたときに応答したりできるようになります。バインドされたスクリプトを作成するには、Google スプレッドシート内から [拡張機能] > [Apps Script] を選択します。

スプレッドシート サービスでは、Google スプレッドシートを 2 次元配列で操作するグリッドとして扱います。スプレッドシートからデータを取得するには、データが格納されているスプレッドシートにアクセスし、データを保持するスプレッドシートの範囲を取得して、セルの値を取得する必要があります。Apps Script では、スプレッドシートで構造化データを読み込み、JavaScript オブジェクトを作成することで、データへのアクセスを容易にします。

データの読み取り

下の画像で示すように、スプレッドシートに保存する商品名と商品番号のリストがあるとします。

次の例は、商品名とプロダクト番号を取得してログに記録する方法を示しています。

function logProductInfo() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

ログの表示

記録されたデータを表示するには、スクリプト エディタの上部にある [実行ログ] をクリックします。

データの書き込み

新しいプロダクト名や番号などのデータをスプレッドシートに保存するには、スクリプトの最後に次のコードを追加します。

function addProduct() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

上記のコードにより、スプレッドシートの一番下に、指定された値を含む新しい行が追加されます。この関数を実行すると、スプレッドシートに新しい行が追加されます。

カスタム メニューとユーザー インターフェース

Google スプレッドシートをカスタマイズするには、カスタム メニュー、ダイアログ ボックス、サイドバーを追加します。メニュー作成の基本については、メニューに関するガイドをご覧ください。ダイアログ ボックスの内容のカスタマイズについては、HTML サービスガイドをご覧ください。

スプレッドシート内の画像や図形描画にスクリプト関数を添付することもできます。この関数は、ユーザーが画像または図形描画をクリックすると実行されます。詳しくは、Google スプレッドシートの画像と図形描画をご覧ください。

アドオンの一部としてカスタム インターフェースを公開する場合は、スタイルガイドに沿って Google スプレッドシート エディタのスタイルとレイアウトに一貫性を持たせます。

Google フォームに接続する

Apps Script では、フォームスプレッドシートのサービスを使用して Google フォームを Google スプレッドシートに接続できます。この機能では、スプレッドシート内のデータに基づいて Google フォームを自動的に作成できます。Apps Script では、トリガーによってユーザーがフォームに応答した後、onFormSubmit などの特定のアクションを実行することもできます。Google スプレッドシートを Google フォームに接続する方法について詳しくは、5 分間のクイックスタート(Google フォームの回答の管理)をご覧ください。

Formatting

Range クラスには、セルまたはセルの範囲の形式にアクセスして変更するための setBackground(color) のようなメソッドがあります。次の例は、範囲のフォント スタイルを設定する方法を示しています。

function formatMySpreadsheet() {
  // Set the font style of the cells in the range of B2:C2 to be italic.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

データの入力規則

Apps Script を使用すると、Google スプレッドシートの既存のデータ検証ルールにアクセスしたり、新しいルールを作成したりできます。たとえば、次のサンプルは、セルに 1 ~ 100 の数値のみを許可するデータ検証ルールの設定方法を示しています。

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  var cell = SpreadsheetApp.getActive().getRange('B4');
  var rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

データ検証ルールの操作の詳細については、SpreadsheetApp.newDataValidation()DataValidationBuilderRange.setDataValidation(rule) をご覧ください。

チャート

Apps Script では、特定の範囲内のデータを表すグラフをスプレッドシートに埋め込むことができます。次の例では、セル A1:B15 にグラフ化可能なデータがあることを前提として、埋め込み棒グラフが生成されます。

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

スプレッドシートにグラフを埋め込む方法については、EmbeddedChart と特定のグラフ作成ツール(EmbeddedPieChartBuilder など)をご覧ください。

Google スプレッドシートのカスタム関数

カスタム関数は、=SUM(A1:A5) のような組み込みのスプレッドシート関数に似ていますが、Apps Script で関数の動作を定義する点が異なります。たとえば、値をインチからミリメートルに変換するカスタム関数 in2mm() を作成し、スプレッドシートで =in2mm(A1) または =in2mm(10) という数式を入力して数式を使用できます。

カスタム関数の詳細については、メニューとカスタム関数の 5 分間のクイックスタートをご覧ください。また、カスタム関数の詳細については、こちらをご覧ください。

マクロ

マクロは、Google スプレッドシートの UI から Apps Script コードを実行するもう 1 つの方法です。 カスタム関数とは異なり、キーボード ショートカットまたは Google スプレッドシートのメニューから有効にできます。詳しくは、Google スプレッドシートのマクロをご覧ください。

Google スプレッドシート用のアドオン

アドオンは、Google スプレッドシート内で実行され、Google スプレッドシートのアドオンストアからインストール可能な、特別にパッケージ化された Apps Script プロジェクトです。Google スプレッドシート用のスクリプトを開発し、世界に向けて共有したい場合は、Apps Script でスクリプトをアドオンとして公開し、他のユーザーがアドオンストアからそのスクリプトをインストールできるようにすることができます。

Triggers

Google スプレッドシートのファイルにバインドされたスクリプトは、onOpen()onEdit() などの関数などの単純なトリガーを使用して、スプレッドシートの編集権限があるユーザーがスプレッドシートを開いたり編集したりすると、自動的に応答できます。

シンプルなトリガーと同様に、インストール可能なトリガーを使用すると、特定のイベントが発生したときに Google スプレッドシートで自動的に関数を実行できます。ただし、インストール可能なトリガーは、単純なトリガーよりも柔軟性が高く、オープン、編集、変更、フォーム送信、タイムドリブン(時計)などのイベントをサポートします。