GASで名前付き範囲の「定義側」をコピーしようとする話

まとめ

• Googleスプレッドシートで、シート間で名前付き範囲の定義をコピーしたくなる時がある
• 以下コードで解決できる

function myFunction() {
  //原本から名前付き範囲を取得
  var SS_CopyFrom =SpreadsheetApp.openById("ここにシートのID");
  var namedRanges = SS_CopyFrom.getNamedRanges();

  //名前付き範囲をこのシートにも反映させる
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  for (var i = 0; i < namedRanges.length; i++) {
    ss.setNamedRange(
      namedRanges[i].getName(), //名前付き範囲の名前を指定
      ss.getRange(namedRanges[i].getRange().getSheet().getName() + "!" + namedRanges[i].getRange().getA1Notation())
    )  
  }

  // 以下、中身みたい人用、コメントアウトしているので実行するときにお試しでどうぞ
  //for (var i = 0; i < namedRanges.length; i++) {
  //Logger.log( "名前付き範囲の名前:"+ namedRanges[i].getName() +
  //            ", 名前付き範囲のA1表記" + namedRanges[i].getRange().getSheet().getName() + "!" + namedRanges[i].getRange().getA1Notation());
  //}
}

以下、駄文

名前付き範囲、いつの間に

知らない間にGoogle スプレッドシートにも名前付き範囲指定が実装されていた。Excel側だとテーブルとか構造化参照とか言っていたやつ。

support.google.com

便利な反面、ちゃんと名前を付けないと、その名前付き範囲の参照先がどこか分からなくなるので、使い手次第なところはある。

と思っていたら、上気したようなコードが書きたくなるケースに出会ったのでメモしておく。

具体的な使用例

• 何らかのテンプレートシートが存在しており、そのシート内で名前付き範囲を使用して関数を書いていた
• テンプレートを更新した際、名前付き範囲の内容を変更した
  • 例： A:B という範囲に名前を付けていたが、 A:C のように参照する行を足したくなった
• この際、テンプレートと同様の名前付き範囲を他のシートにも反映させたい

実際にやっていることはコードを読んだ通り。

ハマりポイントは、 setNamedRange() がシート間で使用することができないこと。 素直に getRange() を使うと以下のようなエラーが表示される。

Exception: Target range and source range must be on the same spreadsheet.

そのため、わざわざ一度文字列に起こして、その後に新しいRange を作って関数に渡している。

一応これができると、テンプレートの数式を一切変更せずに、参照するセルを増やすと言うことが可能になるので、便利なことがあるかもしれない。

自分が今取り組んでいる限りではそんなにイケてるやり方に見えないが、今後便利なことが起きうるかもしれないのでメモ。