How to embed HTML in Google sheet? [Various Methods]

👤 Diwas Poudel 🕒 07 Jan 2023 📁 TECH

Introduction

Google sheets is the spreadsheet software owned by Google. It is part of Google’s workspace productivity suite. It is like Microsoft Excel and Apple’s Numbers. Google spreadsheet is compatible with excel’s file format. Google sheets is a flexible application. It allows users to enter and manipulate the data using formulas. We can also create graphs and charts and also collaborate with other people in real time. Another interesting feature of Google spreadsheet is it can manipulate some of the HTML data in the spreadsheet as well.

Formulas available for HTML

So you might be curious, why do we need any formulas related to HTML in spreadsheets?

One of the reasons is to get the content of the web in the spreadsheet for manipulation. We can also provide the link to the other websites using the formula. Web Scraping is also possible using the "=IMPORTHTML()" formula.

Some of the useful formulas to embed HTML in Google sheets are

=IMAGE()
=HYPERLINK()
=ISURL()
=IMPORTHTML()
=IMPORTFEED()
=IMPORTXML()
=IMPORTDATA()

Fact: = is used at the beginning of the formula in the spreadsheet applications

Embed Html into Google Sheet using Formula

We will learn to use some of the formulas mentioned above.

=IMAGE()

This formula is used in sheets to embed or insert images available on the web. The general syntax of this formula is

=IMAGE(URL, Mode, Height, Width)

Here Mode, Height, and Width are optional arguments.

To insert an image follow the following steps.

Open a spreadsheet and click on the cell where you want to insert the image.
Then on the formula tab or on the cell type =IMAGE (). The suggestion will be displayed for the formula.

On the parenthesis insert the link for the image available on the web.
After a while, you can see the image loaded.

Image Loaded from Image — **fig. Image Loaded using Image Function**

You can also specify the mode for the image. Mode is the second parameter available in the =IMAGE formula

Mode numbers	Functions
1	Exactly fits the image in the cell
2	Fills the image in the cell
3	Puts image with original dimensions
4	Helps to specify the dimensions

Note: Be sure to keep the URL inside a double quotation i.e. “ ”

Did you know?: To open a new spreadsheet you can type spreadsheet.new in the URL bar.

=IMPORTHTML()

This formula is used to extract or scrape a specific table or list of the web. The general syntax of this formula is

=IMPORTHTML(URL, query, index, locale)

The first parameter is the website link from where we want the data. The second parameter specifies whether we need a table or list from that website. The third parameter is for specifying which table or list. The last parameter is used to specify the region or language. The last parameter is optional if not used it uses the default locale.

Web Scraping using IMPORTHTML

Here we will be learning basic web scraping using the formula IMPORTHTML. To do so, follow the following steps.

Click on the cell from where you want to present the data got from web Scraping.
In the cell or in the formula box start typing =IMPORTHTML. By doing so you should get a suggestion box. The box will specify the general syntax.

fig. use of importhtml() function
Specify the article from where you want to scrape the data. In the query, you will usually have two choices either table or list. The index is specified according to the table or list the website has.

fig. importhtml() function in action
After filling in all required parameters. Click enter then you see your data.

Note: Be sure to keep the URL and query inside a double quotation i.e. “ ”. The index is the number. Also, try out the same formula but use the list in place of a table in the query. Try it and find what you will get.

=HYPERLINK

This formula is used to embed a link in the sheets. You might be wondering if you plainly paste the URL link, you can get the link there so why use this function? This function is used to embed the URL link to the label. The general syntax of this formula is

=HYPERLINK(URL, label)

To embed the link to the label follow these steps

Click on the cell where you want to keep this link
On the formula box or on the cell use the formula =HYPERLINK(URL, label)
In place of the URL insert the link you want to embed. In the label enter the name of the link you want to give.

Note: Be sure to keep the URL and label inside a double quotation i.e. “ ”.

=ISURL()

This formula is used to check whether the given URL is valid or not. This function returns the boolean value.

The general syntax for this formula is

=ISURL(URL)

Note: Be sure to keep the URL inside a double quotation i.e. “ ”.

Using Google App Script to Embed Html into Google Sheet Dialog

You can embed HTML into google sheets cells using Google App Scripts. So let's do it.

1 Open Google Sheets and select Extension > App Scripts.

This will open Google App Scripts Editor.

Create new Google App Script — **fig. Create a new Google App Script**

2 Just Paste down the script below.

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Gemerate Html Menu')
      .addItem('Open Dialog Box', 'openDialog')
      .addToUi();
}

function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile('index');
  SpreadsheetApp.getUi()
    .showModalDialog(html, 'This is Title');
}

In Google Sheets, this script creates a custom menu with a single menu item called "Open Dialog Box." When the user selects this menu item, a modal dialog box with the contents of the "index" HTML file will appear. The dialog box's title will be "This is Title."

When the user accesses the Google Sheets file, the onOpen() code runs automatically and creates the custom menu. When the user clicks the "Open Dialog Box" menu option, the openDialog() method is invoked, and it shows the modal dialog box with the HTML contents.

This script can be used to provide a user interface for a Google Sheets script, allowing users to interact with the script via a dialog box rather than the Sheets file itself. The contents of the "index" HTML file can be modified to generate a dialog box with any layout and functionality required.

3 Create an index Html page.

Click on the + Add icon in the upper left corner, choose HTML, and name it 'index' and then paste down your html content into the main screen on the right.

indexhtml and its content — **fig. index html and its content**

4 To view the HTML content, go to your Google Sheet, then click "Generate Html Menu" in the menu bar, and then click "Open Dialog Box" again. After some time, you should see a dialog window with html content, as shown below.

Conclusion

Google spreadsheets provide flexibility for the manipulation of data. Here we learned some of the formulas and Google App Script to make our work with spreadsheets easier. These formulas and Google App Scripts will surely come in handy when you are performing analysis on the data available on the web. For more content stay tuned.

Article by: Prashant Raj Bista