【GAS】スプレッドシートにGmailからスクレイピング～GASでセルに関数を入れる～【第9回】

2022/08/16

前回は、重複したデータが書き込まれないように対策しました。今回は、あらかじめ関数をいれてしまうとうまくGASが動かなくなる問題の対策をします。

目次

今回やること

あらかじめ関数を入れてしまうと、GASがうまく動かなくなるので、GASで関数を入れてしまおうというのが今回の対策です。幸い、今回の関数の式には複雑なものがないので、サクッと入れてしまいましょう。

.setFormulaR1C1()

関数式を入れるメソッドです。getRangeしてから使います。RはRange、CはColumnを表します。例として連続日数を表す列の数式を入れる文を使います。例えば、C34に入れたい数式は以下です。

=IF(A34=(A33+1),C33+1,1)

.setFormulaR1C1では、基準となるセルからの行と列の移動数でセルを表します。ですので、C34から、A34は「RC[-2]」となり、C34からC33は「R[-1]C」と表現されます。ですので、上記の式は以下のようになります。

.setFormulaR1C1('=IF(RC[-2]=(R[-1]C[-2]+1),R[-1]C+1,1)')

GAS全体

function myFunction() {
  
  const query ='さんの脳トレ記録をお届けします。';
  const start = 0;
  const max = 10;
  const threads = GmailApp.search(query,start,max);
  const st = SpreadsheetApp.openById('スプレッドシートID').getSheetByName('シート1');
  const st2 = SpreadsheetApp.openById('スプレッドシートID').getSheetByName('シート2');

  const fetchData = (str ,pre ,suf) =>{
    const reg = new RegExp(pre + '.*?' + suf,'g');
    const preg = new RegExp(pre,'g');
    const sreg = new RegExp(suf,'g');
    var data = str.match(reg);
    var data1 = null;
    if(data !== null){
      data1 = data[0].replace(preg,'').replace(sreg,'');
    }
    return data1;
  };
  const fetchData1 = (str ,pre ,suf) =>{
    const reg = new RegExp(pre + '*?' + suf,'g');
    const preg = new RegExp(pre,'g');
    const sreg = new RegExp(suf,'g');
    var data = str.match(reg);
    var data1 = null;
    if(data !== null){
      data1 = data[0].replace(preg,'').replace(sreg,'');
    }
    return data1;
  };

  const moment = (str1) =>{
    const minute = str1.replace('分',':').replace('秒','.');
    return minute;
  };

  var date10 = [['日付','経過日数','脳年齢','抑制力','処理速度','短期記憶','計算100','人数数え','ID']];
  var calc1001 = null;
  var people1 = null;
  var calc250 = null;
  var double_task1 = null;
  var kanji1 = null;
  var last_photo1 = null;
  var finger_calc1 = null;
  var finger_exe1 = null;

  for(const thread of threads){
    const messages = GmailApp.getMessagesForThread(thread);

    for(const message of messages){
      const plainBody = message.getPlainBody();
      const id = message.getId();
      const continuity = fetchData(plainBody,'脳トレ\\s','日目');
      const date = fetchData(plainBody,'日目\\s','（');
      const ba = fetchData(plainBody,'今回：脳年齢\\s','才');
      const ba_suppression =fetchData(plainBody,'・抑制力\\s','才');
      const ba_process = fetchData(plainBody,'・処理速度\\s','才');
      const ba_memory = fetchData(plainBody,'・短期記憶\\s','才');

      const calc100 = fetchData(plainBody,'計算100\\s・','（');
      const calc25 = fetchData(plainBody,'計算25\\s・','（');
      const ns_read =fetchData(plainBody,'新聞音読\\s・','音');
      const song_perf = fetchData(plainBody,'名曲演奏\\s・','点（');
      const people = fetchData(plainBody,'人数数え\\s・','/6問');
      const kanji = fetchData(plainBody,'漢字合成\\s・','（')
      const last_photo = fetchData(plainBody,'直前写真\\s・','（');
      const ins_memory = fetchData(plainBody,'瞬間記憶\\s・','個（');
      const finger_calc = fetchData(plainBody,'指計算\\s・','（');
      const finger_exe = fetchData(plainBody,'指体操\\s・','（');
      const double_task = fetchData(plainBody,'二重課題\\s・','（');
      const sudoku_class = fetchData(plainBody,'数独（','）');
      const sudoku_art = fetchData(plainBody,'・','問クリア');
      const anecdote = fetchData1(plainBody,'川島教授の脳の小話\\s[\\s\\S]','\\s\\s\\s\\s')

      if(calc25 !== null){
        calc250 = moment(calc25);
      }
      else{calc250=null;}
      if(calc100 !== null){
        calc1001 = moment(calc100);
      }
      else{calc1001 = null;}
      if(people !== null){
        people1 = moment(people);
      }
      else{people1 = null;}
      if(kanji !== null){
        kanji1 = moment(kanji);
      }
      else{kanji1 = null;}
      if(last_photo !== null){
        last_photo1 = moment(last_photo);
      }
      else{last_photo1 = null;}
      if(finger_calc !== null){
        finger_calc1 = moment(finger_calc);
      }
      else{finger_calc1 = null;}
      if(finger_exe !== null){
        finger_exe1 = moment(finger_exe);
      }
      else{finger_exe1 = null;}
      if(double_task !== null){
        double_task1 = moment(double_task);
      }
      else{double_task1=null;}
      date10.push([date,continuity,ba,ba_suppression,ba_process,ba_memory,calc1001,calc250,ns_read,song_perf,people1,kanji1,last_photo1,ins_memory,finger_calc1,finger_exe1,double_task1,sudoku_class,sudoku_art,id,anecdote]);
    }
  }
  date10.sort(function(a,b){
    return new Date(a[0]) - new Date(b[0]);
  });
  console.log(date10);
  var j = date10.push();
  console.log(j);

  const hasId = id =>{
    //getRange(行番号, 列番号, 行数, 列数)
    const range = st.getRange(7,28,st.getLastRow()-6).getValues();
    return range.flat().includes(id);
  }

  for(let k=1;k<j;k++){
    const lastrow = st.getLastRow()+1;
    if(hasId(date10[k][19])) continue;
      //日付
      st.getRange(lastrow,1).setValue(date10[k][0]);
      //経過日数
      st.getRange(lastrow,2).setValue(date10[k][1]);
      //数式入れる
      st.getRange(lastrow,3).setFormulaR1C1('=IF(RC[-2]=(R[-1]C[-2]+1),R[-1]C+1,1)');
      //脳年齢
      st.getRange(lastrow,4).setValue(date10[k][2]);
      //脳年齢-抑制力-処理速度-短期記憶
      st.getRange(lastrow,5).setValue(date10[k][3]);
      st.getRange(lastrow,6).setValue(date10[k][4]);
      st.getRange(lastrow,7).setValue(date10[k][5]);
      //トレーニング結果-計算100-計算25-新聞音読-名曲演奏
      st.getRange(lastrow,8).setFormulaR1C1('=IF(ISNUMBER(RC[1]),IF(RC[1]>1,IFERROR(("00:00:"&RC[1])*1),RC[1]),IFERROR(("00:"&RC[1])*1))');
      st.getRange(lastrow,9).setValue(date10[k][6]);
      st.getRange(lastrow,10).setFormulaR1C1('=IF(ISNUMBER(RC[1]),IF(RC[1]>1,IFERROR(("00:00:"&RC[1])*1),RC[1]),IFERROR(("00:"&RC[1])*1))');
      st.getRange(lastrow,11).setValue(date10[k][7]);
      st.getRange(lastrow,12).setValue(date10[k][8]);
      st.getRange(lastrow,13).setValue(date10[k][9]);
      //トレーニング-人数数え-漢字合成-直前写真-瞬間記憶
      st.getRange(lastrow,14).setValue(date10[k][10]);
      st.getRange(lastrow,15).setFormulaR1C1('=IF(ISNUMBER(RC[1]),IF(RC[1]>1,IFERROR(("00:00:"&RC[1])*1),RC[1]),IFERROR(("00:"&RC[1])*1))');
      st.getRange(lastrow,16).setValue(date10[k][11]);
      st.getRange(lastrow,17).setFormulaR1C1('=IF(ISNUMBER(RC[1]),IF(RC[1]>1,IFERROR(("00:00:"&RC[1])*1),RC[1]),IFERROR(("00:"&RC[1])*1))');
      st.getRange(lastrow,18).setValue(date10[k][12]);
      st.getRange(lastrow,20).setFormulaR1C1('=IF(ISNUMBER(RC[1]),IF(RC[1]>1,IFERROR(("00:00:"&RC[1])*1),RC[1]),IFERROR(("00:"&RC[1])*1))');
      st.getRange(lastrow,19).setValue(date10[k][13]);
      //トレーニング-指計算-指体操-二重課題-数独級-数独数-GmailID
      st.getRange(lastrow,21).setValue(date10[k][14]);
      st.getRange(lastrow,22).setFormulaR1C1('=IF(ISNUMBER(RC[1]),IF(RC[1]>1,IFERROR(("00:00:"&RC[1])*1),RC[1]),IFERROR(("00:"&RC[1])*1))');
      st.getRange(lastrow,23).setValue(date10[k][15]);
      st.getRange(lastrow,24).setFormulaR1C1('=IF(ISNUMBER(RC[1]),IF(RC[1]>1,IFERROR(("00:00:"&RC[1])*1),RC[1]),IFERROR(("00:"&RC[1])*1))');
      st.getRange(lastrow,25).setValue(date10[k][16]);
      st.getRange(lastrow,26).setValue(date10[k][17]);
      st.getRange(lastrow,27).setValue(date10[k][18]);
      st.getRange(lastrow,28).setValue(date10[k][19]);
      
      const lastrow1 = st2.getLastRow()+1;
      st2.getRange(lastrow1,1).setValue(date10[k][20])
  }
}

実行結果

関数が書き込まれたので、連続記録が表示されるようになりました。また、この下は空白なので、何も書き込まれません。

最後に

今回は、関数をGASで書き込むことによって、GAS内の.getLastRowが正常に動き、スプレッドシートの中もきちんと書き込まれるようになりました。これで、GASとスプレッドシートは完成したので、次回はトリガーを設定して自分で実行しなくても、勝手に書き込まれるようにしていきたいと思います。

ケーススタディでしっかり身につく！　Google Apps Script超入門

Amazon
楽天
サーバーレスでお手軽自動化！Google Apps Script活用入門　2018年最新改訂版 (技術の泉シリーズ（NextPublishing）)

Amazon
楽天

PC,GASGmail,Google,Googleスプレッドシート,Google Apps Script,スクレイピング

Posted by pyonnyan