【GAS】Google Apps Script 活用事例　スプレッドシートのキーワードからGoogle検索の結果を書き出すスクリプト

nepia_infinity

2021年2月22日 22:35

新卒関連の競合他社調査でひたすら、企業名を検索して結果をスプレッドシートに貼り付けるような作業が、面倒くさいなぁと思って、スクリプト書きました。完璧では無いものの、及第点に達したスクリプトを備忘録のために書いておきます。

何を検索しているかで、その人となりが分かってしまう時代ですし、特定の企業名を出すのは、あまり良く無いかなと感じて、企業名が登場する部分を、モザイク処理しています。結果は、ちゃんと取得できています。

setResult()　実行時

スクリーンショット_2021-02-22_21_34_14

検索ワードというシートのB列にリンクが入ります。このリンクをクリックすると....検索窓に検索語句が入った状態の画面に遷移します。コピペしたり、ベタ打ちしなくて済みます。

スクリーンショット_2021-02-22_21_19_38

getSearchResult() 　実行時

スクリーンショット_2021-02-22_21_36_57

取得結果のシートに、検索結果の1番目のタイトルとURLが書き出されます。書き出された内容とURLが、必ずしも欲しい情報とは限らないのですが、ゼロベースで調べるよりは少しは楽になるかなと思います。

出力結果と取得内容が、微妙に違う？よく分からん....。

スクリーンショット_2021-02-22_22_15_59

<h3 class="zBAuLc"><div class="BNeawe vvjwJb AP7Wnd">採用情報 *****株式会社</div>

const results  = html.match(/<h3.*<\/span>/);

HTMLとして出力された内容と、match()メソッドで返ってくる内容が微妙に？.....結構違いがある気がします.....。この辺は、経験豊富なエンジニアの方に聞かないと分からない気がします。

.+?　正規表現　最短マッチ

function test() {
 const string = '私は昨日、無断欠勤しました。ズル休みでした。';

 console.log(string.match(/私は.+?た。/)[0]);
 console.log(string.match(/私は.*た。/)[0]);
}

スクリプト

繰り返し処理する回数が少なければ、このスクリプトでも問題ありません。回数が多くエラーが出てしまうようであれば、後述のスクリプトを試してみてください。

function setResult(){
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('検索ワード');
 const keyWords    = getKeyWords_(1);//取得する列
 let values        = [];

 for(let i = 0; i < keyWords.length; i++){

   const encodeWord = encodeURI(keyWords[i]);
   const requestUrl = "https://www.google.com/search?q=" + encodeWord;
   values.push([requestUrl]);
 }

sheet.getRange(2, 2, values.length, values[0].length).setValues(values);
}



function getKeyWords_(column) {

 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('検索ワード');
 const lastRow     = sheet.getLastRow();
 const keyWords    = sheet.getRange(2, column, lastRow).getValues().flat();

 console.log(keyWords);
 console.log('検索キーワード数：', keyWords.length);

 return keyWords
}



function getSearchResult() {
 const urls = getKeyWords_(2);
 let values = [];
 let count  = 0;
 
 
 for(const requestUrl of urls){
 
   count += 1;
   
   //urlFetchAppを7回以上連続で、使うとエラーが起きてしまうため、10秒停止させる。
   if(count%7 === 0){
     Utilities.sleep(10000);
   }

   //検索結果のh3タグを取得する
   const response = UrlFetchApp.fetch(requestUrl);
   const html     = response.getContentText();
   const results  = html.match(/<h3.*<\/span>/);

   console.log(results);
   
   //検索結果の文字列から、1番目の検索結果と、URLを取得
   const title = results[0].match(/<h3.+?<\/div>/);
   const url   = results[0].match(/<a href="\/url?.+?&/);
   
   //正規表現を使用して、HTMLタグを取り除く
   const formatedTitle = title[0].replace(/<h3.*">/,'').replace(/<\/div>/, '');
   const formatedUrl   = url[0].replace(/.*=/, '').replace(/&/, '');

   console.log(formatedTitle, formatedUrl);
   values.push([formatedTitle, formatedUrl, requestUrl]);
 }
 
 //スプレッドシートに配列を貼り付け
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('取得結果');
 sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

429 Too Many Request

やりすぎ....って事ですね。エラーが出ました。

エラーが起きた時点で、一度書き出す処理を加えたスクリプト

上記のスクリプトですが、7回処理する前に、429 Too Many Requestが出て、コケる事がありました。安定しているとは言えなかったので、try catch構文を加えました。

ポイントは、for文の中に、try catchが入っているので、それまでに得た取得結果をシートに一旦吐き出して、途中から処理を再開出来るようになっています。

function getSearchResult() {
 const urls = getKeyWords_(2);
 let values = [];
 let count  = 0;

 for(const requestUrl of urls){
   //3回連続でFetchしたら、10秒休ませる
   count += 1;

   if(count%3 === 0){
     Utilities.sleep(10000);
   }

   try{
     const response = UrlFetchApp.fetch(requestUrl);
     const html     = response.getContentText();
     const results  = html.match(/<h3.*<\/span>/);

     console.log(results);

     const title = results[0].match(/<h3.+?<\/div>/);
     const url   = results[0].match(/<a href="\/url?.+?&/);

     const formatedTitle = title[0].replace(/<h3.*">/,'').replace(/<\/div>/, '');
     const formatedUrl   = url[0].replace(/.*=/, '').replace(/&/, '');

     console.log(formatedTitle, formatedUrl);
     values.push([formatedTitle, formatedUrl, requestUrl]);

   }
   catch(e){
     //エラーが起きた段階で、シートに書き込む
     setValues_(values);

     //書き込んだ後で、空の配列に初期化　同じ内容がシートに書き込まれないようにする
     values = [];
     console.log(values);

   }
 }//for
 setValues_(values);
}//end




function setValues_(values){
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('取得結果');
 const lastRow     = sheet.getLastRow() + 1;

 sheet.getRange(lastRow, 1, values.length, values[0].length).setValues(values);

}

配列処理の実験

function myFunction() {
 const values = [[1],[4],[9],[16]];

 const array     = values.map(array => array.shift());
 const newValues = values.map(array => array.splice(0, 0));
 
 //配列処理の実験
 console.log(newValues); //output: [ [], [], [], [] ]
 console.log(array);     //output: [ 1, 4, 9, 16 ]
}

IMPORT系の関数

特にIMPORTXML関数が、調査系業務にすごく役立ちます！！

【GAS】Google Apps Script 活用事例 スプレッドシートのキーワードからGoogle検索の結果を書き出すスクリプト

setResult() 実行時

getSearchResult() 実行時