use Google Apps Scripts to check gmail into Speadsheet

checkGmail.js

// Main処理
function checkMasterDeDiff() {
    var query = getQueryStr();

    var deNoMail = 'de_recovery_thanks_automation_no_mail';
    var deNoMailNum = getCountNum(query, deNoMail);
    var deNoMailRec = 'de_recovery_thanks_automation_no_mail_rec';
    var deNoMailRecNum = getCountNum(query, deNoMailRec);

    var deNoCar = 'de_recovery_thanks_automation_no_car';
    var deNoCarNum = getCountNum(query, deNoCar);
    var deNoCarRec = 'de_recovery_thanks_automation_no_car_rec';
    var deNoCarRecNum = getCountNum(query, deNoCarRec);

    var deNoMC = 'de_recovery_thanks_automation_no_mail_and_car';
    var deNoMCNum = getCountNum(query, deNoMC);
    var deNoMCRec = 'de_recovery_thanks_automation_no_mail_and_car_rec';
    var deNoMCRecNum = getCountNum(query, deNoMCRec);

    if (deNoMailNum || deNoMailRecNum || deNoCarNum || deNoCarRecNum || deNoMCNum || deNoMCRecNum) {
        fillCell(deNoMailNum, "C");
        fillCell(deNoMailRecNum, "G");
        fillCell(deNoCarNum, "D");
        fillCell(deNoCarRecNum, "H");
        fillCell(deNoMCNum, "E");
        fillCell(deNoMCRecNum, "I");
    } else {
        fillCell(0, "C");
        fillCell(0, "G");
        fillCell(0, "D");
        fillCell(0, "H");
        fillCell(0, "E");
        fillCell(0, "I");
    }
    console.log(deNoMail + ": " + deNoMailNum);
    console.log(deNoMailRec + ": " + deNoMailRecNum);
    console.log(deNoCar + ": " + deNoCarNum);
    console.log(deNoCarRec + ": " + deNoCarRecNum);
    console.log(deNoMC + ": " + deNoMCNum);
    console.log(deNoMCRec + ": " + deNoMCRecNum);
}

//Fill the cell
function fillCell(num, column) {
    var sht = SpreadsheetApp.getActive().getSheetByName("マスターズレ件数");
    var range = (column + ":" + column);
    var colVls = sht.getRange(range).getValues();
    var lastRow = colVls.filter(String).length;
    try {
        sht.getRange(column + (lastRow + 3)).setValue(num);
    } catch(e) {
        console.error('fill cell with an error: ' + e);
    }
}

// test case
//function test_getCountNum(){
//  var query = getQueryStr();
//  var deName = "de_recovery_thanks_automation_no_mail_rec";
//  var sNum = getCountNum(query, deName);
//  Logger.log(deName+" : "+sNum);
//}
//ゲットカウント件数
function getCountNum(query, deName) {
    var threads = GmailApp.search(query, 0, 8);
    for (var th in threads) {
        var msgs = threads[th].getMessages();
        for (var msg in msgs) {
            var body = msgs[msg].getPlainBody();
            body = body.replace(/[\r\n]/g, "");
            var start = body.indexOf('[VerifyAudienceCount]');
            var end = body.indexOf('ターゲットデータエクステンション');
            var dName = body.slice(start + 21, end);
            //      Logger.log("got de_name is: "+dName);
            if (dName == deName) {
                var sNum = getNum(body, 'レコードカウントは', '件でした');
            } else {
                var sNum = 0;
            }
        }
        break;
    }
    return sNum;
}

//メール本文からの件数抜き出し処理
function getNum(str, pre, suf) {
    str = str.replace(/[\r\n]/g, "");
    var start = str.indexOf(pre);
    start += pre.length;
    var end = str.indexOf('件でした');
    var num = parseInt(str.slice(start, end));
    console.log("get the count number is: " + num);
    return num;
}

// get date
function today() {
    var date = new Date();
    format_str = 'YYYY/MM/DD';
    format_str = format_str.replace(/YYYY/g, date.getFullYear());
    format_str = format_str.replace(/MM/g, date.getMonth() + 1);
    format_str = format_str.replace(/DD/g, date.getDate());
    //  Logger.log(format_str);
    console.log("get the date of today is: " + format_str);
    return format_str;
}

//get query string
function getQueryStr() {
    var queryStr = "subject:Marketing Cloud オートメーション検証範囲外: AS_recovery_thanks_automation after:" + today();
    //  var queryStr = "subject:Marketing Cloud オートメーション検証範囲外: AS_recovery_thanks_automation after:2019/06/09";
    console.log("the query is:" + queryStr);
    return queryStr;
}