Google Apps Script to extract data from Gmail and fill in matching row in Google Sheets-CodePudding

I have a Google Sheets that look like this...

My goal is to get certain email in my Gmail account and extract the data in the email and populate column C with the matching value from Column A.

This is the sample email body...

New status update for your shipment!
Tracking No. JK5SD8F4M6
Items Product A
Status At Warehouse 2

Following the tutorial from https://youtu.be/gdgCVqtcIw4 , I manage to extract the data and append to a new row in the sheet using this function.

function extractDetails(message){

  var emailData = {
    body: "Null",
    date: "Null",
    trackingno: "Null",
  }

  emailData.date = message.getDate();
  emailData.body = message.getPlainBody();
  emailData.trackingno = emailData.body.match(/(?<=Tracking No. ).*/).toString().trim();

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  activeSheet.getSheetByName('Sheet1').appendRow([emailData.trackingno, emailData.date]);

}

However, this function only add the data into a new row.

How do I make it so that it finds the matching tracking number on column A and fill in the date on corresponding cell in column C?

CodePudding user response：

I believe your goal as follows.

You have a Spreadsheet including the values of "Tracking No." at the column "A".
You want to retrieve the value of "Tracking No." and date from an email and put the date to the column "C" of the same row of the existing "Tracking No.".

In this case, how about the following modification?

From:

var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
activeSheet.getSheetByName('Sheet1').appendRow([emailData.trackingno, emailData.date]);

To:

var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = activeSheet.getSheetByName('Sheet1');
var range = sheet.getRange("A2:A"   sheet.getLastRow()).createTextFinder(emailData.trackingno).findNext();
if (range) {
  range.offset(0, 2).setValue(emailData.date);
} else {
  sheet.appendRow([emailData.trackingno, emailData.date]);
}

In this modification, emailData.trackingno is searched from the column "A" using TextFinder and when the existing emailData.trackingno is found, emailData.date is put to the column "C". When the existing emailData.trackingno is not found, [emailData.trackingno, emailData.date] is appended to the sheet.
If you don't want to do anything when the existing emailData.trackingno is not found, please remove sheet.appendRow([emailData.trackingno, emailData.date]);.

From:

To:

References: