There are so many features inside of Google Spreadsheets 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 Spreadsheets have to offer. Most people aren’t aware that Spreadsheets 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 Spreadsheets expert, we’ve put together six of our favorite advanced tips, tricks and tools. If you’re interested in building your foundation before you get started with these tips, feel free to check out the Google Spreadsheets Course at Gooru University.
1. Use arrayformula, match and offset for dynamic data
The arrayformula in Google Spreadsheets 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 Spreadsheet cells
If you’re a frequent user of Google Spreadsheets, 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 Spreadsheet, rather than inside a cell. So if you’re trying to insert several images into a Spreadsheet 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, Spreadsheets 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 Spreadsheets.
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 Spreadsheets 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, Spreadsheets and Gmail to create a personalized newsletter, without having to write several different versions.
6. Use Apps Script to send email notifications from your Spreadsheets
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.