GASを使って、定期的に送られるメールに添付されたcsvを自動でSpreadsheetに取り込む

##やったこと

1. Gmailから該当のメールを取得して
2. 添付ファイルを取得して
3. 添付ファイル(csv)をSpreadSheetに転記して
4. 1~3を定期的に実行するトリガーを作る

##はじめに
###参考URL
Qiita:Google Apps Scriptの日毎のトリガーで時間をもっと細かく設定する
　　ざっくりしてない定時トリガーの設定の方法
Qiita:GASのProperties Serviceを使ってスクリプトのプロパティを読み書きをする
　　プロパティを使って前回実行時の状況を保存・利用する

###きっかけ
他部署の事務方が「毎日18時に取引先のシステムから自動送信されるCSVが添付されたメールを、手作業で開いてダウンロードしてスプレッドシート開いてCSVインポートして貼り付ける」という虚無な作業を、毎日5分ぐらいかけてやっていた。
これをどうにかしてくれという依頼が業務改善担当の自分にきたのでGASで自動化した。

##実装

###全体

autoCsvToSpreadsheet.gs

Copied!

const searchConfig = "subject:*********"; //取得したいメールの件名の一部
const tempFolderId = "******************************"; //csvを一時的に保存するフォルダ
const ssId = "*************************"; //このspreadsheetのID

//その日の18時30分にトリガーを設定する
function setTrigger(){
  const time = new Date();
  time.setHours(18);
  time.setMinutes(30);
  ScriptApp.newTrigger("main").timeBased().at(time).create();
}

//実行済みのトリガーを削除する
function deleteTrigger(){
  let triggers = ScriptApp.getProjectTriggers();
  for(let i=0; i<triggers.length; i++){
    if(triggers[i].getHandlerFunction() == "main"){
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

function main(){
  deleteTrigger();
  //実行日時(=csvの取得日時)を取得
  let nowDate = new Date();
  nowDate = Utilities.formatDate(nowDate, "Asia/Tokyo", "最終取得日時: yyyy/MM/dd HH:mm:ss");
  console.log(nowDate); //debug
  
  //該当メールを検索
  let threads = GmailApp.search(searchConfig, 0, 3); //最大3件取得
  let messages = GmailApp.getMessagesForThread(threads[0]);
  let message = messages[0];
  let subject = message.getSubject();
  console.log("検索したメールのタイトル: "+subject);
  
  //プロパティから前回取得したメールのタイトルを取得
  let lastMessageSubject = PropertiesService.getScriptProperties().getProperty('LatestSubject');
  console.log("前回取得したメールのタイトル: "+lastMessageSubject);
  
  
  //前回取得したメールと今回取得したメールが違う場合に、処理を行う
  if(lastMessageSubject != subject){
    if(message.getAttachments().length = 0){
      console.log("添付ファイルがありません");
      return;
    }
    let attachmentBlobs = message.getAttachments();//メールの添付ファイルをblobの配列で取得
    let attachmentBlob = attachmentBlobs[0];//添付ファイル0番目をblobで取得
    
    //添付ファイルを一時フォルダに保存
    let tempFolder = DriveApp.getFolderById(tempFolderId);
    let tempFile = DriveApp.createFile(attachmentBlob).moveTo(tempFolder); 
    
    importCsv(tempFile);
    let sheet = SpreadsheetApp.openById(ssId).getSheetByName("readCSV");
    sheet.getRange(1,3).setValue(nowDate);
    PropertiesService.getScriptProperties().setProperty("LatestSubject", subject); //プロパティの値を更新
    tempFile.setTrashed(true); //一時保存したCSVを削除
  }//end if
  
}//end function getMessage

//CSVのFileを受け取って、SpreadSheetのreadCSVシートに転記する
function importCsv(readCsvFile){
  let ss = SpreadsheetApp.openById(ssId);
  let sheet = ss.getSheetByName("readCSV");
  
  let data = readCsvFile.getBlob().getDataAsString("UTF-8"); //文字コードを修正しないと文字化けする
  let csv = Utilities.parseCsv(data);
  
  console.log("csvの列と行のサイズ: " + csv.length + ", " + csv[0].length);
  sheet.getRange(2,1, csv.length, csv[0].length).setValues(csv);
}//end function readCsv

###メモ

• GASの日毎のトリガーは正確な日時でトリガーを実行してくれないので、大体の時間(本来の設定時間より前)に「トリガーを設定するスクリプトを実行するトリガー」を設定する必要がある

• 実行済みのトリガーを削除する処理を入れないとトリガーが無限にたまり続ける

• csvを削除する処理を入れないとCSVファイルがフォルダに無限にたまり続ける(→ドライブを圧迫する)

###その他

• 自動化の改善入れる前はCSVですらなく、Excel2003のXMLスプレッドシートが届いていた。(GoogleSpreadSheetで直接開けないしインポートできないので、担当がローカルのエクセルで毎回開いて(Excel形式に復元して)、手動でコピペしてた)

• 自動化にあたって取引先に頼んでフォーマットをCSVに変えてもらった。スクリプトでなんでも頑張るだけじゃなく、ある程度交渉でやりやすい形に変えるのも大事。その業務フローいらなくね？とか