The Complete Guide to Simple Automation using Google Sheets Macros

Google Sheets Macros are small programs you create inside of Google Sheets without needing to write any code.

They’re used to automate repetitive tasks. They work by recording your actions as you do something and saving these actions as a “recipe” that you can re-use again with a single click.

For example, you might apply the same formatting to your charts and tables. It’s tedious to do this manually each time. Instead you record a macro to apply the formatting at the click of a button.

In this article, you’ll learn how to use them, discover their limitations and also see how they’re a great segue into the wonderful world of Apps Script coding 😀

Contents

  1. What are Google Sheets macros?
  2. Why should you use macros?
  3. How to create your first macro
  4. Other options
  5. Best Practices for Google Sheets Macros
  6. Limitations of Google Sheets Macros
  7. A peek under the hood of Google Sheets Macros
  8. Example of Google Sheets Macros
  9. Resources

1. What are Google Sheets macros?

Think of a typical day at work with Google Sheets open. There are probably some tasks you perform repeatedly, such as formatting reports to look a certain way, or adding the same chart to new sales data, or creating that special formula unique to your business.

They all take time, right?

They’re repetitive. Boring too probably. You’re just going through the same motions as yesterday, or last week, or last month. And anything that’s repetitive is a great contender for automating.

This is where Google Sheets macros come in, and this is how they work:

  • Click a button to start recording a macro
  • Do your stuff
  • Click the button to stop recording the macro
  • Redo the process whenever you want at the click of a button

They really are that simple.

^ Back to Contents

2. Why should you use macros in Google Sheets?

There’s the obvious reason that macros in Google Sheets can save you heaps of time, allowing you to focus on higher value activity.

But there’s a host of other less obvious reasons like: avoiding mistakes, ensuring consistency in your work, decreased boredom at work (corollary: increased motivation!) and lastly, they’re a great doorway into the wonderful world of Apps Script coding, where you can really turbocharge your spreadsheets and G Suite work.

^ Back to Contents

3. Steps to record your first macro

Let’s run through the process of creating a super basic macro, in steps:

1) Open a new Google Sheet (pro-tip 1: type sheets.new into your browser to create a new Sheet instantly, or pro-tip 2: in your Drive folder hit Shift + s to create a new Sheet in that folder instantly).

Type some words in cell A1.

2) Go to the macro menu: Tools > Macros > Record macro

Google Sheets macro menu

3) You have a choice between Absolute or Relative references. For this first example, let’s choose relative references:

Macro with relative reference

Absolute references apply the formatting to the same range of cells each time (if you select A1:D10 for example, it’ll always apply the macro to these cells). It’s useful if you want to apply steps to a new batch of data each time, and it’s in the same range location each time.

Relative references apply the formatting based on where your cursor is (if you record your macro applied to cell A1, but then re-run the macro when you’ve selected cell D5, the macro steps will be applied to D5 now). It’s useful for things like formulas that you want to apply to different cells.

4) Apply some formatting to the text in cell A1 (e.g. make it bold, make it bigger, change the color, etc.). You’ll notice the macro recorder logging each step:

Macro logging step

5) When you’ve finished, click SAVE and give your Macro a name:

Save macro

(You can also add a shortcut key to allow quick access to run your macro in the future.)

Click SAVE again and Google Sheets will save your macro.

6) Your macro is now available to use and is accessed through the Tools > Macros menu:

select macro menu

7) The first time you run the macro, you’ll be prompted to grant it permission to run. This is a security measure to ensure you’re happy to run the code in the background. Since you’ve created it, it’s safe to proceed.

First, you’ll click Continue on the Authorization popup:

Macro authorization

Then select your Google account:

Macro choose Google account

Finally, review the permissions, and click Allow:

Macro grant permissions

8) The macro then runs and repeats the actions you recorded on the new cell you’ve selected!

You’ll see the following yellow status messages flash across the top of your Google Sheet:

Macro running script

Macro finished script

and then you’ll see the result:

Macro result

Woohoo!

Congratulations on your first Google Sheets macro! You see, it was easy!

Here’s a quick GIF showing the macro recording process in full:

Recording a macro

And here’s what it looks like when you run it:

Run your macro

^ Back to Contents

4. Other options

4.1 Macro Shortcuts

This is an optional feature when you save your macro in Google Sheets. They can also be added later via the Tools > Macros > Manage macros menu.

Shortcuts allow you to run your macros by pressing the specific combination of keys you’ve set, which saves you further time by not having to click through the menus.

Any macro shortcut keys must be unique and you’re limited to a maximum of 10 macro shortcut keys per Google Sheet.

Macro shortcut

In the above example, I could run this macro by pressing:

⌘ + option + shift + 1

keys at the same time (takes practice 😉). Will be a different key combo on PC/Chromebooks.

4.2 Deleting macros

You can remove Google Sheets macros from your Sheet through the manage macros menu: Tools > Macros > Manage macros

Under the list of your macros, find the one you want to delete. Click the three vertical dots on right side of macro and then choose Remove macro:

remove macro

4.3 Importing other macros

Lastly, you can add any functions you’ve created in your Apps Script file to the Macro menu, so you can run them without having to go to the script editor window. This is a more advanced option for users who are more comfortable with writing Apps Script code.

import function to macro menu

This option is only available if you have functions in your Apps Script file that are not already in the macro menu. Otherwise it will be greyed out.

^ Back to Contents

5. Best Practices for Google Sheets Macros

Use the minimum number of actions you can when you record your macros to keep them as performant as possible.

For macros that make changes to a single cell, you can apply those same changes to a range of cells by highlighting the range first and then running the macro. So it’s often not necessary to highlight entire ranges when you’re recording your macros.

^ Back to Contents

6. Limitations of Google Sheets Macros

Macros are bound to the Google Sheet in which they’re created and can’t be used outside of that Sheet. Similarly, macros written in standalone Apps Script files are simply ignored.

Macros are not available for other G Suite tools like Google Docs, Slides, etc. (At least, not yet.)

You can’t distribute macros as libraries or define them in Sheets Add-ons. I hope the distribution of macros is improved in the future, so you can create a catalog of macros that is available across any Sheets in your Drive folder.

^ Back to Contents

7. A peek under the hood of Google Sheets Macros

Behind the scenes, macros in Google Sheets converts your actions into Apps Script code, which is just a version of Javascript run in the Google Cloud.

If you’re new to Apps Script, you may want to check out my Google Apps Script: A Beginner’s Guide.

If you want to take a look at this code, you can see it by opening the script editor (Tools > Script editor or Tools > Macros > Manage macros).

You’ll see an Apps Script file with code similar to this:

/** @OnlyCurrentDoc */

function FormatText() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRangeList().setFontWeight('bold')
  .setFontStyle('italic')
  .setFontColor('#ff0000')
  .setFontSize(18)
  .setFontFamily('Montserrat');
};

Essentially, this code grabs the spreadsheet and then grabs the active range of cells I’ve selected.

The macro then makes this selection bold (line 5), italic (line 6), red (line 7, specified as a hex color), font size 18 (line 8) and finally changes the font family to Montserrat (line 9).

The video at the top of this page goes into a lot more detail about this Apps Script, what it means and how to modify it.

Macros in Google Sheets are a great first step into the world of Apps Script, so I’d encourage you to open up the editor for your different macros and check out what they look like.

(In case you’re wondering, the line /** @OnlyCurrentDoc */ ensures that the authorization procedure only asks for access to the current file where your macro lives.)

^ Back to Contents

8. Examples of Google Sheets Macros

8.1 Formatting tables

Record the steps as you format your reporting tables, so that you can quickly apply those same formatting steps to other tables. You’ll want to use Relative references so that you can apply the formatting wherever your table range is (if you used absolute then it will always apply the formatting to the same range of cells).

Check out the video at the top of the page to see this example in detail, including how to modify the Apps Script code to adjust for different sized tables.

8.2 Creating charts

If you find yourself creating the same chart over and over, say for new datasets each week, then maybe it’s time to encapsulate that in a macro.

Record your steps as you create the chart your first time so you have it for future use.

The video at the top of the page shows an example in detail.


The following macros are intended to be copied into your Script Editor and then imported to the macro menu and run from there.

8.3 Convert all formulas to values on current Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// convert all formulas to values in the active sheet
function formulasToValuesActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will convert any formulas in the current sheet to values.

8.4 Convert all formulas to values in entire Google Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// convert all formulas to values in every sheet of the Google Sheet
function formulasToValuesGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will convert all the formulas in every sheet of your Google Sheet into values.

8.5 Sort all your sheets in a Google Sheet alphabetically

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// sort sheets alphabetically
function sortSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var sheetNames = [];
  sheets.forEach(function(sheet,i) {
    sheetNames.push(sheet.getName());
  });
  sheetNames.sort().forEach(function(sheet,i) {
    spreadsheet.getSheetByName(sheet).activate();
    spreadsheet.moveActiveSheet(i + 1);
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will sort all your sheets in a Google Sheet alphabetically.

8.6 Unhide all rows and columns in the current Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// unhide all rows and columns in current Sheet data range
function unhideRowsColumnsActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  sheet.unhideRow(range);
  sheet.unhideColumn(range);
}

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will unhide any hidden rows and columns within the data range. (If you have hidden rows/columns outside of the data range, they will not be affected.)

8.7 Unhide all rows and columns in entire Google Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// unhide all rows and columns in data ranges of entire Google Sheet
function unhideRowsColumnsGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    sheet.unhideRow(range);
    sheet.unhideColumn(range);
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will unhide any hidden rows and columns within the data range in each sheet of your entire Google Sheet.

8.8 Set all Sheets to have a specific tab color

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// set all Sheets tabs to red
function setTabColor() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.setTabColor("ff0000");
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will set all of the tab colors to red.

Want a different color? Just change the hex code on line 5 to whatever you want, e.g. cornflower blue would be 6495ed

Use this handy guide to find the hex values you want.

8.9 Remove any tab coloring from all Sheets

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// remove all Sheets tabs color
function resetTabColor() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.setTabColor(null);
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will remove all of the tab colors from your Sheet (it sets them back to null, i.e. no value).

Here’s a GIF showing the tab colors being added and removed via Macros (check the bottom of the image):

color tabs with Macros

8.10 Hide all sheets apart from the active one

Copy and paste this code into your script editor and import the function into your Macro menu:

function hideAllSheetsExceptActive() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    if (sheet.getName() != SpreadsheetApp.getActiveSheet().getName()) 
      sheet.hideSheet();
  });
};

Running this macro will hide all the Sheets in your Google Sheet, except for the one you have selected (the active sheet).

8.11 Unhide all Sheets in your Sheet in one go

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

function unhideAllSheets() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.showSheet();
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will show any hidden Sheets in your Sheet, to save you having to do it 1-by-1.

Here’s a GIF showing how the hide and unhide macros work:

hide unhide sheets with macros

You can see how Sheet6, the active Sheet, is the only one that isn’t hidden when the first macro is run.

8.12 Resetting Filters

Ok, saving the best to last, this is one of my favorite macros! 🙂

I use filters on my data tables all the time, and find it mildly annoying that there’s no way to clear all your filters in one go. You have to manually reset each filter in turn (time consuming, and sometimes hard to see which columns have filters when you have really big datasets) OR you can completely remove the filter and re-add from the menu.

Let’s create a macro in Google Sheets to do that! Then we can be super efficient by running it with a single menu click or even better, from a shortcut.

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// reset all filters for a data range on current Sheet
function resetFilter() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.getFilter().remove();
  range.createFilter();
}

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will remove and then re-add filters to your data range in one go.

Here’s a GIF showing the problem and macro solution:

Macro to reset filters

^ Back to Contents

9. Resources

If you’re interested in taking things further, check out the following resources for getting started with Apps Script:

Macro reference guide in Google Docs help

Macro reference guide in the Google Developer documentation

And if you want to really start digging into the Apps Script code, you’ll want to bookmark the Google documentation for the Spreadsheet Service.

Finally, all of this macro code is available here on GitHub.

How To Connect Google Sheets To A Database, Using Apps Script

This is a guest post from Mike Ritchie, co-founder of Seekwell.io, which adds SQL to the apps you need it in.

Google Sheets is great for quickly spinning up dashboards and analysis, but getting raw data into Sheets from databases can be tedious.

In this post we cover a few ways to get data from your SQL database into Google Sheets.

Google Sheets Database Connection With Apps Script

Sheets comes with a built-in app development platform called “Apps Script”.

Based on JavaScript, it covers a lot of the tasks you’d use VBA for in Excel.

App Script comes with a JDBC Service that lets you connect to MySQL, Microsoft SQL Server, and Oracle databases.

Steps To Connect Google Sheets To A Database

1) Open the Script Editor in Sheets using “Tools” → “Script editor”. Or just copy this Sheet here.

How To Connect Google Sheets To A Database
Access Apps Script under the menu Tools > Script editor

2) Replace “Code.gs” with the code here. (Skip this if you copied the Sheet above)

3) We included credentials for SeekWell’s demo MySQL database. To connect to your database, replace the six fields below. Note you’ll need to whitelist Google’s IP addresses.

var HOST = 'yourhostname'
var PORT = '3306 or your port'
var USERNAME = 'yourusername'
var PASSWORD = 'yourpassword'
var DATABASE = 'youdatabasename'
var DB_TYPE = 'mysql or your type'

4) You might also want to change the MAXROWS, but you don’t go too crazy, Sheets has a hard limit of 5 million cells and the query will take longer to run with more rows.

5) Save the file and refresh / refresh the Sheet. You’ll see a new menu option of “SeekWell Lite” show up.

6) The script is set up to read the query from query!A2 and write the results to your active cell, so you’ll need to add a sheet called “query” and add the query below in the cell query!A2 (skip if you copied the Sheet above).

SELECT *
FROM dummy.users
LIMIT 100

7) Go back to Sheet1, click in cell C4 (or any other cell) and click “SeekWell Lite” → “Run SQL”.

In a few moments you’ll see the data show up!

A few problems with this approach

You need to store your password in plain text in the Code.gs file.

Sharing the script with your team and adding the script to different Sheets is a bit of a pain. You can publish an addon, but that comes with some overhead.

And scheduling / automating refreshes can be cumbersome when you need many different queries going to many different Sheets.

Google’s JDBC service doesn’t work for Postgres, Snowflake or RedShift and requires a long list of whitelisted IP’s. It also doesn’t support SSH.

Alternatives to Google Sheets database connections with App Script

Python

If you’re comfortable with Python, you can put together a program using Pandas and the Sheets API. Pandas has great SQL support built in.

SaaS Products

A lot of people hate paying for things they can do for free, but you should always do some napkin math when making the “build vs. buy” decision.

In the case of automating reports, the ROI can be pretty high, especially if you have several daily, hourly, or near real time dashboards you need to keep updated. ActionDesk did a good overview of the options out there.


This is a guest post written by Mike Ritchie. Mike is the co-founder of Seekwell and has over 15 years experience in analytics.

SeekWell features include:

  • Takes < 2 minutes to get your first schedule set up
  • A shared code repository with every query anyone on your team has ever written
  • Beautiful query editor with autocomplete and snippets
  • Ability to automate alerts via Slack and email
  • Support for MySQL, Postgres, Snowflake, Redshift, and SQL Server

Apps Script V8 Runtime Is Here! What Does That Mean?

In February 2020, Google announced the launch of the V8 runtime for Apps Script, which is the same runtime environment that powers Chrome. It allows us to take advantage of all the modern JavaScript features.

A runtime environment is the engine that interprets your code and executes the instructions.

Historically, Apps Script used a runtime environment called Rhino, which locked Apps Script to an older version of JavaScript that excluded modern JavaScript features.

But no more!

In this guide, we’ll explore the basics of the new V8 runtime, highlighting the features relevant for beginner-to-intermediate level Apps Script users.

Enabling The Apps Script V8 Runtime

When you open the Apps Script editor, you’ll see a yellow notification bar at the top of your editor window prompting you to enable V8:

Enable V8 runtime in Apps Script

If you don’t see this notification, you can select Run > Enable new Apps Script runtime powered by V8

Enable V8 runtime in Apps Script

Save your script to complete the enabling process.

If you need to return to the old version (in the unlikely scenario your script isn’t compatible with the new V8 runtime) then you can switch back to the old Rhino runtime editor.

Select Run > Disable new Apps Script powered by V8.


New Logging In The Apps Script V8 Runtime

The new V8 runtime logger shows both the Logger.log and console.log results for the most recent execution under the View > Logs menu.

Previously the console results where only accessible via the Stackdriver Logging service.

Here’s an example showing the Logger and console syntax (notice Logger is capitalized and console is not, it matters):

function loggerExample() {
  Logger.log("Hello, world from Logger.log!");
  console.log("Hello, world from console.log!")
}

The output in our logger window (accessed via View > Logs) shows both of these results:

Logger and console logs in V8


Modern JavaScript Features

There are a lot of exciting new features available with modern JavaScript. They look strange at first but don’t panic!

There’s no need to start using them all immediately.

Just keep doing what you’re doing, writing your scripts and when you get a chance, try out one of the new features. See if you can incorporate it in your code and you’ll gradually find ways to use them.

Here are the new V8 features in a vague order of ascending difficulty:

Multi-line comments

We can now create multi-line strings more easily by using a back tick syntax:

// new V8 method
var newString = `This is how we do 
multi-line strings now.`;

This is the same syntax as template literals and it greatly simplifies creating multi-line strings.

Previously each string was restricted to a single line. To make multi-line comments we had to use a plus-sign to join them together.

// old method
var oldString = 'This is how we used\n'
+ 'to do multi-line strings.'; 

Default Parameters

The Apps Script V8 runtime lets us now specify default values for parameters in the function definition.

In this example, the function addNumbers simply logs the value of x + y.

If we don’t tell the function what the values of x and y are, it uses the defaults we’ve set (so x is 1 and y is 2).

function addNumbers(x = 1, y = 2) {
  console.log(x + y);
}

When we run this function, the result in the Logger is 3.

What’s happening is that the function assigns the default values to x and y since we don’t specify values for x and y anywhere else in the function.

let Keyword

The let statement declares a variable that operates locally within a block.

Consider this fragment of code, which uses the let keyword to define x and assign it the variable of 1. Inside the block, denoted by the curly brackets {…}, x is redefined and re-assigned to the value of 2.

let x = 1;
  
{
  let x = 2;
  console.log(x); // output of 2 in the logs
}
  
console.log(x); // output of 1 in the logs

The output of this in the logs is the values 2 and 1, because the second console.log is outside the block, so x has the value of 1.

Note, compare this with using the var keyword:

var x = 1;
  
{
  var x = 2;
  console.log(x); // output of 2 in the logs
}
  
console.log(x); // output of 2 in the logs

Both log results give the output of 2, because the value of x is reassigned to 2 and this applies outside the block because we’re using the var keyword. (Variables declared with var keyword in non-strict mode do not have block scope.)

const Keyword

The const keyword declares a variable, called a constant, whose value can’t be changed. Constants are block scoped like the let variable example above.

For example, this code:

const x = 1;
x = 2; 
console.log(x);

gives an error when we run it because we’re not allowed to reassign the value of a constant once it’s been declared:

const keyword error

Similarly, we can’t declare a const keyword without also assigning it a value. So this code:

const x;

also gives an error when we try to save our script file:

Apps Script V8 runtime const error message

Spread syntax

Suppose we have the following array of data:

var arr = [[1,2],[3.4],[5,6]];

It’s an array of arrays, so it’s exactly the format of the data we get from our Sheets when we use the getRange().getValues() method.

Sometimes we want to flatten arrays, so we can loop over all the elements. Well, in V8, we can use the spread operator (three dots … ), like so:

var flatArr = [].concat(...arr);

This results in a new array: [1,2,3,4,5,6]

Template Literals

Template literals are a way to embed expressions into strings to create more complex statements.

One example of template literals is to embed expressions within normal strings like this:

let firstName = 'Ben';
let lastName = 'Collins';
console.log(`Full name is ${firstName} ${lastName}`);

The logs show “Full name is Ben Collins”

In this case, we embed a placeholder between the back ticks, denoted by the dollar sign with curly brackets ${ some_variable }, which gets passed to the function for evaluation.

The multi-line strings described above are another example of template literals.

Arrow Functions

Arrow functions provide a compact way of writing functions.

Arrow Function Example 1

Here’s a very simple example:

const double = x => x * 2;

This expression creates a function called double, which takes an input x and returns x multiplied by 2.

This is functionally equivalent to the long-hand function:

function double(x) {
  return x * 2;
}

If we call either of these examples and pass in the value 10, we’ll get the answer 20 back.

Arrow Function Example 2

In the same vein, here’s another arrow function, this time a little more advanced.

Firstly, define an array of numbers from 1 to 10:

const arr = [1,2,3,4,5,6,7,8,9.10];

This arrow function will create a new array, called evenArr, consisting of only the even numbers.

const evenArr = arr.filter(el => (el % 2 === 0));
console.log(evenArr);

The filter only returns values that pass the conditional test: (el % 2 === 0) which translates as remainder is 0 when dividing by 2 i.e. the even numbers.

The output in the logs is [2,4,6,8]:

Apps Script V8 runtime arrow function logs

Other Advanced Features

There are more advanced features in V8 that are not covered in this post, including:

I’m still exploring them and will create resources for them in the future.


Migrating Scripts To Apps Script V8 Runtime

The majority of scripts should run in the new V8 runtime environment without any problems. In all likelihood, the only adjustment you’ll make is to enable the new V8 runtime in the first place.

However, there are some incompatibilities that may cause your script to fail or behave differently.

But for beginner to intermediate Apps Scripters, writing relatively simple scripts to automate workflows in G Suite, it’s unlikely that you’ll have any problems.

You can read more about migrating scripts to the V8 runtime and incompatibilities in the detailed documentation from Google.


Other Apps Script V8 Runtime Resources

V8 Runtime Overview

ES 6 Features for Google Apps Script: Template Literals

ES6 Features for Google Apps Script: Arrow Functions

Here’s a good explanation of the V8 runtime from Digital Inspiration

The new V8 runtime offers significant performance improvements over the old Rhino editor. Your code will run much, much faster! Here’s a deep dive: Benchmark: Loop for Array Processing using Google Apps Script with V8

Add A Google Sheets Button To Run Scripts

Learn how to add a Google Sheets button to run your Google Apps Script functions.

Let’s see how this works with a simple example.

Imagine you have an invoice template you use on a regular basis, but it’s a pain to clear out all the values each time you need to start over. Well, you can add a button to Google Sheets so you can run scripts and clear your invoice with a single button click.

Google Sheets button

Let’s start by creating a basic invoice template with placeholders to hold information:

Add button to Google Sheets invoice example

The user can enter information into cells B5, B8, E5 and E6 (shown in yellow).

In the script editor, accessed through Tools > Script Editor, add a very simple script to clear these specific cells out:

function clearInvoice() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var invoiceNumber = sheet.getRange("B5").clearContent();
  var invoiceAmount = sheet.getRange("B8").clearContent();
  var invoiceTo = sheet.getRange("E5").clearContent();
  var invoiceFrom = sheet.getRange("E6").clearContent(); 
}

You can run this function from the script editor and it will clear out the contents of the invoice.

But that’s a pain.

You don’t want to have to open up the script editor every time. You want to do that directly from your Google Sheet.

To do that, add a Google Sheets button.

You add a button via the Insert > Drawing menu.

This brings up the drawing editor where you can easily add a box and style it to look like a button:

Google Sheets button drawing

When you click Save and Close, this drawing gets added to your Google Sheet. You can click on it to resize it or drag it around to reposition it.

To assign a script, click the three little dots in the top right of the drawing and select Assign Script:

Google Sheets button assign script

Then type in the name of the function you want to run from your Apps Script code. In this example, choose the clearInvoice function (i.e. like the code above!).

Now, when you click the button it will clear out the invoice for you!

Button with apps script in google sheets

Note: to edit or move the button after you’ve assigned it to a script, you now need to right-click on it.

See Create, insert & edit drawings in the Google Documentation for more info on the Drawing feature.

Google Apps Script: A Beginner’s Guide

What is Google Apps Script?

Google Apps Script is a cloud based scripting language for extending the functionality of Google Apps and building lightweight cloud-based applications.

What does this mean in practice?

It means you use Apps Script to write small programs that extend the standard features of Google Apps. I like to say it’s great for filling in the gaps in your workflows.

For example, I used to be overwhelmed with feedback from my courses and couldn’t respond to everyone. Now, when a student submits their feedback, my script creates a draft email in Gmail ready for me to review. It includes all the feedback so I can read it within Gmail and respond immediately.

It made a previously impossible task manageable.

With Apps Script, you can do cool stuff like automating repetitive tasks, creating documents, emailing people automatically and connecting your Google Sheets to other services you use.

Writing your first Google Script

In this Google Sheets script tutorial, we’re going to write a script that is bound to our Google Sheet. This is called a container-bound script.

(If you’re looking for more advanced examples and tutorials, check out the full list of Apps Script articles on my homepage.)

Hello World in Google Apps Script

Let’s write our first, extremely basic program, the classic “Hello world” program beloved of computer teaching departments the world over.

Begin by creating a new Google Sheet.

Then click the menu Tools > Script editor... to open a new tab with the code editor window.

This will open a new tab in your browser, which is the Google Apps Script editor window:

Google Apps Script editor window

By default, it’ll open with a single Google Script file (code.gs) and a default code block, myFunction():

function myFunction() {
  
}

In the code window, between the curly braces after the function myFunction() syntax, write the following line of code so you have this in your code window:

function myFunction() {
  Browser.msgBox("Hello World!");
}

Your code window should now look like this:

Google Sheets script tutorial editor menu

Google Apps Script Authorization

Google Scripts have robust security protections to reduce risk from unverified apps, so we go through the authorization workflow when we first authorize our own apps.

When you hit the run button (the black triangle) for the first time, you will be prompted to authorize the app to run:

Google Apps Script Authorization

Clicking Continue pops up another window in turn, showing what permissions your app needs to run. In this instance the app wants to view and manage your spreadsheets in Google Drive, so click Allow (otherwise your script won’t be able to interact with your spreadsheet or do anything):

Google Apps Script Authorization

❗️When your first run your apps script, you may see the “app isn’t verified” screen and warnings about whether you want to continue.

In our case, since we are the creator of the app, we know it’s safe so we do want to continue. Furthermore, the apps script projects in this post are not intended to be published publicly for other users, so we don’t need to submit it to Google for review (although if you want to do that, here’s more information).

Click the “Advanced” button in the bottom left of the review permissions pop-up, and then click the “Go to Starter Script Code (unsafe)” at the bottom of the next screen to continue. Then type in the words “Continue” on the next screen, click Next, and finally review the permissions and click “ALLOW”, as shown in this image (showing different script):

More information can be found in this detailed blog post from Google Developer Expert Martin Hawksey.

Running a function in Apps Script

Once you’ve authorized a Google App script, the function will run (or execute). You will see two status messages to tell you what’s happening.

First this one:

GAS execute script

And then this one:

GAS execute status 2

If anything goes wrong with your code, this is stage when you’d see a warning message (instead of the yellow message, you’ll get a red box with an error message in it).

Now, assuming you got those two yellow status messages and they’ve both automatically disappeared from view, then your program has run successfully. Click back on the browser tab with your spreadsheet (most likely the tab to the left of the one we’re in).

You should see the output of your program, a message box popup with the classic “Hello world!” message:

Google Apps Script output hello world

Click on Ok to dismiss.

Great job! You’ve now written your first apps script program.

Rename functions in Google Apps Script

We should rename our function to something more meaningful.

At present, it’s called myFunction which is the default, generic name generated by Google. Every time I want to call this function (i.e. run it to do something) I would write myFunction(). This isn’t very descriptive, so let’s rename it to helloWorld(), which gives us some context.

So change your code in line 1 from this:

function myFunction() {
  Browser.msgBox("Hello World!");
}

to this:

function helloWorld() {
  Browser.msgBox("Hello World!");
}

Note, it’s convention in Apps Script to use the CamelCase naming convention, starting with a lowercase letter. Hence, we name our function helloWorld, with a lowercase h at the start of hello and an uppercase W at the start of World.

Adding a custom menu in Google Apps Script

In its current form, our program is pretty useless for many reasons, not least because we can only run it from the script editor window and not from our spreadsheet.

Let’s fix that by adding a custom menu to the menu bar of our spreadsheet, so that a user can run the script within the spreadsheet without needing to open up the editor window.

This is actually surprisingly easy to do, requiring only a few lines of code. Add the following 6 lines of code into the editor window, above the helloWorld() function we created above, as shown here:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('My Custom Menu')
      .addItem('Say Hello', 'helloWorld')
      .addToUi();
}

function helloWorld() {
  Browser.msgBox("Hello World!");
}

If you look back at your spreadsheet tab in the browser now, nothing will have changed. You won’t have the custom menu there yet. We need to re-open our spreadsheet (refresh it) or run our onOpen() script first, for the menu to show up.

To run onOpen() from the editor window, first select the onOpen function as shown in this image:

Google Apps Script custom menu

Once you’ve selected the onOpen function, the small triangle button will change from light gray to black, meaning it can be clicked to run your chosen function:

Run function

Now, when you return to your spreadsheet you’ll see a new menu on the right side of the Help option, called My Custom Menu. Click on it and it’ll open up to show a choice to run your Hello World program:

Custom menu

Run functions from buttons in Google Sheets

An alternative way to run Google Scripts from your Sheets is to bind the function to a button in your Sheet.

For example, here’s an invoice template Sheet with a RESET button to clear out the contents:

Button with apps script in google sheets

For more information on how to do this, have a look at this post: Add A Google Sheets Button To Run Scripts

Google Apps Script Examples

Macros in Google Sheets

Another great way to get started with Google Scripts is by using Macros. Macros are small programs in your Google Sheets that you record so that you can re-use them (for example applying a standard formatting to a table). They use Apps Script under the hood so are a great way to get started in seeing what you can do.

Read more: The Complete Guide to Simple Automation using Google Sheets Macros

Custom function using Google Apps Script

Let’s create a custom function with Apps Script, and also demonstrate the use of the Maps Service. We’ll be creating a small custom function that calculates the driving distance between two points, based on Google Maps Service driving estimates.

The goal is to be able to have two place-names in our spreadsheet, and type the new function in a new cell to get the distance, as follows:

GAS custom function for maps

The solution should be:

GAS custom map function output

Copy the following code into the Apps Script editor window and save. First time, you’ll need to run the script once from the editor window and click “Allow” to ensure the script can interact with your spreadsheet.

function distanceBetweenPoints(start_point, end_point) {
  // get the directions
  var directions = Maps.newDirectionFinder()
     .setOrigin(start_point)
     .setDestination(end_point)
     .setMode(Maps.DirectionFinder.Mode.DRIVING)
     .getDirections();
  
  // get the first route and return the distance
  var route = directions.routes[0];
  var distance = route.legs[0].distance.text;
  return distance;
}

Saving data with Google Apps Script

Let’s take a look at another simple use case for this Google Sheets Apps Script tutorial.

Here, I’ve setup an importxml function to extract the number of followers a specific social media channel has (e.g. in this case a Reddit channel), and I want to save copy of that number at periodic intervals, like so:

save data in google sheet

In this script, I’ve created a custom menu (as we did above) to run my main function. The main function, saveData(), copies the top row of my spreadsheet (the live data) and pastes it to the next blank line below my current data range as text, thereby “saving” a snapshot in time.

The code for this example is:

// custom menu function
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Save Data','saveData')
      .addToUi();
}

// function to save data
function saveData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var url = sheet.getRange('Sheet1!A1').getValue();
  var follower_count = sheet.getRange('Sheet1!B1').getValue();
  var date = sheet.getRange('Sheet1!C1').getValue();
  sheet.appendRow([url,follower_count,date]);
}

See this post: Saving Data in Google Sheets, for a step-by-step guide to creating and running this script.

Google Apps Script example in Google Docs

Google Apps Script is by no means confined to Sheets only, and is equally applicable in the Google Docs environment. Here’s a quick example of a script that inserts a specific symbol or text string into your Doc wherever your cursor is:

Google Docs Apps Script

We do this using Google App Scripts as follows:

1. Create a new Google Doc

2. Open script editor from the menu: Tools > Script editor...

3. Click on: Create script for > Blank Project

Google Apps script menu

4. In the newly opened Script tab, remove all of the boilerplate code (the `myFunction` code block)

5. Copy in the following code:

// code to add the custom menu
function onOpen() {
  var ui = DocumentApp.getUi();
  ui.createMenu('My Custom Menu')
      .addItem('Insert Symbol', 'insertSymbol')
      .addToUi();
};

// code to insert the symbol
function insertSymbol() {  
  // add symbol at the cursor position
  var cursor = DocumentApp.getActiveDocument().getCursor();
  var element = cursor.insertText('§§');
  
};

6. You can change the special character in this line

var element = cursor.insertText('§§');

to whatever you want it to be, e.g.

var element = cursor.insertText('( ͡° ͜ʖ ͡°)');

7. Click Save and give your script project a name (doesn’t affect the running so call it what you want e.g. Insert Symbol)

8. Run the script for the first time by clicking on the menu: Run > onOpen

9. Google will recognize the script is not yet authorized and ask you if you want to continue. Click Continue

10. Since this the first run of the script, Google Docs asks you to authorize the script (I called my script “test” which you can see below):

Docs Apps Script Auth

11. Click Allow

12. Return to your Google Doc now.

13. You’ll have a new menu option, so click on it:
My Custom Menu > Insert Symbol

14. Click on Insert Symbol and you should see the symbol inserted wherever your cursor is.

Google Apps Script Tip: Use the Logger class

Use the Logger class to output text messages to the log files, to help debug code.

The log files can be accessed after the program has finished running, by going to View > Show Logs (or Cmd + Enter, or Ctrl + Enter (on PC)).

The syntax in its most basic form is Logger.log(something in here). This records the value(s) of variable(s) at different steps of your program.

For example, add this script to a code file your editor window:

function logTimeRightNow() {
  var timestamp = new Date();
  Logger.log(timestamp);
}

Run the script in the editor window, then View > Show Logs and you should see:

logger output

Real world examples from my own work

I’ve only scratched the surface of what’s possible using G.A.S. to extend the Google Apps experience.

Here’s a couple of interesting projects I’ve worked on:

1) A Sheets/web-app consisting of a custom web form that feeds data into a Google Sheet (including uploading images to Drive and showing thumbnails in the spreadsheet), then creates a PDF copy of the data in the spreadsheet and automatically emails it to the users. And with all the data in a master Google Sheet, it’s possible to perform data analysis, build dashboards showing data in real-time and share/collaborate with other users.

2) A dashboard that connects to a Google Analytics account, pulls in social media data, checks the website status and emails a summary screenshot as a PDF at the end of each day.

Marketing dashboard using Google Apps Script

3) A marking template that can send scores/feedback to students via email and Slack, with a single click from within Google Sheets. Read more in this article: Save time with this custom Google Sheets, Slack & Email integration

Send data from Google Sheets to Slack

My own journey into Google Apps Script

My friend Julian, from Measure School, interviewed me in May 2017 about my journey into Apps Script and my thoughts on getting started:

Google Apps Script Resources

For further reading, I’ve created this list of resources for information and inspiration:

Course

Documentation

Official Google Documentation

G Suite Developers Blog

Communities

Google Apps Script Group

Stack Overflow GAS questions

Books

Going GAS book

Going GAS by Bruce Mcpherson is a newly published (i.e. bang up-to-date as of April 2016) book covering the entire GAS ecosystem, with a specific focus on making the transition from Office/VBA into Google Apps/GAS. Even if you don’t use Office or VBA much or at all, it’s still a very useful resource. It’s been a few years since I’ve done any serious VBA work, but I still found the book very helpful and a great overview of the GAS environment.


Imagination and patience to learn are the only limits to what you can do and where you can go with GAS. I hope you feel inspired to try extending your Sheets and Docs and automate those boring, repetitive tasks!

Related Articles