Googleスプレッドシートへのハイパーリンク挿入

Question

元シートをコピーした場合、コピー先に挿入されたリンクから飛ぶとコピー元のシートに飛んでしまいます。

=HYPERLINK("https://docs.google.com/spreadsheets/d/×××××/edit#gid=*****&range=B3","ハイパーリンク")

というリンクなので、元シートのURLやシートIDが指定されてるのでそれは当然ではあると思うのですが、コピーした場合はコピー先のシートを参照して欲しいのです。ハイパーリンクを入れてるシートが30個以上ありまして、コピーするたびに手動で対応するのはちょっと手間が掛かってしまいます。
先頭のシートなら#gid=0ですみますが、他にもシートが沢山あるので、何か良い方法があればご教示いただきたいです。

=HYPERLINK("https://docs.google.com/spreadsheets/d/#range=B3","ハイパーリンク")

=HYPERLINK("#range=B3","ハイパーリンク")

=HYPERLINK("B3","ハイパーリンク")

など色々試したのですがどれもダメでした。

Comments

[退会済みユーザー]

=HYPERLINK("#gid=xxxxxxxxxxx&range=A3","ハイパーリンク")
（xxxxxxxxxxは実際には「1614528569」　のようなシートIDの数字です）のように設定してもだめでしょうか

Answer 1

リンクを設定するときに、URL形式ではなく、
=HYPERLINK("#gid=xxxxxx&range=A3","ハイパーリンク")
という形式（xxxxxxはシートのgid）で数式を入力してください。

このように設定することで、元スプレッドシートをコピーした場合であっても、コピー先スプレッドシートで自動的にgidが修正され、リンク先もコピー先のスプレッドシート内の該当するシートに自動的に変更されます。

（現状、コピーしたスプレッドシートの参照先がコピー元に固定されてしまうのは、https～から始まるurlで指定してしまっているからです。
「#gid」以降だけ設定すれば、シートコピー後も自動的に参照先が変更されます）

---

元のスプレッドシート内のHYPERLINK数式を一括修正する方法

元のスプレッドシート内のHYPERLINK数式を一括修正したいのであれば、
①元のシートを開いて、スプレッドシートのメニューの「編集」→「検索と置換」をクリック

②検索と置換のダイアログボックスで
・「検索」に、URLの「edit」までのURLを入力（※#gid～以降は削除すること）
・「置換後の文字列」は空欄のままにしておく
・「数式内も検索」にチェックを入れる

③このような状態で、 検索→「置換」をクリックすると、数式内のURL部分の「edit」までが削除され、

=HYPERLINK("#gid=xxxxxx&range=A3","ハイパーリンク")

という数式に修正できるはずです。

Comments

[snd1000]

ご回答いただきありがとうございます。
http～を抜いて最初から#gid～で指定すればコピーしても自動で修正してくれるのですね。大変勉強になりました。

置換して不要部分を削除してからコピーしてみたのですが、成功しました。本当に感謝致します。

検索と置換の方法まで教えてくださりありがとうございました。

Answer 2

シートの何処かに自身のGIDを格納するセルを用意しておいて、コピー先でそのセルを修正するというのはいかがでしょうか。
例えばA1にgidを入れるとしたら、B2へのリンクを表示する数式は次のようになります。
= HYPERLINK(CONCATENATE("#gid=", $A$1, "&range=B2"))

１つのシートに複数リンクが有るのであれば、１つ１つ修正するよりは手間がかからないと思います。

---

コピー先、コピー元どちらでもApps Scriptが利用可能な環境であれば、次のカスタム関数を定義することで現在のシートの特定のレンジへのリンクを生成できます。

function genAnchor(range) {
  const app = SpreadsheetApp.getActiveSpreadsheet();
  const gid = app.getActiveSheet().getSheetId();
  return "#gid=" + gid+ "&range=" + range;
}

B４へのリンクを表示する数式は下記のようになります。
=HYPERLINK(genAnchor("B4"))

(カスタム関数の作り方が不明な場合はコメントでお知らせください。)

Comments

[snd1000]

ご回答いただきありがとうございます。

プログラミング自体初心者で、最近GASをはじめたので利用環境はございます。コードも何とか理解することができました。今回は他の方をベストアンサーにさせて頂きましたが、勉強のためにこちらの方法も試してみたいと思います。

コードの記述までしていただき本当にありがとうございます。
頑張って精進いたします