🐙
【GAS】スプレッドシートのセル関数を置換する
1つのセルの関数を置換する場合
A1のAAAという記述をBBBに置換します。
const change = () => {
var spreadsheet = SpreadsheetApp.openById('スプレッドシートのID');
const sheetName = spreadsheet.getSheetByName('シート名');
if (sheetName) {
let formula = settingsSheet.getRange('A1').getFormula();
formula = formula.replace(/AAA/g, "BBB");
spreadsheet.getRange('A1').setFormula(formula);
}
gフラグ
gは、gフラグと言われています。
gフラグがあることでセル内の全ての該当するパターンが置換の対象となり、セル内の全てのマッチするテキストを一括で置換できます。
gフラグがない場合は、文字列内で最初にマッチした部分のみを検索し、それを置換します。最初のマッチの後に検索は停止します。先ほどの例では最初に見つかったAAAの部分のみが置換され、2番目以降AAAは無視されたままになります。
絶対参照などで$A$1などと記述したい場合
$1と記述しないようにしてください。正規表現の置換機能では、キャプチャグループ（()で囲まれた部分）にマッチしたテキストを$に続く数字（例えば、$1、$2）を使って参照できます。よって、マッチしたテキストを参照する目的でない場合に$1を使ってしまうと、誤ってキャプチャグループにマッチしたテキストに置き換えられてしまいます。
絶対参照などで$A$1などと記述したい場合には、$A$$1と記述してください。
複数のシートの複数のセルを指定して関数を置換する場合
SUM関数が見つかるたびに、その関数の引数をキャプチャグループ（(.*?)）を使用して、AVERAGE関数の引数として再利用しています。
const sheetNames = ['シート1', 'シート2'];
const cells = ['A1', 'B1'];
// 各シートに対して処理を実行
sheetNames.forEach(sheetName => {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (sheet) {
// 指定されたセルに対して処理を実行
cells.forEach(cellAddress => {
const cell = sheet.getRange(cellAddress);
let formula = cell.getFormula();
// SUM関数をAVERAGE関数に置換して、直接formulaに再代入
formula = formula.replace(/SUM\((.*?)\)/g, 'AVERAGE($1)');
cell.setFormula(formula);
});
}
});
正規表現
キャプチャグループ
(.*?)はキャプチャグループと呼ばれます。
キャプチャグループは、正規表現内で特定の部分式を括弧()で囲むことによって、その部分式にマッチするテキストを1つのグループとして扱うことができます。正規表現の中で複数回使用でき、$1, $2...のように自動的に番号付けされます。
範囲を指定してセルの関数を置換する場合
const range = sheet.getRange('A1:B10');
const formulas = range.getFormulas();
for (let i = 0; i < formulas.length; i++) {
for (let j = 0; j < formulas[i].length; j++) {
formula[i][j] = formula[i][j].replace(/AAA/g, "BBB");
}
}
range.setFormulas(formulas);
終わりに
何かありましたらお気軽にコメント等いただけると助かります。
ここまでお読みいただきありがとうございます🎉
Discussion