【GAS】スプレッドシートからセルの値を取得・追加するベストプラクティス

はじめに

GASでスプレッドシートを操作する場合の基本となるセルの値の取得・追加の方法を解説します。

この記事では以下のステップでコードを解説しています。

1. スプレッドシートを取得する

GASでスプレッドシートの操作を行うにはSpreadsheetAppクラスを使用してスプレッドシートオブジェクトを生成します。
スプレッドシートオブジェクトを生成するメソッドは次の5つです。

• SpreadsheetApp.openById(id)
• SpreadsheetApp.openByUrl(url)
• SpreadsheetApp.open(file)
• SpreadsheetApp.getActive()
• SpreadsheetApp.getActiveSpreadsheet()

ファイルの指定方法とそれぞれのメソッドの使い方を見ていきましょう。

1.1. ファイルIDで指定

ファイル作成時に生成されるファイルIDを使用して指定します。
ファイルIDはスプレッドシートのURLから確認することが可能です。
https://docs.google.com/spreadsheets/d/{ファイルID}/

const ss = SpreadsheetApp.openById("{ファイルID}");

1.2. URLで指定

スプレッドシートのURLを入れて指定します。
https://docs.google.com/spreadsheets/d/xxxxxxx/
※ edit 以降はあっても動作しますが、ない方がシンプルでわかりやすいです

const ss = SpreadsheetApp.openByUrl("{ファイルURL}");

1.3. ファイルオブジェクトを渡して指定

DriveAppクラスで取得したファイルオブジェクトを入れて指定します。
主にフォルダ内のファイルを取得した後にスプレッドシートとして操作する場合に用います。

const ss = SpreadsheetApp.open({ファイルオブジェクト});

【使用例】マイドライブ直下のスプレッドシートファイルを取得し、順にスプレッドシートオブジェクト化しています

const files = DriveApp.getRootFolder().searchFiles('mimeType = "application/vnd.google-apps.spreadsheet"');
while (files.hasNext()) {
  const file = files.next();
  const ss = SpreadsheetApp.open(file);
　// ここに処理を記載
}

1.4. GASに紐づくスプレッドシートを指定

GASと紐づいているスプレッドシートを指定します。
GASをスプレッドシートから作成した場合に使用可能な指定方法です。

const ss = SpreadsheetApp.getActive();

const ss = SpreadsheetApp.getActiveSpreadsheet();

※ Object.getActive()とObject.getActiveSpreadsheet()に違いはありません

2. シートを取得する

スプレッドシートオブジェクトに対してSpreadsheetクラスを使用してシートオブジェクトを生成します。
シートオブジェクトを生成するメソッドは次の3つです。

• SpreadsheetObject.getSheetByName(name)
• SpreadsheetObject.getSheets()
• SpreadsheetObject.getActiveSheet()

シートの指定方法とそれぞれのメソッドの使い方を見ていきましょう。

2.1. 名前で指定

シートに設定されている名前を入れて指定します。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");

2.2. 全シートをシートオブジェクト型で取得

スプレッドシート内の全シートをシートオブジェクトとして一次元配列に格納された状態で取得します。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheets = ss.getSheets();

【使用例】配列の中身をそのままシートオブジェクトとして利用可能です。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheets = ss.getSheets();
sheets.forEach(function(sheet){
  // ここに処理を記載
})

2.3. 選択中のシートを指定

現在選択しているシートを指定します。
GASをスプレッドシートから作成した場合かつGASをスプレッドシート上で動かす処理に使用可能な指定方法です。

const ss = SpreadsheetApp.getActive();
const sheet = ss.getActiveSheet();

3. セルを指定する

シートオブジェクトに対してSheetクラスを使用してレンジオブジェクトを生成します。
レンジオブジェクトを生成するメソッドは次の5つです。

• SheetObject.getDataRange()
• SheetObject.getRange(row, column [, numRows, numColumns])
• SheetObject.getRange(セル範囲)
• SheetObject.getActiveRange()
• SheetObject.getCurrentCell()

セルの指定方法とそれぞれのメソッドの使い方を見ていきましょう。

3.1. データがあるセルを指定

A1セルを起点にデータが入っている最下行、最右列までの範囲を指定します。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getDataRange();

3.2. 行位置・列位置・行数・列数で指定

行位置・列位置で起点になるセルを指定し、行数・列数で範囲を指定します。
以下の場合、2行目1列目から3行4列の範囲のため、SheetObject.getRange(2, 1, 3, 4)になります。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置}, {行数}, {列数});

指定するのが1セルであれば行数と列数は不要です。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置});

指定する範囲が1列のみであれば列数は不要です。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置}, {行数});

3.3. セル範囲を指定

“A1” や “B3:C5” 等のセル範囲で指定します。
※ 名前付き範囲やR1C1形式でも指定可能です

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange("{セル範囲}");

3.4. 選択中のセル範囲を指定

現在選択しているセル範囲を指定します。
GASをスプレッドシートから作成した場合かつGASをスプレッドシート上で動かす処理に使用可能な指定方法です。

const ss = SpreadsheetApp.getActive();
const sheet = ss.getActiveSheet();
const range = sheet.getActiveRange();

3.5. 選択中のセルで指定

現在選択しているセルを指定します。
GASをスプレッドシートから作成した場合かつGASをスプレッドシート上で動かす処理に使用可能な指定方法です。

const ss = SpreadsheetApp.getActive();
const sheet = ss.getActiveSheet();
const range = sheet.getCurrentCell();

※ Object.getActiveCell()でも同様の動作をしますが、GoogleからObject.getCurrentCell()の使用が推奨されています

4. セルの値を取得する

レンジオブジェクトに対してRangeクラスを使用して値を取得します。
値を取得するメソッドは次の2つです。

• RangeObject.getValue()
• RangeObject.getValues()

それぞれのメソッドの使い方を見ていきましょう。

4.1. 単一セルの値を取得

レンジオブジェクト内の一番左上のセルの値を取得します。
A1:C3セルをレンジオブジェクトに格納している場合、A1のセルの値を取得します。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置}, {行数}, {列数});
const value = range.getValue();

4.2. 範囲内の全てのセルの値を取得

レンジオブジェクト内の全てのセルの値を取得します。
取得した値は一階層目が行、二階層目が列として二次元配列で格納されます。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置}, {行数}, {列数});
const value = range.getValues();

【使用例】赤枠の値を取得し、C3セルの値を出力しています。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange("A2:D4");
const value = range.getValues();
console.log(value[1][2]);

5. セルに値を書き込む

レンジオブジェクトに対してRangeクラスを使用して値を書き込みます。
値を書き込むメソッドは次の6つです。

• RangeObject.setValue(value)
• RangeObject.setValues(values)
• RangeObject.setFormula(formula)
• RangeObject.setFormulas(formulas)
• RangeObject.setFormulaR1C1(formula)
• RangeObject.setFormulasR1C1(formulas)

値の書き込み方とそれぞれのメソッドの使い方を見ていきましょう。

4.1. 単一の値を書き込む

セルに数値・文字列・ブール値・日付などの値を書き込むためのメソッドです。
複数セルに対して実行した場合、同一の値が書き込まれます。

“=”をつけることで数式を書き込むことも可能ですが、後述のRangeObject.setFormula(formula)が推奨されます。
使用する場合の挙動についてはRangeObject.setFormula(formula)を参照ください。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置}, {行数}, {列数});
range.setValue({値});

4.2. 複数の値を書き込む

セルに数値・文字列・ブール値・日付などの値を書き込むためのメソッドです。
セル範囲と同じ行数・列数の二次元配列である必要があります。

“=”をつけることで数式を書き込むことも可能ですが、後述のRangeObject.setFormulas(formulas)が推奨されます。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置}, {行数}, {列数});
range.setValues({二次元配列});

4.3. 単一の数式を書き込む

セルに数式を書き込むためのメソッドです。
複数セルに対して実行した場合、一番左上のセルを基準に相対参照で書き込まれます。
相対参照にしたくない場合は数式を絶対参照にしてください。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置}, {行数}, {列数});
range.setFormula("{数式}");

数式をR1C1形式で記述する場合は以下のメソッドになります。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置}, {行数}, {列数});
range.setFormulaR1C1("{数式}");

4.4. 複数の数式を書き込む

セルに数式を書き込むためのメソッドです。
セル範囲と同じ行数・列数の二次元配列である必要があります。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置}, {行数}, {列数});
range.setFormulas({二次元配列});

数式をR1C1形式で記述する場合は以下のメソッドになります。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
const range = sheet.getRange({行位置}, {列位置}, {行数}, {列数});
range.setFormulasR1C1({二次元配列});

6. 【番外編】レコードを追加する

シートオブジェクトに対してSheetクラスのSheetObject.appendRow(rowContents)を使用してデータが入っている最下行の下にレコードの追加が行えます。
追加するデータは一次元配列である必要があります。
数値・文字列・ブール値・日付の他、”=”をつけることで数式も追加可能です。

const ss = SpreadsheetApp.openById("{ファイルID}");
const sheet = ss.getSheetByName("{シート名}");
sheet.appendRow({一次元配列});

7. 【番外編】選択中要素の取得について

各クラスに用意されているObject.getActiveXXXX()等のメソッドの対象は「選択中のXXX」です。
そのため、スプレッドシート等のオブジェクトがなくてもSpreadsheetAppクラスで直接取得が可能です。

本記事で紹介したうち、以下の3つのメソッドが対象になります。

• SpreadsheetApp.getActiveSheet()
• SpreadsheetApp.getActiveRange()
• SpreadsheetApp.getCurrentCell()

まとめ

GASでスプレッドシートを操作する場合の基本となるセルの値の取得・追加の方法を解説しました。
スプレッドシートへの入出力は他の機能とも連携しやすく汎用性が高いので、ぜひ活用してみてください。