GAS(Google Apps Script)で、条件に従って行を削除するコードを書きたいとき、こちらの記事を参考にしてください。例えば『D列に販売終了日付が入っている行を削除したい』といったときに役立ちます。
こんにちは。当ブログの管理人の『くろん』です。
30代サラリーマン、新規事業推進室に所属。ブラック企業努め時代に身に着けた業務効率化ノウハウをアウトプットしていきます。
読んでくれた方の業務が一秒でも早く終わりますようにの精神で記事書いてます!!
条件に応じて行を削除する
サンプルデータを作ってみました。
これのD列が空欄ではなく、日付が入っている行を削除していく。という内容のGASを考えていきたいと思います。
ちなみに、特定の行を削除するときは、単純に『deleteRowメソッド』『deleteRowsメソッド』を使えばいいので、こちらをご確認ください。
実際にGASを作ってみた
function delete_jyouken() {
// コンテナバインドされてるスプレッドシートを取得
let spreadsheet1 = SpreadsheetApp.getActiveSpreadsheet();
// シート名指定でシートを取得
let sheet1 = spreadsheet1.getSheetByName('データ');
// 最後の行番号を取得します
let lastRow1 = sheet1.getLastRow();
// 販売終了年月が存在する列を確認
let endDatesRange = sheet1.getRange("D2:D" + lastRow1);
let endDatesValues = endDatesRange.getValues();
// 行を最終行から順に処理します
for (let i = lastRow1 - 2; i >= 0; i--) {
if (endDatesValues[i][0] !== "") { // 販売終了年月が空欄ではない場合
sheet1.deleteRow(i + 2); // スプレッドシートの行番号は1から始まるが、配列のインデックスは0スタートなので『1』追加するのと、D2からD最終行まで取得したので、ズレ解消のため『1』追加
}
}
}
実行後がこちらになります。D列に日付が記載されていた行に限り削除されていることが分かるかと思います。
というわけで、ここからは、GASコードの解説をステップごとにしていきますね。
// コンテナバインドされてるスプレッドシートを取得
let spreadsheet1 = SpreadsheetApp.getActiveSpreadsheet();
// シート名指定でシートを取得
let sheet1 = spreadsheet1.getSheetByName('データ');
GASを動かすスプレッドシート、シートを指定します。
今回は、コンテナバインドされているスプレッドシートと、シート名からシートを指定しました。
環境に応じて記載を変えてください。
// 最後の行番号を取得します
let lastRow1 = sheet1.getLastRow();
こういうデータをループ処理する上で絶対必要なのが、どこまでやるねんの確定です。
最終行の取得の仕方は、別の記事にいろいろまとめましたので、参考にしてみてください。
// 販売終了年月が存在する列を確認
let endDatesRange = sheet1.getRange("D2:D" + lastRow1);
let endDatesValues = endDatesRange.getValues();
D2からD列の最終行までのデータをひとまず変数に格納させます。
変数に格納させずに、ループ処理のなかで、getValueメソッドで、一回一回D列のセルを取得してIF関数当てても動きはするのですが、時間がかかってしまうことが予想されます。getの処理はなるべく少なく済ませるのが、早く動くGASを作成するポイントですので、なれるまで大変ですが、一発で変数にgetすることを心がけてください。
for (let i = lastRow1 - 2; i >= 0; i--) {
if (endDatesValues[i][0] !== "") { // 販売終了年月が空欄ではない場合
sheet1.deleteRow(i + 2); // スプレッドシートの行番号は1から始まるが、配列のインデックスは0スタートなので『1』追加するのと、D2からD最終行まで取得したので、ズレ解消のため『1』追加
}
}
後述しますが、行削除をループ処理でやる場合は、上から下ではなく、下から上に順に処理していく必要があります。
『endDatesValues』の変数の[0][0]に格納されているのは、D2セルなので、『i』と消したい行数には、『2』の開きがあります。配列のインデックスのスタートが『0』であることと、見出し行(1行目のこと)を配列変数に格納していないことに起因します。
For文による繰り返しループ処理(数が大きいほうから小さいほうへ順次)に組み込んで、条件が『』(空欄)でなかったら、その配列に対応する行を『deleteRowメソッド』で削除してねという内容になっています。
どうして行削除ループ処理を上からではなく、下からやらないといけないか
このループ処理を上から実行すると、意図した行が消えてくれなくなります。
試しに上から実行した結果を載せておきますね。
function ng_delete_jyouken() {
// コンテナバインドされてるスプレッドシートを取得
let spreadsheet1 = SpreadsheetApp.getActiveSpreadsheet();
// シート名指定でシートを取得
let sheet1 = spreadsheet1.getSheetByName('データ');
// 最後の行番号を取得します
let lastRow1 = sheet1.getLastRow();
// 販売終了年月が存在する列を確認
let endDatesRange = sheet1.getRange("D2:D" + lastRow1);
let endDatesValues = endDatesRange.getValues();
// 行を2行目から順に処理します
for (let i = 0; i <= lastRow1 - 2; i++) {
if (endDatesValues[i][0] !== "") { // 販売終了年月が空欄ではない場合
sheet1.deleteRow(i + 2); // スプレッドシートの行番号は1から始まるため、配列のインデックスは0スタートなので『1』追加するのと、D2からD最終行まで取得したので、ズレ解消のため『1』追加
}
}
}
このように、削除したいD列に日付が入っている行が残ってしまい、反対に消えてほしくない行が消えてしまっています。
どうしてこうなってしまうのでしょうか??
上から順に削除していくと、配列に格納されたD列のインデックス番号と、実際のシートの行数の整合性が合わなくなっていくためです。
具体的に言えば、このサンプルデータですと、上から順にいくと『endDatesValues』のインデックス番号0は削除対象となるので、シートの2行目が削除されます。
次の削除対象はインデックス番号6なので、シートの8行目が削除されることになるのですが、先程シート2行目が削除されたことにより、シートの8行目にあるデータは、元々のデータの9行目ということになってしまい、『消したい行を消せないこんな世の中じゃPOISON』となります。
なので、行を削除するループ処理をする場合には、上から順にではなく、下から順に実行する必要があります。