There's lots to unpack here.
Ostensibly, the ask isn't hard - extract some data from the email message and insert it into a Numbers sheet.
However, as is common with these kinds of requests, there are many gotchas.
Starting with the source - since this looks like a standard email form, it should be consistent, so that helps, but there is the question of how to identify the content in question.
My initial instinct would be to look for the first line that begins 'Lot ID', and assume the next line contains the data. Assumptions can always fail, though.
Alternatively, you could assume it's always the 6th line in the email, but email formats may change.
Ultimately, there needs to be some agreement/compromise on the method, so that you know what to change when things fail :)
Secondly, getting the data into Numbers.
The first issue is identifying where to put the data. Is the spreadsheet you want already open? Are you looking at the right sheet/table? (Note: you don't have to have the table visible at all in order to add data to it, but the script needs to know where to put the data.
Also, it's easy to append the data to the end of a table, but is there a footer row that needs to be taken account of?
Like I said, lots of gotchas.
Here's a skeleton script that should get you going. Applied as a Mail rule (so it sees the messages as they arrive in your inbox), it grabs the data and appends it to the bottom of the first table in the frontmost Numbers document.
This may need work if the columns in the table don't match the data (e.g. there are other columns, or they're out of order), and there is little error checking (to make sure the data exists, that Numbers is running with the spreadsheet open, etc.), but this can be added if the base functionality is valid (and based on the answers to the above questions).
The script should be saved in ~/Library/Application Scripts/com.apple.mail and use Mail preferences to run this script when the incoming triggers fire (presumably there's some identifying mark such as the sender's address, etc.)
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
-- standard preamble for invoking AppleScripts via mail rules
using terms from application "Mail"
on perform mail action with messages these_messages for rule this_rule
-- iterate through the new messages
repeat with each_message in these_messages
-- get the message body
set theInputText to content of each_message
-- and break it into paragraphs
set inputData to paragraphs of theInputText
-- count how many paragraphs there are
set num_paras to count of inputData
-- iterate through them
repeat with lineNum from 1 to num_params
-- find the header line
if word 1 of item lineNum of inputData = "Lot" then
-- found it, so assume the next line has the data we want
set dataLine to item (1 + lineNum) of inputData
-- and get out of the loop
exit repeat
end if
-- and add the data to Numbers
end repeat
my addRow(dataLine)
end repeat
end perform mail action with messages
end using terms from
on addRow(dataLine)
-- assume the data is tab-delimited, so break it into fields
set {oldTID, my text item delimiters} to {my text item delimiters, tab}
set cellValues to text items of (dataLine as text)
set my text item delimiters to oldTID
tell application "Numbers"
-- find the first table on the active document
set theTable to table 1 of active sheet of front document
-- add a new row
set newRow to add row below last row of theTable
-- iterate through the input data
repeat with colNum from 1 to count cellValues
-- and set the value of the cells
set value of (cell colNum of row (address of newRow) of theTable) to item colNum of cellValues
end repeat
end tell
end addRow
