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

Question

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?

Tanaike · Accepted Answer · 2021-09-24 04:49:35Z

1

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

References:

answered Sep 24, 2021 at 4:49

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Masterkenobi Over a year ago

arigato Tanaike san. it works as expected. Do you mind to help me on this as well? stackoverflow.com/questions/69313560/…

Collectives™ on Stack Overflow

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

1 Answer 1

From:

To:

References:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

References:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related