Create links to the first item of each group in Google Sheets

Imagine you have a Google Sheet tab containing a course worth of hundreds of students sorted into classes or groups. After each exam, quiz or assignment, your teacher for each class needs to enter in the grades.

To make life easier for your teachers, we want to create a summary page where we can click on their class and be navigated directly to their group.

We don’t want to have to manually create a link for each location so we are going to write a formula to do the work for us.

Now, the formula is a little complex. So first off, we will generate our list of classes and add a separate section start link for each one. Then we will combine the two together to generate a single formula that gets all the unique sections and provides a link for each one.

If you are playing along, you can grab a copy of the starter sheet here:

STARTER SHEET

Continue reading “Create links to the first item of each group in Google Sheets”

Adding Links to Images and Buttons in Google Sheets

Have you ever tried to add a link or URL to an image in Google Sheets only to discover it is deleted and replaced by just the URL? It’s a pain.

In this tutorial, we will cover a few simple approaches to adding links to images in Google Sheets.

First, we will cover an approach using formulas and then we will use a sneaky approach using a bit of cell and image manipulation.

If you have come to this tutorial looking for a Google Apps Script solution, well… 1) I would discourage it, but 2) if you really must, check out the link to this tutorial for a hacky workaround.

On with the show!

The Formula Approach

In this example, we will use the IMAGE and HYPERLINK Google Sheets functions.

If you want to play along here is a link to the Starter Google Sheet:

Addling links to images in Google Sheets – Starter

(Select both the image and the Google Sheet, right-click and select ‘Make a copy’)

The basic pattern is as follows:

=HYPERLINK("URL - Link",IMAGE("URL to your image"))

Working from the inside out, the IMAGE function requires a link to the image that is being used. There are a number of ways of getting this link that I will cover in a  moment.

The HYPERLINK Google Sheets function takes the destination link as its first argument and then and then a link label as its second argument. Generally, this argument is text, but we can coerce this label into an image by replacing it with our IMAGE function.

But how do we get the image URL?

Image from an external website

If you store your images on a personal website or from some online photo hosting sites you can get the link to the image.

In the example below, I have a link from my website to my image. I want to use that image as my URL label. This is what my image link would look like:

=HYPERLINK("https://yagisanatode.com",IMAGE("https://yagisanatode.com/wp-content/uploads/2022/05/yagisanatodeSiteBanner_titleOnly-500-width.jpg"))

This will result in this image link:

Adding a link to an image in Google Sheets_image url is external
Click to Expand!

Image from Google Drive

Unfortunately, we can’t just go to Insert > Image > Insert image in cell for this. Nor can we go to our Google drive, select the image share it and get the URL from the share.

It won’t work.

There are a couple of workarounds for this, but probably the easiest approach is to open the image in a drawing.

Here is how we do it:

    1. Navigate to the Google Drive folder where your image is saved.
    2. In a blank space in the folder right-click > More… > Google Drawings

      Creating a Google Drawing File in Google Drive
      Click to Expand!
    3. Inside Google Drawings select Insert > Image > Drive.
      Insert an image into Google Drawings
    4. A sidebar will appear. Navigate to your image and double-click it. It will load into your drawing.
      Insert an image into Google Drawings select from drive
    5. Holding the left mouse button down, drag the image up to the top-left corner of the drawing.
    6. You will notice a checkered grey and white area in the background, this is the page area. We want the page area to be equal to the image. In the bottom right of the page area, there is a little move triangle-shaped button click and drag it up to match the size of the image.

      Resizing the Google Drawing page
      Click to expand
    7. In the top right of the Drawing app, rename it by clicking on the top-left text where it says “Untitled”. Make sure it is something meaningful for your task.
    8. Now we get the URL for the image. Select File > Publish to the web. 
    9. A dialogue box will appear. Make sure Link is selected, choose your preferred size and then select Publish.
      Google Drawing Publish to the web as a link
    10. You will get an alert that comes down from the top of the page making sure you want to publish this image. Select Ok.
    11. You will now have a Google Drawing URL. Hit Ctrl + C  (⌘ + C) to copy it.
    12. Head back to your Google Sheet and insert it into IMAGE function. It should look similar to this:

=HYPERLINK("https://yagisanatode.com",IMAGE("https://docs.google.com/drawings/d/e/2PACX-1vR2MqTAC_SBvvoWlAwLL_Pk5gHbLzOb0CJAOvbxitdNvstsX477fX8xRqvvQDOohuF83Ie4UQS4AQvk/pub?w=502&h=503"))

Adding a link to an image in Google Sheets_Google Drawing URL

You can also do the same thing with custom drawings and buttons using Google Drawing. Check out the video below for a demonstration of this.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

The video

The sneaky approach to adding  Links to images.

Another approach to adding links to images in Google Sheets that you may want to consider is to add a URL to a cell that you want to use for your image and then insert an image over cells. Then move that image so that it fits inside the cell with the URL.

When a user clicks or hovers over the cell and image the link will appear.

Here are the detailed steps:

  1. Select a cell. If you want something larger, you may have to merge cells together to make it just right.
  2. Add your URL to the selected cell.
  3. Change the text colour of the cell to match the background colour. This will hide the URL just in case you didn’t fit your image in perfectly.
    Adding a link to a cell in google sheets
  4. Now go to Insert > Image > Insert image in cell
    Insert image over cells in Google Sheets
  5. Select your image from your desired location.
  6. One the image appears in the Sheet, move the image to the cell with the link and resize it so that it fits just inside the cell. Note! You should leave a tiny bit of space from the edges of the cell so that the URL is picked up.

The end result should look a little like this:

Sneaky image with URL in Google Sheets

That’s all there is to it.

The Video

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

GWAOw! 4 – Forms History by Martin Hawksey

In this episode of GWAOw!!! we look at the Google Workspace add-on, Forms History by Martin Hawksey.

About Forms History for Google Forms

Unlike Google Docs and Google Sheets, there is no version history in Google Forms. Forms History is Martin’s solution to this problem. This free add-on allows you to review the version history of your form and make saves at critical points in your form build process.

This is a must-have tool for teams collaborating on a Google Form.

You don’t need to install Forms History for all users who are collaborating on your form for it to run. The add-on will keep track of your edits and provide opportunities for you to make saved versions of your form along the way.

After installation, simply click the add-on puzzle piece in the top menu of your Google Forms project and then select Forms History > Open. A sidebar will appear indicating daily versions. You can then expand these sections to see breakdowns of activity over the day.

From the sidebar, you can also save a current version or navigate to a version that has been saved previously. Intuitive icons make this process very easy.

Some Unavoidable Limitations

There are some limitations to how Google Forms stores updated data for Google Forms and you might find that small edits within around 20 minutes might be consolidated into one version. To be clear this is not a fault of Martin’s add-on it is just how Google Forms handles how it keeps track of different versions.

About Martin Hawksey the developer

Martin is one of the biggest names in the Google Workspace Google Apps Script community and is the mastermind behind the Apps Script aggregate site Apps Script Pulse and host of the long-standing apps script Google workplace YouTube series Totally Unscripted, as well as being a Google recognised Developer Expert and Google Cloud Innovator Champion. This is a fella you can trust which gives me great peace of mind when installing this app.

Get the Add-on

Check it out on the Google Workspace Marketplace: Forms History

Or learn more from the add-on’s homepage.

You can also see a great interview with Martin on Forms History hosted by Alice Keeler.

The Video

Check out more Episodes of GWAOw! here!

How to Easily Calculate Sales Tax (GST 🦘) in Google Sheets

Adding tax to the sales price or subtracting tax from a total price of an item in Google Sheets (GST)[Update 16 Jul 2022]

We have Goods and Services Tax (GST) here in Australia; it is a sales tax on all sorts of things. You might have something similar in your own country. If our business turnover is more than $75,000 AUD for the year we need to register to pay this GST. This means that we have to include the tax in the total value of an item. In this tutorial, I’ll show you how to do this quickly in Google Sheets.

You can calculate the Total Price of an item including Sales Tax in two ways:

  • Add tax to the sales price: Sales Price + Sales Tax = Total Price
  • Subtract tax from the total price: Total Price – Sales Tax = Sales Price

Adding Tax to the Sales Price

This is the easiest and often most common approach. Here we set a sales price and then calculate how much tax needs to be applied to the total price.

Let’s say we have $100 and we need to add 10% tax to our total price.

Calculating the taxable amount of a value in Google Sheets step 1

First, we get the amount of tax we need to add. We do this by multiplying the Sales Price by the Tax percentage.

Sales Price = Sales Price × Tax percentage/100

Sales Price = Sales Price × Tax as a decimal

10 = 100 × 0.1

If you have set up the formatting of your cells in your Google Sheet as I have in the image above where the format for A2 is dollars ($) and B2 is a percentage (%) then you don’t have to worry about converting the percentage to a decimal. Google Sheets will handle that for you. Take a look at the formula:

=A2 * B2

=100 * 0.1

=10

Calculate the taxable percentage portion of a value in Google Sheets

Note that we use the asterisk symbol (*) in Google Sheets (and in most digital calculations) to multiply items.

All we then need to do is add the Sales Price to the Sales Tax:

= A2 + C2

= 100 + 10

= 110

Add the sales price to the tax added to the item in Google Sheets

We can combine both formulas like this:

= Sales Price + Sales Price * Sales Tax

= A2 + A2* B2

= 100 + 100 * 0.1

= 110

Adding sales tax to an amount in Google Sheets

Remember that in our order of operations in maths the multiplication of the Sales Price by the Tax rate as a decimal will occur before adding the Sales Price.

Practice: Go ahead and try and change the Sales Price or Tax % and see what results you get.

Adding Sales Tax Video

Subtracting Tax From the Total Price

Sometimes we don’t want to change the price of an item for a customer, so we will subtract the amount to tax from the original price that we offered them.

Say that we have been selling a widget for $100, but now we have reached the GST threshold and need to include sales tax on the item. Our customers are going to be cranky with us for adding an extra $10 bucks onto the price. Instead, we decide to include the tax in the $100 sales price of the widget.

So, how do we do this?

This time we start off with the Total Price and the Sales Tax.

Subtracting the sales tax from the total price of an item in Google Sheets Part 1

We’ve used the same example before, where our total price is $100.00 and our tax rate is 10% on the item.

To calculate the Sales Tax included in the Total Price we divide the total price by, one plus the tax rate as a decimal multiplied by the tax rate as a decimal.

Total Price /( + Tax as decimal) * Tax as decimal

= A2 / (1 + B2) * B2

= 100 / (1 + 0.1) * 0.1

= 100 / 1.1 * 0.1

= 9.09

Calculate the tax included in the total value of an item in Google Sheets

Then to get the Sales Price, simply subtract the Total Price from the Sales Tax:

= Total PriceSales Tax

= A2C2

= 1009.09

= 90.91

Calculate the sales price after tax based on a total price value in Google Sheets

Subtracting Tax Video

Get The Google Sheets Sales Tax Calculator

Google Sheets Sales Tax Calculator

You can grab your very own copy of the Google Sheets Tax Calculator here. Plus there are Google Sheet tabs with the examples above included.

Google Sheets Sales Tax (GST) Calculator

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

 

~Yagi

Changelog

  • 2022-07-16: Added two videos. One for each section of the tutorial.

GWAOw! 3 – Crop Sheet by Eric Koleda

In this episode of Google Workspace Add On Walkthroughs (GWAOw!), we take a look at Crop Sheet by Eric Koleda.

Crop Sheet allows you to quickly crop a Google Sheet tab by your own selection or by the data in your sheet tab.

With over 1,000,000 installs this is a simple Google Workspace Add On that completes a specific job well.

What’s the alternative? A somewhat slower process of removing outer columns and rows by a whole lot of clicking, shortcut commands and time wasted.

Eric was a Developer Relations Infrastructure Manager at Google before moving to Coda.io (An all-in-one document tool for collaboration) as their Developer Advocate. I’d be pretty confident in this add on. 😉

You can also find the code for Crop Sheet on GitHub.

Check it out on the Google Workspace Marketplace: Crop Sheet

Check out more Episodes of GWAOw! here!

%d bloggers like this: