GWAOw! 4 – Forms History by Martin Hawksey

Last Updated on 2022-07-26 by Yagi

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!

Using Hyperlinks in Dialogs and Sidebars to open a URL in a new Tab with Google Apps Script

Last Updated on 2022-07-19 by Yagi

If you’ve landed on this page you’re probably wondering why your hyperlinks are not working in your Google Workspace dialogue (dialog for my U.S. friends) box or sidebar.

This affects all locations where you can build a sidebar or dialogue with Google Apps Scripts, Sheets, Docs, Slides and Forms.

You might even hit F12 in your browser to inspect the code and found this dreaded error:

Unsafe attempt to initiate navigation for frame with origin ‘https://docs.google.com’ from frame with URL ‘https://n-yyi3lctp…<<fileID>>…-0lu-script.googleusercontent.com/userCodeAppPanel’. The frame attempting navigation of the top-level window is sandboxed, but the flag of ‘allow-top-navigation’ or ‘allow-top-navigation-by-user-activation’ is not set.

linking from a Google Workspace sidebar or dialog error Unsafe attempt to initiate navigation for frame

Why your Links aren’t working in your Google Workspace Dialogs and Sidebars

So what’s going on?

Dialogues and sidebars in Google Workspace are set in iframes. Essentially, this is a nested webpage on your main page. Take a look at the examples, below. I’m in Chrome here and I have selected the Developer Tools Element Selector (Ctrl + Shift + C for PC) and clicked on the Sidebar and Dialogue box respectively.

For the Sidebar.

Google Workspace Sidebars are in iframes

And for the dialogue box.

Google Workspace Dialogs are in iframes

When you create a simple HTML hyperlink in your anchor tag like this:

<a href="https://yagisanatode.com">Website</a>
You are asking the iframe to open a URL in its parent window, your Sheet, Doc, Slide or Form, and browsers generally don’t like to let you do this.

The Solution

The solution is really easy. Simply add target="_blank" to your anchor element:

<a href="https://yagisanatode.com" target="_blank">Website</a>
The target ‘_blank’  attribute will generally open the link in a new tab for most browsers. Note that a user may change their settings to open in a new window instead of a new tab.
Target ‘_blank’ implicitly adds the rel="noopener" behaviour on most browsers to prevent the destination link from tampering with the original source.

The video

Example Implementation

If you want to see an example implementation you can get your own copy of the sample Sheet with bound Apps Script from the link below:
Easter eggs, a bonus Google Sheet formula for linking images in a cell and a fun way to call object methods from a Ui method are all in the example sheet.
Once you made a copy of the Sheet, click on the ‘More’ custom menu item and select from the Sidebar or Dialogue box implementation to see it in action. You will have to Authorise the scopes the first time though.
To look at the code, go to Extensions > Apps Script.
Have fun!

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

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.

Got a more specific problem you need help with, but don’t have the time to develop the skills? Make an enquiry on my 'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

~Yagi

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

Last Updated on 2022-07-16 by Yagi

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.
%d bloggers like this: