Jump to content

Google Sheets, Scripting to add row based on cell value with formulas included

The Flying Sloth

Hi All,
Apologies for not providing more specific information about the coding itself, I'm beyond useless as a coder so don't have much further information.

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 a row should be added to allow for further calculations of gains on the remaining 11.

 

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 this image is a sample of the table currently (though obviously there are many more rows), 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've looked into this stackoverflow thread which is concerning adding a number of rows based on  the value of a cell, however, my usecase is complicated by the inclusion of formulas and values from the original row. Being that it's in table form it would need to be able to be applied selectively to each and every row if required.

 

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.
I'm not expecting someone to have pre-written code I could just copy but if anyone knows functions I could be using and resources for learning the basics of scripting for Google Sheets so I can have a go myself it would be a massive help.

Many thanks.

 

Sloth's the name, audio gear is the game
I'll do my best to lend a hand to anyone with audio questions, studio gear and value for money are my primary focus.

Click here for my Microphone and Interface guide, tips and recommendations
 

For advice I rely on The Brains Trust :
@rice guru
- Headphones, Earphones and personal audio for any budget 
@Derkoli- High end specialist and allround knowledgeable bloke

Link to comment
Share on other sites

Link to post
Share on other sites

3 hours ago, The Flying Sloth said:

resources for learning the basics of scripting for Google Sheets so I can have a go myself it would be a massive help.

You might want to take a look at JavaScript first if you take this route.

 

This looks like a pretty good interactive course for free: https://www.freecodecamp.org/learn/javascript-algorithms-and-data-structures/basic-javascript/ And this is the related video If you don't like this one there are thousands of other free and paid alternatives.

 

 

The Apps Script documentation is pretty good. It contains Guides and samples as well.

 

 

 

 

 

ಠ_ಠ

Link to comment
Share on other sites

Link to post
Share on other sites

3 hours ago, shadow_ray said:

You might want to take a look at JavaScript first if you take this route.

 

This looks like a pretty good interactive course for free: https://www.freecodecamp.org/learn/javascript-algorithms-and-data-structures/basic-javascript/ And this is the related video If you don't like this one there are thousands of other free and paid alternatives.

 

 

The Apps Script documentation is pretty good. It contains Guides and samples as well.

Thanks for the heads up on the documentation, I ended up able to just steal a basic row adding script and then handle the values myself, I'm currently using the code below and it's working great.

// 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")
}

I have quite a bit of experience with complicated google sheets forumulas but that was my first time actually coding anything and it wasn't too painful once I got the hang of it.

Thanks for the help.

Sloth's the name, audio gear is the game
I'll do my best to lend a hand to anyone with audio questions, studio gear and value for money are my primary focus.

Click here for my Microphone and Interface guide, tips and recommendations
 

For advice I rely on The Brains Trust :
@rice guru
- Headphones, Earphones and personal audio for any budget 
@Derkoli- High end specialist and allround knowledgeable bloke

Link to comment
Share on other sites

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×