2022/01/26に更新

【GASで便利に】シート内の全てのリンクを抽出する

スプレッドシート小技

シート内に存在するハイパーリンク(URL)を取り出すGoogle Apps Scriptです。リンクを全て抜き出したくなったので作成してみました。

こんな悩みを解決します


  • シート内にあるリンクを取得したいが、探すのが大変。

用意するもの


特にありません。今お使いのスプレッドシートに組み込めます。

このスクリプトで出来ること


  • 現在開いているシートにあるハイパーリンク(URL)を探して、一覧表示します。
  • 探すのは「リンク状態」になっているものだけです。「単なるURLの文字列」は拾いません。

使い方


1.スクリプトの実行

以下のようなテストデータを作成しました。

  • 表示名が設定されたハイパーリンク
  • 自動的にリンクになった文字列
  • リンクになっていない、ただの文字列
  • 1セルに複数のリンクが設定されている

スクリプトを実行します。メニューから「シート内のURLを抽出する」を実行します。

2.結果の確認

少し待つと、ポップアップでURLのリストが表示されます。
リンクではないただの文字列だったsoumu.go.jpは拾っていませんね。セル内の複数リンクはきちんと取得できています。
シート内のデータが多いとGASのメソッド呼び出し回数が増えてしまうので、時間がかかる(もしくはタイムアウト)してしまうかもしれません。

スクリプト


/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00039/index.html
 */

function onOpen() {
  // スプレッドシートを開いたときに実行される関数

  // UIの取得
  const ui = SpreadsheetApp.getUi()

  // メニューの表示名
  const menu = ui.createMenu('スクリプト実行');

  // メニューに追加するボタン
  menu.addItem('シート内のURLを抽出する', 'extract_hyperlink_from_sheet');

  // メニューを画面に追加する
  menu.addToUi();
}

function extract_hyperlink_from_sheet() {

  // アラートを表示するためにUIを取得
  const ui = SpreadsheetApp.getUi();

  // アクティブなスプレッドシート、シートを取得
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // シート内でデータが入力されている範囲を取得
  let range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());

  // セルのリッチテキスト情報を取得する
  const values = range.getRichTextValues();

  // 結果を格納する配列
  let result = [];

  // セルからURLを取り出す処理
  for (let i = 0; i < values.length; i++) { //行のループ

    for (let j = 0; j < values[i].length; j++) { //列のループ

      // メソッド呼び出しを減らすため、セルに値が入っている場合だけURL取り出し処理を行う
      if (values[i][j] != '') {

        // 空白ではないセルのrangeを取得
        range = sheet.getRange(i + 1, j + 1, 1, 1);

        // runsを取得。runsにはリッチテキストの要素が配列で入る
        let runs = range.getRichTextValue().getRuns();

        // runsの配列をループして、リンクを取得する。
        for (let k = 0; k < runs.length; k++) {

          // リンクを取得
          let link = runs[k].getLinkUrl();

          // リンクが入っていれば結果の配列に追加
          if (link != null) {
            result.push(link);
          }
        }
      }
    }
  }

  if (result.length == 0) {
    ui.alert('リンクはありませんでした');
  } else {
    ui.alert('以下のリンクが見つかりました', result.join('\n'), ui.ButtonSet.OK);
  }

}

関連するアプリ


【GASで時短】結合されたセルを検索&解除する

選択範囲に存在する結合されたセルを検索し、解除することもできるGoogle Apps Scriptです。予想外の結合によって困ったことがある方は多いと思います。このアプリが転ばぬ先の杖になれば幸いです。結合を解除するかどうかはダイアログで選択できます。

【GASで便利に】キーワードを含むセル番地を一覧表示する

検索キーワードを全シートから探して、ヒットしたセル番地をまとめて表示するGoogle Apps Scriptです。標準の検索機能ではヒットしたセルが1個ずつ分かりますが、まとめて知りたくなったので作成しました。

【GASで時短】選択範囲に存在する「改行」を検索&削除する

選択範囲に改行が含まれているセルを検索し、削除することもできるGoogle Apps Scriptです。想定外の改行によって困ることが稀によくありますので作ってみました。改行を削除するかどうかはダイアログで選択できます。

【GASで時短】データ・文字が入っていない行を削除する

シート内の「何も書き込まれていない行」を削除してくれるGoogle Apps Scriptです。スプレッドシートをメモ帳代わりにしていると、特に意味のない空白行がたくさんあったりして、消すのが面倒だったので作成してみました。すべての列に何も文字が入っていない行を消し去ります。

タグ一覧

最新のアプリ

人気のアプリ

myfunc.jp