GoogleAppsScript

スプレッドシートでハイパーリンクからURLを抽出する方法[Spreadsheet公開]

Google spreadsheet
www.google.com f:id:airwho:20200811002523p:plain:w200

ハイパーリンクのURLを抽出するスプレッドシートを作成したので、サンプルテンプレートを公開します。
docs.google.com

使い方

シートをコピー

使うときは、自分のGoogleDriveにコピーします。
[ファイル][コピーを作成]
f:id:airwho:20200811004104p:plain:w300
コピーしたシートは、編集可能なシートになります。

①A列は、ハイパーリンクを取得したいデータ

コピーしたシートの説明をします。

A列は、取得したいハイパーリンク付きのデータです。
試しに、取得したいエクセル、スプレッドシートのセルをコピペしてみてください。

②getHyperlinkUrl関数

コピーしたシートの説明をします。
B列は、getHyperlinkUrl関数が入っています。

getHyperlinkUrl関数は、
ハンパーリンクのURLを取得する関数です。

Spreadsheetの関数ではなく、自作したスクリプトになります。

③getHyperlinkUrlChk関数

コピーしたシートの説明をします。
C列は、getHyperlinkUrlChk関数が入っています。
こちらも自作スクリプトです。

getHyperlinkUrlChk関数は、
ハンパーリンクのURLを取得
 ↓
そのURLの有効性をチェック
 ↓
有効ならURLを表示。無効なら空白を表示。
を行う関数です。

スクリプトを確認する

スクリプトを開く

f:id:airwho:20200811005829p:plain:w300

function getHyperlinkUrl

あまり深いことは考えず「getLinkUrl」でハイパーリンクを取得しています。
いろいろ試しましたが、リッチテキストで渡すとハイパーリンクを取得できました。
もっとスマートな書き方がありましたら、ご教授いただけると嬉しいです。

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

// リッチテキストを取得
var range = sheet.getRange(row,column);
var richText = range.getRichTextValue();

// ハイパーリンクURLを取得
return richText.getLinkUrl();

function getStatusCode

アクセスできるURLか、どうかをチェックしています。
URLへアクセスし、HTTP レスポンスステータスコードを見てます。
developer.mozilla.org

function getHyperlinkUrlChk

先の「getHyperlinkUrl」に、「getStatusCode」を追加したものです。

400, 500番台をエラーとしているため、(statuscode >=400)としています。

// ハイパーリンクを取得
const url = getHyperlinkUrl(row,column);

// URLがない場合:返す
if(!url) return;

// URLがある場合:URL Response Codeを取得
const statuscode = getStatusCode(url);

// URL Response:エラー系ならコードを返す
// コメントアウト if(statuscode >=400) return statuscode;
// URL Response:エラー系なら空を返す
if(statuscode >=400) return;

// URLを戻す
return url;

function getUrlFromHyperlink

※こちらは現在、頭にretrunがついているので、コードの中身は動いていません。
使用するときは「return」を削除、コメントアウトしてください。
f:id:airwho:20200811012749p:plain:w300
コードを変更したら[保存]します。

A列にあるハイパーリンクURLを取得し、B列にURLを記載する関数です。
getHyperlinkUrl関数、getHyperlinkUrl関数は、関数を書かなければならないですが、
こちらのgetUrlFromHyperlinkは、マクロで実行すことでURLを取得できるので、作業時間が短縮できます。

マクロの実行方法

①マクロを開く

[ツール][マクロ][インポート]
f:id:airwho:20200811011839p:plain:w300

②マクロを選択する

getUrlFromHyperlinkの[関数を追加]
f:id:airwho:20200811013033p:plain:w300

③実行するシートを用意

A列にハイパーリンク付きのデータを入力します。

④マクロを実行

[ツール][マクロ][getUrlFromHyperlink]
f:id:airwho:20200811013215p:plain:w300

認証します。
[続行]
f:id:airwho:20200811013450p:plain:w300

[詳細を表示]
 ↓
[スプレッドシートでハイパーリンクからURLを抽出(安全ではないページ)に移動]
f:id:airwho:20200811013609p:plain:w300

アプリケーション[許可]
f:id:airwho:20200811013745p:plain:w300

B列にリンクが表示されました。
f:id:airwho:20200811013915p:plain:w300

参考サイト

ありがとうございました。
ja.raw3h.net