Google sheet script to search and retrieve Gmail data-CodePudding

I've started a script that matches an email address in Col D with Gmail and writes the last message date in Col E. I'd like that script to repeat row by row - now it only writes to Row 2 (newbie question - sorry).

I'd also like to search for data in addition to "from". I'd like to also search "to" and other variables.

Lastly, I'd like this to have a trigger that is time-based (daily or weekly) and I'm still getting my head wrapped around triggers, so any help is really appreciated.

function myFunction() {
// Get the value in the cell D2 (which will have the email to check the latest interaction)
var ss = 
SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('D2').getValue();

// Search the date of the last message of the search from the email in cell D1 and log it
var latestEmail = GmailApp.search('from:"' ss '"')[0].getLastMessageDate();
Logger.log(latestEmail);

// Print the date on the cell to the right
SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('E2').setValue(latestEmail);
}

CodePudding user response：

I'd like that script to repeat row by row - now it only writes to Row 2

You will need to iterate ("loop") through your sheet's rows.

Try this:

function myFunction() {

  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1')
  const addresses = sheet.getRange(2, 4, sheet.getLastRow()-1).getValues().flat().filter(Boolean)

  for (let [index, address] of addresses.entries()) {
    const latestEmail = GmailApp.search(`from: ${address}`)[0].getLastMessageDate()
    sheet.getRange(index 2, 5).setValue(latestEmail);
  }

}

Commented:

function myFunction() {

  // Your sheet.
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1')
  // All cells in Column D (Column 4).
  const addresses = sheet.getRange(2, 4, sheet.getLastRow()-1).getValues().flat().filter(Boolean)

  // For each address in Column D... (While keeping track of the index)
  for (let [index, address] of addresses.entries()) {
    // Get the last message date from this address...
    const latestEmail = GmailApp.search(`from: ${address}`)[0].getLastMessageDate()
    // And set the adjacent cell to that date.
    sheet.getRange(index 2, 5).setValue(latestEmail);
    // "Index 2" because index starts at 0 and we started our range at row 2.
  }

}

I'd also like to search for data in addition to "from". I'd like to also search "to" and other variables.

That can all be done using GmailApp.search()

Lastly, I'd like this to have a trigger that is time-based (daily or weekly) and I'm still getting my head wrapped around triggers, so any help is really appreciated.

Couldn't be easier, see here:

Managing triggers manually

In your Script Editor, click the Triggers icon and then click the Add Trigger button.

You will be looking to choose this function, from event source: "Time-driven", and select when you would like it to run.