初心者でもわかるGoogle Apps Script活用のススメ

GASを使って一覧表からハイパーリンクURLを一瞬で抽出する

サイト管理人

Google Discoverのフィードに下記の記事が流れてきていたので、Google Apps Scriptを活用したソリューションをご紹介したいと思います。 スプレッドシートでハイパーリンクからURLを抽出する方法は結局これが一番簡単なんじゃないかな？ – 株式会社LIG 実はこの会社、私が以前転職活動をしていた時に事業内容などが私がやってみたい内容だなぁと感じて、なんとか面接まで行きたい！と思って転職エージェントの担当者に相談した会社だったのです。そこで担当者から頂いたアドバイスで『熱い志望理由』をA4サイズ1枚でまとめて提出した会社でしたので、なんとなく会社名が目に飛び込んできて記事をクリックしていたのでした。 熱い志望理由は何度も修正を重ねて数時間かけて書きましたが、結果は書類審査落ちでしたけどね（笑） まぁ私の個人的なくだらない話は置いといて、今回は記事にあるようなハイパーリンクからURLを効率よく取得するGASを活用したソリューションをご紹介致します。 記事中では、58件のURL抽出を手動で効率化した場合は2分弱、外部のサービスを利用した方法では最終的に約35秒で完了できたとありますが、今回ご紹介する方法では1000件でも約2秒でできます！ あとでコードサンプルも記載しますので、使いたい方はぜひご活用ください。 今回はSpreadsheetAppのgetRichTextValues()というメソッドを使ってハイパーリンクからリンクURLを取得していきます。 こちらのメソッドはその名の通り、リッチテキストの値を取得するためのメソッドですが、そもそもリッチテキストって何が取得できるのか？よくわかりませんよね。簡単にいうと、テキストや書式を含むデータをまとめて取得できるメソッドです。取得したリッチテキストからハイパーリンクのURLを抽出するgetLinkUrl()というメソッドもGASには用意されていますので、今回はこの機能を利用します。 先述したgetRichTextValues()では二次元配列にてリッチテキストデータが取得できますので、取得した配列をループ処理で必要なURLデータを取得していきます。

サンプルコード

サンプルコード
//ハイパーリンクからURLを取得するコード
function getUrlFromHyperlink() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var data = range.getRichTextValues();
  var array = [];
  var lastRow = sheet.getLastRow();
  for (var i = 1; i < lastRow; i++) {
    var url = data[i][0].getLinkUrl(); //A列にあるハイパーリンクURLを取得
    array.push([url]);
  }
  range = sheet.getRange(2, 2, array.length, 1); //B2セルからURLを出力
  range.setValues(array);
}
こちらのコードでは、A列にあるハイパーリンク付きのセルからURLを抽出してB列に出力する用になっています。 ハイパーリンクの含まれる列がA列以外であれば、data[i][0].getLinkUrl()の赤い数字部分を変更してもらえれば対応が可能です。（配列なので0から数字がスタートします） 0 = A列 1 = B列 2 = C列 URL出力する列を変更したい場合も、sheet.getRange(2, 2, array.length, 1)の赤い数字部分を変更してもらえれば対応が可能です。（こちらは配列ではないので1から数字がスタートします） 1 = A列 2 = B列 3 = C列

上記コードをスクリプトエディタに貼り付けて実行してもらうことで開いているシートのハイパーリンクからURLを取得して出力することができます。

スクリプトエディタの開き方

スプレッドシートを開き、上部メニューの中から『ツール』→『スクリプトエディタ』をクリックする

開けたら、code.gsというものが開かれているので、function myFunction() {}と書いてある部分をすべて選択して、上記コードを上書きします。

上書きができたらCtrl + Sを押すか、保存用アイコンをクリックしてデータを保存します。

次に『関数を選択』という部分をクリックして、『getUrlFromHyperlink』を選択し、▶ボタンをクリックします。

 

ここまでできれば、あとはプログラムを実行する許可を与えれば自分の環境でプログラムを実行できます。

 

許可の手順はこちらの投稿（別ウインドウで開きます）でスクショ付きでまとめていますので、ご参考にしてください。

プログラムの実行が終了すると、ハイパーリンクに設定したリンク先URLが出力列（デフォルトではB列）に出力されます。

何をするにしてもラクをしたい私ですが、継続してラクをするためには意外と色々なことを調べてます。

まとめ

今回は、ハイパーリンクで設定したリンク先のURLを抽出し、指定の列に出力するGASのソリューションをサンプルコード付きでご紹介しました。私自身は今までにこのようなことをする必要性がなかったので、思いつきませんでしたが、具体的な運用で不便を感じている物があればお悩みを共有いただければなにかしらソリューションを考えてみますので、お悩みをお持ちの方は何でもご相談くださいね。投稿のコメント欄からでも構いませんし、メールを送ってもらっても構いません。

本当は早くSpreadsheetAppのリファレンスも拡充したいと思っているのですが、なかなか時間が取れずまだ全然リファレンスが完成していません。こういうのってスピードが大事ですよね。。誰かモチベーションの維持方法教えて下さいｗ

 

 

 

Exit mobile version