How do I run automated data entry and formula in the same row?

[Molly_Molika1]

Hello everyone I having a problem related to my google sheet blow, I wrote a code script to run automated data entry with formula. However, I am encountering a slight issue where my automated data entry and formula code are unable to run in the same row. I would be immensely grateful if you could offer your assistance once again to help me resolve this matter. Would you kindly lend your support?

Thank you in advance.

My google sheet: 

My App script:

[luis_alegria]

Hi

First at all I think it's important to explain the ARRAYFORMULA function. I understand it in this way: this function repeat the operation over all the range you define inside the arrayformula, the function understand from and up to what row must run the operation.

Here an example:

And you can define a range over all the sheet up to the last row number (without know what is the last rownumber). My recommendation is to add a formula to check if the cell is empty, so return a empty value. 

So, after the explanation, In my opinion you could use one of this solutions:

a) Change your code to set the arrayformula in just M8 and N8 cells, but you must update the formula, for example:

• Here change the range from M8:M + lastRow to just M8
• Change the formula from '=ARRAYFORMULA (H8+I8+L8)' to =ARRAYFORMULA (H8:H +I8:I + L8:L)
  
  • I recommended you check if the cells are empty with this function to be sure 
    • =ARRAYFORMULA ( if ( and(H8:H<>"", I8:I<>"",L8:L<>""), H8:H +I8:I + L8:L, ) )
    • 

b) Write the array formula in the header cells, so you don't need to set it in the code

Note that I rewrite the ranges from row 8 to row 7: for example from H8:H to H7:H

The formula checks for rownumber 7 with ROW function, so if it's 7, it's the header and show the header instead doing the operations.

I set this formula in M7 cell

• =ARRAYFORMULA ( if ( row(H7:H)=7, "Header M7", if ( and(H7:H<>"", H7:I<>"",L7:L<>""), H7:H +I7:I + L7:L, ) ) )
• 

Well, I hope I explained this correctly and helps to you. And feel free to update the formulas based on your expected behavior.

Regards