Continuing from

Currently, the script is like this...

function extractDetails(message){

  var emailData = {
    body: "Null",
    trackingno: "Null",
    weight: "Null",
    orderno: "Null",
    courier: "Null",
    shipmentno: "Null"
  }

  emailData.body = message.getPlainBody();
  emailData.trackingno = emailData.body.match(/(?<=Tracking No. \(Actual Weight／Chargeable Weight\) ).*/).toString().trim();
  emailData.weight = emailData.body.match(/????????/).toString().trim();
  emailData.orderno = emailData.body.match(/(?<=Order No. ).*/).toString().trim();
  emailData.courier = emailData.body.match(/(?<=Courier ).*/).toString().trim();
  emailData.shipmentno = emailData.body.match(/(?<=Shipment No. ).*/).toString().trim();

  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, 3).setValue(emailData.weight);
    range.offset(0, 4).setValue(emailData.orderno);
    range.offset(0, 5).setValue(emailData.courier);
    range.offset(0, 6).setValue(emailData.shipmentno);
  } else {
    sheet.appendRow([emailData.trackingno, '', '', emailData.weight, emailData.orderno, emailData.courier, emailData.shipmentno]);
  }
}

I know there are few errors in the above script. First, I have no idea how to write the regex to find the weight. Then, I don't know how to extract all tracking numbers. I should probably store the extracted the tracking numbers in an array and then do the matching using for loop. Please help. Thanks.

CodePudding user response：

I believe your goal is as follows.

• You want to retrieve the values of Order No, Courier, Shipment No, Parcel Weight, and Tracking No from the following text.

    Your order is ready to be redeemed/ delivered. 
    Order No. 91401111 
    Tracking No. (Actual Weight／Chargeable Weight) JK5SD8F4M6 (2.1lb) 
    J6HDO9L665 (2.1lb) 
    J3SDG76435 (9.8lb) 
    Courier UPS
    Shipment No. 23879924905
  

• You want to put the values to the Spreadsheet by searching the tracking No.

• You want to achieve this using Google Apps Script.

In this case, how about the following sample script? In this case, I used your sample text for testing the script.

Sample script:

I think that in your script, text of my sample script is the same as emailData.body.

function myFunction(){
  // This text is from your question.
  const text = `Your order is ready to be redeemed/ delivered. 
Order No. 91401111 
Tracking No. (Actual Weight／Chargeable Weight) JK5SD8F4M6 (2.1lb) 
J6HDO9L665 (2.1lb) 
J3SDG76435 (9.8lb) 
Courier UPS
Shipment No. 23879924905`;

  // 1. Retrieve each value you want to retrieve.
  const obj1 = text.split("\n").reduce((o, e) => {
    const regex = new RegExp("([A-Z0-9] ) \\(([0-9.] )lb\\)");
    if (e.includes("Order")) {
      o.orderNo = Number(e.trim().split(" ").pop());
    } else if (e.includes("Courier")) {
      const t = e.trim().split(" ");  // Modified
      t.shift();  // Modified
      o.courier = t.join(" ");  // Modified
    } else if (e.includes("Shipment")) {
      o.shipmentNo = Number(e.trim().split(" ").pop());
    } else if (regex.test(e)) {
      const [, trackingNo, parcelNo] = e.match(regex);
      o.trackingNo.push({trackingNo: trackingNo, parcelNo: parcelNo});
    }
    return o;
  }, {orderNo: "", courier: "", shipmentNo: "", trackingNo: []});

  // 2. Create an object for searching tracking No.
  const obj2 = obj1.trackingNo.reduce((o, {trackingNo, parcelNo}) => Object.assign(o, {[trackingNo]: [parcelNo, obj1.orderNo, obj1.courier, obj1.shipmentNo]}), {});

  // 3. Retrieve existing values from Spreadsheet and creating new values for putting to Spreadsheet.
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activeSheet.getSheetByName('Sheet1');
  var range = sheet.getRange("A2:G"   sheet.getLastRow());
  var newValues = range.getValues().map(([a, b, c, ...efg]) => {  // Modified
    var temp = [];
    if (obj2[a]) {
      temp = [a, b, c, ...obj2[a]];
      delete obj2[a];
    } else {
      temp = [a, b, c, ...efg];  // Modified
    }
    return temp;
  });

  // 4. If the tracking No is not found, the values are appended to the last row.
  var tempAr = Object.entries(obj2);
  if (tempAr.length > 0) {
     newValues = newValues.concat(tempAr.map(([k, v]) => [k, "", "", ...v]));
  }

  // 5. Clear sheet and put the new values to the sheet.
  range.clearContent();
  sheet.getRange(2, 1, newValues.length, newValues[0].length).setValues(newValues);
}

Note:

• In this sample script, when your text is different from the text in your question, the script might not be able to be used. So please be careful about this.

References:

• reduce()
• map()