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.
- Write a small Apps Script program
copyAsHTMLTable()to transform the selected range in the active sheet into a HTML code for table
- According to Apps Script documentation, getActiveRange() returns the selected range in the active sheet.
- According to Apps Script documentation, getDisplayValues() returns the rectangular grid of values for a range.
- From the rectangular grid of values:
- Use 2
forloops to iterate row by row and then column by column
- Each row is wrapped in a HTML row tag
- Each column is wrapped in a HTML column tag
- Use 2
- Present the HTML table code in a dialog so that user can copy it manually
- Add the program
copyAsHTMLTable()to a menu on the toolbar to easily run it
If you want to learn more how to use Google Sheets and Google Apps Script to manage stock portfolio investment, please check out LION stock portfolio tracker guide