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

Google Apps Script を使用してスプレッドシートを拡張します。スプレッドシートにカスタムメニュー、ダイアログ、サイドバーを追加します。スプレッドシート用のカスタム関数を作成し、Google カレンダー、Google ドライブ、Gmail などの他の Google サービスと統合します。

Google スプレッドシート用に設計されたほとんどのスクリプトでは、配列を操作してスプレッドシート内のセル、行、列を操作することができます。JavaScript の配列に慣れていない場合は、Codecademy の配列に関する優れたトレーニングモジュールをご覧ください。このコースは Google が開発したものではなく、Google とは関連がありません。

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

始める

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

スプレッドシートサービスは、シートをグリッドとして扱い、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']);
}

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

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

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

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

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

Google フォームに接続する

フォームサービスとスプレッドシートサービスを使用して、Google フォームをスプレッドシートに接続します。この機能では、スプレッドシートのデータに基づいて Google フォームが自動的に作成されます。Apps Script では、トリガー（onFormSubmit など）を使用して、ユーザーがフォームに回答した後に特定のアクションを実行することもできます。スプレッドシートとフォームの接続について詳しくは、フォームの回答を管理する 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');
}

データの検証

スプレッドシートで既存のデータ入力規則にアクセスするか、新しい規則を作成します。たとえば、次のサンプルは、セルで 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 分間のクイックスタートをお試しいただくか、カスタム関数の詳細なガイドをご覧ください。

マクロ

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

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

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

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

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

ベストプラクティスに従う: ベストプラクティスガイドで、サービス呼び出しを最小限に抑え、バッチオペレーションを使用するためのヒントを確認します。
数式を最適化する: 複雑な数式（VLOOKUP、ARRAYFORMULA、IMPORTRANGE など）が原因でスプレッドシートの動作が遅い場合は、Apps Script を使用してメモリ内でこれらの計算を実行し、結果を一括で書き戻すことを検討してください。
データベースの代替案を検討する: 非常に大きなデータセット（1,000 万セルに近い）や高頻度のデータ入力（多くの接続されたフォームなど）の場合は、JDBC を使用した Google Cloud SQL または BigQuery の使用を検討してください。

トリガー

スプレッドシートファイルにバインドされたスクリプトは、onOpen() や onEdit() などの関数のようなシンプルなトリガーを使用して、スプレッドシートに対する編集権限を持つユーザーがスプレッドシートを開いたり編集したりしたときに自動的に応答できます。シンプルなトリガーと同様に、インストール可能なトリガーを使用すると、特定のイベントが発生したときに Sheets が関数を自動的に実行できます。ただし、インストール可能なトリガーは、シンプルなトリガーよりも柔軟性が高く、open、edit、change、form submit、time-driven（clock）のイベントをサポートしています。

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

始める