GASスクレイピングサンプル



<p> Google Apps Scriptを使用したWebスクレイピングのサンプルです<br>
今回作成したソースはcode2019-02-16-1.gsです.
</p>

<p>内容</p>
 <ol>
   <li>参考サイト</li>
   <li>出力プレビュー</li>
   <li>code2019-02-16-1.gs</li>
 </ol>
<hr>
<p>1.参考サイト</p>
<p>以下のサイトを参考に作成しました</p>
<a href="https://qiita.com/inaling/items/23248b7290d8140f53fc">
Google Apps Scriptでスクレイピングする方法 - Qiita
</a>
<br>
<a href="https://tonari-it.com/gas-regular-expression/">
Google Apps Scriptで正規表現を使って必要な情報を抽出する最も簡単なスクリプト
</a>
<br>
<a href="https://spreadsheet.blue/google-apps-script/sheet/getactive/70/">
Google apps scriptでスプレッドシートを取得する | GASでスプレッドシートを操作する
</a>
<br>
<a href="https://excel-ubara.com/apps_script1/GAS030.html">
並べ替え｜Google Apps Script入門
</a>
<br>
<a href="http://www.japan-reit.com/list/rimawari/">
JAPAN-REIT.COM - 全ての投資家のための不動産投信情報ポータル REIT一覧（リートデータ）
</a>
<br>
<a href="https://haitokekaku.com/2018/10/20/post-1558/">
☆毎月、高配当金・分配金を貰うための投資方法について | 幸せ配当計画
</a>
<br>
<hr>
<p>2.出力プレビュー</p>
<img src="https://shizuoka0miyako.web.fc2.com/image-10-07/image-page4-1.PNG">
<hr>
<p>3.code2019-02-16-1.gs</p>

<pre>
<code class="JavaScript">
function scraping() {
 
  const preURL = &#039;http://www.japan-reit.com/meigara/&#039;;
  const URL2 = &#039;http://www.japan-reit.com/list/rimawari/&#039;
  
  var sclaypingText = &quot;&quot;;
  
  var response = UrlFetchApp.fetch(URL2);
  var html = response.getContentText(&#039;UTF-8&#039;);
  var lines = html.split(&#039;\r\n&#039;);
  
  var myRegexp1 = &quot;\&lt;table.*?&gt;&quot;;
  var myRegexp2 = &quot;\&lt;/table&gt;&quot;;
  var myRegexp3 = &quot;class=\&quot;.*\&quot;&quot;
  var find_table_class = &#039;&quot;meigara-table x-x-hidden&quot;&#039;;
  var find_table_class2 = &#039;&quot;simple list-rimawari&quot;&#039;;
  var line;
  var classList = [];
  var value;
  var tableSize = [];
  var meigaraTable;
  var meigaraList = [];
  const haitouOtiKankaku = 3;
  
  var meigaraConstractor = function(){
    this.meigaraId = &#039;&#039;;
    this.meigaraNm = &#039;&#039;;
    this.rimawari = &#039;&#039;;
    this.kessan = &#039;&#039;;
    this.haitouOti = [];
  };
  
  //html上の各テーブルの情報を取得
  var tableTagList = getTableTagString(lines, classList);
  for(var i=0; i&lt;tableTagList.length; i++){
    if(classList[i]　== find_table_class2){
    //Logger.log(&quot;class[&quot;+i+&quot;]&quot;+classList[i]);
    //Logger.log(&quot;table[&quot;+i+&quot;]&quot;+tableTagList[i]);
     meigaraTable = tableTagList[i];
     //tableSize = getTableSize(tableTagList[i]);
     //value = getTableTagValue(1,0,tableTagList[i]);
     //Logger.log(&quot;size:&quot;+tableSize);
     //Logger.log(&quot;value:&quot;+value);
    }       
  }
  
  
  //テーブルの情報から中身をセット
  tableSize = getTableSize(meigaraTable);
  for(var j=1; j&lt;tableSize[0]; j++){
    var mId = getTableTagValue(j,0,meigaraTable);
    var mNm = getTableTagValue(j,1,meigaraTable);
        mNm = (getTagValueList(mNm, &#039;a&#039;))[0];
        mNm = deleteCommentTag(mNm);  
    var mrimawari = getTableTagValue(j,4,meigaraTable);
    var mkessan = getTableTagValue(j,9,meigaraTable);
    var mObj = new meigaraConstractor();
    mObj.meigaraId = mId;
    mObj.meigaraNm = mNm;
    mObj.rimawari = mrimawari;
    mObj.kessan = mkessan;
    
    mObj.haitouOti = mObj.kessan.split(&#039;/&#039;);
    for(var j2=0; j2&lt;mObj.haitouOti.length; j2++){
      mObj.haitouOti[j2] = mObj.haitouOti[j2].replace(&#039; &#039;,&#039;&#039;);
      mObj.haitouOti[j2] = parseInt(mObj.haitouOti[j2])+haitouOtiKankaku;
      
      if(parseInt(mObj.haitouOti[j2]) &gt; 12){
        mObj.haitouOti[j2] = parseInt(mObj.haitouOti[j2])-12;
      }
    }
    if(mObj.haitouOti.length &gt;= 2){
      if(mObj.haitouOti[0] &gt; mObj.haitouOti[1]){
        var temp = mObj.haitouOti[0];
        mObj.haitouOti[0] = mObj.haitouOti[1];
        mObj.haitouOti[1] = temp;
      }
    }
    
    meigaraList.push(mObj);    
  }

  
  makeHeader();
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  
  var cellAnote;
  var cell;
  for(var k=0; k&lt;meigaraList.length; k++){
    
    cellAnote = getColAnote(1)+(k+2);
    cell = spreadsheet.getRange(cellAnote);
    cell.setValue(meigaraList[k].meigaraId);
    
    cellAnote = getColAnote(2)+(k+2);
    cell = spreadsheet.getRange(cellAnote);
    cell.setValue(meigaraList[k].meigaraNm);

    cellAnote = getColAnote(3)+(k+2);
    cell = spreadsheet.getRange(cellAnote);
    cell.setValue(meigaraList[k].kessan);

    cellAnote = getColAnote(4)+(k+2);
    cell = spreadsheet.getRange(cellAnote);
    cell.setValue(meigaraList[k].rimawari); 
    
    for(var k2=0; k2&lt;meigaraList[k].haitouOti.length; k2++){
      cellAnote = getColAnote(5+k2)+(k+2)
      cell = spreadsheet.getRange(cellAnote);
      var t = meigaraList[k].haitouOti[k2];
      cell.setValue(meigaraList[k].haitouOti[k2]);
    }
    
  }
  SortSheet({column: 4, ascending: false});
  SortSheet({column: 5, ascending: true});

  Logger.log(&quot;meigaraList:&quot;+meigaraList);
  
  
}

function makeHeader(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  
  var cellAnote;
  var cell;
  
  cellAnote = getColAnote(1)+&#039;1&#039;;
  cell = spreadsheet.getRange(cellAnote);
  cell.setValue(&quot;銘柄ID&quot;);
  
  cellAnote = getColAnote(2)+&#039;1&#039;;
  cell = spreadsheet.getRange(cellAnote);
  cell.setValue(&quot;銘柄名&quot;);
  
  cellAnote = getColAnote(3)+&#039;1&#039;;
  cell = spreadsheet.getRange(cellAnote);
  cell.setValue(&quot;決算月&quot;);
  
  cellAnote = getColAnote(4)+&#039;1&#039;;
  cell = spreadsheet.getRange(cellAnote);
  cell.setValue(&quot;利回り&quot;);
  
  cellAnote = getColAnote(5)+&#039;1&#039;;
  cell = spreadsheet.getRange(cellAnote);
  cell.setValue(&quot;配当落ち予想月1&quot;);
  
  cellAnote = getColAnote(6)+&#039;1&#039;;
  cell = spreadsheet.getRange(cellAnote);
  cell.setValue(&quot;配当落ち予想月2&quot;);
}

function SortSheet(row) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  sheet.getRange(2, 1, lastRow, lastCol).sort(row);
}
function getColAnote(row){
  var result;
  
  if(row == 1){
    return &#039;A&#039;;
  }else if(row == 2){
    return &#039;B&#039;;
  }else if(row == 3){
    return &#039;C&#039;;
  }else if(row == 4){
    return &#039;D&#039;;
  }else if(row == 5){
    return &#039;E&#039;;
  }else if(row == 6){
    return &#039;F&#039;;
  }else if(row == 7){
    return &#039;G&#039;;
  }else if(row == 8){
    return &#039;H&#039;;
  }else if(row == 9){
    return &#039;I&#039;;
  }else if(row == 10){
    return &#039;J&#039;;
  }
  
  return &#039;Z&#039;;
}
/*Spreadsheetタイトル台帳を作成*/
function newsheet(){
 
//createメソッドでスプレッドシートを作成
 var ssNew = SpreadsheetApp.create(&quot;spreadsheetTest1&quot;);
 
 return ssNew.getUrl();
 
}

function fetchData(str, pre, suf) {
  var reg = new RegExp(pre + &#039;.*&#039; + suf);
  if(str.match(reg) == null){
    return null;
  }
  
  var data = str.match(reg)[0]
    .replace(pre, &#039;&#039;)
    .replace(suf, &#039;&#039;);
  return data;  
}

function getTableSize(tableTagStr){
  var result = [0,0];
  var myRegExp1 = /&lt;tr.*?&gt;.*?\&lt;\/tr&gt;/g;
  var myRegExp2 = /&lt;td.*?&gt;.*?\&lt;\/td&gt;/g;
  var myRegExpTHead1 = /&lt;thead.*?&gt;.*?\&lt;\/thead&gt;/g;
  var myRegExpTHead2 =  /&lt;th.*?&gt;.*?\&lt;\/th&gt;/g;
 

  var HeaderList = tableTagStr.match(myRegExpTHead1);
  var rowList = tableTagStr.match(myRegExp1);
  
  
  if(HeaderList != null){
    if(rowList != null){
      result[0] =  rowList.length;
      var colHeaderList = rowList[0].match(myRegExpTHead2);
      if(colHeaderList != null){
        result[1] = colHeaderList.length; 
      }else{
        result[1] = 0;
      }
      return result;
    }else{
      result[0] = 0;
      result[1] = 0;
      return result;
    }
  }
  
  if(rowList != null){
    result[0] = rowList.length;
    var colList = rowList[rowIdx].match(myRegExp2);
    if(colList != null){
      result[1] = colList.length;
    }else{
      result[1] = 0
    }
    return result;
  }else{
    result[0] = 0;
    result[1] = 0;
    return result;
  }
}
function getTableTagValue(rowIdx,colIdx,tableTagStr){
  var myRegExp1 = /&lt;tr.*?&gt;.*?\&lt;\/tr&gt;/g;
  var myRegExp2 = /&lt;td.*?&gt;.*?\&lt;\/td&gt;/g;
  var myRegExpTHead1 = /&lt;thead.*?&gt;.*?\&lt;\/thead&gt;/g;
  var myRegExpTHead2 =  /&lt;th.*?&gt;.*?\&lt;\/th&gt;/g;
 

  var HeaderList = tableTagStr.match(myRegExpTHead1);
  var rowList = tableTagStr.match(myRegExp1);
  
  
  if(HeaderList != null &amp;&amp;  rowIdx == 0){
    if(rowList != null &amp;&amp; rowList.length &gt;= rowIdx){
      var colHeaderList = rowList[rowIdx].match(myRegExpTHead2);
      if(colHeaderList != null &amp;&amp; colHeaderList.length &gt;= colIdx){
        return getTagValue(colHeaderList[colIdx], &#039;th&#039;); 
      }
    }
  }
  
  if(rowList != null &amp;&amp; rowList.length &gt;= rowIdx){
    var colList = rowList[rowIdx].match(myRegExp2);
    if(colList != null &amp;&amp; colList.length &gt;= colIdx){
      return getTagValue(colList[colIdx], &#039;td&#039;);
    }
  }
  return null;
}

function getTagValue(str, tagName){
  var startTagExp = new RegExp(&#039;&lt;&#039;+tagName+&#039;.*?&gt;&#039;,&#039;g&#039;);//&#039;&lt;&#039;+tagName+&#039;.*?&gt;/g;
  var matchedTag = str.match(startTagExp);
  var result;
  
  if(matchedTag != null){
    result = str.replace(matchedTag[0],&#039;&#039;);
  }
  return result.replace(&#039;\&lt;/&#039;+tagName+&#039;&gt;&#039;,&#039;&#039;);
}

function deleteCommentTag(str){
  var tagExp = new RegExp(&#039;&lt;!--.*?--&gt;&#039;,&#039;g&#039;);
  var matchedTagList = str.match(tagExp);
  var result = str;
  
  for(var i=0; i&lt;matchedTagList.length; i++){
    result = result.replace(matchedTagList[i],&#039;&#039;);
  }
  return result;
}
function getTagValueList(str, tagName){
  var tagExp = new RegExp(&#039;&lt;&#039;+tagName+&#039;.*?&gt;&#039;+&#039;.*?&#039;+&#039;\&lt;/&#039;+tagName+&#039;&gt;&#039;,&#039;g&#039;);//&#039;&lt;&#039;+tagName+&#039;.*?&gt;/g;
  var tagExp2 = new RegExp(&#039;&lt;&#039;+tagName+&#039;.*?&gt;&#039;,&#039;g&#039;);
  var matchedTagList = str.match(tagExp);
  var result = [];
  var tagHead;
  
  for(var i=0; i&lt;matchedTagList.length; i++){
     tagHead = matchedTagList[i].match(tagExp2);
     result.push(matchedTagList[i].replace(tagHead[0],&#039;&#039;).replace(&#039;\&lt;/&#039;+tagName+&#039;&gt;&#039;,&#039;&#039;));
  }
  return result;
}
function getTableTagString(lines, classList){
  var resultList = [];  
  var myRegExp1 = &quot;\&lt;table.*&gt;&quot;;
  var myRegExp2 = &quot;\&lt;/table&gt;&quot;;
  var myRegExp3 = &quot;class=\&quot;.*\&quot;&quot;
  var line1, line2;
  var j;
  var resultStr;
  
  for(var i=0; i&lt;lines.length; i++){
    line1 = lines[i];
    if(line1.match(myRegExp1) != null){
      
      if(line1.match(myRegExp3) != null){
        var matchedStrList = line1.match(myRegExp3);
        classList.push(matchedStrList[0].replace(&#039;class=&#039;,&#039;&#039;));
      }else{
        classList.push(&quot;&quot;);
      }
      
      resultStr = &quot;&quot;;
      for(j=i+1; j&lt;lines.length; j++){
        line2 = lines[j];
        if(line2.match(myRegExp2) == null){
          resultStr += line2.replace(&#039;\r\n&#039;,&#039;&#039;);
        }else{
          resultList.push(resultStr);
          resultStr = &quot;&quot;;
          i = j;
          break;
        }
      }
    }
  }
  
  return resultList;
}
</code>
</pre>

<script src="jquery-3.3.1.min.js"></script>