Have you seen those fancy looking cleaning spreadsheets for sale on Etsy? You know… the ones with progress charts, themed designs, instant updates, and mobile compatibility? Want to know how to make your own for free in Google Sheets? Fear not… I’m here to help (and my template is available for you to download for … Read more
You can’t deliberately delay the execution of an entire Google Sheet using a cell function. Most of the in-built formula functions on you can use in a cell execute very quickly. Some of them, such as IMPORT functions, can take a little time to return data. But these, like any Apps Script function you can … Read more
There are 3 types of loops you can use when writing JavaScript inside the Apps Script editor in Google Sheets. Those are do,for and while loops. They each work slightly differently. For loops are our favorite construct when updating all the cells in a range. They offer outstanding performance and make for easily readable code. … Read more
Google Sheets makes sharing your spreadsheets with other users easy. It only takes two or three clicks to give one of your contacts editor, commenter or view-only access to your sheet. But what if you want to go a little further? What if you want to protect parts of a sheet from being edited, but … Read more
If you want to keep track of the exact date and time when cells change in your spreadsheet, you have several options. You can write Apps Script that will automatically record timestamps (and usernames) for you whenever certain cells change. If you want control over when the timestamp gets recorded, you could add a tick-box … Read more
It’s quick and easy to get the ID of a Google Spreadsheet or Sheet Tab from its URL. If you have the sheet open in your browser, you should be able to see the URL within the address bar. The Spreadsheet ID comprises letters, numbers, hyphens and underscores and is the part of the URL … Read more
Google Sheets are pretty smart. If you enter a valid date into a cell, the next time you try to edit that cell by double clicking it or hitting enter with it selected, a date picker will appear. You can pick a date, and the cell will update. But what about empty cells that need … Read more
The 2 simplest ways to make a button in Google Sheets are by adding a clickable drawing that looks like a button and runs some Apps Script, or adding a custom item to the menu. This article will take you through how to do both and how to write some simple apps script that clears … Read more
If you need to assign a script to a button in Google Sheets, you’ve probably created a drawing and need to know how to Assign a script to it that does something. You need to right click on the drawing and open the three dot menu, then select Assign a script from the drawing menu. … Read more
Google Sheets does not allow you to add buttons to the toolbar. In fact, there are no toolbar customization options available at all. But you can add buttons and sub-menus to the menu. You can easily link these with Apps Scripts and make them perform whatever actions you want. If you need more flexibility, or … Read more
There are 3 ways to make a button that performs an action in Google Sheets. You can create a drawing that looks like a button, and runs Apps Script when clicked, or you can use a tick-box that will run a script whenever it gets ticked, and then un-tick itself to appear to act like … Read more
This article will focus on examples of how to write Apps Script to clear different ranges of your sheet contents. In order to run this script you’ll need to pick from the one of 3 ways to make a button on Google Sheets. You can create a clickable drawing that runs script, you can add … Read more
There are 2 ways to create links in Google Sheets. You can either manually create them using the keyboard shortcut (Ctrl+k Win, ChromeOS, ⌘+k on Mac) or the ‘Insert link‘ menu option (Right-click > Insert link). OR you can create them using the HYPERLINK function as a formula. How to use the HYPERLINK formula to … Read more
The HYPERLINK function in Google Sheets does not support the file (file://) protocol. This means that any link to a local file that you enter will get rejected as an Invalid URL. If you really must link to local files from a sheet, there are ways to work around that limitation, such as installing a … Read more
There are 2 types of links in Google Sheets. Auto-generated links that sheets creates itself when you type in URLs, and links created using the HYPERLINK function. How you remove them differs slightly depending on how many links there are, and what type they are. There are pretty quick shortcuts to get rid of each … Read more
If you need to display the results of two calculations in a single cell, the simplest way is by concatenating the results of 2 formulas together as text either using an ampersand (&) or the CONCATENATE function. If your formula results are numbers or dates that need formatting, you will have to do this using … Read more
The simplest way to convert a range of cells from formulas to values is by copying them and using paste special to paste the values only. You can do this using the menus, or with keyboard shortcuts. I often need to convert formulas to values in a sheet when I’ve used a formula to fix … Read more
The quickest way to show all the formulas on a Google Sheet is to use the shortcut Ctrl+’ (⌘+’ on Mac). This will make every cell in your sheet display its formula instead of the result. Hit the same keyboard shortcut again to toggle the view back to the standard display. To view the formula … Read more
If you don’t want viewers of your sheet seeing how you generate your data, you’ll need to hide your formulas. There are 2 ways to do just that. Which you choose will depend on how technical your users are and exactly what you want to accomplish. Hiding the formula bar is very simple (View > … Read more
If you need variables in your google sheet, you have 3 options. The best option is to create a named range in your sheet that refers to a cell containing your variable. If you don’t want to store the variable in the sheet, you can use a named function or an Apps Script function. Which … Read more
Google sheets will automatically refresh formulas whenever you change the value of any cell in a sheet. If your data is based on the value of a volatile function like NOW(), TODAY(), RAND() or RANDBETWEEN() you can automate a recalculation of sheets formulas every minute or every hour by using sheet settings. To refresh your … Read more
In order to use line breaks, new lines or soft returns within a formula you need to enter them in as part of the formula definition. Google Sheets has a CHAR function that returns any Unicode character from its code or number. The decimal Unicode for a new line (line feed) is 10. So using … Read more
Adding alternating colors every 2 rows in Google Sheets is easy. Simply open the menu and click Format > Alternating colors. Select a color scheme or theme and you’re done! But what if you want to alternate colors every 3, 4 or even 5 rows? You can’t do that using the in-built menus. Your best … Read more
You’ve spent some time getting your conditional formatting rules just perfect. But now you need to apply them to another column or range of cells in your sheet without affecting the data in those cells. You just want the formatting duplicated. How do you do it? You have 2 options. You can use the format … Read more
Are you working with zip code data in your Google Sheet? If you’ve imported some 5 digit zip code data and lost the leading zeros from certain states like Connecticut, Massachusetts, Puerto Rico, and New Jersey, I’ll show you several easy ways to put it back. If you need to import zip code data and … Read more
When you import data into Google Sheets using copy and paste, unless you format the destination cells as Plain Text, sheets will convert your data into numbers and lose the leading zeros. 0000123 will become 123. This will also happen if you leave the Convert to numbers option ticked during a file import. Read our … Read more
If a 7 digit fixed width field contains a number less than 1 million, that number will normally have leading zeros, i.e. 0001234 (the 000 at the start being the leading zeros). A problem can arise in Google Sheets sometimes because without proper consideration, leading zero numbers can get formatted as regular numbers and 0000124 … Read more
To edit the default font in Google Sheets, you need to edit your current sheet theme and select a new font. This change will only affect the currently opened file. Doing this using the menus only allows you to select from 6 fonts. To change your default font to anything you like, you need to … Read more
If you want to hide gridlines in Google Sheets, you need to open the View menu and select Show and then ensure the Gridlines option is unticked. Hiding gridlines only affects the current sheet. That’s it! Simple right? Here’s a screenshot of where you can find the option. Why would you want to turn gridlines … Read more
The default width of a column in Google Sheets is 120 pixels. You can be sure that whatever you end up putting in your sheet, 120 pixels won’t always be wide enough. Your data will get cropped and hidden, and sometimes that’s annoying. Don’t spend your valuable time tweaking the widths of columns. There is … Read more