【GAS】Google Apps Script 活用事例 Maps Serviceで、住所を一括で調べて大学一覧を作りました。
新しい会社に転職して、最初の1週間が過ぎました。前職では、労務系の色合いが濃かったのですが、打って変わって、現職は採用の事務サポートになりました。仕事内容も、これでもかってくらい、180°変わりました。一貫したキャリアという点では、良くないのかもしれません.....。
守秘義務があるので業務の詳細を書くことは、出来ませんが、採用のサポートという事で、応募者の大学名を調べたり、まとめたりする機会が増えました。大学の一覧がCSVとかで欲しいなぁと思うものの、なかなか検索で探し当てられない......現状、WebサイトとSpreadsheeetを行き来する、コピペ地獄を繰り返す事になってしまっていたので、何とかしたいなと思っていました......泣。
そこで文部科学省が公開している情報から抽出物し、統合して（多少の手作業を挟みましたが）、大学の一覧を作りました。
欲言えば、大学の就職サポート課のメールアドレスや連絡先も欲しいと思っていますが.....それを自動で取得するのは今の僕の力量だと辛い......。
最初は、大学のWebサイトのフッターに小さな字で書かれている住所から抽出する事を、ずっと考えていたのですが......複数の構造が異なるWebサイトから情報を抽出するのって、そりゃ、素人じゃ無理だわ.....シュン......。ってなりました。
UrlFetchAppを試してみるも、上手くいかず。IMPORTXML関数も上手くいかなかったので、IMPORTDATA関数でソースコードをスプレッドシートに転記した後、REG系の関数でaタグのみを抽出するという方法でDBを作りました。
<p><a href="http://www.seian.ac.jp/">成安造形大学（※成安造形大学ウェブサイトへリンク）</a><br />大学のWebサイトを抽出する
＝SUBSTITUTE(REGEXREPLACE(REGEXEXTRACT(E84,"<a href.*"),">.*",""),"<a href=","")<a href="hogehoge.ac.jp"
上記を、正規表現で抽出し、SUBSTITUTE関数で消しています。残りは、置換を駆使して、消していきます。
大学名を抽出する。
jp/">成安造形大学（
上記のように抽出した後、不要な文字列を消しています。上手いかない場合は、置換で地道に消していきます。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REGEXEXTRACT(F84,"jp.*（"),"jp/",""),"（",""),">","")
正規表現を使用した検索にチェックマークをつけてください。それから特定の範囲にする事も忘れずに
完成系のソースコード
シートのA列に書いてある大学名の住所を一括で調べて貼り付ける事を想定して書いたコードです。
function findAddress() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('DB');
const values = sheet.getDataRange().getValues();
values.shift();
//住所情報を格納するための配列
let newValues = [];
for(let i = 0; i < values.length; i++){
if(!values[i][0])continue;
const maps = Maps.newGeocoder()
.setLanguage("ja")
.geocode(values[i][0]);
//大学名をMaps Serviceで検索し、得られた住所情報を配列に格納する
const address = maps.results[0].formatted_address;
address.replace('日本、','');
console.log(address);
newValues.push([address]);
}//for
sheet.getRange(2, 4, newValues.length, 1).setValues(newValues);
}//end
無料枠だと実行回数に限界があり、確か2500回、Map Serviceを使うとエラーになってしまいます。大学が現時点で600近くあるので、実質、4回くらい。スクリプト実行時、大学名が空白だったら、処理をスキップというのを書いていなかったせいで、すぐに上限に達してしまいました。
圧倒的に、何かを調べたりする機会が増えたので、Webサイトをスクレイピングしたりしたいなぁと思うことがしばしば。Python勉強したいなぁ....と思うこの頃です。
追記：Webサイトから、就職課の関連ページを、見つけ出す
結論から言うと、https://****university.ac.jp 左記が、元のURLだった場合、大体、8割近くは、https://****university.ac.jp/carrerってやれば、キャリアセンターないし、就職活動のページに遷移します。
=ARRAYFORMULA(IF($A$2:$A<>"",HYPERLINK($B$2:$B&"/career"),""))すげー無駄な事やってました。
https://****university.ac.jp にアクセスして、HTMLのソースコードを文字列で取得、キャリアの文字列を探して、それをシートに書き出すなんて、クソ無駄な事をやっていました。それで休日を潰しました。俺は、一体何やってんだ。でもすごい勉強になりました。
一人でやっていると、そういう無駄に、気付きにくい.....。
今後の使い分けとして、listやtableタグであれば、IMPORTHTML関数を使ってしまう。divタグなどであれば、GASを使って処理をしようと思います。
Parserを使って、Webサイトから任意の情報を抽出する。
こういう感じで、ダイアログに入力された大学のwebサイトから、キャリアの文字列があったら、正規表現を駆使して、URLを生成。それをシートに書き出すという処理をしています。
/*
* parser
* M1lugvAXKKtUxn_vdAG9JZleS6DrsjUUV
* 参考URL
*
*/
function getUrl() {
const ui = SpreadsheetApp.getUi();
const response = ui.prompt(
'大学HPのURLを入力してください。',
'（例）https://********.ac.jp',
ui.ButtonSet.OK
);
const inputName = response.getResponseText()
console.log(inputName);
switch (response.getSelectedButton()) {
case ui.Button.OK:
console.log('%s と入力され、OKが押されました。',inputName);
break;
case ui.Button.CLOSE:
console.log('閉じるボタンが押されました。');
}//switch
return inputName;
}
//就職または、キャリアセンターのURLを取得する。
function getCareerUrl() {
let url = getUrl();
console.log(url);
const response = UrlFetchApp.fetch(url);
const html = response.getContentText();
//console.log(html);
let newValues = [];
let count = 0; //異なる大学になったら、+1をする
let sameRecords = 1; //同じ大学のURLかどうかを判定するための数値
//paserでの処理
const lists = Parser.data(html).from('<li>').to('</li>').iterate();
//console.log(lists);
for(const list of lists){
if(list.indexOf('キャリア')!== -1){
//キャリアに一致した文字列から、aタグを削除し、任意のURLを作成
const part = list.match(/.*">/)[0]
.replace('<a href="','')
.replace('">','')
.replace('<span>','');
const targetUrl = url + part;
console.log(targetUrl);
//同じ大学のURLかどうかを判定するための数値
newValues.push([sameRecords, targetUrl]);
//異なる大学のURLを調べる段階で、数値を+1する。
if(count === lists.length){
sameRecords += 1;
}//if
}//if
}//for
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const targetSheet = spreadsheet.getSheetByName('parser');
const targetRow = targetSheet.getLastRow() + 1;
targetSheet.getRange(targetRow, 1, newValues.length, newValues[0].length).setValues(newValues);
}//end超クソ無駄その2 for文を回す.....初めて、起動時間の制限を超えました。
もっと違うやり方、例えば、こういう風に出来るんじゃない？と言ってくれる知人・友達・家族はマジで大切.....という事を痛感した週末でした。
/*
* parser
* M1lugvAXKKtUxn_vdAG9JZleS6DrsjUUV
* 参考URL
* https://www.kotanin0.work/entry/2019/01/06/200000
* https://qiita.com/takaito0423/items/259097b55b026800c875
*/
//キャリアセンターのURLを書き込む
function getTargetUrlList(){
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('住所を調べたい大学名');
const values = sheet.getDataRange().getValues();
values.shift();
//一次元配列を返す
return values.map(record => record[1]).filter(value => value);
}
//就職または、キャリアセンターのURLを取得する。
function getCareerUrl() {
const urlArray = getTargetUrlList();
console.log(urlArray);
let newValues = [];
for(const url of urlArray){
const response = UrlFetchApp.fetch(url);
const html = response.getContentText();
//console.log(html);
let count = 0; //異なる大学になったら、+1をする
let sameRecords = 1; //同じ大学のURLかどうかを判定するための数値
//paserでの処理
const lists = Parser.data(html).from('<li>').to('</li>').iterate();
//console.log(lists);
for(const list of lists){
if(list.indexOf('キャリア')!== -1){
//キャリアに一致した文字列から、aタグを削除し、任意のURLを作成
const part = list.match(/.*">/)[0]
.replace('<a href="','')
.replace('">','')
.replace('<span>','');
const targetUrl = url + part;
console.log(targetUrl);
//同じ大学のURLかどうかを判定するための数値
newValues.push([sameRecords, targetUrl]);
//異なる大学のURLを調べる段階で、数値を+1する。
if(count === lists.length){
sameRecords += 1;
}//if
}//if
}//for
}//for
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const targetSheet = spreadsheet.getSheetByName('paser');
const targetRow = targetSheet.getLastRow() + 1;
targetSheet.getRange(targetRow, 1, newValues.length, newValues[0].length).setValues(newValues);
}//end自分が過去に書いていたこの記事が、今回役に立ちました。
正規表現を使った関数については、こちらで解説しています。
この記事が気に入ったらサポートをしてみませんか？