Google sheet calculations following data insert

0
votes

enter image description here

I have a static site with a form which I am hosting on netlify . Using the netlify to sheets zap , when the form is submitted on the site, the data shows up as a row in my sheet, as expected.

After the insert which adds the last row. I want to do some apps script post processing, I'm triggering this using the onchange event. Before I do the post processing I want to fill in a couple of blank cells which are not submitted by the form, such as a date field and a TOTAL field. which takes the price cell to the left of it and adds 1500.

I originally thought of just using a formula:

 =(cell on left)+1500

and copying this down the row. The problem is that this would mess up my ability to get the last submitted row.

enter image description here

I need it to look like:

enter image description here

before I start additional processing.

Is there another approach to doing calculations within a specific row?

1
javascriptgoogle-apps-scriptgoogle-sheets
Does the onChange trigger actually trigger on row insert by a thing that is not human? Test and see. - TheMaster
Yes it is triggered by the sheet row insert. I'm using it to activate the post processing. How to trigger is not my question. I'm asking how best to do some intra row calculations? - user1592380
Total field can be easily done with just formula. What other fields are you expecting to fill up? - TheMaster
Yes but I want to keep the submitted row as the last row. Adding a column of formulas breaks this.Please see screenshot. - user1592380
I got that before your edit. I could show you a way with formula for the total field. But, if you want other fields, you need to show some scripts and explain other fields, positions and what have you tried so far... - TheMaster

1 Answers

1
votes

Assumptions:

  • PRICE is in A1
  • No blank cells in between rows

Strategy:

  • Clear B1:B
  • COUNT the number of cells in ColA
  • Use INDEX/INDIRECT to create a dynamic range with COUNT
  • Use Arrayformula with array literals to fill up Col B just up to the last filled ColA

Sample Formula:

B1:

=ARRAYFORMULA({"TOTAL";A2:INDEX(A:A,COUNTA(A:A))+1500})