How to build a Chrome Extension Integrating Google Sheet in Minutes

Aug 21, 2023

∙ Paid

For those immersed in the finance world, staying updated with real-time Stock Market information is vital.
While there's a plethora of remarkable apps available today, many of us still swear by our customized Google Sheets tailored to our specific data and requirements.

Inspired by this,
I developed a personal Chrome Extension that integrates with the Stock Market sheet to provide me with the exact information I need.
Today, I'll be sharing my insights on how you can set this up in minutes, along with the source code, but important to know, this approach can be adapted to any Google Sheet you want to remain connected with, be it for short durations or switching between files as needed.

This tool's beauty lies in its ability to let you engage with your data while you're browsing other web applications, reading articles, or even watching YouTube, all without the hassle of opening new tabs or getting distracted.

What is a Chrome Extension?

A Chrome extension is a software program that customizes and enhances the functionality of the Chrome web browser and it is integrated in the browser itself.

They are built using standard web technologies such as HTML, JavaScript, and CSS, allowing users to tailor their Chrome browser behaviour to individual needs or preferences or to extend potentially any application we want, engaging and accelerating our work.

With the advent of AI and Automation, thousand of new applications if well settled in our browsers and especially our needs, Chrome Extension can be incredibly powerful.

Here are some key features and points about Chrome extensions:

1. Functionality:
Extensions can range in functionality from simple tasks (like capturing a screenshot of a web page) to more complex actions (like analyzing web content and blocking ads).

2. Distribution:
They are primarily distributed through the Chrome Web Store, which is Google's online store for its Chrome browser extensions.

3. Permissions:
When installing an extension, users are often informed about the types of data and browser actions the extension can access. This helps users make informed decisions about the security and privacy implications of installing a given extension.

4. User Interface:
Many extensions have a user interface, which might be a pop-up window that appears when you click on the extension's icon near the browser's address bar. Some extensions may also add a button to the browser's toolbar, provide context menu options, or modify the content of web pages.

5. Applications:
Some common applications of Chrome extensions include password managers, ad blockers, productivity tools, shopping assistants, and many more.

6. Security:
As with any software, Chrome extensions can have vulnerabilities. Google actively reviews extensions in the Chrome Web Store for malicious behaviour, but it's essential for users to be cautious, only install extensions from trusted developers, and regularly check permissions.

7. Updates:
Extensions can be updated, either to introduce new features or fix bugs and vulnerabilities. Usually, Chrome automatically updates installed extensions, ensuring users have the latest, most secure version.

8. Cross-platform:
While they're called "Chrome" extensions, many of these extensions also work on other browsers based on the Chromium project, such as Brave.

Let’s Build a Chrome Extension

But, before we dig in the Chrome Extension itself, we want to build a Stock Market Sheet, where we gather information from the Market Itsef:

Create a new Google File Sheet
In the cell A1, we can write the technical name of the Stock, for example, MSFT
In the Cell A2, we can write this Formula =GOOGLEFINANCE($A1,"NAME")

The result will be: Microsoft Corp

Google Finance for Google Sheet

Google Finance is a tool provided by Google that offers real-time stock quotes, financial news, and other related information.
Within Google Sheets, Google Finance is available as a built-in function that allows users to pull financial and stock market data directly into their spreadsheets.

The Google Finance function in Google Sheets can retrieve a variety of information related to stocks, mutual funds, and currency values.

The main function of Google Finance is, to give a start-end date, we can retrieve the prices in the Spreadsheet itself:

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

ticker: The symbol of the stock or mutual fund.
attribute (optional): The type of data to fetch (e.g., "price", "volume").
start_date & end_date|num_days (optional): Date range for historical data.
interval (optional): Data interval (e.g., "DAILY", "WEEKLY").

The function makes it straightforward for users to integrate live financial data into their Google Sheets, whether for personal tracking, analysis, or any other related purpose.

Which market is connected by?

The `GOOGLEFINANCE` function in Google Sheets primarily retrieves data from the major stock exchanges, such as the NYSE (New York Stock Exchange), NASDAQ, and others.
However, the function's coverage is not limited to just these exchanges; it can fetch data from various global stock markets depending on the availability and region. The data sources and accuracy might vary, and not all stock exchanges or securities globally might be covered.

Google Finance Command for Sheets

The command to retrieve data are infinite, I suggest reading the following Google Document, but before, stay here we build in minutes a useful application:

Google Finance Prices Command

Let’s write in the cell:
B1 = Last
C1 = Change
D1 = Change %
E1 = Vol
F1 = High
G1 = Low

The formula will be:
B2; =GOOGLEFINANCE($A1,"price")
C2; =GOOGLEFINANCE($A1,"change")
D2; =GOOGLEFINANCE($A1,"changepct")
E2; =GOOGLEFINANCE($A1,"volume")
F2; =GOOGLEFINANCE($A1,"high")
G2; =GOOGLEFINANCE($A1,"low")

We are asking Google Finance:

B2 = Last Price
C2 = The change in Amount
D2 = The change in %
E2 = Market Volume
F1 = The Highest price
G1 = The Lowest Price

Now, let’s print the value market price in a range time with this formula in L1

=GOOGLEFINANCE(A1, "Price", 2022-01-01, 2023-08-21, "daily")

The formula is requesting the price, with the daily closed market prices between the two dates.

If correctly spelled you will see something of this:

Move with the dates

As you can see, the command outputs data for a specific date range.
The beauty of Google Sheets is that we can create advanced functions where we can set a start date and an end date, allowing count the days between today and a past date, facilitating the study of events that occurred during that period and gathering evidence within a specific time frame.

For this application, I build a method able to define the days of study, interfering with the “start date” and “end date”. This method will represent the data in a time windows and restrict the information as I desire.

Triggering the “days evidence”, the entire sheet will move as a consequence, and obviously, also the Finance data will be represented between the dates chosen

Days in evidence, the data will move as consequence as:

Average
Change %
Top Days (of the date)
And the Standard Deviation

Standard deviation is a statistical measure that quantifies the amount of variation or dispersion in a set of data points. It tells you how much individual data points deviate from the mean (average) of the data set.

In other words, it measures the spread or the "average distance" of each data point from the mean. A higher standard deviation indicates that the data points are more spread out from the mean, while a lower standard deviation indicates that the data points are closer to the mean.

more info about what is standard deviation here

Dynamic Charts

Now, we have the data we can build to fundamental analysis chart from my point of view.
Price over time, and Price delta. The second is important for trading information, and it is to use with Mean (average) and the Standard Deviation

chart price — Price Movement in the data time

Both graphs can be triggered by aggregating the data Google Finance explained before. In my case DateTime and Price are gathered in Column L and M.

Adding AI Google Spreadsheet

the final touch of the Google Sheet is to use some basic AI info from ChatGpt.
Having the info from the company, through the CHATGPT extension for Sheet we can ask for information about the stock:

In this model, I used to have information in the column HIJ, aggregated from 6 to 36 Rows. The command will be like this:

=GPT(CONCATENATE("may I have the best summarize information about this company: ",A2))

The final result will be like:

Once we build the file, we can move to test it: check the video below:

Create the Chrome Extension

now, Building the Chrome Extension is incredibly easy.
The extension will recall in the browser our Google Drive Sheet and every time we want to check our file, it will be present without interrupting our tasks.

I do remember, we can apply and recall whatever Drive File we want and this procedure is just “private” It will work only for your Browser, we are not going to make in production (publish) to all.

What we need:

Create a local folder and call it Stock-Study You can use whatever you want to write code
Create 3 files:
.background.js
.manifest.json
.popup.html
Background could be empty for the moment

Manifest add this code and save:

{
    "manifest_version": 3,
    "name": "Stock-Study",
    "version": "1.0",
    "description": "Displays an alert when clicked",
    "background": {
      "service_worker": "background.js"
    },
    "action": {
      "default_popup": "popup.html"
    },
    "permissions": ["activeTab"]
  }

popup.html, add this code

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>External Website Popup</title>
    <style>
        body, html, iframe {
            width: 1000px;  /* Adjust width as needed */
            height: 990px; /* Adjust height as needed */
            margin: 0;
            padding: 0;
            border: none;
            overflow: hidden;
        }
    </style>
</head>
<body>
    <iframe src="" sandbox="allow-scripts allow-same-origin"></iframe>
</body>
</html>

The Iframe command, between the body has the src value to be filled with your Google Drive Sheet address.

Simply, get your file drive address and paste between the “ “ and save the file.

Thas is it!

How to add your application to the Chrome Extension.

Quite easy.

Open the Chrome browser menu and click Extension

On the top-right, click the button on “Developer Mode”
On the Top-Left side, now you should have Load Unpacked
Click Load Unpacked and the browser will open the local folder.
Select the folder of the app created before and click select
Chrome will add a “new square” containing your application.

Check your Chrome Extension Assistant you will find the new app there. In our case having loaded a logo, we will have an “S” as logo name

Click on the logo, wait for the app to load the extension and file and Ta-Da:

Conclusion

In this new post, we build a Financial Sheet using Google services from the Drive/Sheet suite, all available for free.

Additionally, we develop a Chrome Extension that allows users to access the sheet directly in the browser, ensuring it's always at their fingertips. I'd like to highlight two main points:

This method enables us to efficiently manage our favourite GSheet or multiple GSheets (more apps mean more sheets).
For both cases, these are merely starting points. While the Google Finance extension is powerful, this Chrome Extension example is just an introduction. With advanced knowledge, one can create beautiful and incredibly useful applications."

Gustavo’s The Business Automator