Googleのスプレッドシートの値を使ってGoogle App Script（以下 GAS）で処理を作る時にスプレッドシートのセルの値を取得したいことがありました。その時の備忘録です。
その前に シートを取得する
まず、取得するセルのあるシートをGASに認識させる必要があります。GASの表示のさせ方がわからない場合は以下を参考にしてみてください。
アクティブなシートを指定する
アクティブなシートを取得できます。
let mySheet = SpreadsheetApp.getActiveSheet();シート名を指定する
シート名を指定してシートを取得できます。
let mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート名');アクティブなシートの場合たまに意図しないシートを読み込んでしまうので、シート名を指定する方が確実です。
シートを取得し「mySheet」という変数にシートの情報が入っています。事項からmySheetという変数が登場しますがシート情報のことです。
セルの値を取得する方法
セルの値を取得するにはgetRange()で場所を指定し値を取得します。実際にサンプルコードを見ながらどのように使うか見ていきます。
セルを指定して1つの値を取得する
まずは指定したセルの値を取得する方法です。getRange()で場所を指定した後にgetDisplayValue()で値も合わせて取得しています。
※getRange()で値を取得後あとから「取得した変数.getDisplayValue()」で値だけ取得することも可能
行と縦列を指定して取得する
セルを指定して取得する場合は以下のようになります。
getRange(行,縦列).getDisplayValue();例えばC2のセルの値を取得する場合は以下のようになります。
const data = mySheet.getRange(2,3).getDisplayValue();2行目の3つめの縦列の値を取得しています。
セル番号を指定して取得する
上記の方法は引数はどちらも数値なので少し分かりづらいです。そのままセル番号を指定することも可能です。
getRange("セル番号").getDisplayValue();実際に書くと以下のようになります。
const data = mySheet.getRange("C2").getDisplayValue();サンプルコード
以下のような画像のスプレッドシートがあったとした場合のサンプルコードとその取得結果です。
function myFunction() {
let mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');
const data1 = mySheet.getRange(1,2).getDisplayValue();
console.log(data1); // か
const data2 = mySheet.getRange(5,3).getDisplayValue();
console.log(data2); // そ
const data3 = mySheet.getRange("C2").getDisplayValue();
console.log(data3); // し
}セルの範囲を指定して取得する
A地点からB地点を四角で囲った範囲を指定する方法です。getRange()で場所の範囲を指定した後にgetValues()で範囲の値を取得します。複数の値が戻り値となるため配列の値が返されます。
行, 列, 行数を数値で指定して取得する
いくつか取得方法があるのでまずは行, 縦列, 行数からです。
getRange(行, 縦列, 行数).getValues();実際に書くと以下のようになります。
const range = mySheet.getRange(1,2,5).getValues();1行目の２列目から5行分取得となります。
行, 列, 行数, 縦列数を数値で指定して取得する
縦列数も増えてより多くの範囲を取得できます。
mySheet.getRange(行, 縦列, 行数, 縦列数).getValues();実際に書くと以下のようになります。
const range = mySheet.getRange(3,2,5,2).getValues();行数ごとに値が配列化されます。
セル番号で範囲を指定して取得する
セル番号で指定するのが一番分かりやすい気がしますね。
mySheet.getRange("セル番号1:セル番号2").getValues();実際に書くと以下のようになります。
const range3 = mySheet.getRange("A1:C5").getValues();こちらも行数ごとに値が配列化されます。
サンプルコード
function myFunction() {
let mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');
const range1 = mySheet.getRange(1,2,5);
console.log(range1.getValues()); // [ [ 'か' ], [ 'き' ], [ 'く' ], [ 'け' ], [ 'こ' ] ]
const range2 = mySheet.getRange(3,2,2,2);
console.log(range2.getValues()); // [ [ 'く', 'す' ], [ 'け', 'せ' ] ]
const range3 = mySheet.getRange("A1:C5");
console.log(range3.getValues()); // [ [ 'あ', 'か', 'さ' ],[ 'い', 'き', 'し' ],[ 'う', 'く', 'す' ],[ 'え', 'け', 'せ' ],[ 'お', 'こ', 'そ' ] ]
}入力のある最後の行と列までまでまとめて取得する
入力されている箇所を全て取得する方法です。値を指定しないので、スプレッドシートの値に変更があってもコードを修正せずに取得できます。
getRange(行数, 縦列, mySheet.getLastRow() - 1, mySheet.getLastColumn()).getValues()行数と桁数で起点となる位置を決めたら、あとは下と右方向に入力されている値があれば取得してくれます。
サンプルコード
function myFunction() {
let mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');
const range = mySheet.getRange(2, 1, mySheet.getLastRow() - 1, mySheet.getLastColumn())
const data = range.getValues()
console.log(data);//[ [ 'い', 'き', 'し' ], [ 'う', 'く', 'す' ], [ 'え', 'け', 'せ' ], [ 'お', 'こ', 'そ' ] ]
}入力のある縦列だけ取得する
縦列に入力されている値を最後まで取得します。最後の行数を指定しないので可変にも耐えられます（コードを修正しなくていい）。上記の例では「あ、い、う、え、お」だけを取得する方法です。
function myFunction() {
let mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');
const val = mySheet.getRange("A:A").getValues(); // A列の全ての行を取得
const numberOfValues = val.filter(String).length; // 空以外の配列の数を数える
const data = mySheet.getRange(1,1,numberOfValues).getValues();
console.log(data);//[ [ 'あ','い','う','え','お']
}値が入っているセルと空のセルが飛び飛びになっている場合は、ループとif文で取得する方がいいのかもしれません。
おわり
セル番号で指定した方が分かりやすいと思うのですが、いろいろ調べていると数値で指定する方法を多く見かけました。なにか理由があるのだろうか。
取得する順番が行ごとのようなので、セルにどの順番で書き込むか事前に考えた方が良さそうですね。
他にもセルを取得する方法があるようですが、使い道に合わせて今後まとめていければと思います。
