スプレッドシート上でURLを一括取得したいときは、GAS（Google Apps Script）を使うのが便利です。GASは、さまざまなGoogleサービスと連携できるだけでなく、Webサイト上の情報を自動で収集するスクレイピングも可能なので、業務効率化とも相性が良いと注目されました。
この記事では、GASを利用したスプレッドシートでURLを取得する関数を紹介します。
スプレッドシートで利用するGASとは？
GAS（Google Apps Script）とは、Googlel社が開発・提供しているプログラミング言語です。GooglelスプレッドシートやGmail、Googlelドライブ、Googlelカレンダー、Googlel翻訳などGooglel社が提供する各種サービスと連携でき、業務の自動化が可能です。
例えば、Googlelスプレッドシート上に蓄積した店舗の売上データを集計したり、データに基づいて請求書を自動作成したりする際に役立ちます。また、スプレッドシート上のURL自動取得も可能です。
GASはGooglelアカウントを保有している方であれば、無料で使えます。専用の開発環境も不要なので、設備投資も要りません。GASはGooglelスプレッドシートの「拡張機能」→「Apps Script」から設定できます。
関連記事：【図解】スプレッドシートでファイル共有する設定方法を解説！便利テクニックも紹介
スプレッドシートの全URLを取得する方法
スプレッドシートの全URLを取得するには、下記の方法がおすすめです。「全シートのURLを一気に抽出してリンクにしたい」という場合に、活用していきましょう。
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);
}
}
ハイパーリンク付きのURLを取得する方法
ハイパーリンク付きのURLを取得する際は、下記を活用します。ハイパーリンクの中身であるURLだけ取得できます。
A列にあるハイパーリンク付きセルからURLを抽出してB列に出力するコードであり、特定セルの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);
}
ファイル名を指定してシートのURLを取得する方法
ファイルやフォルダ名を指定してシートのURLを取得したいときは、下記を活用します。なお、「let fileName = file.getName();」でファイル名の取得、「let fileId = file.getId();」でファイルIDの取得もできるので、シートURL以外も同時に取得したいときに活用しましょう。
function getFiles(){
// フォルダの指定
const folderId= ‘[フォルダID]’;
//フォルダ内のすべてのファイルを取得
const folder = DriveApp.getFolderById(folderId);
const files = folder.getFiles();
//各ファイルごとに出力
while(files.hasNext()){
let file = files.next();
let fileURL = file.getUrl(); // ファイルURL
console.log([fileName,fileId,fileURL]);
}
}
スプレッドシートのGASを利用した事例
GooglelスプレッドシートのGASは、下記の業務に役立ちます。代表的な活用法を紹介するので、自社で課題になっていることと一致する場合はGASの利用を検討してみましょう。
定型業務の自動化
定型業務を自動化することで、工数・人員・コストの削減に貢献します。
例えば、Googleスプレッドシート上のデータを別のファイルに転記できます。人間が実施するとコピー＆ペーストのミスや二重登録などが発生する可能性がありますが、GASで自動化すればヒューマンエラーを防げます。売上や在庫の管理・人事評価・アンケート集計などさまざまなシーンで役立つ方法です。
また、Googleスプレッドシートをガントチャートのように活用し、タスク期限までの残り日数に応じてセルの色分けをすることも可能です。色別に緊急度合いがわかるため業務を可視化しやすく、タスク管理の手間を大幅に省きます。
GASによる自動化は、DXにも最適です。DX（デジタルトランスフォーメーション）は社内の業務効率を見直し、働き方を変革していく取り組みです。定型業務を効率化することは工数、人員、コストの削減につながります。
Webアプリケーションの開発
GASを使ってWebアプリケーションを開発・構築し、インターネット上に公開することも可能です。例えば、Gmail上の署名情報を自動で読み取ってスプレッドシート上に転記するアプリケーションを開発すれば、取引先情報の自動管理ができます。名刺管理などの手間もなくなり社内共有もしやすくなります。便利なアプリケーションとして、活用できるでしょう。
またスプレッドシート上のタスク情報をGoogleカレンダーに登録するなど、別のツールに情報を移動させるアプリケーションを開発する方法もあります。自社の業務課題に合わせてアプリケーション開発してみましょう。
ウェブスクレイピング
ウェブスクレイピングとは、Webサイト上から特定のデータだけを抽出する作業のことです。例えば商品名、価格、サイズ、容量や特定キーワードなどを自動抽出でき、手作業で情報収集する手間がかかりません。
そのためECサイト、オークションサイト、フリマサイト上のデータを収集し、スプレッドシート上に転記するなどの作業を自動化できます。定期スクレイピングには、サーバーレンタルなどが必要なことも多いですが、GASであれば無料で実行できるのもメリットです。
よくある質問
最後に、GASを利用したスプレッドシート利用に関して「よくある質問」を紹介します。
スプレッドシート上にWebページの情報を抽出することはできますか？
スプレッドシート上に、Webページの情報を抽出することも可能です。ウェブスクレイピングのためGASを利用している方も多いので、活用してみましょう。
例えば「OneChat Blog」上のページに特定のキーワードが入っているか抽出したいときは、下記の方法を試します。
【1】抽出したいWebサイト上で右クリックし、「検証」を選択します。
【2】セレクタを有効にします。
【3】今回は「運営会社」をキーワードに抽出します。左画面の該当部分をクリックし、コードが表示されていることを確認します。
【4】右クリックで「Copy」から「Copy XPATH」を選択します。Copy XPATHとはWebページ上の住所を表すコードであり、スプレッドシート上でデータ収集する際に利用します。
【5】Googleスプレッドシート上に抽出元のURLと、【4】でコピーしたXPATHを貼り付けます。IMPORTXML関数を使うと、該当キーワードの有無が抽出されます。
IMPORTXML関数はGoogleスプレッドシート上で利用できる独自の関数であり、Webサイト上から指定の条件に合う情報を抽出する際に使います。「=IMPORTXML(URL, XPATH)」と入力します。
シートIDを取得する際のgetSheetid()の使い方を例と一緒に知りたいです。
シートIDを取得する際には、「getSheetid()」が便利です。例えば、下記のように使います。
// 現在アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
// そのスプレッドシートにある最初のシートを取得
var sheet = ss.getSheets()[0];
// そのシートの ID を取得しログに出力
Logger.log(sheet.getSheetId());
URLを取得する際に関数ではなくGASを利用するメリットはなんですか？
関数を使ってURLを取得できますが、GASを利用するメリットは自動化にあります。スクリプトの実行を自動化できるため、関数を実行するときのように手作業が不要になります。
また、Google社が提供する各種サービスと連携できることもGASの強みです。「Googleかカレンダー上の情報を読み取ってGoogleスプレッドシート上に転記する」など、サービスをまたいだデータ抽出をしたいときに活用しましょう。
まとめ
スプレッドシートでURLを取得する際は、GASを利用するのがおすすめです。スクレイピングを自動化できるだけでなく、Webサイトやスプレッドシート以外のGoogleサービスと連携でき、活用の幅が広くなります。
抽出できるデータも多いので、自社の課題に合わせて活用していきましょう。
スプレッドシートURLに関する重要用語
| 用語 | 意味 |
| GAS | 「Google Apps Script」の略称。Google社が開発・提供しているプログラミング言語であり、業務自動化によく使われる。 |
| ウェブスクレイピング | Webサイト上から特定のデータだけを抽出する作業のこと。商品名・価格・サイズ・容量や特定キーワードの抽出に最適。 |
| XPATH | XTML形式の文書から特定の部分を指定して取得するための簡易言語。 |
| IMPORTXML関数 | Googleスプレッドシート上で利用できる独自の関数。Webサイト上から指定の条件に合う情報を抽出できる。 |
