Question

I m trying to make a script to search my inbox for email replies and timestamp my sheet when I reply is found.

I have email adresses in Col A that I have emailed and when any of them reply/email me back I then want to record the reply by putting a timestamp in Col C on the same row of the email that have replied.

What I have managed to so far:

I have rewritten the script many times and been able to timestamp when a reply is found on only 1 email adress but when there is more emails I get stuck.

I m trying to figure out how to write the loop and if statements to make the script loop through every row of emails and timestamp the correct rows.

I m not an expert at this but I do now have a decent understanding around things hence to all the searching, googling and youtubeing to edjucate myself. I have been trying to make this work for weeks and without success, would really appreciate some help and input on what is wrong here.

Here is the sequence that I m trying to achieve explained:

1. The search

Check if there is email adresses in a Col A
If there is emails then check if a response is already recorded (timestamp) in Col C

2. Recorded reply

If there is a timestamp in Col C then skip that row and go to the next one and repeat step 1

3. No reply

If there is no timestamp then do a search with the email on that row in my inbox for replies.
If no reply is found then skip the row and repeat step 1

4. Reply found

If a reply is found then timestamp Col C in the same row and go to the next one

5. Searched through all rows with data

When the script has gone through every row with data in it will stop and exit the script.

Here is a link to the sheet and the script down below.

Spreadsheet: https://docs.google.com/spreadsheets/d/1HzLRtSFULijIVbtW8xcIPDOgQKCYdQ4TLN2FgEtSqIg/edit?usp=sharing

Script:

function myFunction() {

  
  const sheet = SpreadsheetApp.openByUrl( https://docs.google.com/spreadsheets/d/1HzLRtSFULijIVbtW8xcIPDOgQKCYdQ4TLN2FgEtSqIg/edit#gid=1870420095 ).getSheetByName( Sheet1 );
  const email = sheet.getRange( A:A ).getValues().flat();
  const responseColumn = sheet.getRange( C:C ).getValues().flat();
  const q = GmailApp.search(email); 


  
    var messages = q[0].threads.getMessages();
  for (var i = 0; i < email.length; i++) {
    if (email[i]) { // Make sure the cell is not empty
     if (responseColumn[i]) {
        continue; // Skip if response already recorded
      }
   
      
      

      if (q.length > 1){
    
        

        for (var i = 0; i < messages.length; i++)
  {sheet.getRange(i + 2, 3).setValue(new Date());
        } 

Logger.log(email);
            
  
} 
} 
} 
}

Answer 1

You have a list of email addresses (in Column A of your spreadsheet) to whom you have sent an email. In Column C of the spreadsheet you have a date (adjacent to the relevant email address) which shows whether or not you have received a reply.

The process involved is as follows:

get the values for address and date (if any)
loop through the email addresses
- for each email address, test if the value in Column C contains a date
- if yes, then move to the next email address
- if no, then test for a reply
  - search emails for a reply (from:(<<insert email>>)
  - if threads are found, then insert a date in Column C

Insert these scripts in the Project editor of the spreadsheet containing the Email and Response data.
Assume a single header row in row#1
Execute findEmail

function findEmail() {

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getSheetByName( Sheet1 )
  const numRows = sheet.getLastRow()-1 //(subtract header row)
  const emailData = sheet.getRange(2,1,numRows,3).getValues()
  Logger.log("DEBUG: email range = "+sheet.getRange(2,1,numRows,3).getA1Notation())
  const emailSent = emailData.map(function(e){return e[0]})
  Logger.log("DEBUG: Number of email addresses = "+emailSent.length)
 
  for (var i=0;i<emailSent.length;i++)
  {
    var result = isValidDate(emailData[i][2])
    if (result == true){
      // there s a date in Column C - next row
      Logger.log("DEBUG: i="+i+", email address = "+emailData[i][0]+", ColC = "+emailData[i][2]+" , the ColC value is a date")
    }else
    {
      // there s no date in column C, check for response
      Logger.log("DEBUG: i="+i+", email address = "+emailData[i][0]+" , the ColC value is NOT a date")
      const searchTerm = "from:("+emailData[i][0]+")"
      const threads = GmailApp.search(searchTerm); 
      // if there are threads, then insert date in Column C
      if (threads.length >= 1){
        var tz = ss.getSpreadsheetTimeZone()
        const date = new Date();
        sheet.getRange(i+2,3,1,1).setValue(Utilities.formatDate(date, tz,  d/MM/yyyy ))
      }
    }
  }
}

//so_1353684
// Detecting an "invalid date" Date instance in JavaScript
function isValidDate(d) {
  return d instanceof Date && !isNaN(d);
}

SAMPLE

友情链接