Skip to content

6 Advanced Google Sheet Tips with Formulas and Apps Script

3 minute read

ltpurple cube

There are so many features inside of Google Sheets that it’s really hard to say you’ve completely mastered the platform. It can be especially tough if you’re moving from Excel, or you’re just not familiar with what Google Sheets have to offer. Most people aren’t aware that Google Sheets actually support some really advanced formulas, along with another impressive tool in Google Apps Script.

To help you on your journey in becoming a Google Sheets expert, we’ve put together six of our favorite advanced tips, tricks and tools.

1. Use arrayformula, match and offset for dynamic data

The arrayformula in Google Sheets has a number of great different use cases. This video pairs the arrayformula with Match and Offset to pull in a constantly updating range of data in one sheet to create a chart in another sheet.

The advantage of using the arrayformula in conjunction with Match and Offset allows you to constantly add rows to your selected range, so you never have to worry about updating the formula. This would work great, for example, if you were pulling from the responses from a Google Form and wanted to consolidate them into a chart.

2. This is the best way to insert images into Sheet cells

If you’re a frequent user of Google Sheets, you probably already know that you can insert images from the Insert Image menu option. This doesn’t actually insert images into a cell, which is often what people are trying to do.

The Insert Image menu option actually places an image on top of a Sheet, rather than inside a cell. So if you’re trying to insert several images into a Sheet in some sort of order this isn’t terribly helpful.

Using the =IMAGE Spreadsheet function allows us to insert an image inside a cell, using the image’s URL. This makes it really easy to insert images into a particular cell, and will get larger or smaller depending on the size of the cell (while maintaining the image’s aspect ratio).

3. Getting started with Doctopus for EDUs

One of the best ways to create assignments in bulk, while maintaining the same naming conventions and sharing them directly with students, is the Google Apps Script Doctopus. With Doctopus you can ensure that all students have their own copy of an assignment while using the power of Google Drive, Docs, Sheets and Folders.

This video walks you through all of the steps you need to take before actually running the Doctopus script. This involves setting up your template (if you need one), along with building a student roster in Google Sheets.

4. Running the Doctopus script

After you’re all set up you can find the Doctopus script in the script gallery, and enabled it after approving a few permissions. From there you can select all of the relevant files to create an assignment and run the script!

Doctopus gives a lot of power to teachers, all while saving time in the process. Teachers can create assignments in bulk, share them with their students and even track whenever a student has edited the file!

5. Use Sheets to send a merged newsletter

In this video, you’ll learn all about the AutoCrat script, and how you can use it to put together the perfect holiday newsletter. With the power of Google Apps Script you can combine Google Docs, Sheets, and Gmail to create a personalized newsletter, without having to write several different versions.

6. Use Apps Script to send email notifications from your Sheets*

While there are a lot of things to love about the new Google Sheets, some much loved features have yet to make the transition. One of the most notable missing features is Notification Rules. While the feature has yet to make it to the new Google Sheets, there is an easy workaround available that takes very little time to set up. The workaround involves Google Apps Script, and this is a wonderful opportunity for those of you who have yet to give it a try.

*Notification rules are now available in Google Sheets without having to use a workaround.

Sign up for our newsletter