DEV Community

All Stacks Developer
All Stacks Developer

Posted on

2 1

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

AWS Security LIVE!

Join us for AWS Security LIVE!

Discover the future of cloud security. Tune in live for trends, tips, and solutions from AWS and AWS Partners.

Learn More

Top comments (0)

nextjs tutorial video

Youtube Tutorial Series 📺

So you built a Next.js app, but you need a clear view of the entire operation flow to be able to identify performance bottlenecks before you launch. But how do you get started? Get the essentials on tracing for Next.js from @nikolovlazar in this video series 👀

Watch the Youtube series