Google Sheets is part of the Google Suite and is by far one of the most powerful free tools at your disposal. It lets you track, analyze, or log just about anything you could imagine. What makes it even more powerful is using Google Scripts to enhance the capabilities of Google Sheets.
Coding in Google Sheets might sound a little intimidating. You may think at first that anything with the word "scripts" in it requires advanced programming knowledge. That actually isn't the case.
Let's take a look at some pretty cool Google Sheets scripts examples that can boost your productivity.
What Are Google Sheets Scripts?
Google Sheets Scripts are pieces of code you can write inside your Google Sheets that can power up your sheets. Google Sheets Scripts are written in JavaScript, and given that JavaScript is becoming more and more popular you may already be familiar with it.
Writing Google Scripts is very similar to writing VBA in Microsoft Excel to create programs. Scripts in your Google Sheets are powered by the Google Apps Script, which also works with other Google services. Here are four scripts that really demonstrate the power of Google Sheets scripting.
1. Create Your Own Custom Functions
One of the easiest ways of creating a Google Script can greatly enhance your Google Sheets experience is by creating custom functions. Google Sheets already offers a long list of functions. You can see the most common ones by clicking on the Menu > Functions icon.
Clicking on More functions shows you a long list of math, statistical, financial, text, engineering, and other functions. However, Google Scripts gives you the flexibility to create your very own personalized formulas.
For example, say you often import information from a digital thermostat at your job, but the thermostat is set for Celsius. You could create your own custom formula to convert Celsius to Fahrenheit, so with one click, you could automatically convert all of those imported values.
To create your first custom function, you'll need to open up the Script editor. To do this, click Tools > Script Editor.
You will see the project screen, where you can write your JavaScript code.
Here, replace what's in this window with your own custom function. The function name is the same as the name that you'll start typing into a cell in Google Sheets after the "=" symbol in order to invoke your formula. A function to convert Celsius to Fahrenheit would look something like this:
function CSTOFH (input) {
return input * 1.8 + 32;
}
Paste the function above into the code window, and then select File > Save, name the project something like "CelsiusConverter" and click OK.
That's all there is to it! Now, all you have to do to use your new function is type the "=" sign followed by your function, with the input number to convert:
Press Enter to see the result.
That's all there is to it. You can quickly see how you can write just about any custom formula that you need to fit your Google Sheet.
2. Auto-Generate Charts
In other articles, we've shown you how to do things like log data from your home Wi-Fi cameras to a Google spreadsheet, or maybe you're using Google Sheets with a team and other people are entering data for you.
Let's say you're working on a sheet with new data every month. You would like to automatically create a chart using the data in the spreadsheet. You can accomplish this by creating a function that will create a new chart for you, based on the data in the current spreadsheet you have open.
In this scenario, you're a teacher and at the end of the year you have a spreadsheet for each student with the list of monthly exam scores:
What you would like to do is run a single function on this sheet that would generate a chart in seconds. Here is what that script looks like:
function GradeChart()
{ var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getSheets()[0]; var gradechart = sheet.newChart() .setChartType(Charts.ChartType.LINE) .addRange(sheet.getRange('A1:B11')) .setPosition(5, 5, 0, 0) .build(); sheet.insertChart(gradechart); }
Now, open each of your student's spreadsheets and click on the Run icon in the menu in Google Scripts to auto-generate the chart.
Whenever you click on the run icon, it'll run the script that you've created on the "active" spreadsheet (the one you have open in your current browser tab).
For reports that you have to generate frequently, like weekly or monthly, this kind of auto-generated chart function can really save you a lot of time.
3. Create Custom Menus
What if you don't want to have the script open to auto-generate that chart? What if you'd like the convenience of having that function right at your fingertips in the menu system, right inside of Google Sheets? Well, you can do that too.
In order to create a custom menu, you need to tell the spreadsheet to add your new menu item every time it opens. You do this by creating an onOpen() function in the Script editor window above the Gradechart function you just created:
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{ name: 'Create Grade Chart...', functionName: 'GradeChart' }
];
spreadsheet.addMenu('Charts', menuItems);
}
Save the script, and then reload your spreadsheet. You'll discover that now your new Menu item shows up with the name you defined it as in your script. Click on the menu and you'll see the menu item for your function.
Click on the menu item and it'll run the function just like it did when you pressed the "run" icon from inside of the Google Scripts editor!
4. Send Automated Reports
The last script example we're showing you is a script that will send email from inside of Google Sheets.
The way this may come in handy is if you are managing a large team of people and you have multiple emails to send on the same topic.
Maybe you've done a performance review with individual team members and logged your review comments for each person in a Google Spreadsheet.
Would it be nice to just run a single script and have those comments automatically emailed out to the 50 or 60 employees all at the same time without you having to manually create all of those individual emails? That's the power of Google Scripting.
Similar to how you created the scripts above, you'll create a script by going into the Script editor and creating a function called sendEmails(), like this:
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 7; // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 3)
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[1]; // Second column
var message = row[2]; // Third column
var subject = "My review notes";
MailApp.sendEmail(emailAddress, subject, message);
}
}
So let's say you have your spreadsheet organized as shown here.
The script above will work all the way through each row in the spreadsheet, and send out an email to the address in the second column with the message that you've typed into the third column.
The sendEmail function in Google Scripts is by far one of the most powerful functions in Google Scripts because it opens up a whole world of email automation that will save you time.
This script shows you the real power of Google Apps Scripting, combining Gmail with Google Sheets Scripts to automate a task. While you've seen scripts that work on Google Sheets, the best thing to take away is the power of scripting across the whole Google Suite.
Automation Is the Secret to Productivity Success
What all of these automation Google Scripts should show you is that with just a few simple lines of code, Google Scripts has the power to partially or fully automate Google Sheets.
These functions can be set up to run on a schedule, or you can run them manually whenever you want to trigger them. Google scripts can automate boring tasks like sending emails or sending invoices from Google Sheets. Looking for more? Check out these 3 Google Scripts to automate your documents.