4 Google Scripts That Make Google Sheets Much More Powerful
Pinterest Stumbleupon Whatsapp
Advertisement

Google Sheets 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 the fact that you can use what are called Google Scripts to enhance the capabilities of this online tool.

Something like “Google Scripts” sounds scary. You’re probably thinking that anything with the word “scripts” in it requires advanced programming knowledge. That actually isn’t the case.

In this article I’m going to show you four fairly simple scripts that will let you customize your own functions (like a temperature conversion), auto-generate charts based on any data, customize your own menu inside of Google Sheets, and even automate the sending of monthly emails. All that in a single article? You bet!

1. Create Your Own Custom Functions

One of the easiest ways 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.

Sheets Custom Function

Clicking on “More functions…” turns up a long list of math, statistical, financial, text, engineering, and many 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 and a drag of your mouse, you could automatically convert all of those imported values.

To create your first custom function, first you’ll need to open up the script editor. To do this, click on Tools > Script Editor.

Script Editor

You will likely see a screen like the one below, or something similar to it.

Script Editor

What you’ll need to do is replace what’s in this Window with your own custom function. The function name is the same as the function 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:

Sheets Function

Press Enter and you’ll see the result.

Sheets Function Result

That’s all there is to it. Just imagine all of the cool custom functions you could quickly create now that you know this little trick!

2. Auto-Generate Charts

In past articles, we’ve showed you how to do things like log data from your home Wi-Fi cameras to a Google spreadsheet Log Your Home Activity With Google Sheets and USB Cameras Log Your Home Activity With Google Sheets and USB Cameras If you have a USB camera and a computer, you already have everything you need to set up an inexpensive motion detection system. Read More , or maybe you’re using Google Sheets with a team 5 Google Sheets Settings Essential for Teamwork 5 Google Sheets Settings Essential for Teamwork Google Sheets has more features to support collective data input and processing than you may think. We'll introduce you to Google Sheets functions and features that are essential for group projects. Read More and other people are entering data for you.

Eventually, you may have a new spreadsheet each month with new data, and you’d like to automatically create a new chart without having to manually recreate it every month. 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.

First, in this scenario, let’s say 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:

Sheets Exam Scores

What you would like to do is run a single function on this sheet that would autogenerate 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, all you have to do is open each of your student’s spreadsheets, and click on the run icon in the menu in Google Scripts to auto-generate the chart.

Scripts Run Icon

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).

Sheets Generated Chart

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, as you don’t have to recreate the wheel every time you want to chart data on a new sheet.

3. Create Custom Menus

What if you don’t want to have 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 showed 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.

Sheets Custom 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 final script worth covering here, since the idea is to add to the power and functionality of Google Sheets, 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 Delegation: A Must Have Leadership Skill to Reduce Your Workload Delegation: A Must Have Leadership Skill to Reduce Your Workload Once you're in a leadership role, you'll have to learn how to delegate work. We teach you the five Ws of delegation: the Who, What, When, Where, and Why. Read More 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 in this article, 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.

Sheets Sample Data

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 you might not have considered before.

If you consider the fact that you could potentially have other people responsible for entering data into a Google spreadsheet for you, and then if you automate the distribution of emails based on that entered data, you could potentially do something like send a monthly report to your boss, without ever actually having to open up your email client yourself. The script can automatically do all the work for you!

Automation Is the Secret to 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 automations can be set up to run on a schedule, or you can run them manually whenever you want to trigger them. Either way they provide you with a way to automate a lot of work, with very little effort.

Have you ever used any of the functions described in this article to automate any of your own tasks? Do you know any other cool, simple scripts to simplify effort in Google Sheets? Share your ideas in the comments section below!

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Cam Sylvester
    June 9, 2017 at 2:58 am

    Just wondering why you didn't name var row as column like it was commented as?