表計算ソフトにおける特定の作業を自動化するには、ExcelではVBAと呼ばれるプログラミング言語を用います。一方、Googleスプレッドシートでは、GAS（Google Apps Script）と連携することで自動化が可能になります。Excelと違い、トリガーにイベントと日時を指定できたり、Web上から制限なくデータを収集できたりと、さまざまな恩恵を受けられるのが特徴です。
では、GoogleスプレッドシートとGASを連携すると、具体的にどのようなことができるのでしょうか。本記事では、GoogleスプレッドシートにおけるGASの使い方や活用方法を解説します。Googleスプレッドシートの作業を自動化・効率化させたいと考えている方は、ぜひ参考にしてください。
そもそもGAS（Google Apps Script）とは
GAS（Google Apps Script）とは、Google社が提供するローコード開発ツールです。JavaScriptをベースに簡易的なプログラミングコードを入力するだけで、特定の処理を実行できるため、一般的な開発ツールに比べて工数を抑えられるほか、コーディングの知識がない人でも容易に扱えるメリットがあります。また、クラウド上で動作するため、ハードウェアやミドルウェアといった開発環境を用意せずに済むのも利点です。
実際にGASを利用する際は、コードを記述し、実行ボタンを押すことで任意の作業が実行されますが、トリガーを設定して自動的に処理を行うことも可能です。トリガーには、「イベントが起動したとき」や「指定した時間が訪れたとき」といった条件を設定でき、それを起点に処理が自動的に実行されます。例えば、定型的な作業を行う際、トリガーを使って作業の予約を設定しておけるため、コーディングの省力化や工数削減につながります。
GASでできること
GASでは、多種多様なGoogleサービスと連携し、そのサービス上で特定の処理を自動的に実行できるのが特徴です。具体的には、次のような形でGASを活用できます。
- Googleスプレッドシートに記載された内容をGmailで指定の宛先へメール送信する
- Googleスプレッドシートで集計したデータを毎朝Googleチャットに送信する
- Web上から収集した情報をGoogleスプレッドシート上にまとめる
- Googleドライブのファイルが更新されたタイミングで通知を送る
- Googleフォームから問い合わせが入った段階でGoogleカレンダーに予定を登録する
このように、GASはGoogle社が提供するツールだけあって、各種Googleサービスと互換性が高い特徴があります。上記のような処理を行う場合でも、難解かつ複雑なプログラミングコードを記述する必要がなく、簡易的なコーディングとトリガーの設定だけで作業が完結します。
また、APIを使って外部サービスと連携できるのもポイントです。例えば、GoogleスプレッドシートのデータやGoogleフォームの回答結果を、ビジネスチャットツールのSlackに送信したり、Googleスプレッドシートのマスタデータを、クラウド会計ソフトのfreeeに反映させたりと、さまざまな活用が可能です。
GoogleスプレッドシートにおけるGASの基本的な使い方
GoogleスプレッドシートとGASを連携することで、スプレッドシート内での処理を自動的に実行できるようになります。基本的な使い方としては、次のような方法があげられます。
- 特定のセルのデータを取得する
- GASからスプレッドシートにデータを書き込む
- 複数のセルへ一度に入力する
- スプレッドシートの行や列を削除する
- データを並び替える
- データをフィルタリングする
- トリガーで定期的に処理を実行させる
それぞれ画像を用いて詳細な使い方を解説します。
1. 特定のセルのデータを取得する
GoogleスプレッドシートにおけるGASの最も基本的な使い方としては、特定のセルのデータを取得することがあげられます。専用のコードを記述して実行することで、指定したセルからデータを取得したうえで、GASのログにそのデータを表示できるという仕組みです。
準備をするために、まず新規のスプレッドシートを立ち上げましょう。スプレッドシートのタイトルは任意で設定して構いません。そして、今回はテストとして、A1セルに「確認用」というテキストを入力します。
この状態でメニューバーから［拡張機能 > Apps Script］の順にクリックします。
すると、GASのエディタ画面が開くので、画面上部のタイトル（無題のプロジェクトと記載されている箇所）を任意のものに変更しましょう。そして、コード編集部分に以下のコードを記述します。
【コピー用コード】
| function getData() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var data = sheet.getRange('A1').getValue(); Logger.log(data); } |
上記のコード内の「function」とは、コンピュータが特定の処理を実行するための関数です。今回は「function」の後に「getData」と続けることで、「特定のデータを取得する」という命令をコンピュータに与えたことを意味します。ほかにも、この「function」と特定のキーワードを組み合わせることで、さまざまな処理を自動的に実行できます。
続けて、メニューバーの［ドライブにプロジェクトを保存］をクリックすると、自身のGoogleドライブにコードの情報が保存されるとともに、その隣の［実行］ボタンがアクティブになります。この［実行］ボタンをクリックしましょう。
初回のみGASのコードを実行するには承認が必要です。［権限を確認］をクリックするとGoogleアカウントのログイン画面が開くので、画面の案内に沿って承認を行います。
承認手続きが完了するとコードが実行されます。その後、コード編集部分の下部に自動的に［実行ログ］の画面が現れます。そのなかに先ほどA1セルに入力した「確認用」というテキストが反映されていれば、スプレッドシートから当該情報を取得できたことを表します。
2. GASからスプレッドシートにデータを書き込む
先ほどは、スプレッドシートに入力したデータをGAS上で表示しましたが、その反対にGASに入力した内容をスプレッドシート上に反映させることも可能です。その場合は、「function writeData」の関数を使用します。
今回は、スプレッドシートのA1セルにGASからデータを反映させるため、A1セルを空白の状態にしておいてください。そして、GASのコード編集部分に以下のコードを記述します。「setValue」の括弧内のテキストがスプレッドシートに反映されるため、今回は「GASからデータを反映」と入力しています。
【コピー用コード】
| function writeData() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.getRange('A1').setValue('GASからデータを反映'); } |
続けて、［実行］をクリックします。
すると、実行ログに［実行開始］と［実行完了］のログが表示されます。その状態でスプレッドシートを表示すると、A1セルに「GASからデータを反映」というテキストが自動的に入力されていることがわかります。
3. 複数のセルへ一度に入力する
「function rangeData」の関数を使用すると、GASに入力したデータをスプレッドシートの複数のセルへ一度に反映させることが可能です。行や列を指定して任意のテキストを入力できるため、表を作成することもできます。
今回は、スプレッドシートのA1～B3セルにGASからデータを反映させるため、A1～B3セルを空白の状態にしておいてください。そして、GASのコード編集部分に以下のコードを記述します。
【コピー用コード】
| function rangeData() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange('A1:B3'); // var values = [['氏名', '年齢'], ['鈴木太一', 28], ['田中早苗', 21]]; range.setValues(values); } |
コード内の「var values」の項目が、実際にスプレッドシートに反映される情報です。最初の[ ]で括られた部分がA1～B1セルを表しており、その後にA2～B2セル、A3～B3セルといった形で続きます。[ ]内に入力するテキストが数字以外の場合は、「’」でそのテキストを囲ってください。
続けて、［実行］をクリックします。
すると、実行ログに［実行開始］と［実行完了］のログが表示されます。その状態でスプレッドシートを表示すると、A1～B3セルに指定したテキストが自動で入力されていることがわかります。
4. スプレッドシートの行や列を削除する
スプレッドシート内の不要な行や列は、GASのコードで自動的に削除することが可能です。行を削除する場合は「function deleteRow」を、列を削除する場合は「function deleteColumn」の関数を使用します。
それでは、先ほど入力したA1～B3セルのうち、2行目のみを削除してみましょう。GASのエディタを開いて、コード編集部分に以下のコードを記述します。今回は2行目を削除するので、「sheet.deleteRow」の( )内に「2」と入力します。
【コピー用コード】
| function deleteRow() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.deleteRow(2); } |
そして、［実行］ボタンをクリックします。
その後、実行ログに［実行完了］が表示されると作業は完了です。スプレッドシートを見ると、2行目が削除されていることがわかります。また、行を削除すると同時に空白の行を自動的に穴埋めしてくれています。
5. データを並び替える
スプレッドシートで表を作成した際、そのなかに数値が含まれている場合、GASを使ってデータを並び替えることが可能です。そこで、まずはスプレッドシートに数値を含むデータを入力しましょう。
今回は、バラバラに入力された年齢のデータを昇順で並び替えてみます。GASのエディタを開いて、コード編集部分に以下のコードを記述します。
【コピー用コード】
| function sortData() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange('A2:B7'); range.sort({column: 2, ascending: true}); } |
並び替える範囲を指定するには、「sheet.getRange」の( )内にセルの範囲を入力します。そして、今回は年齢が記載されている列（左から2列目）の数値を基準に並び替えるため、「column:」の箇所を「2」で指定します。この状態で［実行］ボタンをクリックすると、以下のように年齢が低い順にデータが並び変わります。
なお、降順で並び替える場合は、コード内の「ascending」の箇所を「descending」に変更します。
6. データをフィルタリングする
GASの「function filterData」の関数を使用することで、スプレッドシート内のデータをフィルタリング（特定の条件に該当するデータのみに絞り込み）できます。
例えば、今回の場合は、「年齢が30歳以上」の条件を指定し、そのデータのみを表示させるといったことが可能です。この条件を指定するなら、GASに以下のコードを記述します。
【コピー用コード】
| function filterData() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange('A1:B10'); var existingFilter = sheet.getFilter(); if (existingFilter) { existingFilter.remove(); } var filter = range.createFilter(); filter.setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria() .whenNumberGreaterThanOrEqualTo(30) .build() ); } |
このコードのうち、「var existingFilter」と「if」を使って、既存のフィルタが存在していれば削除するように指示しています。そのうえで、「var filter」を記述することで新規フィルターを作成するように設定しています。フィルタリングの条件としては、「filter.setColumnFilterCriteria」でスプレッドシートの2列目を、「whenNumberGreaterThanOrEqualTo」で「30歳以上」という数値を指定する仕組みです。
上記のコードを実行しスプレッドシートを確認すると、以下のように、30歳以上のデータのみが画面上に表示されていることがわかります。
7. トリガーで定期的に処理を実行させる
ここまでに紹介した内容では、GASに一つひとつのコードを記述して処理を実行させていましたが、トリガーを設定することで、特定の条件に従って自動的に処理を行うことも可能になります。例えば、1時間おきに同じ処理を繰り返したり、スプレッドシートに何らかの変更を加えた時点で処理を実行させたりと、さまざまな条件を設定できます。
トリガーを設定するには、GASの左側のメニューから［トリガー］をクリックします。
すると、トリガーの一覧画面が表示されるので、画面右下の［トリガーを追加］をクリックしましょう。
新規作成するトリガーの条件を設定します。
ここで重要となるのが、［イベントのソースを選択］の項目です。仮に［イベントのソースを選択］で［時間手動型］を選択し、時間間隔を［1時間おき］に設定した場合、1時間経過するたびに［実行する関数を選択］で選択した処理（関数）が自動的に実行されます。
イベントのソースは時間手動型以外にも、GoogleスプレッドシートやGoogleカレンダーの特定のアクションを起点にすることもできます。また、［エラー通知設定］を有効にすると、エラーが発生したタイミングで通知を受け取れるようになります。
設定が完了すれば、画面右下の［保存］をクリックしましょう。
【応用編】スプレッドシートにおけるGASの便利な使い方
特定の処理を自動的に実行させるGASの仕組みを活用すると、次のようなことも可能になります。
- オリジナルのメニューを追加する
- ボタンからGASの処理を実行する
- スプレッドシート用の検索ボックスを作成する
- メールを一斉送信する
さまざまな活用方法を押さえることで、Googleスプレッドシートでの作業をより効率化することが可能です。それぞれの設定方法や手順を解説します。
オリジナルのメニューを追加する
「ss.addMenu」の関数を使用することで、スプレッドシート上にGASを起動するためのオリジナルメニューを追加できます。そのメニューをクリックすると、GASを開いていない状態でも特定の処理を実行させることが可能です。
オリジナルメニューを追加するには、GASのコード編集部分に以下のコードを記述します。
【コピー用コード】
| function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = []; menuEntries.push({ name: "メニュー1", functionName: "menuOne" }); menuEntries.push(null); // line separator menuEntries.push({ name: "メニュー2", functionName: "menuTwo" }); ss.addMenu("オリジナルメニュー", menuEntries); } function menuOne(){ Browser.msgBox('メニュー1を実行'); } function menuTwo(){ Browser.msgBox('メニュー2を実行'); } |
「ss.addMenu」の( )内にメニューの名称を任意で記述します（今回は「オリジナルメニュー」と記載）。その下の「function menu」が、メニューをクリックして特定の処理を実行させる関数です。今回は、「メニュー1を実行」というメッセージボックスが開くという処理を指定しています。また、「menuOne」や「menuTwo」といった形でサブメニューを増やすことも可能です。
上記のコードを実行すると、スプレッドシートのメニューバーに「オリジナルメニュー」の項目が現れます。
そのメニューをクリックすると、「メニュー1を実行」というメッセージボックスが処理が実行されました。
なお、「Browser.msgBox」を別の関数に変更すると、メニューをクリックするだけでそのアクションを実行できるようになります。
ボタンからGASの処理を実行する
スプレッドシートにボタンを配置してGASと連携すると、そのボタンをクリックするだけでGASの一連の処理を実行できます。この設定を行うには、まずスプレッドシートの図形描画の機能を使ってボタンを配置します。
編集ツールを用いて任意のボタンを作成します。
GASのコード編集部分に処理したいコードを記述します。今回は、先ほど紹介した「A1セルに任意のデータを反映させる」の処理を設定しています。また、コードを記述した後はGoogleドライブに保存することも忘れないでください。
そして、作成したボタンを選択します。ボタンの右上に三点リーダが表示されるので、それをクリックした後に［スクリプトを割り当て］を選択しましょう。
続けて、割り当てるスクリプトを入力します。今回は、「A1セルに任意のデータを反映させる」という処理を実行させるため、枠内に「writeData」と入力します。完了すれば［確定］をクリックしましょう。
上記の設定が完了すれば、ボタンを1回クリックします。すると、GASに記述したコードが読み取られ、スプレッドシート上にその内容が反映されます。
今回は「function writeData」の関数を使用しましたが、ほかの関数を使用することも可能です。
スプレッドシート用の検索ボックスを作成する
GASを活用すると、スプレッドシート上に便利な検索機能を追加できます。設定を行うには、まずスプレッドシートに任意の表と検索ボックスの項目を作成します。今回は以下のような表と検索ボックスを作成しています。
そして、GASのコード編集部分に以下のコードを記述します。
【コピー用コード】
| function onEdit(e) { const sheet = e.source.getActiveSheet(); const searchBoxCell = 'B10'; // 検索ボックスのセル位置 const resultsStartCell = 'A11'; // 結果を表示する開始セル位置 // 編集されたセルが検索ボックスでない場合は何もしない if (e.range.getA1Notation() !== searchBoxCell) return; const searchTerm = e.value; if (!searchTerm) return; // 検索ボックスが空の場合は何もしない const dataRange = sheet.getDataRange(); const values = dataRange.getValues(); const searchResults = []; // シート全体を検索し、一致するセルの行を結果に追加 for (let row = 0; row < values.length; row++) { for (let col = 0; col < values[row].length; col++) { if (values[row][col].toString().includes(searchTerm)) { searchResults.push(values[row]); break; // 行全体を追加したら、次の行へ } } } // 結果の表示エリアをクリア const resultsStartRange = sheet.getRange(resultsStartCell); sheet.getRange(resultsStartRange.getRow(), resultsStartRange.getColumn(), sheet.getMaxRows(), sheet.getMaxColumns()).clearContent(); // 検索結果をシートに書き込み if (searchResults.length > 0) { sheet.getRange(resultsStartRange.getRow(), resultsStartRange.getColumn(), searchResults.length, searchResults[0].length).setValues(searchResults); } } |
加えて、今回はトリガーを設定します。GASの左側のメニューから［トリガー］をクリックし、トリガーの一覧画面の右下にある［トリガーを追加］をクリックしてください。そして、以下のような形でトリガーを設定します。
設定後に［保存］をクリックし、その状態で先ほど記述したコードもGoogleドライブに保存しておきます。続けて、スプレッドシートに移動し、検索ボックスの隣に検索したい情報を入力します。
すると、その下に検索結果が表示されます。
メールを一斉送信する
GASではほかにも、スプレッドシート上に入力されたアドレス帳をもとに、複数のメールアドレスに向けて一斉にメールを送信することも可能です。設定を行うには、まずスプレッドシートにアドレス帳を作成しましょう。
そして、GASのコード編集部分に以下のコードを記述します。
【コピー用コード】
| function main() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(); const firstRow = 2; //データの開始行 const mailCol = 2; //メールアドレスの列 var rowLength = sheet.getLastRow() - (firstRow - 1); var mailaddressList = []; // メールアドレスリスト取得 for(let i=0; i < rowLength; i++){ let address = sheet.getRange(firstRow + i, mailCol).getValue(); mailaddressList.push(address); } // 宛先ごとにメール作成、送信 mailaddressList.forEach(address => { // メールアドレスがある場合、実行 if(address != ''){ sendMailToAll(address); } }); } function sendMailToAll(address) { const subject = '社内連絡'; // メールの件名 const body = ` 従業員の皆さま 総務部より社内連絡です。 ～～～～～～～～ ～～～～～～～～ `; GmailApp.sendEmail(address, subject, body); } |
上記のコードを実行すると、自動的にGmailが起動して、指定した複数のメールアドレスにメールを一斉送信してくれます。
GASとGoogleスプレッドシートを連携して作業効率を高めよう
GASは、簡易的なコードを記述するだけで一連の処理を実行できる便利なツールです。今回紹介したように、Googleスプレッドシートと連携することで、データの入力や抽出、検索、メール送信など、さまざまな処理を実行できることがわかりました。
また、トリガーの機能を利用すれば、記述したコードを自動的に実行させることも可能です。このような機能を最大限に活用することで、Googleスプレッドシートを利用する際の効率性を高められます。使用する関数次第で幅広い活用が可能なので、さっそくGASを連携させてみてはいかがでしょうか。
電算システムでは、環境構築やコンサルティングなど、Googleサービスの導入支援サービスを提供しています。GoogleスプレッドシートやGmailといった個別のサービスはもちろん、Google Workspaceのサポートにも対応しています。専門領域に精通した数多くのエンジニアが在籍しているので、スピーディかつ質の高いサポートを行えるのが強みです。「Googleサービスを活用したいが具体的なイメージが湧かない」といったお悩みを抱える方は、ぜひ電算システムへと気軽にお問い合わせください。
