In this episode of GWAOw!!! we look at Insert Special Characters by Sheets Help.
About Insert Special Characters for Google Sheets.
As the name suggests, the Insert Special Character Add-on allows you to access the complete list of special characters like maths symbols, shapes and arrows, currency symbols, dingbat and Greek and Coptic symbols from the sidebar of your Google sheets.
This is all accessible from the convenience of the sidebar within your selected Google Sheet.
The Insert Special Character Google Workspace Add-on comes with some handy features. You can:
Modify the colour and size of the characters before installing them.
Insert Multiple characters into one cell.
Filter the character list by:
Greek and Coptic Symbols
Use the search bar to search for a specific character.
The add-on comes with a 7-day free trial followed by a very reasonable one-time $7 purchase, but…I spoke to Adam from Sheets Help and managed to get you 20% off.
I don’t know how long the offer will last so I recommend that if you find this Add-on useful, take advantage of the discount while it is still available.
Use the code SPECIAL20 at checkout to get the discount. You can find the code in the description below this video.
About Sheets Help
Along with their recent publication of the Google Workspace Add-on Insert Special Characters for Google Sheets, Sheets Help provides a wide variety of tutorials, tips and tools to help you with your next project.
I need to create a lot of sample data for tutorials and courses. One of the things that I needed for a recent course I am building was to generate a column containing dummy passwords in Google Sheets. Each password needed to consist of letters, numbers and characters.
Until recently, this task would have been relegated to Google Apps Script.
However, with the recent introduction of the LAMBDA function (Well, at the time of writing this anyway), we can do so much more with our Google Sheets.
Before we dive into the formula, it’s important to understand that these ‘passwords’ or random strings of characters are dynamically generated. This means every time you update a cell or reload your Google Sheet the characters in each cell will change.
So once you generate your passwords, copy the range and paste the values back in (Ctrl + c, Ctrl + Shift + v). This way only the values remain.
If you just want to grab the formula and be on your way, you can copy it from the section below. However, if you want to learn how it all works, read on for a breakdown.
Table of Contents
The Password Maker Formula
Note in the formulas below there are three parameters that you can change:
[NumChars]: The number of characters in each string in each cell.
[NumRows]: The number of rows to produce the random string of characters in.
[NumCols]: The number of columns to produce the random string of characters in.
To quickly change the formula to produce only a certain subset of characters, you will need to delete the desired characters from the string contained in the MID function on line 14 of the example above.
You will also need to change the second argument of the RANDBETWEEN function to the length of your new string of characters.
Hint! You can quickly count the string of characters by copying the characters in the formula (including the double quotation marks on each end) and pasting it inside a LEN function. For example:
=LEN("GOAT!1234") = 9
Check out the sample sets below and their letter lengths for convenience.
In this section, we will walk through the process of creating the random string generator. Not only does this help to provide an understanding of how the formula works, but it also gives you some insight into a good workflow for building your own complex formulas in Google Sheets.
The Starter Sheet - To Play Along
If you want to get hands-on to make things more fun, grab the starter sheet from here:
The MAP function allows you to traverse an array and modify each item in the array. For us, we are going to apply a random character to each item in the sequence we generated.
MAP can take a number of arrays or ranges as its first argument and then a LAMBDA function as its last argument.
The nested LAMBDA array takes an iterator item argument. This represents the current cell the item is modifying as it works through each item in the range. We need to add this argument even if we don’t use it in our formula.
The second LAMBDA argument is the formula expression. That is, what we are doing to change the value in each of the cells.
The delimiter – This is the value we want to use to separate each cell item when we combine it into a string. In our example, we don’t want to separate the characters so we will leave this as an empty string.
The array – This will be the array we generated in the previous section.
In the example above we have created an array two columns wide and five columns deep with a random password length of 5 characters (See the second argument of the SEQUENCE)..
This password generator is a really helpful tool for templating spreadsheets to create examples or quickly generate a password or random string of characters for codes.
As we mentioned above, the passwords will change each time you update the sheet. The best solution to provide a static password would be to use a bit of Google Apps Script Magic connected to an onEdit() function similar to this tutorial:
For whatever reason, sometimes we just need a list of alphanumeric letters and numbers like this “0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ” to run down a column or across a row, or just all packed into one string of text in a cell in our Google Sheet.
I most commonly use an alphanumeric list to index data or use an alphanumeric string in the random assignment of a value.
In this tutorial, we will cover 4 ways to achieve this. Some approaches have different benefits than others. While two of the approaches even use the new Google Sheets LAMDA function.
If you just want to dive in, copy the formula, and get back into your own project, click on the link to the main numbered headers in the table of contents below. You can copy the formula into your project using the copy symbol in the dropdown menu of the formula bar and selecting the formula you want.
Each formula type can be:
Transposed to run across a row.
Join into a string in a cell.
Set to lowercase.
Extended to include a lowercase option.
The How it Works sections are not mandatory reading but might be helpful if you plan to extend the formula or are simply curious.
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:
(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:
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:
Navigate to the Google Drive folder where your image is saved.
In a blank space in the folder right-click > More… > Google Drawings
Inside Google Drawings select Insert > Image > Drive.
A sidebar will appear. Navigate to your image and double-click it. It will load into your drawing.
Holding the left mouse button down, drag the image up to the top-left corner of the drawing.
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.
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.
Now we get the URL for the image. Select File > Publish to the web.
A dialogue box will appear. Make sure Link is selected, choose your preferred size and then select Publish.
You will get an alert that comes down from the top of the page making sure you want to publish this image. Select Ok.
You will now have a Google Drawing URL. Hit Ctrl + C (⌘ + C) to copy it.
Head back to your Google Sheet and insert it into IMAGE function. It should look similar to this:
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:
Select a cell. If you want something larger, you may have to merge cells together to make it just right.
Add your URL to the selected cell.
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.
Now go to Insert > Image > Insert image in cell
Select your image from your desired location.
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.