本記事では、GASでスプレッドシートに書き込む方法を解説しています。
3パターンの書き込み方法を実際のコードと合わせて紹介しているので、ぜひ参考にしてみてください。
今回のサンプル動画
GASでスプレッドシートに書き込みを行う方法
GASでスプレッドシートに書き込む方法は、2パターンあります。
1つ目がsetValue
メソッドを利用した書き込みです。setValue
メソッドは、単一セルへ書き込みを行う際に使用します。
2つ目がsetValues
メソッドを利用した方法です。setValues
メソッドは、setValue
メソッドと異なり複数行・複数列への書き込みが可能です。
- setValue・・・単一セルへの書き込み
- setValues・・・複数行・複数列への書き込み
それぞれ解説していきます。
setValueで単一セルへ書き込み
setValue
は単一セルへの書き込みを行えるメソッドで、以下のように使用します。
Rangeオブジェクト.setValue(value)
以下はsetValue
メソッドを使用して値を書き込むスクリプトです。
function writing_on_sheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
// 書き込む内容
const content = "単一セルへの書き込み";
// 指定したセルに書き込み
sheet.getRange("A1").setValue(content);
}
setValuesで複数行・複数列にまとめて書き込み
複数行・複数列にまとめて書き込めるsetValues
メソッドは以下のように使用します。
Rangeオブジェクト.setValues(values)
setValues
メソッドの引数は、二次元配列にする必要があります。また、getRange
で指定する範囲と書き込む範囲の大きさを揃えないとエラーになってしまうので注意してください。
setValues
は複数行・複数列へ書き込めるメソッドですが、単一セルへの書き込みも可能です。ただし、その場合も引数は[[“書き込む値”]]
のように二次元配列にしましょう。
実際にsetValues
メソッドを使用して複数の範囲に書き込んでみます。
function writing_on_sheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
// 書き込む内容(二次元配列)
const content =
[
// 1行目
["A1","B1"], // A列 B列
// 2行目
["A2","B2"] // A列 B列
]; // 2行2列の二次元配列
// 指定した範囲に書き込み
sheet.getRange(1,1,content.length,content[0].length).setValues(content);
}
15行目の書き込む範囲を指定する際、getRange(“A1:C5”)
のような形式でも問題ありません。ですが、書き込む範囲の行数・列数が不明な場合に上記のようなエラーが出てしまう可能性があります。
上記のコードでは、length
プロパティを用いて自動的に範囲が揃うようにしています。
setValueとsetValuesどちらを使うべきか
先述したように、単一セルへの書き込みはsetValues
メソッドでも可能です。また、for文を使うことで、複数行・複数列にsetValue
メソッドで書き込むこともできます。
結果的に、setValue
setValues
のどちらでも書き込むことができますが、どちらを使うべきなのでしょうか。
結論として、シートへの書き込みは単一セルであれ複数行・複数列であれsetValues
メソッドを使うことをおすすめします。
理由は処理速度です。単一セルへ書き込むsetValue
メソッドは、セルへ書き込む度にシートにアクセスするため負荷がかかってしまいます。
数行の書き込みであれば遅延を気にすることはありませんが、何千・何万行になるとシートが重くなり明らかに処理速度が変わってくるので、シートへの書き込みは基本的にsetValues
メソッドを使うようにしましょう。
シートの最終行に書き込みを行う方法
ここまでで、シートに書き込む方法を解説してきました。実際の仕事では、該当シートの最終行に値を追加したいというケースが頻繁に発生します。
次に、シートの最終行に書き込みを行う方法をご紹介します。
appendRowで最終行に追加
appendRow
メソッドを使用して最終行に値を書き込むコードは以下になります。
function writing_on_sheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
// 書き込む内容(一次元配列)
const content = ["A列","B列","C列","D列"];
// 最終行に書き込み
sheet.appendRow(content);
}
- 引数の書き込む値は配列にしなければならない(二次元配列はNG)
- nullを指定することで列をずらせる(2列目だけに入力したい場合は[null,2列目の値])
- =SUM(A1:A)のように指定すれば関数も入力できる
- 関数・数式が入っている行をカウントしてしまう
getLastRowと組み合わせて最終行に追加
次に、getLastRow
メソッドと組み合わせて最終行に値を追加するコードです。
function writing_on_sheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const lastRow = sheet.getLastRow(); // 最終行を取得
// 書き込む内容
const content =
[
// 1行目
["A1","B1"], // A列 B列
// 2行目
["A2","B2"] // A列 B列
]; // 2行2列の二次元配列
// 最終行に書き込み
sheet.getRange(lastRow + 1,1,content.length,content[0].length).setValues(content);
}
- 単一行だけでなく複数行に一括で入力できる
- getRangeの範囲とsetValuesの範囲が異なるとエラーになる
- =SUM(A1:A)のように指定すれば関数も入力できる
- 関数・数式が入っている行をカウントしてしまう
入力したい範囲が1行だけならappendRow
を、複数行ならgetLastRow
と使い分けると良いでしょう。
数式が入っていると正しく機能しない
appendRow
メソッドもgetLastRow
メソッドも、関数や数式が入っている場合は値が入っているとみなしてしまうため、想定していない最終行に書き込みをしてしまいます。
以下の画像をご覧ください。
一見、6行目が最終行に見えますがB列には=IF(A2="","",A2)
の関数がB11
まで入力されています。この状態で上記のコードを実行すると12行目に書き込んでしまうのです。
このように、関数や数式が下の行まで入力されている場合は別の方法で最終行を取得する必要があります。
function writing_on_sheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
// 書き込む内容
const content =
[
// 1行目
["A1","B1"], // A列 B列
// 2行目
["A2","B2"] // A列 B列
]; // 2行2列の二次元配列
// 最終行を取得したい列番号を指定(※A、Bなどの列名はNG)
const columnNum = 1;
// 指定した列番号の最終行を取得
const lastRow = sheet.getRange(1,columnNum).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
// 最終行に書き込み
sheet.getRange(lastRow + 1,1,content.length, content[0].length).setValues(content);
}
上記のコードは、関数や数式が入力されていても正しい最終行を取得して書き込むことができます。
15行目で関数・数式が入っていない列を指定し、その列の最終行を取得することで正しく書き込むことが可能です。
GASでスプレッドシートに書き込むサンプル動画のコード全体像
最後に、記事冒頭のサンプル動画のコード全体像を掲載しておきます。
function m_writing_on_sheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const inputRange = Browser.inputBox("書き込みたい範囲を指定してください。", Browser.Buttons.OK_CANCEL);
const inputValues = Browser.inputBox("書き込みたい内容を入力してください。\\n指定した範囲:" + inputRange + "\\n\\n列の区切りは「/」行の区切りは「|」", Browser.Buttons.OK_CANCEL);
const inputValue = inputValues.split("|");
const value = [];
for(let i = 0; i < inputValue.length; i++) {
value.push(inputValue[i].split('/'))
}
try {
if(inputRange !== "cancel" && inputValues !== "cancel") {
sheet.getRange(inputRange).setValues(value);
}
} catch(e) {
Browser.msgBox("指定した範囲と入力する値の範囲が異なります。");
}
}
// 独自メニュー作成
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu("シート操作");
menu.addItem("新規作成","m_gas_sheet_create");
menu.addItem("コピー","m_gas_sheet_copy");
menu.addSubMenu(
ui.createMenu('削除')
.addItem("特定のシートを削除","m_gas_sheet_delete_1")
.addItem("特定のシート以外を削除","m_gas_sheet_delete_2")
.addItem("特定の複数シートを削除","m_gas_sheet_delete_3")
.addItem("特定の複数シート以外を削除","m_gas_sheet_delete_4")
.addItem("全てのシートを削除","m_gas_sheet_delete_5")
);
menu.addItem("アクティブ化","m_gas_sheet_active");
menu.addItem("アクティブ列の最終行を取得","m_gas_getlastrow");
menu.addSeparator();
menu.addItem("シートへ書き込み","m_writing_on_sheets");
menu.addToUi();
}
GASでの書き込みができると効率が格段に上がります。ぜひお好みにカスタマイズして、普段の仕事でも活用してみてください。