automate the creation of a new major column

[mengwong]

when a startup that is already well established with Legalese wants to raise a new round, they don't know how to fiddle with the legalese infrastructure to make it happen.

What needs to happen is this:

• there needs to be a new major column in the Cap Table spreadsheet.
• there needs to be a new sheet in the company's spreadsheet.
• they need to have the same name. The tab should have the same name as the title of the major column.

When a new column is inserted, the cell formulas point to the old columns, but need to be updated to point to the new columns:

• the TOTAL column of the spreadsheet should contain cell formulas that add the appropriate cells of the new major column.
• the price per share, pre-money valuation, and other cells of the new column should point to the correct cells of the previous column.
• the price per share, etc, of the TOTAL column should point to the new column.

So, encapsulating all of this functionality, there should be a menu option under Add-Ons / Legalese that says Create a New Round.

This would create a major column (set of 3 physical columns) to the left of the existing TOTAL major column.

We would need to rewire some of the column formulas to acknowledge the new column.

Some work has already been done in this direction by Lauren and Arjun, but we need to pick up that work.

To learn what the script will do, create a new major column manually and record in this issue's description field the specific actions taken to make the above assertions true.

The specific actions are:

• Clone a new investment sheet from the Legalese Demo Master into the target spreadsheet.

from https://docs.google.com/spreadsheets/d/1rBuKOWSqRE7QgKgF6uVWR9www4LoLho4UjOCHPQplhw/edit#gid=1632229599 click on the tab arrow at the bottom of the page; say Copy to… https://docs.google.com/spreadsheets/d/1P1YBHZhp3rWGUQGazb3mgPh724-S5csiuTcnOwfhSr8/edit#gid=827871932

• Rename the "Copy of Seed Round" sheet to be consistent with the actual round name.

We can ask the end-user for the name of the round, if there is an opportunity to seek input without making it too much like a wizard. Or we can give it a date, like My April 2016 Round.

• Copy the three physical columns immediately to the left of the TOTAL major column.

Select them, then insert 3 columns right, then paste.

• Rename row 2 of the new major column to be exactly the same as the name of the new sheet.
• Update the cell formulas. Assuming the new column is KLM,
• K6 should point to ='My April Round'!$B8
• L6 should point to I15
• N6 should point to K15
• O6 should point to L15
• K3 should point to the new tab's B12, or whatever happens to be the Security Type Plural.
• the TOTAL sum columns N9 and O9 down need to include a term from the new column.
• when doing this by hand i add the new term, copy the cell, then select the entire range of money & shares, and Edit / paste special / formula only.

In most cases, a new round means new investors.

Insert a couple of new rows in the Cap Table spreadsheet, above the "amount raised" line.

You will observe that the sum formulas in the "amount raised" line do not include the new rows. This is super annoying. Update the sum formulas to include the newly created lines.

Change the background colours of the cells – yellow means the end user is to fill in.

Ask the end-user for the names of the new investors. Or, just create a couple of new defaults. Put those names in the Entities tab, immediately below any existing Investor lines.

Get the new investors' names into the Cap Table using a formula like =Entities!B18

Create some obviously fake default values for the investment amount.

The TOTAL column needs to handle the new investors correctly.

If the security type of the round is equity, then the investors get shares. Otherwise they don't. A simple formula like =if('My April Round'!$B$16="equity",floor(K14/L$7),"") fills the shares correctly.

Both the cap table and the new round tab should now be sufficiently well-formed (see #108) to produce PDFs.

Work continues in https://script.google.com/a/legalese.io/d/16_rsbUnNi6sUvHk2ecstvPRkLVAi5kzDw59nlpSt2zByVcSIK3OG2KYg/edit?usp=drive_web

[wtf]

Using =SUM(K9:INDIRECT(ADDRESS(ROW()-1,COLUMN()))) removes the need to update the "amount raised" formula for new rows. We can use this whenever we need to sum "all values above this cell"

[wtf]

• N6 should point to K15
• O6 should point to L15

Are these really needed? It doesn't seem right for them to be displayed in the "pre-money" row.

[mengwong]

blow away cell E6.

[mengwong]

if user clicks "cancel" when prompted with the dialog, don't add a blank column -- don't do anything.

[mengwong]

the =SUM formula in the "amount raised" row doesn't span the correct range of rows … pls fix

[mengwong]

the new column should not carry over content from previous columns, just formulas.

[mengwong]

the "price per share" of the investment detail sheet should link back to the new columns row 7; currently it links back to I7

[wtf]

We should update all "amount raised" (and similar) formulas to =SUM(<colName>9:INDIRECT(ADDRESS(ROW()-1,COLUMN()))) in all spreadsheets. This eliminates the need to ever update them again.

[mengwong]

i got a red error message when running "add column" on the legalese auto sheet.

Cannot find method getRange(number,(class)).

logs:

[16-11-11 10:54:47:625 PST] captable I am checking column 55 [16-11-11 10:54:47:627 PST] captable I am checking column 56 [16-11-11 10:54:47:629 PST] captable I am checking column 57 [16-11-11 10:54:47:631 PST] captable I am checking column 58 [16-11-11 10:54:47:632 PST] captable Round does not exist [16-11-11 10:54:47:633 PST] captable termrow is: undefined, capRow is: 3.0, roundCol is: undefined

[16-11-11 10:54:47:630 PST] Sheet.getRange([2, 57]) [0 seconds] [16-11-11 10:54:47:630 PST] Range.getValue() [0 seconds] [16-11-11 10:54:47:631 PST] Logger.log([captable I am checking column 58, []]) [0 seconds] [16-11-11 10:54:47:632 PST] Sheet.getRange([2, 58]) [0 seconds] [16-11-11 10:54:47:632 PST] Range.getValue() [0 seconds] [16-11-11 10:54:47:633 PST] Logger.log([captable Round does not exist, []]) [0 seconds] [16-11-11 10:54:47:633 PST] Logger.log([captable termrow is: %s, capRow is: %s, roundCol is: %s, [undefined, 3.0, undefined]]) [0 seconds] [16-11-11 10:54:47:740 PST] Execution failed: Cannot find method getRange(number,(class)). (line 1835, file "captable", project "legaleseMain") [13.889 seconds total runtime]