I am writing code in Google Apps Script to search for a string in emails that appear when I search for a specific ticket number related to insurance claims. I have written code so far that takes a cell with a ticket number from a Google spreadsheet, searches that number in Gmail and returns any related emails to the spreadsheet to another column, "total amount reimbursed" on the ticket number's row. Then, it takes the date of the email and puts it in the "reimbursement date" column.
My problem is that I can't figure out how to do a search within a search. In other words, I need to search for the following text when the emails related to the ticket number are pulled up: "reimbursed your account X for...". Then, I need the code to pull the amount from the string (X) and put it in the "total amount reimbursed" column. When I run the code, it puts the entire email found related to the ticket number in the "total amount reimbursed" column.
Here is my code:
function searchGmail(){
var value = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tickets/Tolls Reimbursements
Master').getRange(8686,12).getValue();
if(value.length > 0)
var threads=GmailApp.search(value);
var myspreadsheet = SpreadsheetApp.openById("1Jn7r4...");
var mysheet = myspreadsheet.getSheets()[0];
var threads = GmailApp.search(value);;
var messages = GmailApp.getMessagesForThreads(threads);
var body = [];
var date = [];
for(var i = 0; i < threads.length; i++)
{
body.push([messages[i][0].getPlainBody(),i]);
}
mysheet.getRange(8686,19,threads.length,2).setValues(body);
for(var i = 0; i < threads.length; i++)
{
date.push([messages[i][0].getDate(),i]);
}
mysheet.getRange(8686,20,threads.length,2).setValues(date);
}
I would greatly appreciate any help here. I am new to coding and JavaScript, and I am really stuck.
You could try something like the code below. I use a regular expression to find the phrase you are looking for then the number that immediately follows the search phrase. I believe you can write the regular expression to exclude the search string from the results and just return the number, but I've never been very good with RegEx so, instead, I split the string up by each word and then grab the last word, which is the number, represented by X in your question. That should hopefully give you a start to figure out how you'd like to solve it.
for (var i = 0; i < threads.length; i++) {
// creates a regular expressiom that finds the phrase "reimbursed your account" and number that immedatly follows it
let regExp = new RegExp('reimbursed your account [0-9]+');
// assign the message text to a variable
let message = messages[i][0].getPlainBody();
// executes the regular expression on the message variable and stores the results in a new array called phrase
let phrase = regExp.exec(message);
// I assume the search phrase will only apprear once in the email. If appears more than once, you'll need to loop through pharse and deal with each instance appropriately.
// split the string into an array at every space in the string, pop off the last value (the number) and store it in num variable
let num = phrase[0].split(" ").pop()
body.push([num, I]);
}
mysheet.getRange(8686, 19, threads.length, 2).setValues(body);