{"id":71423,"date":"2024-03-06T21:00:32","date_gmt":"2024-03-06T13:00:32","guid":{"rendered":"https:\/\/www.hongkiat.com\/blog\/?p=71423"},"modified":"2024-03-04T16:37:47","modified_gmt":"2024-03-04T08:37:47","slug":"google-sheets-tricks","status":"publish","type":"post","link":"https:\/\/www.hongkiat.com\/blog\/google-sheets-tricks\/","title":{"rendered":"9 Things You Didn’t Know You Can Do With Google Sheets"},"content":{"rendered":"

Google Sheets can do more than just tracking and dealing with data and numbers. There\u2019s a whole world of cool tricks hidden inside Google Sheets that can make your life easier, help you work smarter, and get things done faster. Many of us use it for the basics, but there\u2019s so much more it can do.<\/p>\n

In this guide, we\u2019re going to show you nine amazing things you probably didn\u2019t know you could do with Google Sheets.<\/p>\n

Access Real-time Stock Prices<\/h2>\n

GOOGLEFINANCE<\/code> function in Google Sheets grants you access to live financial information.<\/p>\n

For example, to keep an eye on Google stock\u2019s current price, simply use the formula:<\/p>\n

=GOOGLEFINANCE(\"GOOGL\", \"price\")<\/pre>\n
This formula fetches the current price of Google\u2019s stock directly into your spreadsheet. It\u2019s a great way to stay updated with financial markets in real-time without ever leaving Google Sheets.<\/p>\n
If you\u2019re interested in exploring the GOOGLEFINANCE<\/code> function further, consider reading our detailed guide on tracking stock portfolios with Google Sheets<\/a>. It offers deeper insights on utilizing the GOOGLEFINANCE<\/code> function for portfolio management.<\/p>\n
\"Stock<\/figure>\n
Additionally, if you\u2019re keen on managing crypto portfolios<\/a> with Google Sheets, we have free templates available for immediate use.<\/p>\n
Generating QR Codes<\/h2>\n
Did you know that Google Sheets can be used to generate QR codes from URLs? This feature is quite handy, especially because it supports creating multiple QR codes at once. All you need to do is enter the URLs in one row, and Google Sheets will automatically generate QR codes in the next row for you.<\/p>\n
\"generate<\/figure>\n
For example, placing a URL in cell A1<\/em> will result in the QR code appearing in cell A2<\/em>.<\/p>\n
To make this happen, simply insert the following formula into cell A2<\/em>:<\/p>\n
=image(\"https:\/\/chart.googleapis.com\/chart?chs=300x300&cht=qr&chl=\"&ENCODEURL(A1))<\/pre>\n
This command transforms the URL from cell A1<\/em> into a QR code that is 300\u00d7300<\/em> pixels in size. It\u2019s an easy and effective method for generating QR codes right from your spreadsheet.<\/p>\n
For more details, have a look at our previous post on how to create QR codes with Google Sheets<\/a>. Additionally, you can create barcodes with Google Sheets<\/a> as well.<\/p>\n
Create Custom Functions<\/h2>\n
Google Apps Script offers a powerful way to enhance Google Sheets by enabling you to craft custom functions tailored to your needs.<\/p>\n
To create a custom function, go to \"Extensions\" on the Google Sheets menu bar and select \"Apps Script\" from the list. This will launch the Google Apps Script editor in a new browser tab.<\/p>\n
\"Google<\/figure>\n
Within the editor, you\u2019ll be greeted by a coding environment where you can script in JavaScript. This is where you can create your custom function.<\/p>\n
Let\u2019s illustrate this by coding a straightforward function to multiply two numbers:<\/p>\n
\/**\r\n * Multiplies two numbers.\r\n *\r\n * @param {number} num1 The first number to multiply.\r\n * @param {number} num2 The second number to multiply.\r\n * @return The product of num1 and num2.\r\n * @customfunction\r\n *\/\r\nfunction MULTIPLY(num1, num2) {\r\n  return num1 * num2;\r\n}\r\n<\/pre>\n
This code snippet introduces a function named MULTIPLY<\/code> that accepts two arguments (num1<\/code> and num2<\/code>) and outputs their multiplication result.<\/p>\n
Once your function is ready, hit the save icon (resembling a floppy disk) or navigate to \"File\" > \"Save\" to store your script. You\u2019ll be prompted to name your project; opt for a name that reflects the purpose of your script.<\/p>\n
\"Save<\/figure>\n
Returning to your Google Sheets, your custom function now integrates seamlessly with the existing functions. In a cell, type =MULTIPLY(<\/code> to see your function suggested in the autocomplete menu.<\/p>\n
Enter your desired parameters in the formula, like so:<\/p>\n
=MULTIPLY(10, 20)\r\n<\/pre>\n
This example demonstrates using the MULTIPLY<\/code> function to calculate the product of 10<\/em> and 20<\/em>, resulting in 200<\/em> displayed in the cell.<\/p>\n
Web Scraping with IMPORTXML<\/code><\/h2>\n
Google Sheets can also use it to gather data from the web<\/a> through the IMPORTXML<\/code> function. Let\u2019s say you want to fetch the latest headline from a news website, you might use:<\/p>\n
=IMPORTXML(\"https:\/\/www.examplenews.com\", \"\/\/h1[@class='top-news-headline']\")<\/pre>\n
This command retrieves the text from the leading news headline, provided it is enclosed in an <h1><\/code> tag and assigned the class top-news-headline<\/code>, and places it directly into your Google Sheet.<\/p>\n
This technique is a straightforward yet effective method for consolidating information from various websites into a single location.<\/p>\n
Checking for Broken Links<\/h2>\n
If you\u2019re dealing with a large number of links and need to determine whether they\u2019re broken, Google Sheets can be too, be an invaluable tool for this task. By inputting all the links into a Google Sheet, you can use Google Apps Script to automatically check each link\u2019s HTTP status.<\/p>\n
This process will populate adjacent cells with the HTTP status codes of each link, efficiently indicating their operational status.<\/p>\n
\"Google<\/figure>\n
To understand how to set this up, check out our  guide on how to check for broken links using Google Sheets<\/a>.<\/p>\n
Workflow Automation with Macros<\/h2>\n
Macros in Google Sheets are a powerful feature for automating repetitive tasks, saving you time and ensuring consistency in your work.<\/p>\n
A macro records a series of actions in a spreadsheet and then plays them back in the same order. This is particularly useful for tasks that need to be repeated regularly, such as formatting data, applying filters, or performing calculations.<\/p>\n
Imagine, at the end of every month, you generate a report in Google Sheets that requires a series of formatting steps: setting specific column widths, applying a header style, and sorting data by a particular column. Instead of manually performing these steps each time, you can automate the process with a macro.<\/p>\n
Here\u2019s an example on how to record, using and edit your Macro.<\/p>\n
Step 1. Record the Macro<\/h3>\n
    \n
  1. Open your Google Sheets document.<\/li>\n
  2. Go to Extensions<\/strong> > Macros<\/strong> > Record Macro<\/strong>.<\/li>\n
  3. Perform the actions you want to automate. For example:<\/li>\n<\/ol>