前回は、スプレッドシート上に設置したボタンを押すと、指定行から指定行にセルの内容をコピペするスクリプトを作成しました。
今回は、開始行と終了行を指定して、その間を連続で処理するスプリクトにアップデートしたいと思います。
・・・
いよいよここまで来たか！って感じです！！
そうなんですか？
今回は
① 作成するスクレイピングスクリプトの説明
② スプレッドシート側の各要素について
③ スプレッドシートで繰り返し処理の回数（範囲）を指定できるスクリプト
という流れで、前回の記事を見ないでもできるような内容で解説していきたいと思います。
前回の「コピペスクリプト」の内容についてはこちらを確認してください！
スクレイピングの注意点
スクレイピングについて、サイトによっては許可してない場合があります。
サイトのポリシーや、取得するサイトのサーバに負荷をかけること、集めたデータを商用で使ったり、などは法令に抵触する可能性もあります。
スクレイピングする際には、取得して問題がない情報なのか十分に注意しておこないましょう。
また、こちらを参考にスプレイピングをおこなう際は自己責任でお願いします。
作成するスクレイピングスクリプトの説明
この記事の上部動画を見てもらうとイメージできるんじゃないかと思いますが、
「start」と「end」のセルで始まりと終わりの行番号を指定し、スクリプトを実行すると、その間のセルを順々に処理していくって内容です。
スクリプト自体はただのコピペの繰り返しですが、貼り付けているのが「URLからtitleタグを取得する数式」になるので、結果としては「URLからtitleタグを指定行分取得するスクリプト」となります。
- スプレッドシート「実行」ボタン：これをクリックすると作成したスクリプトを実行する
- B1:B2：GASに渡す「開始行」と「終了行」を指定する値
- B3：スプレッドシートの数式。スクリプトを実行すると、この値を取得し「開始行」に貼り付ける。
この数式はB列のURLから「titleタグ」を取得してくる。
スクリプトは、この値の貼り付けを「開始行」から「終了行」までの間、繰り返しおこなう。 - B4：B3に指示を与えている「Xpath」。これを変更すれば、「タイトルタグ」だったり「ディスクリプションタグ」だったりを取得してくることも可能。
- A7:A：B3の数式を貼り付け、「title」タグを出力するセル。ラベルが「Title」となっているのは間違い。
- B7:B：「title」タグを取得する元のURL。好きなものを記載してください。
えっと・・・つまりは
B3 の数式を A列 に繰り返し貼り付けていくスクリプトってことで合っていますか？
そうです。そういうことになりますね。
GASの内容は以下の通り。
この内容についてはこちらで解説しますね。
function myFunctionStartEnd() {
//************ ファイル・シート・セルなどの設定 ************//
//アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
//スプレッドシートの指定シートを取得
var sheet = ss.getSheetByName("コピペ");
//シートの指定セルと、その値を取得
var start_num = sheet.getRange('B1').getValue();
var end_num = sheet.getRange('B2').getValue();
var scr = sheet.getRange('B3').getValue();
//************ 上で取得した値を動かすスクリプト ************//
//「for」文による繰り返し処理
for(let i = start_num; i <= end_num; i++){
//「start_num」で取得した値でセルを指定し、「scr」の値を貼り付け
sheet.getRange('A' + i).setValue('=' + scr);
//3000ミリ秒=3秒中断
Utilities.sleep(3000);
//スプレッドシートで数式が貼り付けられたセルの”値”を取得
var copy = sheet.getRange('A' + i).getValue();
//取得した”値”を貼り付け（数式を削除し値のみを貼り付け）
sheet.getRange('A' + i).setValue(copy);
//ログ出力
Logger.log('copy : ' + copy);
}
//開始番号を上書き
sheet.getRange('B1').setValue(end_num + 1);
sheet.getRange('B2').setValue(end_num + 1);
}スプレッドシート側の各要素について
今回のベースとなるスプレッドシート側の各要素については次のように値を入れています。
B1はGASに渡す「開始行」を指定する値。
B2は同じくスクリプトの「終了行」を指定する値になります。
| A列 | B列 |
| start | 7 |
| end | 10 |
| scr | IMPORTXML(INDIRECT(“B”&ROW()),$B$4) |
| Xpath | /html/head/title |
また、シートの名前は「コピペ」としておいてください。
スプレッドシート IMPORTXML関数
B1やB2はまあいいとして、問題はB3セルの数式がどういう処理をしているかってことですね。
IMPORTXML(INDIRECT("B"&ROW()),$B$4)この数式を階層を分けてみていきましょう。
IMPORTXML(
INDIRECT("B"&
ROW()
),$B$4
)一番内側にある「ROW()」は「数式が入ったセルの行数」を出力する関数です。
IMPORTXML(
INDIRECT("B"&'7'
),$B$4
)例えばA7セルにこの関数が書かれた場合、「7」が出力されます。
IMPORTXML(
INDIRECT("B"&'7'
),$B$4
)「INDIRECT」はテキストをセルの座標として置き換える関数ですね。
この場合は「B」と「7」が合わさり、「B7」セルを参照することになります。
IMPORTXML('B7',$B$4)「IMPORTXML」は、URLとXpathによって、構造化データ（WEBサイトの情報）を引っ張ってくる関数です。
IMPORTXML( 'https://kabu-noma.com/ss/avlookup/' , $B$4)B7セルの値が入り
IMPORTXML( 'https://kabu-noma.com/ss/avlookup/' , '/html/head/title')B4セルの値が入ります。
ちなみに「$B$4」とは絶対参照で、この数式が別のセルに書かれても必ずこのセルを参照しろって指示になります。
ここでは「/html/head/title」、ヘッダー要素の中にある「title」を取得するという指示になります。
Xpathの取得方法
この「/html/head/title」は「Xpathクエリ」というもので、WEBサイトの要素を指定した値になります。
調べ方・取得の仕方としては、
WEBサイト上で「Shift＋Ctrl＋C」を押すと「検証」ツールが開きます。
好きなのコンテンツをクリックします。
クリックされたコンテンツが「検証」ツール上でもハイライトされます。
そのコンテンツ上で 「右クリック」 ＞ 「Copy」 ＞ 「Copy XPath」 でXpathを取得することができます。
説明をしておいてなんですが・・・今回のtitleタグはこの検証ツールでは探せない場所にあります。
見た目には現れない「head」というブロックの中に入っているため、「/html/head/title」と指定しています。
スプレッドシートで繰り返し処理の回数（範囲）を指定できるスクリプト
では、スクリプト（GAS）の解説をしていきますね。
スプレッドシートからセルの値を取得
スクリプトの上部はこんな感じです。
一番先頭のファンクション名には好きな名前を指定してください。
ここでは「myFunctionStartEnd」としています。
function myFunctionStartEnd() {
//************ ファイル・シート・セルなどの設定 ************//
//アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
//スプレッドシートの指定シートを取得
var sheet = ss.getSheetByName("コピペ");
//シートの指定セルと、その値を取得
var start_num = sheet.getRange('B1').getValue();
var end_num = sheet.getRange('B2').getValue();
var scr = sheet.getRange('B3').getValue();これらは「コメント」です。
好きなテキストを記載できます。
人が見て分かるように、”この処理はこういう意味”という補足の役割として使います。
function myFunctionStartEnd() {
//************ ファイル・シート・セルなどの設定 ************//
//アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
//スプレッドシートの指定シートを取得
var sheet = ss.getSheetByName("コピペ");
//シートの指定セルと、その値を取得
var start_num = sheet.getRange('B1').getValue();
var end_num = sheet.getRange('B2').getValue();
var scr = sheet.getRange('B3').getValue();この処理は、「ss」という変数を作成して、直近で動かしたスプレッドシートと紐づけるように指示をしています。
function myFunctionStartEnd() {
//************ ファイル・シート・セルなどの設定 ************//
//アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
//スプレッドシートの指定シートを取得
var sheet = ss.getSheetByName("コピペ");
//シートの指定セルと、その値を取得
var start_num = sheet.getRange('B1').getValue();
var end_num = sheet.getRange('B2').getValue();
var scr = sheet.getRange('B3').getValue();これは「sheet」という変数を作成し、直近で動かしたスプレッドシートの「コピペ」というシートとの紐付けをおこなっています。
function myFunctionStartEnd() {
//************ ファイル・シート・セルなどの設定 ************//
//アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
//スプレッドシートの指定シートを取得
var sheet = ss.getSheetByName("コピペ");
//シートの指定セルと、その値を取得
var start_num = sheet.getRange('B1').getValue();
var end_num = sheet.getRange('B2').getValue();
var scr = sheet.getRange('B3').getValue();「start_num」という変数を作り、「コピペ」シートの「B1」セルをターゲットにし、その中身を取得（getValue）しています。
function myFunctionStartEnd() {
//************ ファイル・シート・セルなどの設定 ************//
//アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
//スプレッドシートの指定シートを取得
var sheet = ss.getSheetByName("コピペ");
//シートの指定セルと、その値を取得
var start_num = sheet.getRange('B1').getValue();
var end_num = sheet.getRange('B2').getValue();
var scr = sheet.getRange('B3').getValue();「end_num」も同じようなことをしています。
「コピペ」シートの「B2」セルの中身を取得してきています。
function myFunctionStartEnd() {
//************ ファイル・シート・セルなどの設定 ************//
//アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
//スプレッドシートの指定シートを取得
var sheet = ss.getSheetByName("コピペ");
//シートの指定セルと、その値を取得
var start_num = sheet.getRange('B1').getValue();
var end_num = sheet.getRange('B2').getValue();
var scr = sheet.getRange('B3').getValue();最後も同様ですね。
「scr」という変数を作って、「B3」の値を取得しています。
「start_num」には「7」が入り、
「end_num」には「10」が入り、
「scr」には「IMPORTXML(INDIRECT(“B”&ROW()),$B$4)」が入るわけです。
「for」文による繰り返し処理
続いて、繰り返し処理について解説していきます。
スクリプトの下部はこんな感じになっています。
「for」文で貼り付けを繰り返し、
for文後に「B1」と「B2」セルの値を上書きしています。
「for」文・・・ですか？？
「for」文は指定した数分、処理を繰り返しおこなうフローですね。
//************ 上で取得した値を動かすスクリプト ************//
//「for」文による繰り返し処理
for(let i = start_num; i <= end_num; i++){
//「start_num」で取得した値でセルを指定し、「scr」の値を貼り付け
sheet.getRange('A' + i).setValue('=' + scr);
//3000ミリ秒=3秒中断
Utilities.sleep(3000);
//スプレッドシートで数式が貼り付けられたセルの”値”を取得
var copy = sheet.getRange('A' + i).getValue();
//取得した”値”を貼り付け（数式を削除し値のみを貼り付け）
sheet.getRange('A' + i).setValue(copy);
//ログ出力
Logger.log('copy : ' + copy);
}「for」文のみを切り取って見てみましょう。
for(let i = start_num; i <= end_num; i++){・・・}定数「i」を作成し、「start_num」の値を格納しています。
for(let i = start_num; i <= end_num; i++){・・・}「i」に（1を）足し続けて、
for(let i = start_num; i <= end_num; i++){・・・}「i」が「end_num」以下まで
for(let i = start_num; i <= end_num; i++){
・・・
}この中の処理を続けてね、っていうのが「for」文ですね。
変数は変更できる値で
定数は変更できない値です。
気になる方は「変数 定数 違い」で Google検索 してみてください。
//************ 上で取得した値を動かすスクリプト ************//
//「for」文による繰り返し処理
for(let i = start_num; i <= end_num; i++){
//「start_num」で取得した値でセルを指定し、「scr」の値を貼り付け
sheet.getRange('A' + i).setValue('=' + scr);
//3000ミリ秒=3秒中断
Utilities.sleep(3000);
//スプレッドシートで数式が貼り付けられたセルの”値”を取得
var copy = sheet.getRange('A' + i).getValue();
//取得した”値”を貼り付け（数式を削除し値のみを貼り付け）
sheet.getRange('A' + i).setValue(copy);
//ログ出力
Logger.log('copy : ' + copy);
}「for」文の中身を見ていきましょう。
この処理は、「コピペ」シートの「‘A’ + i」セルに、「‘=’ + scr」を入力しています。
ん？んん？？？
少し前を思い出してもらえれば分かると思いますが・・・
「start_num」には「7」が入り、
「end_num」には「10」が入り、
「scr」には「IMPORTXML(INDIRECT(“B”&ROW()),$B$4)」が入るわけです。
for(let i = start_num; i <= end_num; i++){
sheet.getRange('A' + i).setValue('=' + scr);よって、これは
for(let i = '7' ; i <= '10' ; i++){
sheet.getRange('A' + '7' ).setValue('=' + 'IMPORTXML(INDIRECT("B"&ROW()),$B$4)' );こうなるわけです。
結果として、
「A7」セルに「=IMPORTXML(INDIRECT(“B”&ROW()),$B$4)」を入れるという処理になります。
なるほど〜！
//************ 上で取得した値を動かすスクリプト ************//
//「for」文による繰り返し処理
for(let i = start_num; i <= end_num; i++){
//「start_num」で取得した値でセルを指定し、「scr」の値を貼り付け
sheet.getRange('A' + i).setValue('=' + scr);
//3000ミリ秒=3秒中断
Utilities.sleep(3000);
//スプレッドシートで数式が貼り付けられたセルの”値”を取得
var copy = sheet.getRange('A' + i).getValue();
//取得した”値”を貼り付け（数式を削除し値のみを貼り付け）
sheet.getRange('A' + i).setValue(copy);
//ログ出力
Logger.log('copy : ' + copy);
}次は簡単ですね。
コメントの通り、3秒中断（休憩）しています。
「IMPORTXML」関数はインターネット上から情報を引っ張ってきているため、読み込みのための時間を作っています。
//************ 上で取得した値を動かすスクリプト ************//
//「for」文による繰り返し処理
for(let i = start_num; i <= end_num; i++){
//「start_num」で取得した値でセルを指定し、「scr」の値を貼り付け
sheet.getRange('A' + i).setValue('=' + scr);
//3000ミリ秒=3秒中断
Utilities.sleep(3000);
//スプレッドシートで数式が貼り付けられたセルの”値”を取得
var copy = sheet.getRange('A' + i).getValue();
//取得した”値”を貼り付け（数式を削除し値のみを貼り付け）
sheet.getRange('A' + i).setValue(copy);
//ログ出力
Logger.log('copy : ' + copy);
}「copy」という変数を作成して、「‘A’ + i」＝「A7」セルの値を取得しています。
//************ 上で取得した値を動かすスクリプト ************//
//「for」文による繰り返し処理
for(let i = start_num; i <= end_num; i++){
//「start_num」で取得した値でセルを指定し、「scr」の値を貼り付け
sheet.getRange('A' + i).setValue('=' + scr);
//3000ミリ秒=3秒中断
Utilities.sleep(3000);
//スプレッドシートで数式が貼り付けられたセルの”値”を取得
var copy = sheet.getRange('A' + i).getValue();
//取得した”値”を貼り付け（数式を削除し値のみを貼り付け）
sheet.getRange('A' + i).setValue(copy);
//ログ出力
Logger.log('copy : ' + copy);
}同じく「‘A’ + i」＝「A7」セルに、今度は「copy」の値を書き込んでいます。
うんん？？
同じセルから同じセルにコピペしているってことですよね？？
何しているんですか！？
これはですね、
「数式を消して」、「値のみを貼り付け」しているんですね。
先ほども言ったように、「IMPORTXML」関数はインターネット上から情報を引っ張ってきているため、大量に使うと「使うの多すぎるぞエラー」が出てしまいます。
これを回避するために、「IMPORTXML」関数で取得した”値のみ”を取得しなおしセルに上書きしているんですね。
あぁ〜
数式を貼り付けて、値をコピーして、値を貼り付けているのはそういうことだったんですね！！
//************ 上で取得した値を動かすスクリプト ************//
//「for」文による繰り返し処理
for(let i = start_num; i <= end_num; i++){
//「start_num」で取得した値でセルを指定し、「scr」の値を貼り付け
sheet.getRange('A' + i).setValue('=' + scr);
//3000ミリ秒=3秒中断
Utilities.sleep(3000);
//スプレッドシートで数式が貼り付けられたセルの”値”を取得
var copy = sheet.getRange('A' + i).getValue();
//取得した”値”を貼り付け（数式を削除し値のみを貼り付け）
sheet.getRange('A' + i).setValue(copy);
//ログ出力
Logger.log('copy : ' + copy);
}最後にGAS側で状況を確認できるように、ログを出力しています。
スプレッドシートの開始列と終了列をアップデート
もう2行、お尻についているのはどういう内容なんですか？
//開始番号を上書き
sheet.getRange('B1').setValue(end_num + 1);
sheet.getRange('B2').setValue(end_num + 1);
}「for」文の外のこれですね。
//開始番号を上書き
sheet.getRange('B1').setValue(end_num + 1);
sheet.getRange('B2').setValue(end_num + 1);
}「for」文が終わった後、
「B1」セルに「end_num」に「1」を足した値を書き込んでいます。
「B1」には元々「start_num」の値「7」が入っていました。
これを「11」に上書きしています。
//開始番号を上書き
sheet.getRange('B1').setValue(end_num + 1);
sheet.getRange('B2').setValue(end_num + 1);
}こちらの行は「B1」＝「end_num」の「10」が入っていたのを「11」に上書きしています。
わかった！
処理が終わったので、次の処理のために値を更新してあげているんですね！
そうそう。
処理が無事終わっていれば次はその次から始めるケースが多いと思うので、あらかじめ更新しておくよう処理を書いておいたって感じです。
なのでこの2行は書いても書かなくてもどちらでも結構です。
function myFunctionStartEnd() {
//************ ファイル・シート・セルなどの設定 ************//
//アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
//スプレッドシートの指定シートを取得
var sheet = ss.getSheetByName("コピペ");
//シートの指定セルと、その値を取得
var start_num = sheet.getRange('B1').getValue();
var end_num = sheet.getRange('B2').getValue();
var scr = sheet.getRange('B3').getValue();
//************ 上で取得した値を動かすスクリプト ************//
//「for」文による繰り返し処理
for(let i = start_num; i <= end_num; i++){
//「start_num」で取得した値でセルを指定し、「scr」の値を貼り付け
sheet.getRange('A' + i).setValue('=' + scr);
//3000ミリ秒=3秒中断
Utilities.sleep(3000);
//スプレッドシートで数式が貼り付けられたセルの”値”を取得
var copy = sheet.getRange('A' + i).getValue();
//取得した”値”を貼り付け（数式を削除し値のみを貼り付け）
sheet.getRange('A' + i).setValue(copy);
//ログ出力
Logger.log('copy : ' + copy);
}
//開始番号を上書き
sheet.getRange('B1').setValue(end_num + 1);
sheet.getRange('B2').setValue(end_num + 1);
}で、最終的なスクリプトがこちらです。
「実行」ボタンにスクリプトを割り当てる
スクリプトの先頭行のファンクション名、ここでいうところの「myFunctionStartEnd」をコピーします。
「実行」ボタンの「…」をクリックして、「スクリプトの割り当て」を押します。
「myFunctionStartEnd」を貼り付けて「確定」を押してください。
この「ボタン」の作り方については、こちらの記事で解説していますので、わからない方はのぞいてみてください！
ではスクリプトを実行してみましょう。
「実行」ボタンを押すと・・・
どうでしょう。
この記事の先頭の動画のように動いたでしょうか？
URLからタイトルタグを抽出し、スクリプトが終了すると「start」と「end」が更新されれば無事成功です。
まとめ
今回は
① 作成するスクレイピングスクリプトの説明
② スプレッドシート側の各要素について
③ スプレッドシートで繰り返し処理の回数（範囲）を指定できるスクリプト
ということで話をしました。
今回は「for」文を使って、繰り返しの処理をおこないましたね！
はい。
titleタグの取得自体はスプレッドシートの関数を使用しています。
GASでは、その関数を順々にセルにコピペしていくって処理でした。
コメント