Email script - How do I make it to loop through every row?

[Eric Morandeau]

Hi!

I'm having trouble with a email search script that is searching for replies.

Here is how it works:

I have a script that is sending emails to email adresses that I have in Col A (This part is working) and when any of them reply to my message I want to record that they replied in Col C with a timestamp.

So I'm trying to make a time triggered search script loop through every row and search for replies in the following sequence:

1. The search

• Check if there is a email in a row in Col A 
• If there is a email adress then check if a response is recorded (timestamp) in Col C on the same row

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 replies is found then skip the row and repeat step 1

4. Searched through all rows with data

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

At the moment I'm only able to do the search if there is only 1 email in Col A and when I put more emails in the column I get this error 

"TypeError: Cannot read properties of undefined (reading 'getMessages')

myFunction

@ Code.gs:24"

I have been trying to make this work for weeks and without success, would really appreciate some help and input on with is wrong here.

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);

}

}

}

}

[Eric Morandeau]

I forgot to put in the step when reply is found.

So the sequence is the following:

1. The search

• Check if there is a email in a row in Col A 
• If there is a email adress then check if a response is recorded (timestamp) in Col C on the same row

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 replies 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 and repeat step 1

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.

[Brett Grear]

At a glance your issue is with this line. - 

    var messages = q[0].threads.getMessages();

Maybe run an if statement above that checks I'd q[0].threads exists before calling getmessages()