フォームの回答などをGoogle Spreadsheetで受け取ると、最終行に新しく1行追加されますが、関数を設定していても新しい行に関数が適用されたりはせず、関数が入っていないまっさらな行として挿入されています。
ちょっとしたRPAであれば、自動で関数が適用されるだけでスクリプトに頼らなくて良くなるシーンもこれまでありましたので、
- 最終行までのセルをチェック
- 1行上に関数が入った行があれば、同じ関数を該当のセルに適用
を行うスクリプトを用意し、シートの変更をトリガーに起動させることで、常に関数が設定された状況を作ります。
仕組み
| A | B | C | |
|---|---|---|---|
| 一行目 | 5 | 4 | =SUM(A1:B1) |
| 二行目 | 3 | 2 | =SUM(A2:B2) |
例えば、C1C2セルにSUM関数が使われていた場合、
三行目が追加された際に、A3B3セルには数値が挿入されてもC3セルには関数は適用されません。
そこで、A-Zのアルファベット一文字と数値の組み合わせを探して、数値を置換します。
正規表現で /[A-Z]\d+/g に合致した文字列だけを、
var letter = match.substring(0, 1); // アルファベット部分を取得
var number = parseInt(match.substring(1)); // 数字部分を取得し、整数に変換
return letter + (number + 1); // 数字部分に1を足して結合する
という感じで処理する形にしてみました。
C3 → C4
D13 → D14
AA24 → AA25（処理しているのはA24→A25部分のみ。）
絶対参照を行っている場合は、$A$3という記述になっているため、
置換対象の文字列から外れます。
懸念として、フリーフォームのテキストの結合を関数で行っている場合等で、意図しない置換が発生してしまう可能性があります。
かっちり運用のRPAを組みたいなら普通にスクリプト組むべきだと思います。
スクリプト
function insertFormulasAndCheck() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const trigger_check = sheet.getRange("A2").getValue();
// A2がTRUE（チェック済み状態）になっているシートのみ実行対象にする
if (trigger_check == true) {/*そのまま実行*/}else{return /*スプリプト終了*/};
// 最後の行と最後の列を取得
const lastRow = sheet.getLastRow();
const lastCol = sheet.getLastColumn();
// A列の値をチェックして関数挿入済みか判定するので、A列は"関数挿入判定列"とか"Check"とか名前をつけて、開けておいてください。
var checkCells = sheet.getRange(1, 1, lastRow).getValues();
for (var row = 2; row <= lastRow; row++) { // 最初の行はヘッダー行を想定しているため無視します
if (checkCells[row-1][0] !== true) { // A列がtrueでなければ実行
for (var col = 1; col <= lastCol; col++) {
// 1行上のセルから関数の取得
var sourceCell = sheet.getRange(row - 1, col);
var formula = sourceCell.getFormula();
// 関数が空でない場合のみ、書き換えを行う
if (formula) {
// 関数内の"A1"（や"B2", "C3"など）を"A2"（や"B3", "C4"など）に置き換える
var newFormula = formula.replace(/[A-Z]\d+/g, function(match) {
var letter = match.substring(0, 1); // アルファベット部分を取得
var number = parseInt(match.substring(1)); // 数字部分を取得し、整数に変換
return letter + (number + 1); // 数字部分に1を足して結合する
});
// 新しい関数を現在の行のセルに書き込む
var targetCell = sheet.getRange(row, col);
targetCell.setFormula(newFormula);
}
}
// 更新が完了したので、A列にチェックボックスを追加
sheet.getRange(row, 1).insertCheckboxes().setValue(true);
}
}
}
注意点
A列を、処理を行ったか判別するための列として使用しています。
毎回全セルをチェックしていると莫大な実行時間がかかってしまい、実行時間制限に引っかかるためです。
A列は「Check」とか名前をつけて、そっとしておいてあげてください。