{"action":"reopened","issue":{"url":"https://api.github.com/repos/legalese-io/legalese-io.github.io/issues/73","repository_url":"https://api.github.com/repos/legalese-io/legalese-io.github.io","labels_url":"https://api.github.com/repos/legalese-io/legalese-io.github.io/issues/73/labels{/name}","comments_url":"https://api.github.com/repos/legalese-io/legalese-io.github.io/issues/73/comments","events_url":"https://api.github.com/repos/legalese-io/legalese-io.github.io/issues/73/events","html_url":"https://github.com/legalese-io/legalese-io.github.io/issues/73","id":128511541,"number":73,"title":"automate the creation of a new major column","user":{"login":"mengwong","id":1480631,"avatar_url":"https://avatars.githubusercontent.com/u/1480631?v=3","gravatar_id":"","url":"https://api.github.com/users/mengwong","html_url":"https://github.com/mengwong","followers_url":"https://api.github.com/users/mengwong/followers","following_url":"https://api.github.com/users/mengwong/following{/other_user}","gists_url":"https://api.github.com/users/mengwong/gists{/gist_id}","starred_url":"https://api.github.com/users/mengwong/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mengwong/subscriptions","organizations_url":"https://api.github.com/users/mengwong/orgs","repos_url":"https://api.github.com/users/mengwong/repos","events_url":"https://api.github.com/users/mengwong/events{/privacy}","received_events_url":"https://api.github.com/users/mengwong/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"0x0ddba11","id":2460739,"avatar_url":"https://avatars.githubusercontent.com/u/2460739?v=3","gravatar_id":"","url":"https://api.github.com/users/0x0ddba11","html_url":"https://github.com/0x0ddba11","followers_url":"https://api.github.com/users/0x0ddba11/followers","following_url":"https://api.github.com/users/0x0ddba11/following{/other_user}","gists_url":"https://api.github.com/users/0x0ddba11/gists{/gist_id}","starred_url":"https://api.github.com/users/0x0ddba11/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/0x0ddba11/subscriptions","organizations_url":"https://api.github.com/users/0x0ddba11/orgs","repos_url":"https://api.github.com/users/0x0ddba11/repos","events_url":"https://api.github.com/users/0x0ddba11/events{/privacy}","received_events_url":"https://api.github.com/users/0x0ddba11/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/legalese-io/legalese-io.github.io/milestones/3","html_url":"https://github.com/legalese-io/legalese-io.github.io/milestones/v2.0%20general%20release%20end-user%20ready","labels_url":"https://api.github.com/repos/legalese-io/legalese-io.github.io/milestones/3/labels","id":1326727,"number":3,"title":"v2.0 general release end-user ready","description":"Better UI for end-users than just the raw Google Spreadsheet.\r\n\r\nCharge money.","creator":{"login":"mengwong","id":1480631,"avatar_url":"https://avatars.githubusercontent.com/u/1480631?v=3","gravatar_id":"","url":"https://api.github.com/users/mengwong","html_url":"https://github.com/mengwong","followers_url":"https://api.github.com/users/mengwong/followers","following_url":"https://api.github.com/users/mengwong/following{/other_user}","gists_url":"https://api.github.com/users/mengwong/gists{/gist_id}","starred_url":"https://api.github.com/users/mengwong/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mengwong/subscriptions","organizations_url":"https://api.github.com/users/mengwong/orgs","repos_url":"https://api.github.com/users/mengwong/repos","events_url":"https://api.github.com/users/mengwong/events{/privacy}","received_events_url":"https://api.github.com/users/mengwong/received_events","type":"User","site_admin":false},"open_issues":28,"closed_issues":2,"state":"open","created_at":"2015-09-28T13:01:37Z","updated_at":"2016-05-10T12:52:02Z","due_on":"2016-05-30T16:00:00Z","closed_at":null},"comments":3,"created_at":"2016-01-25T11:00:32Z","updated_at":"2016-05-10T12:52:02Z","closed_at":null,"body":"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.\r\n\r\nWhat needs to happen is this:\r\n- there needs to be a new major column in the Cap Table spreadsheet.\r\n- there needs to be a new sheet in the company's spreadsheet.\r\n- they need to have the same name. The tab should have the same name as the title of the major column.\r\n\r\nWhen a new column is inserted, the cell formulas point to the old columns, but need to be updated to point to the new columns:\r\n- the TOTAL column of the spreadsheet should contain cell formulas that add the appropriate cells of the new major column.\r\n- the price per share, pre-money valuation, and other cells of the new column should point to the correct cells of the previous column.\r\n- the price per share, etc, of the TOTAL column should point to the new column.\r\n\r\nSo, encapsulating all of this functionality, there should be a menu option under Add-Ons / Legalese that says Create a New Round.\r\n\r\nThis would create a major column (set of 3 physical columns) to the left of the existing TOTAL major column.\r\n\r\nWe would need to rewire some of the column formulas to acknowledge the new column.\r\n\r\nSome work has already been done in this direction by Lauren and Arjun, but we need to pick up that work.\r\n\r\nTo 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.\r\n\r\nThe specific actions are:\r\n\r\n- Clone a new investment sheet from the Legalese Demo Master into the target spreadsheet.\r\n\r\nfrom https://docs.google.com/spreadsheets/d/1rBuKOWSqRE7QgKgF6uVWR9www4LoLho4UjOCHPQplhw/edit#gid=1632229599\r\nclick on the tab arrow at the bottom of the page; say Copy to…\r\nhttps://docs.google.com/spreadsheets/d/1P1YBHZhp3rWGUQGazb3mgPh724-S5csiuTcnOwfhSr8/edit#gid=827871932\r\n\r\n\r\n- Rename the \"Copy of Seed Round\" sheet to be consistent with the actual round name.\r\n\r\nWe 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.\r\n\r\n- Copy the three physical columns immediately to the left of the TOTAL major column.\r\n\r\nSelect them, then insert 3 columns right, then paste.\r\n\r\n- Rename row 2 of the new major column to be exactly the same as the name of the new sheet.\r\n- Update the cell formulas. Assuming the new column is KLM,\r\n- K6 should point to ='My April Round'!$B8\r\n- L6 should point to I15\r\n- N6 should point to K15\r\n- O6 should point to L15\r\n- K3 should point to the new tab's B12, or whatever happens to be the Security Type Plural.\r\n- the TOTAL sum columns N9 and O9 down need to include a term from the new column.\r\n- 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.\r\n\r\nIn most cases, a new round means new investors.\r\n\r\nInsert a couple of new rows in the Cap Table spreadsheet, above the \"amount raised\" line.\r\n\r\nYou 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.\r\n\r\nChange the background colours of the cells – yellow means the end user is to fill in.\r\n\r\nAsk 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.\r\n\r\nGet the new investors' names into the Cap Table using a formula like =Entities!B18\r\n\r\nCreate some obviously fake default values for the investment amount.\r\n\r\nThe TOTAL column needs to handle the new investors correctly.\r\n\r\nIf the security type of the round is equity, then the investors get shares. Otherwise they don't.\r\nA simple formula like =if('My April Round'!$B$16=\"equity\",floor(K14/L$7),\"\") fills the shares correctly.\r\n\r\nBoth the cap table and the new round tab should now be sufficiently well-formed (see #108) to produce PDFs.\r\n\r\nWork continues in https://script.google.com/a/legalese.io/d/16_rsbUnNi6sUvHk2ecstvPRkLVAi5kzDw59nlpSt2zByVcSIK3OG2KYg/edit?usp=drive_web\r\n\r\n"}}