How can I search for the last email received from a domain in Google Sheets?

Question

I have a list of people who I have contacted in the past, and I want to pull the last email that they sent to me in google sheets.

I found this: https://dilipkashyap15.medium.com/how-to-fetch-gmail-data-in-google-spreadsheet-using-google-apps-script-b9c63b260f8f

Which works for me to get emails from the inbox.

I modified the code to

function findLastEmail(text){
var threads = GmailApp.search('from:"'&text&'"');
var messages = threads[threads.length - 1].getMessages();
var msgCount = threads[threads.length - 1].getMessageCount();
message = messages[messages.length - 1];
extractDetails(message,msgCount)
}

However, when I try to run this using a custom function (typing =findLastEmail("[email protected]")), I get the following error -

Exception: The script does not have permission to perform that action. Required permissions: (https://www.googleapis.com/auth/gmail.metadata || https://www.googleapis.com/auth/gmail.readonly || https://www.googleapis.com/auth/gmail.modify || https://mail.google.com/) at findLastEmail(Code:25:24)

However, I do not receive any error if I manually change the function to

function findLastEmail(text){
var threads = GmailApp.search('from:"[email protected]"');
var messages = threads[threads.length - 1].getMessages();
var msgCount = threads[threads.length - 1].getMessageCount();
message = messages[messages.length - 1];
extractDetails(message,msgCount)
}

and run from the Apps Scripts

Answer 1

On Google Sheets extended by Google Apps Scripts, custom functions have limitations that JavaScript functions run from the Google Apps Script editor haven't. One of those limitations is that they can't call services that require authorization, like GmailApp. For details, see https://developers.google.com/apps-script/guides/sheets/functions.

Answer 2

function findLastEmail(domain) {
  let idA = [];
  const ts = GmailApp.search(`From:${domain}`);
  ts.forEach(t => {
    let ms = t.getMessages();
    ms.forEach(m => {
      idA.push({ id: m.getId(), date: m.getDate() });
    })
  })
  idA.sort((a, b) => {
    return b.date.valueOf() - a.date.valueOf();
  })
  let m = GmailApp.getMessageById(idA[0].id);
  Logger.log('Date: %s,Subject: %s, Message: %s', m.getDate(), m.getSubject(), m.getPlainBody());
}