Google Sheets, Add row based on cell value with formulas included

Question

I have a fairly complicated (for me at least) table in a google sheets spreadsheet designed to track stock holdings over financial years (it also includes calculations for cost base adjustments and capital gains).

The table functions in two halves, the left side details the purchase including price, amount purchased and dates where the right relates to disposal of stock. I am trying to allow for calculations where the entire purchase order is not disposed of at the same time, IE, if 15 are purchased but only 4 are sold with the further 11 to be sold at a later time.

The easiest to read way I can see this being implemented is if a formula / script can read the number sold and if below the purchase amount, add a row below, filling the appropriate values where required to allow for further calculation. Unfortunately I have absolutely no idea how to get this to work.

Is there a function within Google sheets to allow for adding of rows based on the values of a cell? if so would I be able to also have it fill the appropriate formulas for the table to allow for appropriate calculation?

As an example of what I'm hoping for, in the image below is a sample of the table currently, if K8 < D8 having another row added below prefilling formulas and values from row 8 with the exception of columns A B C D F G K L N and O which may be left blank and column H which should prefill as the value of H8 rather than the underlying formula.

I know this is very niche and assuming it's even possible, if anyone were to have any suggestions on where to even start looking, youtube videos or whatnot it would be greatly appreciated.

Many thanks.

Sample Image of Table

[EDIT : I was directed to the Apps script documentation by a user on another forum, with some slight stealing off of stackoverflow, encouragement from users there and the documentation I was able to code the function myself]

user_15752942 · Answer

As an update, with some slight help and a healthy amount of stealing I was able to put this scripting in place to do exactly what I needed.

// global 
var ss = SpreadsheetApp.getActive();

function onOpen() {
  var menu = [{name:"Split stock parcel", functionName:"addRow"}];
  ss.addMenu("Split Sale", menu);
}

function addRow() {
  var sh = ss.getActiveSheet();
  var cell = sh.getActiveCell();
  var SelectRow = cell.getRow(); 
  var lCol = sh.getLastColumn(), range = sh.getRange(SelectRow,5,1,1), range1 = sh.getRange(SelectRow, 9, 1, 2), range2 = sh.getRange(SelectRow,13,1,1), range3 = sh.getRange(SelectRow,16,1,4), clone = sh.getRange(SelectRow,8,1,1).getFormula(), clone1 = sh.getRange(SelectRow+1,8,1,1), range4 = sh.getRange(SelectRow+1,1,1,1), range5 = sh.getRange(SelectRow,3,1,1); 
  sh.insertRowsAfter(SelectRow, 1);
  range.copyTo(sh.getRange(SelectRow+1, 5, 1, 1), {contentsOnly:false});
  range1.copyTo(sh.getRange(SelectRow+1, 9, 1, 2), {contentsOnly:false});
  range2.copyTo(sh.getRange(SelectRow+1, 13, 1, 1), {contentsOnly:false});
  range3.copyTo(sh.getRange(SelectRow+1, 16, 1, 4), {contentsOnly:false});
  range5.copyTo(sh.getRange(SelectRow+1,3,1,1),{contentsOnly:false});
 clone1.setFormula(clone)
 range4.setValue("Split Sale")

Google Sheets, Add row based on cell value with formulas included

1 Answers