スプレッドシートでリンクが設定されたテキストからURLを抽出する方法

スプレッドシートでリンクが設定されたテキストからURLを抽出する方法

普段はあまり使うことはないかもしれませんが、サイトのページ一覧やサイトマップを整理したりするときに、ハイパーリンクがついたページ名の一覧などをコピーしてスプレッドシートに貼り付けをし、その隣の列に URL(アドレス)を抽出したいという場面が稀にあります。一度経験してしまえば「こんな簡単に一気にできるんだ!」と感動するのですが、初めて行う時には頭を抱えてしまうところです。

この記事は実際にどんな風にしたらリンク付きのテキストから、 URL を抽出して別の列に表示できるようにするのかを解説します。

リンク付きのテキストから URL を抽出して別の列に表示できるようにするために使うもの

  • Google スプレッドシート
  • Apps Script

使い方は後ほど解説します。

実際のやり方

多数のセルでハイパーリンクのURLを抽出して別の列に表示する必要がある場合、Google Apps Scriptを使用して一括処理を自動化することができます。

以下のスクリプトは、指定された範囲内のセルに設定されたハイパーリンクのURLを抽出し、それを隣接する列に出力する例を示しています。

function extractUrlsAndOutput() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A1:A"); // A列のデータ範囲を指定
  var values = range.getValues(); // A列のすべての値を取得
  
  // リッチテキスト値を取得してURLを抽出
  var urls = values.map(function(row, index) {
    var cell = sheet.getRange("A" + (index + 1));
    var richTextValue = cell.getRichTextValue();
    var runs = richTextValue.getRuns();
    for (var i = 0; i < runs.length; i++) {
      var url = runs[i].getLinkUrl();
      if (url) {
        return [url]; // 最初に見つかったURLを返す
      }
    }
    return [""]; // リンクが見つからない場合は空文字を返す
  });
  
  // 抽出したURLをB列に出力
  var outputRange = sheet.getRange(1, 2, urls.length, 1); // B列の出力範囲を指定
  outputRange.setValues(urls);
}

上の内容をコピーしてApps Scriptで実行していきます。

スクリプトの使用方法・作業手順

スプレッドシートでリンクが設定されたテキストからURLを抽出する方法

  1. スプレッドシートの A 列にハイパーリンク付きのテキストを貼り付けます。
  2. 「拡張機能」メニューから「Apps Script」を選択します。
  3. 上記のスクリプトをApps Scriptエディタに貼り付けます。
  4. スクリプトを保存し、エディタの再生ボタン(▶)をクリックしてextractUrlsAndOutput関数を実行します。
  5. スクリプトが実行されると、A列にあるハイパーリンクのURLがB列に自動的に出力されます。

今回は関数は使わずにApps Scriptで実行することで処理を走らせるタイプのやり方でした。

まとめ

Google スプレッドシートだけではなかなかできないことも、Apps Scriptを利用することでできちゃうこともあります。

リンクが設定されたテキストから URL だけを抜きたい 場面 というのはそんなに多くはないかもしれませんが、このような方法もあるので参考にしてみてください。