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

Google Apps Script を使用して Google スプレッドシートを拡張します。Google スプレッドシートにカスタムメニュー、ダイアログ、サイドバーを追加します。Google スプレッドシート用のカスタム関数を作成し、Google カレンダー、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() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

ログを表示

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

データを書き込む

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

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

上記のコードでは、指定された値を使用して、スプレッドシートの末尾に新しい行を追加します。この関数を実行すると、スプレッドシートに新しい行が追加されます。

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

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

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

カスタムインターフェースをアドオンの一部として公開する場合は、Google スプレッドシートエディタのスタイルとレイアウトとの一貫性を保つために、スタイルガイドに沿って作成してください。

Google フォームに接続する

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

データの書式を設定する

Range クラスには、 setBackground などのメソッドがあり、セルまたはセルの範囲の書式にアクセスして変更できます。次の例では、範囲のフォントスタイルを設定します。

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

データの検証

Google スプレッドシートで既存のデータの入力規則にアクセスするか、新しい規則を作成します。たとえば、次のサンプルでは、セルに 1 ～ 100 の数値のみを入力できるようにするデータの入力規則を設定する方法を示しています。

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

データの入力規則の操作について詳しくは、 SpreadsheetApp.newDataValidation、 DataValidationBuilder、および Range.setDataValidation をご覧ください。

チャート

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

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

  const 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 スプレッドシート内で実行される特別なパッケージ化された Apps Script プロジェクトで、 Google スプレッドシートアドオンストアからインストールできます。Google スプレッドシート用のスクリプトを作成して共有する場合は、Apps Script を使用してスクリプトをアドオンとして公開し、他のユーザーがインストールできるようにします。

パフォーマンスとスケーリング

データセットが大きくなると、パフォーマンスの問題が発生する可能性があります。スプレッドシートとスクリプトを最適化するには:

効果的な手法に沿って作成する: サービス呼び出しを最小限に抑え、バッチ処理を使用するためのヒントについては、効果的な手法ガイドをご覧ください。
数式を最適化する: 複雑な数式（VLOOKUP、ARRAYFORMULA、IMPORTRANGE など）が原因でスプレッドシートの動作が遅い場合は、Apps Script を使用してメモリ内でこれらの計算を実行し、結果をバッチで書き込むことを検討してください。
データベースの代替手段を検討する: 非常に大きなデータセット（1,000 万セル近く）や高頻度のデータ入力（接続されたフォームが多いなど）の場合は、JDBC を使用した Google Cloud SQL または BigQueryの使用を検討してください。

トリガー

Google スプレッドシートファイルにバインドされているスクリプトは、シンプルなトリガー（onOpen() 関数や onEdit() 関数など）を使用して、スプレッドシートの編集権限を持つユーザーがスプレッドシートを開いたり編集したりしたときに自動的に応答できます。シンプルなトリガーと同様に、インストール可能なトリガーを使用すると、 Google スプレッドシートで特定のイベントが発生したときに自動的に関数を実行できます。ただし、インストール可能なトリガーはシンプルなトリガーよりも柔軟性が高く、オープン、編集、変更、フォーム送信、時間ドリブン（クロック）のイベントをサポートしています。

Google スプレッドシートを拡張 コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

始める