Posts

How to convert column index into letters with Google Apps Script

Although Google Sheets does not provide a ready-to-use function that takes a column index as an input and returns corresponding letters as output, we can still do the task by leveraging other built-in functions ADDRESS , REGEXEXTRACT , INDEX , SPLIT as shown in the post . However, in form of a formula, that solution is not applicable for scripting with Google Apps Script. In this post, we look at how to write a utility function with Google Apps Script that converts column index into corresponding letters. With the solution in the form of a formula , we don't even need to understand how column index and letters map each other. With apps script, we need to understand the mapping to come up with an algorithm. In a spreadsheet, columns are indexed alphabetically, starting from A. Obviously, the first 26 columns correspond to 26 alphabet characters, A to Z. The next 676 columns ( 26*26 ), from 27th to 702nd, are indexed with 2 letters. [AA, AB, ... AY, AZ], [BA, BB, ... BY, BZ],

How to convert column index into letters with Google Sheets

In Google Sheets, rows are indexed numerically, starting from 1, but columns are indexed alphabetically, starting from A. Hence, it is pretty straightforward to work with rows and trickier to work with columns as we need to convert between column index and corresponding letters. For example, what are the letters of column 999th in Google Sheets? In this post, we will look at how to convert a column index into its corresponding letters by using the built-in functions of Google Sheets. What are letters of the column 999th in a spreadsheet? Unfortunately, Google Sheets does not provide a ready-to-use function that takes a column index as an input and returns corresponding letters as output. However, there is a little trick of combining other available built-in functions to do the task. In Google Sheets, there is the ADDRESS function that returns the cell reference, according to the specified row index (first parameter) and column index (second parameter) in the input. For example,

How to copy data in Google Sheets as HTML table

I often need to extract some sample data in Google Sheets and present it in my blog as an HTML table. However, when copying a selected range in Google Sheets and paste it outside the Google Sheets, I only get plain text. In this post, I explain how to copy data in Google Sheets as an HTML table by writing a small Apps Script program. Concept Implementation Source Code Demo HTML table code HTML table visualization Getting Started Concept On a spreadsheet, users select a range that they want to copy as HTML table. With the selected range, users trigger a command Copy AS HTML table . The command can be added to the toolbar, or to the contextual menu, or accessed via a keyboard shortcut. The command is executed to transform the selected range into HTML code for table. The HTML code can be added to the clipboard or can be displayed somewhere so users can copy it manually. The HTML table must consist of all displayed cells of the selected range and the widths between c

Compare stock portfolio to compound interest saving account

In investing, it is important to define the target that we want to achieve. Many investors like to compare their portfolios with a market index, but is outperforming the market index a good target? It is not a quantitative target to be measurable as market indexes fluctuate daily. I, personally, aim only for 10% growth a year. If at the end of the year, I see my portfolio grows 10%, I am pleased with that result no matter that S&P500 grows 50% the same year. To see if my stock portfolio grows in line with the 10% target, I choose to compare it to a saving account with the same interest. In this post, I will show you how to use a fictive saving account with daily compound interest as the target to benchmark a stock portfolio. Idea Computation Visualization Evolution of target saving account with 5% annual compound interest Evolution of target saving account with 10% annual compound interest Evolution of target saving account with 15% annual compound interest Conclusion