【GAS】スプレッドシートにデータを書きこむ基本

2022 9/09

2020年8月4日 2022年9月9日

今回の記事では、GASを使ってスプレッドシートにデータを書きこむやり方について解説します。

GASでスプレッドシートのセルに書きこむ基本構文

GASでスプレッドシートに出力するにはsetValue関数を使用します。
まずはコードを先に見てみましょう。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  sheet.getRange("A1").setValue("リンゴ")
}

getRange()でセルを指定して、setValue()に出力したい値を渡すことによって書きこんでくれます。

出力先（別スプレッドシートや別シート）を指定する方法

基本の書きこみ方法は既にお伝えしましたが、別スプレッドシートや別のシートに出力したい場合があるかと思います。
その時の書き方について解説します。

別スプレッドシートを指定する

別のスプレッドシートを指定する場合には、SpreadsheetApp.openById()を使います。
openByIdにスプレッドシートのIDを渡すことによって指定したスプレッドシートに対して出力することができます。

スプレッドシートのIDはスプレッドシートを開いた時にURLに表示される
https://docs.google.com/spreadsheets/d/●●●●/editの●●●●部分です。

function myFunction() {
  var id = "●●●●"
  var ss = SpreadsheetApp.openById(id)
  var sheet = ss.getActiveSheet()
  sheet.getRange("A1").setValue("リンゴ")
}

シートを指定する

スプレッドシート内のシートを指定するには2パターンのやり方があります。

シートの名前で指定する方法
シートの位置で指定する方法

シートの名前が固定である場合には名前で指定するのが、オススメです。

ですが、運用上シート名は変わってしまうが、場所は固定であるという時もあるかと思いますので、柔軟に使い分けましょう

シートの名前で指定する

シートの名前で指定する場合には、getSheetByName()を使用します。
getSheetByNameにシート名を渡すことによって、シートを指定することができます。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("シート3")
  sheet.getRange("A1").setValue("リンゴ")
}

シートの位置で指定する

シートの位置で指定する場合には、getSheets()を使用します。
getSheetsはシートの配列を返してくれる関数となりますので、indexを指定してあげる必要があります。

シートのタブが左から2番目を指定したサンプルです。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheets()[1]
  sheet.getRange("A1").setValue("リンゴ")
}

注意点としてはシートの左からの数えと指定する数字が違うところです。
シートのタブが左から2番目なのに、1を入れています。

これは、indexは0から始まりなので、0が1番目となります。

スプレッドシートの最終行に書きこむやり方

GASでスプレッドシートの最終行に書きこむやり方としては3つの方法があります。

appendRowを使う
getLastRowを使う
getNextDataCell(SpreadsheetApp.Direction.UP).getRow()を使う

それぞれについて順に解説します。

appendRowを使って最終行に書きこむやり方

appendRowはスプレッドシート内での最終行を勝手に判断してくれるので、かなりシンプルに使うことができます。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  sheet.appendRow(["オレンジ", "ブドウ"])
}

追加したい列の情報を配列の形で渡すことによって最終行に追加されます。

getLastRowを使うやり方

getLastRowはシート内全体でデータのある最終行を返してくれる関数です。
これを用いることによって最終行に対してデータを出力してあげることができます。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var lastRow = sheet.getLastRow();
  sheet.getRange(lastRow+1, 1).setValue("パイナップル")
}

getLastRowはデータのある最終行を返してくる関数なので、最終行の次に追加したい場合には、＋1してあげる必要があります。

appendRowのほうがかなりシンプルですが、getLastRowを用いることによってより細かい処理が可能となります。

getNextDataCell(SpreadsheetApp.Direction.UP).getRow()を使うやり方

なんだか長くてよくわからない関数ですが、これを覚えておくとかなり柔軟な最終行の指定をすることができます。

getLastRowではシート内全体を見て最終データがある行の情報を返してきてしまうため。特定の列に関する最終行を取ることができません。

サンプルの画像で言うと、getLastRowでは、4を返してきます。
本当はA列の最終行に追加したいという場合には使うことができません。

そんな時に、getNextDataCell(SpreadsheetApp.Direction.UP).getRow()を使うことによってA列の最終行に追加することができます。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var maxRow = sheet.getMaxRows()
  var lastRow = sheet.getRange(maxRow, 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()
  sheet.getRange(lastRow+1, 1).setValue("ピーチ")
}

こちらもgetLastRowと同様にデータのある最終行の行番号を返してくるので、追記する場合には＋1してあげる必要があります。

複数範囲のデータを配列を使って書きこむ方法

これまでは、setValueを使って単一のセルに書きこむ方法を解説しましたが、
複数範囲に対してデータを書きこむ方法を解説します。

複数範囲に対してデータを書き込む場合には、setValuesを使用します。
複数形を扱うsがついているものです。

A1を基点として、3行2列のデータを出力するサンプルです。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var values = [["オレンジ", "グレープ"], ["ピーチ", "ストロベリー"], ["マンゴー", "ドラゴンフルーツ"]]
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values)
}

setValuesを使うには二次元配列の形式で値を渡してあげる必要があります。
values.lengthで行数を、values[0].lengthで列数を自動的に判別できます。

行数と列数を自動的に判別するようにしましたが、データの数と範囲が一致していない場合には処理をさせたくない場合にはgetRangeで行数と列数を指定してあげるのがよいです。