How to Extract Values from Gmail Body using Google Apps Script against Each Key Value?-CodePudding

I am regularly getting emails from the bank regarding Rates of buying and selling against different funds, I want to extract the fund values from the email body and paste them into my google sheets on daily basis to analyze their ups and downs.

Till now what I achieve is extracted the email body as plain text and tried with praise and regex but failed to achieve only values.

below is my Code.

function NBPFundUpdate(){
  
  // SKIP TO OUT OF OFFICE HOURS AND DAYS
    var nowH=new Date().getHours();
    var nowD=new Date().getDay();
    //Logger.log(nowD);
    //Logger.log('day : ' nowD '   Hours : ' nowH)
    if (nowH>19||nowH<8||nowD==0) { return }
    //if (nowH>17||nowH<8||nowD==6||nowD==0) { return }

  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getActiveSheet();
  
    // START OPERATION
  var Gmail = GmailApp;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var MasterSheet = ss.getSheetByName("Master");
  var index = 2;
  var aa = 0;
  var na = 0;

  // SEARCH EMAIL
  var query = 'from: [email protected], subject: NBP FUNDS NAV';
  var threadsNew = Gmail.search(query);
  Logger.log(threadsNew.length);

  //check if thread found or not
  if (threadsNew.length ==0) { return }

  var lastscantime = threadsNew[0].getLastMessageDate();
  var master = ss.getSheetByName("Master");
  master.getRange("Z1").setValue(lastscantime);
  Logger.log(lastscantime);

  //loop all emails
  for(var n in threadsNew){
    var thdNew  = threadsNew[n]; 
    var msgsNew = thdNew.getMessages();　
    var msgNew = msgsNew[msgsNew.length-1];
  // GET ATTACHMENT
    var bodyNew = msgNew.getBody();
    var plainbody  = msgNew.getPlainBody();
    var subject = msgNew.getSubject();
    var Etime = msgNew.getDate();

    Logger.log(Etime);
    Logger.log(subject);
    Logger.log(plainbody);
    
    var NGSLFbuying = /NBP GOVERNMENT SECURITIES LIQUID FUND\n(.*?)$/gm.exec(plainbody);
    Logger.log(NGSLFbuying);

    var NGSLFSelling = /NBP GOVERNMENT SECURITIES LIQUID FUND\n(.*?)$/gm.exec(plainbody);
    Logger.log(NGSLFSelling);
    
  }
}

The Result of Logger.log(plainbody); is below.

result of Logger.log(NGSLFbuying); is null see below image Screenshot

For your reference, I want the final result should look like this in my google spreadsheet;

If somebuddy help me to achive this, shall be very thankfull to him / her.

CodePudding user response：

You are using \n but there is no line break before the prices.

const plainbody = `something before
NBP GOVERNMENT SECURITIES LIQUID FUND 1 2
something after`;

const [line, buy, sell] = /NBP GOVERNMENT SECURITIES LIQUID FUND (.*?) (.*?)$/gm.exec(plainbody);
console.log(line);
console.log(buy);
console.log(sell);