Add support for custom number formats and digitcommas

[mengwong]

We want a cell in the cap table spreadsheet to display "₹1,00,000".

We want that string to appear in an output PDF as well.

The code to do this should mostly live in the google-apps repository. As little of that code as possible should go into the indesign repository; see commits 7f37bab and 448d593 for an example of what is necessary.

BACKGROUND

Different currencies stringify differently. In Google Spreadsheets, the correct way to specify a currency figure is as a pure number in the cell itself, styled using the formatting options with the appropriate currency. Internally, this is represented as Value versus Format.

Some currencies want to be expressed as 123.456,78. Others want to read as 123,456.78. Yet others want to read as 1,23,456.78.

Google knows how to handle some of these. Others, it doesn't. Hence the tiny DSL for Custom Number Formats: http://exceldesignsolutions.com/a-comprehensive-guide-number-formats-excel/

CURRENT CODEBASE

In google-app/format.js you will find a function asCurrency_.

In google-app/lingua.js you will find a function digitCommas_.

These two functions are together responsible for displaying things like US$123,456.78.

You will need to modify both to support a fuller representation of custom number formats.

HOW TO PROCEED

Maybe some nice person has already written a complete library for Google Apps Script that does exactly what we need.

In fact, we know that some nice person has already done this: the functionality is already present in the native Google Spreadsheets app. However, that code is not exposed. So maybe some other third-party non-Googler nice person has written a library. Look for it first so you don't reinvent the wheel.

If it turns out that it is necessary to reinvent the wheel,

While you might be tempted to just shove your implementation directly into the bodies of the above two functions, it would be a public service to develop the code as a public library that other people could use, and that our two functions could just call on. Then you would be the nice person.

[mengwong]

This first arose to satisfy the need in https://docs.google.com/spreadsheets/d/1t7VN4NF8b0KC9n4m03FzIx8KAivLmBbi85JWx4eO6tU/edit#gid=465236508 column Q.

[mengwong]

You can create a separate branch to do this dev work.

[mengwong]

The custom number format I pulled off the Internets is: [>9999999][$₹]##\,##\,##\,##0;[>99999][$₹]##\,##\,##0;[$₹]##,##0

[wtf]

It appears Range.getDisplayValue() (https://developers.google.com/apps-script/reference/spreadsheet/range#getdisplayvalue) will do the trick!

[mengwong]

Yes, there is a better way to do this. Let's put getDisplayValue() into the code.

[mengwong]

formatify calls asCurrency.

Let's look at what calls formatify and what calls asCurrency.

20160328-15:18:08 mengwong@venice2:~/non-db-src/l% grep asCurrency_ ALL | grep -v //
google-app/captable.js:   return asCurrency_(that.getCurrency(), bst_count[0]) + " of " + plural(2, bst_count[1]);
google-app/captable.js:  toreturn.TOTAL.money = asCurrency_(currency, toreturn.TOTAL._orig_money);
google-app/captable.js:  toreturn.TOTAL.money = asCurrency_(currency, toreturn.TOTAL._orig_money);
google-app/captable.js:  toreturn.TOTAL.money = asCurrency_(currency, toreturn.TOTAL._orig_money);
google-app/format.js:     toreturn = asCurrency_(format, string);
google-app/format.js:function asCurrency_(currency, amount, chop) {
www/templates/jfdi.asia/preemptive_waiver.xml:  if (data.price_per_share) { ?> for <?= asCurrency_(currencyFor_(data.price_per_share), (percentage * newIssues.TOTAL._orig_money).toFixed(2)) ?><?}?>.
www/templates/jfdi.asia/preemptive_waiver.xml:<?= asCurrency_(currencyFor_(data.capTable.getActiveRound().amount_raised.money), (percentage * (data._orig_amount_raising || newIssues.TOTAL._orig_money)).toFixed(2)) ?> worth of <?= data.security_type_short_plural ?>.
20160328-15:18:16 mengwong@venice2:~/non-db-src/l% 
20160328-15:18:17 mengwong@venice2:~/non-db-src/l% 
20160328-15:18:17 mengwong@venice2:~/non-db-src/l% 
20160328-15:18:17 mengwong@venice2:~/non-db-src/l% grep formatify_ ALL | grep -v // 
google-app/captable.js:         round.old_investors[ai][attr] = formatify_(totals.all_investors[ai]["_format_" + attr], totals.all_investors[ai]["_orig_" + attr], termsheet, attr);
google-app/captable.js:  toreturn.TOTAL.shares = formatify_("#,##0",  toreturn.TOTAL._orig_shares);
google-app/captable.js:  toreturn.TOTAL.shares = formatify_("#,##0",  toreturn.TOTAL._orig_shares);
google-app/captable.js:  toreturn.TOTAL.shares = formatify_("#,##0",  toreturn.TOTAL._orig_shares);
google-app/captable.js:          myRound[asvar0][             minorByNum[j].minor] = formatify_(formats[i][j], row[j], sheet, minorByNum[j].minor);
google-app/captable.js:          myRound.new_investors[row[0]][minorByNum[j].minor] = formatify_(formats[i][j], row[j], sheet, minorByNum[j].minor);
google-app/format.js:function formatify_(format, string, sheet, fieldname) {
google-app/format.js:     Logger.log("formatify_(%s, %s) called. the input string is a %s", format, string, string != undefined ? string.constructor.name : "undef");
google-app/format.js:     Logger.log("formatify_(): spreadsheetTimezone=%s", spreadsheetTimezone);
google-app/out/captable.js.html:                round.old_investors[ai][attr] = formatify_(totals.all_investors[ai]["_format_" + attr], totals.all_investors[ai]["_orig_" + attr], termsheet, attr);
google-app/out/captable.js.html:          myRound[asvar0][             minorByNum[j].minor] = formatify_(formats[i][j], row[j], sheet, minorByNum[j].minor);
google-app/out/captable.js.html:          myRound.new_investors[row[0]][minorByNum[j].minor] = formatify_(formats[i][j], row[j], sheet, minorByNum[j].minor);
google-app/readrows.js:      terms[           asvar] = formatify_(term_formats[i][0], row[1], sheet, asvar);
google-app/readrows.js:                 forHandler.attrs[             asvar_(row[role_x])] = formatify_(formats[i][role_x+1], row[role_x+1], sheet, asvar_(row[role_x]));
google-app/readrows.js:        var v = formatify_(entity_formats[0][ki], row[ki], sheet, k);
www/templates/jfdi.asia/mod_party_sum.xml:var toReturn = formatify_(myFormat, asNum, data.sheet);
20160328-15:18:21 mengwong@venice2:~/non-db-src/l%

[mengwong]

now blocking on #94.

[mengwong]

yikes. one of the consequences of this change is that date formats now show what's displayed in the spreadsheet. i suppose this is correct, but it suggests that we now need to visit all the spreadsheets and change YYYY-MM-DD to something more human-friendly.

[mengwong]

i have changed all the date fields in the Demo Master from ISO8601 to a human format.

[mengwong]

this may be reading numeric fields as strings. for example, signing_director_index is typed to int, but may turn into a string.