前々回の記事では、Google Apps Script でプログラミングをして、なにか役に立ちそうなものを作ってみるということにしました。その一例として、高齢の親に誰かしら子供などの親族が付き添ってクリニックへ行くということを想定し、報告を行い情報共有をするシステムを考えました。そして、情報を報告、送信するために、Google フォームを使うことにしました。
また、前回の記事では、Googleフォームの使い方を簡単に説明し、回答者から送信されてきた内容を Google スプレッドシートに記録することができるということを説明しました。そして、通院内容を報告するためのフォーム「通院記録」を回答記録用のスプレッドシート「通院記録」へ紐付けるところまで設定しました。
この記事では、スプレッドシートを操作し情報を取り出すことを行うプログラムを Google Apps Script で書いてみることにします。
目次
Google Apps Script という言語についてもう一度
Google Apps Script は JavaScript の文法に従って Google がウェブ上のサービスとして提供している様々なアプリを操作できるようにしたプログラミング言語でした。
プログラム中では、Google がサービスとして提供している様々なアプリやその中で扱われるパーツなどが擬人化された「モノ」として扱われます。また、それらの「モノ」の間で役割分担がなされ、それぞれの「モノ」には扱うことができるデータや行うことができる仕事が定められています。そして、それぞれに仕事を依頼するという形でプログラムが記述されていきます。
これらの「モノ」は「オブジェクト」と呼ばれることがあります。
Google の公式リファレンスページでは「オブジェクト」の代わりに「クラス」と呼んでいます。
プログラミングと調べもの
さて、いよいよプログラミングをすることになりました。JavaScript や Google Apps Script についての知識が非常に豊富な人であれば、尻込みせずに何も見ないでプログラミングできてしまうかもしれません。しかし、程度は人それぞれですが、たいていどんな人でも「必要に応じて調べる」ということをしながらプログラミングをしていくことになります。
よく行われるのは、次のようなことです。
- Google Apps Script の公式リファレンスページを調べる。
- 検索エンジンを使ってウェブ上の参考になりそうな記事を見つける。
公式リファレンスページでは、Google のアプリ（つまり Google スプレッドシートとか Google ドキュメントとか Gmail など）ごとに対応するオブジェクト（クラス）に分かれてプロパティやメソッドの使い方が説明されています。
検索エンジンでキーワードを「Google Apps Script スプレッドシート 操作」などにして検索してみると、様々な人の書いたウェブ上の記事がたくさん見つかります。
また、最近では、
- 生成 AI に適切な質問をして、コードを書いてもらう。
ということも行われるようになっています。
例えば、生成 AI に「Google スプレッドシートからすべてのセルの内容を取り出して二次元配列に格納する Google Apps Script のコードを書いてください。」と質問してみるのも良いかもしれません。ただし、生成 AI は必ずしも適切な回答をするわけではなく、そのままでは動かず修正の必要なコード、書き方が古いコードなどを答えてくることが普通にあります。また、回答で使われる変数の名前が自分のつけたものと違うということは当然起こります。ですから、回答を自分でチェックすることが必須と言えます。
スプレッドシートを操作する
スプレッドシートのオブジェクトとメソッド
Excel を操作する言語 VBA の場合と同様に、Google スプレッドシートで扱われるものは次のように階層構造を持っていると考えることができます。
- スプレッドシートアプリは一般に複数のスプレッドシートを管理し、スプレッドシートの作成、削除などを行います。つまり、スプレッドシートアプリの下にスプレッドシートファイルが属していると考えることができます。
- スプレッドシートファイルはシートが集まってできています。つまり、スプレッドシートファイルの下にシートが属していると考えることができます。
- シートはセルの集まりですが、ある範囲のセルが集まって長方形の形になったものがレンジです。つまり、シートの下にレンジが属していると考えることができます。
Google Apps Script というプログラミング言語では、このような見方に従ってスプレッドシート用の様々なオブジェクトが用意されています。
根幹となるオブジェクトは次のとおりです。
- SpreadsheetApp オブジェクト：Spreadsheetサービスのトップレベルのオブジェクト（つまりスプレッドシートアプリ自体）
- Spreadsheet オブジェクト：スプレッドシートファイル
- Sheet オブジェクト：スプレッドシートファイル内にある各シート
- Range オブジェクト：各シートの中のあるセル範囲
スプレッドシート ID とシート ID
それぞれのスプレッドシートファイルにはファイルを一意に識別するための ID が与えられています。また、スプレッドシートに含まれるそれぞれのシートにも ID が与えられています。これらの ID は文字、数字、特殊文字を含む文字列で、スプレッドシートの URL を見るとわかるようになっています。
スプレッドシート ID は、スプレッドシートファイル名が変更されても変わりません。また、シート ID は、シート名が変更されても変わりません
スプレッドシートの URL はブラウザでそのスプレッドシートを開いたときにアドレスバーに表示されます。そして、次のような形をしています。
https://docs.google.com/spreadsheets/d/スプレッドシートID/edit?gid=シートID#gid=シートIDここからスプレッドシート ID と シート ID を知ることができます。
d/ と /edit の間にあるものがスプレッドシート ID で、gid=の後ろにあるものがシート ID です。
説明のために使うスプレッドシート例
次の図は、ブラウザで、あるスプレッドシートファイルを開いたところをあらわしています。
以下、このスプレッドシートを例として使い、先に挙げた根幹となるオブジェクトが持ついくつかのメソッドを紹介していくことにします。
この画像のスプレッドシートは「sample_spreadsheet」というファイル名で、３つのシート「product」、「category」、「store」を含んでいます。
シート「product」では、途中に空白の行やセルがありますが 12行目までデータが記録されています。また、途中に空白のセルがありますが D 列までデータが記録されています。
SpreadsheetApp オブジェクトのメソッド
getActiveSpreadsheet メソッド
コンテナバインドスクリプトで使うことができるメソッドです。引数はありません。戻り値として Apps Script が紐付けられている Spreadsheet オブジェクトが取得できます。
const ss = SpreadsheetApp.getActiveSpreadsheet();つまりこのコードでは、SpreadsheetApp オブジェクトに紐付けられているスプレッドシートファイルを Spreadsheet オブジェクトとして取得させ変数 ss に代入しています。
ですから、先の画像の「sample_spreadsheet」というファイル名のスプレッドシートファイルがコンテナとしてこの Apps Script に紐付けられている場合、宣言された変数 ss には「sample_spreadsheet」が Spreadsheet オブジェクトとして代入されます。そして、Apps Script で書くプログラムの中で、変数 ss に対して様々な操作を行うことにより、スプレッドシートファイル「sample_spreadsheet」をプログラムによって操作ができるようになります。
openById メソッド
スプレッドシートファイルの ID を引数に与えると、戻り値としてその id を持つスプレッドシートファイルオブジェクトが取得できます。
const ss = SpreadsheetApp.openById('1aDeGes********rSue5');ここでは変数 ss を宣言し、SpreadshhetApp オブジェクトに id が 1aDeGes********rSue5 のスプレッドシートファイルを Spreadsheet オブジェクトとして取得させ変数 ss に代入しています。
もし先の画像のスプレッドシートファイルの ID が「1aDeGes********rSue5」ならば、スプレッドシートファイル「sample_spreadsheet」が Spreadsheet オブジェクトとして取得され、変数 ss に代入されます。（ID を使って明示的にスプレッドシートを指定しているため、コンテナバインドスクリプトになっていなくても構いません。）そして、Apps Script で書くプログラムの中で、変数 ss に対して様々な操作を行うことにより、スプレッドシートファイル「sample_spreadsheet」をプログラムによって操作ができるようになります。
Spreadsheet オブジェクトのメソッド
SpreadsheetApp オブジェクトの getActiveSpreadsheet メソッドや openById メソッドなどを使ってスプレッドシートファイルに対応している Spreadsheet オブジェクトが得られると、今度は Spreadsheet オブジェクトのメソッドを使ってそのスプレッドシートファイルに含まれる各シートに対応する Sheet オブジェクトを取得することができます。
getSheetByName メソッド
スプレッドシートに含まれているシートの名前を引数に与えると、戻り値としてその名前を持つシートに対応する Sheet オブジェクトが取得できます。
const ss = SpreadsheetApp.openById('1aDeGes********rSue5');
const sheet = ss.getSheetByName('product');２行目では変数 sheet を宣言し、１行目で変数 ss に代入された Spreadsheet オブジェクトに「product」という名前のシートに対応する Sheet オブジェクトを取得させ、変数 sheet に代入しています。
Sheet オブジェクトのメソッド
Sheet オブジェクトが取得されると、今度は Sheet オブジェクトのもつメソッドを使い、シートに関する情報を取得したり、シートに属しているセルやセル範囲に対応するオブジェクトを取得できるようになります。
getLastRow メソッド
引数なしで使うことができます。戻り値としてシートに存在するデータの最後の行番号を取得できます。
const ss = SpreadsheetApp.openById('1aDeGes********rSue5');
const sheet = ss.getSheetByName('product');
const last_row_num = sheet.getLastRow();３行目では、変数 last_row_num を宣言し、２行目で変数 sheet に代入された Sheet オブジェクト（つまり product という名前のシート）にそのシートに存在するデータの最終行番号を取得させ、変数 last_row_num に代入しています。先のスプレッドシートの画像の例では、最終行は 12 行目ですから、変数 last_row_num には 12 が代入されます。
getLastColumn メソッド
引数なしで使うことができます。戻り値としてシートに存在するデータの最後の列番号を取得できます。
const ss = SpreadsheetApp.openById('1aDeGes...rSue5');
const sheet = ss.getSheetByName('product');
const last_col_num = sheet.getLastColumn();３行目では、変数 last_col_num を宣言し、２行目で変数 sheet に代入された Sheet オブジェクト（つまり product という名前のシート）にそのシートに存在するデータの最終列番号を取得させ、変数 last_col_num に代入しています。先のスプレッドシートの画像の例では、最終列は D 列、つまり 4 列目ですから、変数 last_col_num には 4 が代入されます。
getRange メソッド
引数にセルのアドレス、行番号と列番号、行数と列数などを与えると、戻り値としてシートの特定のセルやセル範囲を Range オブジェクトとして取得することができます。
引数の指定の仕方がいくつかあるのでそれぞれ例で説明することにします。
アドレスを指定してセル範囲を取得する場合
「A3」や「A1:E5」のようなセルのアドレスを引数に渡すとそのアドレスにあるセル、またはそのアドレス範囲にあるセルの集まりを Range オブジェクトとして取得できます。
const ss = SpreadsheetApp.openById('1aDeGes********rSue5');
const sheet = ss.getSheetByName('product');
const range = sheet.getRange('B2');３行目では、変数 range を宣言し、シート「product」に対応する Sheet オブジェクトに「セル B2」 を Range オブジェクトとして取得させ変数 range に代入しています。
const ss = SpreadsheetApp.openById('1aDeGes...rSue5');
const sheet = ss.getSheetByName('product');
const range = sheet.getRange('A3:C7');３行目では、変数 range を宣言し、シート「product」に対応する Sheet オブジェクトに「左上をセル A3、右下をセル C7 とする長方形の領域にあるセルの集まり」を Range オブジェクトとして取得させ変数 range に代入しています。
行番号と列番号を指定してセルを取得する場合
引数にセルの行番号と列番号を渡すとその位置にあるセルを Range オブジェクトとして取得できます。
const ss = SpreadsheetApp.openById('1aDeGes********rSue5');
const sheet = ss.getSheetByName('product');
const range = sheet.getRange(4, 3);３行目では、変数 range を宣言し、引数に 4 と 3 を渡しています。また、４行３列の位置にあるセルは C4 です。ここではシート「product」に対応する Sheet オブジェクトに「セル C4」を Range オブジェクトとして取得させ、変数 range に代入しています。
行番号, 列番号、行数、列数を指定しセルを取得する場合
Sheetオブジェクト.getRange(行番号, 列番号, 行数, 列数)「左上のセル位置」を最初の二つの引数で指定し、さらに「左上のセル位置」を基点として、（基点の行、列を含めて）何行分、何列分をセル範囲として取得するかを残りの引数で指定します。
const ss = SpreadsheetApp.openById('1aDeGes********rSue5');
const sheet = ss.getSheetByName('product');
const range = sheet.getRange(2, 4, 3, 5);３行目では、変数 range を宣言し、引数に 2、4、3、5 を渡しています。ここではシート「product」に対応する Sheet オブジェクトに、「左上のセル位置が 2 行 4列 であるセルを基点とし、それを含めて、下に 3 行分、右に ５列分をセル範囲とする長方形の領域にあるセルの集まり」を Range オブジェクトして取得させ、変数 range に代入しています。
Range オブジェクト
Range オブジェクトがもついくつかのメソッドを紹介する前に、配列についての説明をしておきます。
配列についての予備知識
他の多くのプログラミング言語と同じように、JavaScript でも「配列」と呼ばれる型を扱うことができます。
配列は、いくつかの値をカンマで区切り、カッコ [ ] で囲んだものです。[ ]の中には数、文字列など様々な型の値を並べることができます。また、１つの配列の中に、型の違う値が並んでいても構いません。
例えば、次のようなものはどれも配列です。
[12, 7, -3 , 8]['さば缶', 'ツナ缶', 'いわし缶']['2025-1-7', 'さば缶', 3, 260]配列の中に並べられるそれぞれの値を「要素」と呼びます。上の配列では、それぞれ ‘2025-1-7’、 ‘さば缶’、 3、 260 が要素です。（ここでは、最初の２つは文字列、残りの２つは整数です。）
配列の要素が配列であることも許されています。
ですから、例えば、
[['2025-1-7', 'さば缶', 3, 260],
['2025-1-8', 'いわし缶', 2, 200],
['2025-1-9', 'ツナ缶', 6, 620]] のようなものも配列です。（見やすくするために改行を入れてあります。）この配列は二次元配列と呼ばれるものの１つです。この配列では、それぞれ [‘2025-1-7’, ‘さば缶’, 3, 260]、[‘2025-1-8’, ‘いわし缶’, 2, 200]、[‘2025-1-9’, ‘ツナ缶’, 6, 620] が要素になっていて、要素はいずれも配列です。
配列の要素には左から順に 0,1,2,3,…というように番号がつけられていて、index と呼ばれます。
プログラム中で、配列から要素を取り出したいときは index を用いて次のようにします。
配列名[index]次の例を見てみましょう。
const data = ['2025-1-7', 'さば缶', 3, 260];
const product = data[1];１行目では、変数 data を宣言し、配列 [‘2025-1-7’, ‘さば缶’, 3, 260] を代入しています。
２行目では、変数 product を宣言し、配列 data の index が 1 の要素（数えるときは左から順に 0 から数えることに注意しましょう）を取得し変数 product に代入しています。ですから変数 product には文字列 ‘さば缶’ が代入されています。
今度は次の二次元配列の例を見てみましょう。
const data = [['2025-1-7', 'さば缶', 3, 260],
['2025-1-8', 'いわし缶', 2, 200],
['2025-1-9', 'ツナ缶', 6, 620]];
const prod_2 = data[2];
const price_2= data[2][3]:
１行目では、変数 data を宣言し、二次元配列を代入しています。
２行目では、変数 prod_2 を宣言し、配列 data の インデックスが 2 の要素を取得し変数 prod_2 に代入しています。ですから変数 prod_2 には配列 [‘2025-1-9’, ‘ツナ缶’, 6, 620] が代入されています。
３行目では、変数 price_2 を宣言し、配列 data のインデックスが 2 の要素（いまこれは配列になっています）を作ってさらにそこからインデックスが 3 の要素を取得し変数 price_2 に代入しています。data[2] は [‘2025-1-9’, ‘ツナ缶’, 6, 620]であることに注意すると、変数 price_2 には 620 が代入されていることがわかります。
では本題に戻り、Range オブジェクトがもついくつかのメソッドを見てみることにしましょう。
getValue メソッド
引数はありません。戻り値として、Range オブジェクトがあらわしているセル範囲の左上のセルの値を取得することができます。
Rangeオブジェクト.getValue()例えば Range オブジェクトがあらわすシートの範囲が ‘A3:C4’ （つまり、左上が A3、右下が C4 となっている長方形の領域）ならば、戻り値は「A3セルの値」になります。
getValues メソッド
引数はありません。戻り値として、Range オブジェクトがあらわしているセル範囲の値からなる２次元配列を取得することができます。
Rangeオブジェクト.getValues()例えば Range オブジェクトがあらわすシートの範囲が ‘A3:C4’ （つまり、左上が A3、右下が C4 となっている長方形の領域）ならば、戻り値は
[[A3セルの値, B3セルの値, C3セルの値],
[A4セルの値, B4セルの値, C4セルの値]]となります。
この他にもスプレッドシートを操作する多数のオブジェクト及びそれぞれのオブジェクトが持っているプロパティ、メソッドがあります。詳しいことは、Google Apps Script 公式リファレンス Spreadsheet Service を見てください。
スプレッドシートに記録された内容を取り出すコード
次の例を見てください。これは Google フォームから送信されてきた内容を記録したスプレッドシートです。ここでは、通院の情報が３件記録されています。
このシートから、赤枠で囲まれた領域（つまり「通院日」、「医療機関」、「診察内容」、「付添者」、「備考」、「次回予定日」、「次回付添予定者」の情報）に記録されているデータを「すべて」取り出して二次元配列に格納し、戻り値として返す関数をプログラミングしてみることにします。
説明のために、ここでは、このスプレッドシートの ID が「1aDeGes********rSue5」となっていて、このシートの名前が「フォームの回答 1」となっているとします。
では、この記事でこれまで紹介してきたオブジェクトとそれらが持つメソッドを用いてプログラムを作ってみたいと思います。
取得したいセル範囲は、縦方向には２行目から最終行まで、横方向には２列目から８列目までです。つまり、「２行２列」の位置にあるセルを左上とし、それを含めて行数は「最終行-1」、列数は「７」となる領域であることを頭に入れておきましょう。
すると、例えば、次のようなプログラムを書けばよいということになります。ここでは、関数の名前を getAllData としておきました。
function getAllData(){
// SpreadsheetApp オブジェクトの openById メソッドを使い
// id が 1aDeGes********rSue5 の Spreadsheet オブジェクトを取得し、
// 変数 ss に代入
const ss = SpreadsheetApp.openById('1aDeGes********rSue5');
// Spreadsheet オブジェクトの getSheetByName メソッドを使い
// シート名 が 'フォームの回答 1'の Sheetオブジェクトを取得し、
// 変数 sheet に代入
const sheet = ss.getSheetByName('フォームの回答 1');
// Sheetオブジェクトの getLastRow メソッドを使い
// シートに存在するデータの最終行番号を取得し、
// 変数 last_row_num に代入
const last_row = sheet.getLastRow();
// Sheetオブジェクトの getRange メソッドを使い
// シートの 2行2列を左上のセルとし、
// それを含めて行数が last_row-1、列数が7の領域を
// Rangeオブジェクトとして取得し、変数 data_range に代入
const data_range = sheet.getRange(2,2,last_row -1, 7);
// Rangeオブジェクトの getValues メソッドを使い、
// data_ranage に含まれているデータを二次元配列として取得し、
// 変数 all_data に代入
const all_data = data_range.getValues();
//確認用にコンソールに all_data を出力
console.log(all_data);
//戻り値として all_data を呼び出し元へ返す
return all_data;
}この関数 getAllData をGoogle Apps Script のエディタで書き、実行ボタンをクリックすると、このプログラムには 29 行目に確認用にコンソールに all_data を出力するコードが書かれているので次のようなログが出力されます。
このプログラム中の、29 行目の処理 「console.log(all_data); 」によって出力されたログを見ると、変数 all_data にはスプレッドシートのセルに記録されている情報が二次元配列として正しく格納されていることがわかります。
次回はこのようにして取得された二次元配列から、Google ドキュメントを作るプログラムを作ってみようと思います。
