Extracting data from auto generated Invoice that is in a calculated field and not collected by the form

Question

I have an Inventory tracker and Work Order generator that utilizes two forms and one sheet. The "Work Order" tab shows the most recent work order entered and is populated with form responses from the "Work Order Responses" tab.

I would like to add another tab that compiles all fields found in the Work Order tab, including the calculated field for "Materials Total" in F31, every time a new work order is generated. I just want to have all the work order history under one new tab, organized by invoice number.

(link to file removed by author)

Thanks in advance for any help you can give me with this!

Answer 1

That requires a script. Collect all fields into one row like this:

=lambda( 
  fields, 
  transpose( filter(fields, len(fields)) ) 
)( { F6; F5; F30:F33; A34; B5:B7; flatten(A13:F29) } )

Then use a script to append those values to an archive sheet when you click a button.

See Apps Script copy row value in History Sheet to first empty row for an example of a suitable script.