Googleスプレッドシート 「Gmailひな形」ハイパーリンクの作り方（他 ネット検索、マップ、電話、メールも）
Googleスプレッドシートの ハイパーリンク機能、HYPERLINK関数について掘り下げる note 第3段です。
前回はスプレッドシート内の特定セルへジャンプするハイパーリンクの作成の方法について書きました。
Googleスプレッドシートの特定セルへのリンクは、シートIDが必須ということもあり少しExcelと比べ厄介な部分もありますが、GASのカスタム関数を組み合わせて解決する方法を紹介しています。
セルのテキストを使って便利なリンクを生成する
今回はHYPERLINK関数を使った、より実用的なリンク生成例を色々紹介していきたいと思います。
Google検索のハイパーリンクを生成する
まずは基本です。
セルに入ったキーワードを Googleで検索した時の画面 ↑ に一発で飛びたい。これを HYPERLINK関数を使って生成してみましょう。
例えば、A2セルに入ったキーワードでGoogle検索をした際の検索結果ページを直接開くリンクであれば
=HYPERLINK("https://www.google.co.jp/search?q="&A2,A2&" の検索結果")
このような式になります。
実際にGoogleで検索した時のURL先頭部分
https://www.google.co.jp/search?q=検索ワード
こちらを活用しています。
リンクラベル部分は A2&" の検索結果" としていますが、ここはお好みで変えていただいてOKです。
生成されたリンクをクリックすると、Googleの検索結果のページに飛んでいるのがわかりますね。
ベースのURL部分の文字列とセル内のテキストを & で連結させた URL字列を生成し、これをHYPERLINK関数で ハイパーリンク化する。
これがスプレッドシートで、便利なWebページへの直リンクを生成する基本となります。
複数の便利ハイパーリンクを一括生成して一気に開く
ハイパーリンクシリーズの1回目で、HYPERLINK関数について触れた時に Googleスプレッドシートでは、HYPERLINK関数とArrayformulaと組み合わせて 一括リンク化出来る（HYPERLINK関数の結果がスピる）と書きました。
これを活用すると、上のようにA列2行目以降に検索ワードがあった場合、B2に
=ARRAYFORMULA(IF(A2:A="",,HYPERLINK("https://www.google.co.jp/search?q="&A2:A,A2:A&" の検索結果")))
こんな式をいれることで、A列のキーワードのGoogle検索ページを隣のB列に一括生成できます。
A列の一部のセルが空白だった場合を考慮して IF関数で
IF(A2:A="",,
という「空白なら空白を返す」という分岐を入れました。
さらに、これまたハイパーリンクシリーズの初回に紹介したテクニックで、このハイパーリンクを一気に開くことも出来ます。
■複数セルのハイパーリンクを一気に開く方法（ショートカット）
1. 開きたいハイパーリンクのセル範囲（列）を選択 （Ctrl + Shift + ↓）
2. Alt + Enter （リンクを開く）
既にリストがあるのであれば、１つずつ検索するよりずっと楽ですね。
まぁ今だったら生成AIを使うって方法もありますが。
ちょっと情報が古かったりするんですよね。
まだまだ活用できるテクニックです。
住所情報からGoogleマップのリンクを生成する
スプレッドシートの住所をクリックしたら その地図（Googleマップ）が開いたら便利ですよね。
こんな住所データから地図へ飛べるリンクもHYPERLINK関数で生成できます。
住所を指定してGoogleマップで地図検索をするには、GoogleマップのURLの冒頭部分
https://www.google.com/maps/place/
この後ろに セルに入った住所情報を組み合わせます。
たとえば 住所が B2に入っていて、隣の C2セルからその住所のGoogleマップの地図を開きたい場合は
=HYPERLINK("https://www.google.com/maps/place/"&B2,"地図へGo")
こんな式を作成します。
生成されたリンクをクリックすると、住所の場所のGoogleマップが開きました。
複数一括生成したい場合は、先ほどと同じように Arrayformulaと組み合わせた式にアレンジしましょう。
しかし、残念ながらこの方法で作成した Googleマップのリンクは プレビューで地図画像は表示されません。
プレビューを表示させたい場合は、スマートチップの地図チップを使いましょう。
ただし残念ながらスマートチップは他のセルを参照といったことは出来ず、基本的には一つ一つ手作業が必要となります。
住所を一括で地図チップに変換、といったことは出来ません。
手作業でも 住所をコピー（Ctrl +C）、Tabで隣のセルに移動して @ を入れて 住所をペースト（Ctrl + V）して、該当の住所の「場所」を選択って流れで、キーボードショートカットで割とサクサク作成は出来るんで、件数が少なければ 地図チップを使うのもアリです。
出発地と到着地から Googleマップの経路を生成する
Googleマップの経路の方も、割とシンプルにハイパーリンク化ができます。
経路の方はURLの構成が
https://www.google.com/maps/dir/出発地/目的地
このようになっているので、例えば A2に出発地の住所、B2に目的地の住所があるなら、
=HYPERLINK("https://www.google.com/maps/dir/"&TEXTJOIN("/",true,A2:B2),"経路へGo")
このような式で生成できます。
スラッシュ区切りの出発地、到着地は、 TEXTJOIN関数でセルを連結するのがおススメです。
ちなみにこの方法は Yahoo乗り換えでも使えます。
電話番号やメールアドレスをハイパーリンク化する
電話番号やメールアドレスをハイパーリンクとして使う方法にも触れておきましょう。
たとえば顧客リストがあって、そこにメールや電話番号の情報もあった場合、セルのメールアドレスをクリックして メール送信（作成）が出来たり、スマホからシートを開いて電話番号タップで、電話が発信できたら便利ですよね？
Googleスプレッドシートで、こんなことが出来るのでしょうか？
Excelで メールアドレスや電話番号をリンクにする方法
Excelの場合は、どちらも割と簡単に出来ます。
メールに関しては WebサイトのURLと同様に普通にセル内にメールアドレスと見なされる文字列(xxxx@xxx.xx)が入っていたら、自動でハイパーリンク化されます。
中身を見ると mailto: メールアドレス となっているのがわかります。
この方式はHYPERLINK関数で利用することも可能で、
=HYPERLINK("mailto: dhc@gmail.com","DHCさん")
こんな感じで名前をクリックすると メール画面に飛ぶハイパーリンクが作成できます。
ちなみに、ここで起動するメーラーの設定は Win11の場合は
アプリ ＞ 既定のアプリ から、Microsoft Edge、または Google Chromeを選択、MAILTOの アプリを選択することで設定できます。
※Gmailを開いた時に アドレスバー 右端に ハンドラが表示される場合は、そこから設定も可能です。
しかし電話番号は、自動ではリンクになりません。
ハイパーリンク機能、または HYPERLINK関数で 電話番号の頭に
tel:
を付けることで リンク化します。HTMLと一緒ですね。
ただ、久しぶりにこの方法でスマホのExcel（Android版 365アプリ）から電話発信を試してみましたが、うまくいきませんでした。
ExcelをPDFでエクスポートしたものをタップすれば、電話発信可能でした。
あまり アプリ版の365は使わないので詳細は不明ですが、なにか設定が必要なんでしょうか？
Googleスプレッドシートで メールアドレスをハイパーリンク化
それでは Googleスプレッドシートの場合はどうか？まずはメールを見ていきましょう。
Excelと違って Googleスプレッドシーとでは、メールアドレスは自動でハイパーリンク化されません。
しかし ユーザーチップと同じようにメールアドレスと判別されるテキストは、マウスポインタをあてると、上のようにポップアップが表示され、ここのメールアイコンから メール作成画面が起動できます。
mirとしては、Excelの自動でメアドがハイパーリンクになって青字下線になる「おせっかい」は好きじゃないんで、Googleスプレッドシートの表示の方が好みです。
ちなみにアプリ版の対応ですが Android版であれば、ハイパーリンク化していないメールアドレスのセルをタップして表示されるメニューからメール送信ができます。
iPhone、iPad版は 同じように操作してもセルのメニューには「メールを送信」という選択肢が表示されません。
ただメアドをテキスト選択状態にすることで、iOS側の機能で メール作成画面に遷移することは可能です。
どうしても ハイパーリンク化したい場合は、リンクを挿入機能で
このように そのままメアドをリンク先に指定するか、
HYPERLINK関数の場合は mailto: をつけて
=HYPERLINK("mailto:"&A2)
このようにすることで、メールのハイパーリンク化が出来ます。
この状態であれば、iPhone、iPadからも セルメニューの「リンクを開く」で メール作成画面に飛ぶことができます。
Googleスプレッドシートで 電話番号をハイパーリンク化
次に電話番号ですが、WebアプリのGoogleスプレッドシートでは、telto: を頭に付ける方法ではハイパーリンクにはなりません。
「Googleスプレッドシート 電話 リンク」あたりで検索すると
https://ctrlq.org/call/
を電話番号の前に付ける方法がガイドされていますが、これもちょっと古いです。（こちらは現在は使えません）
では、どうすればよいか？
2024年3月時点で Googleスプレッドシートで 電話番号をハイパーリンク化して スマホタップで発信できるようにするには、
https://call.ctrlq.org/電話番号
このようにします。
どのような原理で電話が発信できるリンクになっているか、ロジックに関しては以下に説明があります。
こちらはリンクを挿入機能、HYPERLINK関数、どちらも同じように使えます。
=HYPERLINK("https://call.ctrlq.org/"&A2,A2)
実際の動きを Android アプリ版で見てみましょう。
このようにリンクURLをタップすると、一度Webに飛ばされたあと セルの電話番号が入った状態で電話アプリが開きます。
発信の手前で止まってくれるので、誤タップによる発信を防げるのは良いですね。
スプレッドシートで管理した名簿から 電話発信する人には便利じゃないでしょうか。
ちなみにこの https://call.ctrlq.org/ を頭に付ける電話番号ハイパーリンク化は、無料で使えるWeb版 Excel + スマホの365アプリでも使えます！
検索と置換で 電話番号をハイパーリンク化
余談ですが、既に電話番号がセルに入ってる状態で隣に列の追加が難しいという場合は、「検索と置換」＋正規表現で 電話番号の列を直接ハイパーリンク化をする方法もあります。
検索 (.+)
置換後の文字列 =HYPERLINK("https://call.ctrlq.org/$1","$1")
※正規表現を使った検索にチェック
このように設定し、「すべて置換」を実行することで、一気に文字列だった電話番号をハイパーリンク化できます。
以前noteで紹介した 正規表現のグループ化で セル内の電話番号の文字列を $1として 置換後に活用するテクですね。
ちなみにこのテクニック
Googleスプレッドシート 電話番号 ハイパーリンク
で検索した時のトップ表示のサイトで紹介されてるんですが
これの元ネタってmirの 知恵袋の回答
これじゃね？って思うんですよねｗ（違ったら失礼）
正規表現で電話番号判別を正しくやるなら
^([\d-]+)$
こうするんで、普通は (.+) なんて雑な書き方はしないような。（自分は面倒がって楽な書き方にしちゃうんで）
まぁ数式に著作権なんてものはないし、私も他のサイトを参考にして色々勉強させていただいてるんで別に構わないですがｗ
Googleスプレッドシートのハイパーリンクで Gmailのひな形も作れる
最後のハイパーリンクネタです。
先ほどの「メールアドレス発信のリンク」確かに便利なんですが、たとえば Aさんを Toに入れたときは、CCにBさんを入れたいとか、件名は コレにして、本文の出だしは○○としたい・・・、こんな感じでメールのひな形がハイパーリンクで作れたらもっと便利ですよね。
実はGAS不要で、 Googleスプレッドシートの ハイパーリンク機能でGmailのひな形を生成出来ちゃいます。
Gmailのひな形生成は、2024年3月現在だと
Gmailのテンプレート機能を使う
GoogleドキュメントのGmail下書き機能を使う
この2つの方法が用意されています。
特に後から機能として追加された GoogleドキュメントのGmail下書き機能は非常に優秀なんで、Googleスプレッドシートのハイパーリンクで Gmailのひな形を生成するメリットは低下したかなと思います。
でも、同じようなパターンのメールを送る業務が多い人には、今でも便利なハックネタなんで紹介しておきます。
上にあげた基本のGmailひな形機能2つは、また別の機械に noteで触れたいと思います。
今回はGoogleスプレッドシートのハイパーリンク機能で Gmailのひな形を作成する方法を紹介しましょう。
GmailのURL構成を理解する
https://mail.google.com/mail/?view=cmパラメーターの設定
Gmailを新規作成する画面は上のようなURLとなります。パラメーターの設定部分なしで
https://mail.google.com/mail/?view=cm
だけだと、普通に Toや件名の入っていない普通（プレーン状態）の Gmailの新規メール作成画面が開きます。
この冒頭のURLの後ろにパラメーターの設定を記述することで、ToやCC、件名、本文が入った 状態で Gmailの新規作成画面を開くことができます。
たとえば、
このようなメールを生成したいと思った場合
宛先： aaa@gmail.com
Cc： bbb@gmail.com
Bcc： ccc@gmail.com
件名： 致命的な隙
本文：
あああ 様
お世話になっております。mirです。
パラメーターを
&to=aaa@gmail.com
&cc=bbb@gmail.com
&bcc=ccc@gmail.com
&su=致命的な隙
&body=あああ 様
お世話になっております。mirです。
このように設定します。
つまりURLとしては
https://mail.google.com/mail/?view=cm&to=aaa@gmail.com&cc=bbb@gmail.com&bcc=ccc@gmail.com&su=致命的な隙&body=あああ 様お世話になっております。mirです。
こうなります。
noteの仕様上の問題なのか、このままリンクにしてもパラメーターが反映されない為、試したい場合は コピーしてアドレスバーに貼ってEnter してみてください。
設定部分は &と= で繋ぐ書き方なんで、前回のnoteで紹介した 特定セルへのハイパーリンク
#gid= シートID &range=セル位置のA1表記
と基本的には一緒ですね。
実際の動きを見てみましょう。
メール宛先の To,Cc,Bcc、さらに 件名、本文が指定した通り入力された状態で新規メール作成画面が立ち上がりました。
ただ指定した通りと書きましたが、本文がちょっと変わってますね。
【指定した本文】
あああ 様
お世話になっております。mirです。
▼
【結果】
あああ 様お世話になっております。mirです。
このように本文の 改行が無視されて詰まって しまいます。
これは改行のような特殊な文字は URLに渡す際にエンコードが必要となる為です。
たとえば LF（ラインフィード） の改行をエンコードすると %0A となります。
Googleスプレッドシートの ENCODEURL関数を使うと変換できます。（Excelにもある関数です）
変換できるといっても、いちいち変換するの面倒だなーと感じるかもしれませんが、Googleスプレッドシート内で このGmailひな形URLをハイパーリンクとして生成する方法であれば、URLエンコードは気にする必要はありません。
それではGmailひな形ハイパーリンクの生成方法を見ていきましょう。
Googleスプレッドシート作る メールひな形のゴール
生成方法の前に、スプレッドシートを使ったGmailひな形のゴール（完成形）を見ておきましょう。
右上のF1セルのプルダウンでパターンを切り替えると、toやccのアドレス、件名、本文などが連動して切り替わります。
パターンを選択してA1セルの「メール生成」のハイパーリンクをクリックすると、設定どおりにアドレスや件名、本文が入ったメールが生成されました。
注意点としては、この方法で生成したひな形はHTML形式ではなくプレーンテキストなので、太字や赤字など文字の装飾や、文中に写真を入れるといったことは出来ません。
また、ひな形でファイルを添付といったことも出来ません。（Gmail画面生成後に手動でやる分には問題ない）
そして、このGmailひな形生成 スプレッドシートのポイントが
名前付き範囲と INDIRECTを組み合わせ、プルダウンでパターン呼び出し
署名はひな形側で本文に入れ込む
数式でセルを連結し、パラメーター設定の文字列を生成
この3つです。
それぞれ見ていきましょう。
名前付き範囲と INDIRECTを組み合わせ、プルダウンでパターン呼び出し
まずは、実際にメールひな形のハイパーリンクを生成する「Gmail生成」シートと別に「ひな形パターン」シートを用意し、使いたいメールの構成のパターンを幾つか用意します。
「ひな形パターン」シートは、A列をパターン名として、B～F列の見出し行をパラメーターの
to cc bcc su body
としておきます。
to や ccはともかく、件名が su、本文が bodyだとちょっとわかりづらいですが、こうしておくことで後で数式で連結させるときにそのまま使えて便利なので、このような設定をおススメします。
toやcc、bccなどのメールアドレスは複数指定する可能性があるのと、本文は縦に長くなるのを考慮して、1パターン10行程度使うのが良いでしょう。
本文は縦にセル結合してしまいましょう。
2つか3つパターンを作ったら、実際に使うメールの構成、またはダミーで良いので 構成を適当に埋めておきましょう。
本文は出だし部分程度でOK。セル内改行する場合は、Alt+Enterで。
中身を埋めたら、名前付き範囲の機能で、パターン毎のデータ範囲（A列とタイトル行は含まない）だけを A列のパターン名を使って名前付きとします。
名前付き範囲には スペースを含めることは出来ないので、単語間を区切りたい場合は _ アンダーバーを使いましょう。
これで「ひな形パターン」シートの準備は完了です。
再び「Gmai生成」シートに戻り、F1セルにプルダウン（範囲内）を設定します。
プルダウンの選択肢の範囲を
='ひな形パターン'!$A:$A
このように「ひな形パターン」シートの A列とします。
Googleスプレッドシートのプルダウンは重複や余計な空白は除外してくれるので、これだけでプルダウンが完了します。
プルダウン機能の詳細は、過去noteを参照ください。
そして「Gmail生成」シートの3行目をタイトル行として、「ひな形パターン」シートと同じように
to cc bcc su body
として、E列のbodyの下のセル（本文が入るところ）を10行くらいセル結合しておきましょう。
最後に toの下 A4セルに
=IFERROR(INDIRECT(F1))
こちらの式を入れます。
INDIRECT関数は "シート3!A5:F10" などのセル位置情報の文字列をセル参照に変換する関数ですが、今回は「文字列で指定した名前付き範囲を参照する」という機能を使っています。
F1セルのプルダウンが何も選択していない時にエラーとなるのを回避する為、IFERROR関数を組み合わせています。
GoogleスプレッドシートのIFERROR関数は 第2匹数を省略した場合、エラーだったら空白を返すという設定になります。
F1セルのプルダウンで、メールひな形用の設定パターンを切り替える準備が出来ました。
署名はひな形側で本文に入れ込む
Gmailの署名機能は利用している人も多いかと思います。
設定した署名は Gmailを新規作成する際に 本文の下部に表示されます。
さらに 送信元のメールアドレスを複数もっている人であれば、送信元アドレスの切り替えに合わせて、それぞれに設定した署名に切り替わるという便利な機能です。
しかし、URLパラメーターで 本文を埋めた Gmailを生成した場合は、署名が入らないという欠点があります。
その為、ひな形の本文に署名も入れ込む必要があります。
Gmail生成シートの本文セルの隣、F4セル以下を本文セルと同じく10行くらいセル結合し、ここに署名をいれておきます。
先に書いた通り、署名もプレーンテキストとなります。文字の装飾は利用できません。
また、本文との間をあける為、署名の上に改行を2行くらい入れておくと良いでしょう。
メールパターンごとに 署名も変えたいという場合は、「ひな形パターン」の名前付き範囲に含めることも出来ます。
あとは、この各セルの文字列データを連結して、A1セルにGmailを生成するハイパーリンクを作る式を用意するだけです！
数式でセルを連結し、パラメーター設定の文字列を生成
式を作るだけです！と言っても、今回は
項目の切り替え部分は & で連結
見出しと中身（パラメーター）は = で連結
複数メールアドレスは , で連結
本文と署名は 改行 で連結
このように結構入り組んでいるので テキスト連結が厄介です。
A1セルに入れる式は 幾つか作り方はありますが、今回は一例として
=HYPERLINK("https://mail.google.com/mail/?view=cm&"&
TEXTJOIN("&",true,
BYCOL(A4:E,LAMBDA(c,OFFSET(c,-1,0,1)&"="&TEXTJOIN(",",TRUE,c))))&CHAR(10)&F4,"メール生成")
こんな式を紹介しましょう。
冒頭部分の
=HYPERLINK("https://mail.google.com/mail/?view=cm&"
お尻の
,"メール生成")
ここはGmailひな形のハイパーリンクを作って、セル上は 「メール生成」と表示させるってことで、大丈夫ですよね。
この間に入る式ですが、まずは
BYCOL(A4:E,LAMBDA(c,OFFSET(c,-1,0,1)&"="&TEXTJOIN(",",TRUE,c)))
この式から。
これは A4:Eを列毎に連結させる 数式です。
数式の結果をシートに出力させた1行目を見ればなんとなくわかるかと思いますが、BYCOL関数で列毎に 4行目以下を " ," カンマで連結して、そのデータの頭に OFFSET(c,-1,0,1) で取得した列毎の 3行目（見出し行）を "=" で繋げています。
LAMBDAヘルパー関数の BYCOL関数については、Googleスプレッドシート輸入時に紹介しています。
この列毎に生成された連結した文字を、さらに TEXTJOINで & で連結し一つの文字列とします。
最後に 署名F4をお尻に改行で連結して完成。
=HYPERLINK("https://mail.google.com/mail/?view=cm&"&
TEXTJOIN("&",true,
BYCOL(A4:E,LAMBDA(c,OFFSET(c,-1,0,1)&"="&TEXTJOIN(",",TRUE,c))))&
CHAR(10)&F4,"メール生成")これで A1セルのリンクをクリックすることで、設定を反映させたGmailが生成される Gmailひな形生成ハイパーリンク式が完成しました。
使えそう！って思った人は試してみましょう。
【有料コンテンツ】Googleスプレッドシートで作成した Gmailひな形ハイパーリンクを楽に使いたい！
全3回にわたってGoogleスプレッドシートのハイパーリンクの活用を紹介してきました。
参考になれば幸いです。
で、最後に紹介した Gmailハイパーリンクネタですが、上の手順の通り作成すれば無料で使えます。
でも、面倒だからコピーしてすぐ使えるシートが欲しい！って人向けに、 有料コンテンツとして今回のGmailひな形生成Googleスプレッドシート を配布いたします。
解説は全て無料 部分に記載しているので、有料コンテンツに さらなる情報はありません。
自分で作るのは面倒だから 出来上がったスプレッドシートを使いたい
単に mir を応援したいんで 課金しますという足ながおじさんな人
こんな方だけ 有料コンテンツをご利用ください。
多少なりとも 有料コンテンツ利用者がいると、モチベーションにもつながりますし、執筆にも力が入りますので感謝感謝です。
ハイパーリンクネタは今回で終了で次回は違うネタを書きたいと思います。
ここから先は
¥ 200
この記事が気に入ったらサポートをしてみませんか？