Google Form to sheet to Doc, with calculations after pushing to sheet

NOTE! I am not a programmer I’m just fumbling through this! I’ve gotten as far as I can with searches, getting lost on multiple steps as most responses don’t integrate on thing to the next.

Google Form pushes to Google Sheets and outputs a Google Doc prefilled template using GAS. This is for purchase order creation: Success It won’t let me post the code though…

I need the sheet to do some work once the form data populates (as an order PO, the form entry is quantity and price of each item and I need the total on the output PO). I need the resulting data to ALSO push to the Google Doc. This is where I’m getting stuck.

Assign each line a unique ID using an array in an added column to the form response sheet
Successful in creating the ID, although it doesn’t push to the doc with the other data. Defining the variable with the others doesn’t work here.

Is this a var definition issue, or does this have to do with order of operations/triggers? Maybe the only data being sent is the FORM DATA, not the CREATED DATA IN SHEETS from the form.

The array formula that populates “poID” upon submitting the form data:

=arrayformula( if( len(A2:A), "" & text(row(A2:A) - row(A2) + 2, "1100")))

Google sheet form response tab

On the third tab I created new cells to do calculations using the passed data from the form.

The data autofilled from the form doesn’t trigger the formulas on the second tab at all, as if the cells referenced were still empty (they are not).

Rows 2-6 are only populated in this image because I was making sure the formulas were correct, manually. When I took this screenshot there were already more form data lines on the response page that you can’t see on this tab.

Calculation Sheet

Obviously since these are not calculating on their own, there is nothing to pass to the form. Also cannot define those variables in the script.

I think this requires multiple scripts and/or multiple functions for this sheet.

Guessing since the array and calculations happen technically after the form has been submitted, it’s not passing. I can’t sort out how to:

  • define these variables (new script? new function?) and then,
  • perform calculations using that data, automatically (this must be an array thing like the poID is) and then,
  • get them to populate the same output document that the form data populates (new trigger?).

Any help here would be massively appreciated.