DEV Community

All Stacks Developer
All Stacks Developer

Posted on

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.

Copy sheets as HTML table

Concept

  • 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 for loops to iterate row by row and then column by column
    • Each row is wrapped in a HTML row tag <tr></tr>
    • Each column is wrapped in a HTML column tag <td></td>
  • 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

Source Code

https://gist.github.com/allstacksdeveloper/834d0dabec494e08d9719473fd15bc87.js

LION Stock Portfolio Tracker

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

LION stock portfolio tracker dashboard in Google Data Studio

https://www.allstacksdeveloper.com/p/lion-stock-portfolio-tracker.html

Top comments (0)