GASで数式の一部(参照セル)のみ変更したい

Question

シートのセルにimportpangeの数式が既に入っています。

=IMPORTRANGE（“$A$1”,“$A$2!B1”）

このB1の部分を指定のシートでは
C1に変えたいと思っています。

複製したシート内の数式の一部(参照部分)の
アルファベットの部分を一つつずつずらすようにしたくて…。

B1のところを●にしておいて
replace で C1に変えようとしたらエラーが出てしまいうまくいきませんでした…。

setFormulaだと、数式を全て入れ直すようになるとおもうのですが
変えたいのはB1の部分だけなので
他に何か方法があったら知りたいです。

よろしくお願い致します。

Comments

[m.ts10806]

>GAS
タグあります。
https://teratail.com/tags/Google+Apps+Script

Answer 1

2年以上前の記事ですが、行き着いて私も困り先ほど解決したのでコード添付します。

function myFunction() {
  var targetSheet = SpreadsheetApp.getActiveSheet();
  var finder = targetSheet.createTextFinder("B1")
  .matchFormulaText(true);
  var cnt = finder.replaceAllWith("C1");
  SpreadsheetApp.getUi().alert(cnt + "件置換しました。");
}

解説など詳細は以下参考にしてみてください。
https://yukeblog.org/blog/spreadsheet2/

Answer 2

現在のシートのA1セルに参照先のスプレッドシートキー(あるいはURL)が記入済みで、A2セルに参照先のシート名が書いてあるのですよね?
参照先の範囲文字列を自前で生成すれば、GoogleスプレッドシートのIMPORTRANGE関数だけ使って実現できそうですが。

=IMPORTRANGE($A$1, $A$2 & "!B" & row())

で、この数式が書いてあるセルと同じ行番号でB列のセルの値が取得できます。行番号がずれているなら、row()の部分を(row() + 1)とか適宜調整すればいいでしょう。

こいつを縦方向のセルにコピーすると、それぞれのセルでrow()を取得して、目的のセルの値が取得できるはずです。

ここまで書いておいてなんですが、この方法だと、数式の数だけ別のスプレッドシートから値を取得する処理を行なうことになるので、処理が遅くなるんじゃないかな。

代わりに、先頭のセルにひとつだけ、

=IMPORTRANGE($A$1, $A$2 & "!B" & row() & ":B" & (row() + 5))

みたいに参照先の範囲を生成する数式を書けば、合計5個のセルの値を取得して、数式の書いてあるセルから下に自動的に表示してくれます。