【GAS】スプレッドシートに範囲指定で書き込む・最終行に書き込む（setValue / setValues / appendRow）

2024年4月7日GASappendrow,getLastRow,push,setValues,スプレッドシート

この記事では、Gooogle Apps Script（GAS）でGoogle スプレッドシートにデータを書き込む方法を解説します。

1. GASでスプレッドシートにデータを書き込む方法
2. スプレッドシートの範囲を指定してデータを書き込む（setValue / setValues）
3. スプレッドシート最終行に書き込む3つの方法
4. まとめ

GASでスプレッドシートにデータを書き込む方法

スプレッドシートにデータを書き込む方法は、setValueメソッド、setValuesメソッドを使ってシートの範囲を指定してデータを書き込む方法、またappendRowメソッドを使ってデータ範囲の最終行に書き込む方法があります。

まずは、シンプルに「スプレッドシートの範囲を指定して書き込む方法」をご紹介し、GASプログラムでは頻繁に利用する「スプレッドシートの最終行にデータを書き込む方法」をパートを分けて解説します。

スプレッドシートの範囲を指定してデータを書き込む（setValue / setValues）

「setValueメソッド / setValuesメソッド」を使ってスプレッドシートに範囲を指定してデータを書き込むことができます。具体的には下記のような手順を進めます。

書き込み先のスプレッドシート／シートを取得
書き込み先の範囲を取得
「setValueメソッド」または「setValuesメソッド」を使って値／配列を書き込む
・setValueメソッド：一つのセルに値を書き込む
・setValuesメソッド：配列を使って複数のセルに値を書き込む

setValueメソッドの基本構文

Range.setValue(value)

setValueメソッドは単一の値を一つのセルに書き込むメソッドです。空のシートに値を書き込んでみます。

  let ss = SpreadsheetApp.getActiveSpreadsheet(); //書き込み先のスプレッドシートファイル
  let sheet = ss.getActiveSheet(); //書き込み先のシート

  let range_1 = sheet.getRange('A1'); //書き込み先の範囲
  range_1.setValue('テスト'); // 引数の値をセルに入力

  let range_2 = sheet.getRange('C3');  //書き込み先の範囲
  range_2.setValue('テスト');　// 引数の値をセルに入力

setValuesメソッド基本構文

Range.setValues(array)

setValuesメソッドは配列を引数として複数のセルに値を書き込むメソッドです。同様に空のシートに5行×4列の配列を書き込んでみます。

  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('テスト');
  let range = sheet.getRange('A1:D5');
  
  let sales = 	[ 
  [ '商品', '価格', '個数', '売上' ],
  [ 'ハンバーグ', 1000, 12, 12000 ],
  [ 'スープ', 500, 18, 9000 ],
  [ 'カレー', 1500, 8, 12000 ],
  [ 'シチュー', 800, 18, 14400 ] ]

  range.setValues(sales);

setValues呼び出し時の範囲指定の注意点

setValuesメソッドは、引数の配列とRangeオブジェクトの指定範囲の行×列数が一致していなければなりません。上記の場合、配列salesには「5行×4列」のデータが入っているので、範囲指定では行×列数が一致している「A1:D5」を指定しました（※範囲があっていれば良いので「B2:E6」などでも大丈夫です）。

例えば、同じ配列で範囲指定を「A1:C5」にすると、行×列数の不一致で下記のようなエラーになります。

  let range = sheet.getRange('A1:C6');

Exception: The number of rows in the data does not match the number of rows in the range. The data has 5 but the range has 6.

setValueとsetValuesの使い分け

setValuesを使わなくても、setValueで書き込み位置のセルを指定して逐次実行すれば、setValuesで実施したい書き込みは実現できます。しかし、書き込み回数が多くなればなるほど、範囲指定の条件記載が複雑になるのに加え、スプレッドシートの呼び出し回数が増えることでプログラムの処理時間が長くなってしまいます。

複数セルに入力する場合は、特段の理由がなければ複数範囲を指定し書き込めるsetValuesメソッドを使う方が良いでしょう。

以下では、実際にプログラムを組む際に範囲指定でよく使う便利な方法をご紹介します。

getLastRowメソッドと配列の要素数を利用した範囲指定

開始位置：Sheetオブジェクト.getLastRange() + 1
書き込む行数：Array.length
書き込む列数：Array[i].length

行や列の先頭ではなく途中から書き込みを行う際、いちいちスプレッドシートを見に行って書き込み範囲を確認するのは面倒です。その時はデータ最終行（getLastRowメソッドを使用）と書き込むデータの行列数を取得して範囲指定に利用すると楽に記述ができます。

前述で使った売上のシートに追加の売上データ（2行分）を書き込むことを想定します。

この時、書き込み想定範囲である「A6:D7」は下記のように表現できます。

これをスクリプトに落とすと、getRange('A6:D7’).setValues(array)という記述の代わりに下記の表現になります。

 let addSales = 	[ 
  [ 'スパゲッティ', 1000, 4, 4000 ],
  [ 'オムライス', 1200, 5, 6000 ] ]

  let startRow = sheet.getLastRow()+1;
  let row = addSales.length;
  let col = addSales[0].length;

  let addRange = sheet.getRange(startRow, 1, row, col);
  addRange.setValues(addSales);

一見記述が長くなっているように見えますが、スプレッドシートを確認しなくても位置と範囲を指定できるメリットはかなり大きく、特にデータ量が多くなって行数をクイックに確認するのが難しい場面で力を発揮します。ぜひ活用してみてください。

続いてスプレッドシートの最終行にデータを書き込む方法をご紹介します。

スプレッドシート最終行に書き込む3つの方法

Google スプレッドシート（Spreadsheet）の最終行に書き込む方法は大きく分けて、「スプレッドシート最終行の直接書き込む方法」と「一度データを読み込んでからスプレッドシートに書き込む方法」があります。

appendRowメソッドでスプレッドシート最終行に書き込む
lastRowメソッドで最終行番号を指定してデータを書き込む
データを配列に格納し、pushメソッドで最終要素にデータを挿入してからデータを書き込む

下記の各店舗の売上データに、最終行に別店舗データを挿入するシーンを想定してそれぞれの処理を比較してみます。

appendRowメソッドでスプレッドシート最終行に書き込む

Sheet.appendRow(rowContents)

rowContents：シート最終行へ挿入する一次元配列

まずは、もっとも簡単な処理方法です。

スプレッドシートに対して行レベルの書き込み処理を実行するappendRowメソッドで、引数の一次元データを、シート最終行の次の行のデータとして追加できます。

一回一回スプレッドシート上のデータを呼び出ししなくても「最終行の次の行にデータを書き込む」という処理を行ってくれるため、非常にシンプルな記述で完結させることができます。

デメリットは、1次元データずつしか追加ができないため、複数行追加をしたかったらその行数分処理を実行しければならない点です。（2次元データをappendRowメソッドで追加しようとすると、うまく反映されません）

//サンプルコード

  const sheet = SpreadsheetApp.getActiveSheet();

  // 追加用のデータ
  const  shopE =  [ 'E', 120394, 5 ] ;
  const  shopF =  [ 'F', 543011, 24 ] ;

  //最終行にデータを追加
  sheet.appendRow(shopE);
  sheet.appendRow(shopF);

SpreadsheetAppクラスを呼び出して、appendRowメソッドを使うだけなので非常に簡単ですね。appendrowメソッドについては下記の記事で詳しく解説しています。

【GAS】appendRowメソッドの使い方｜スプレッドシートの最終行に書き込む

lastRowメソッドで最終行番号を指定して書き込む

Sheet.getLastRow()

スプレッドシートのデータが入っている最終行を取得して、次の行にデータを追加する方法です。getRangeメソッドで範囲指定を行い、setValuesで追加したいデータを配置します。

1行だけの追加ならappendRowが圧倒的に楽ですが、2行以上のデータを追加したい場合にはこちらの方法が便利です。

function myFunction() {

  const sheet = SpreadsheetApp.getActiveSheet();
 // 最終行を取得
  const lastRow = sheet.getLastRow();

  // 追加用の一次元データ
  const  shopE =  [ 'E', 120394, 5 ] ;
  const  shopF =  [ 'F', 543011, 24 ] ;

  sheet.getRange(lastRow+1,1,2,3).setValues([shopE,shopF]);

}

pushメソッドで最終要素にデータを挿入してから書き込む

Array.push(val1 [, val2,… , valN)

valN：配列に追加したい要素、配列の末尾に追加される
戻り値：要素が追加された新しい配列の要素数、元の配列は追加要素が加わった新しい配列に変更される

pushメソッドを使い、スプレッドシートデータを読み込んだ変数の最終要素として追加することができます。

function myFunction() {

  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getDataRange().getValues();

  // 追加用の一次元データ
  const  shopE =  [ 'E', 120394, 5 ] ;
  const  shopF =  [ 'F', 543011, 24 ] ;

  //変数の最終要素にデータに追加
  values.push(shopE);
  values.push(shopF);

  //スプレッドシートに書き出し
    //データ範囲の最終行と最終列を求める
    const column = sheet.getDataRange().getLastColumn();
    const row = values.length;

    //書き出し
    sheet.getRange(1, 1, row, column).setValues(values);

}