こんにちは
こんにちは、みなさん。
グレンジ Advent Calendar 2024 18日目担当のdonburi_fatherです。
突然ですが「ARRAYFORMULAの下」、消したいですよね。
うん、消したい！経済学的に消したい！
というわけで今回はARRAYFORMULAの下を消すGASを書きます。
君消す
ARRAYFORMULAを使うと、その列のARRAYFORMULA以降が入っている行以降に自動で関数内の式を代入していただけるのですが、こいつがどうも厄介なのです。
というのも行単位でコピペしたりすると、ARRAYFORMULAの列内に値が入ることによってARRAYFORMULAの展開ができなくなってしまうのです。
つまり、
これが
こうなるやつ
上記の例では、「本当はなにもはいってはいけない列」に｢コピーされた〇」が値貼り付けされることでそのほかの行にARRAYFORMULAが展開できなくなるという、いわゆる「最高の状態」が発生しています。
なのでARRAYFORMULAが入った列へのコピペは最新の注意を払う必要があります。
なぜGASで消すのか
ではなぜGASで消すのか、なんで？それ意味ある？
...なぜなら、
｢GASってなんかかっこいいから｣
ではなく、単純に特定が難しいからです。
極端な例
先ほどはARRAYFORMULAの下に｢〇｣が入力されたため、特定が非常に簡単でした。
では｢空白｣が入った場合はどうでしょう？
ぱっと見では見分けはつきません。
ARRAYFORMULAは範囲内の値を検出してくれますが、例えば複数列ARRAYFORMULAが入っているシートでコピー＆ペーストを実施しまくった後に全ての｢空白｣を消しきるのはやや面倒な作業です。
(空白が入るとぱっと見どこを選択・削除すればいいか分からない)
4秒で分かるGAS
GASとは以下のようなものです
- スプシに紐づいているスクリプトです
- 紐づいているスプシを開いたタイミング、編集したタイミング、時間指定でもトリガーを発火させることができます
- 上の使い方は割とどうでもよくて、普通に任意のタイミングで実行できます(後述)
あとは勘の良い方ならなんでもできるんじゃないでしょうか。
というわけで｢ARRAYFORMULAの下｣を実際に消す
サクッと書いてみます。ChatGPTで書けました。
function cleanUnderARRAYFORMULA() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getDataRange(); //左上～有効値が入ってる最大セルまでの領域を返してくれるッポイ
var formulas = range.getFormulas()
// 二次元配列の全探索
var dim_row = formulas.length
var dim_column = formulas[0].length
for (var col = 0; col < dim_column; col++) {
for (var row = 0; row < dim_row; row++) {
var is_ARRAYFORMULA_cell = formulas[row][col].toUpperCase().includes('ARRAYFORMULA')
if(!is_ARRAYFORMULA_cell){
continue
}
//ARRAYFORMULAのセルを見つけた場合, 直下の列の値を全て削除する
//削除した場合その列は探索しなくて良い
sheet.getRange(row+2, col+1, dim_row-row).setValue("")
break
}
}
}
コードについてのコメントは
- getDataRange関数はシートの左上から有効な値が入っている最小領域を二次元配列で返すのでややホスピ―
- セルの全valueを取る → 数式のみでフィルター → 二次元配列の全探索 → ARRAYFORMULAを見つけたら処理実施
- 二次元配列のインデクスは0始まり、getRange関数は1始まりなので引数の変換が必要
といったぐらいでしょうか。
処理は簡単、｢ARRAYFORMULAの下を全て削除する｣です。
この関数をスプシの拡張＞Apps Scriptから開かれたテキトーなコードブロックに記載し、保存しておきます。これで準備は完了です。
消せ！
最後に実践編です。
出ましたね、人類の敵です。
自分たちが消されるとも知らず、言いたい放題ですね。
われらのGASで消し炭にして差し上げましょう。
というわけで｢図形描画｣から｢きみ消す｣ボタンを作ります
次に、右上の三点からスクリプト割り当てを選択し、先ほど作成した｢cleanUnderARRAYFORMULA｣を選択します
あとはこの作成したボタンをクリックすると...
スクリプトが実行され、見事悪を討ち滅ぼすことができました。
意外にボタンでスクリプト実行できるのは良い感じですね。
今回GASの練習としてARRAYFORMULAを消すGASを書いてみましたが、GASを書くのが思ったより大変そうなのでGASで管理すべきか、スプシを落としたものをpythonとかの別の扱いやすい言語で処理にかけるべきかは用途によって変わりそうだな、と感じました。
GAS, GAS, GAS I'm gonna step on the gas.
おわりに
というわけで今回は｢ARRAYFORMULAの下｣を消してみました。
いかがだったでしょうか。スッキリしたでしょうか？
次回は、｢24時過ぎの次郎系のカロリーを消す｣でお会いしましょう。