Have you ever wondered how you could enhance the functionality of your Google applications? Look no further! Google Apps Script is here to help you extend the capabilities of your Google applications and create lightweight cloud applications.
What is Google Apps Script?
Google Apps Script is a cloud-based scripting language that allows you to extend the functionality of Google applications and create lightweight cloud applications. In other words, you can write small programs with Apps Script to complement the standard features of Google Workspace applications. It’s ideal for filling the gaps in your workflows.
With Apps Script, you can automate repetitive tasks, create documents, automatically send emails, and connect your Google spreadsheets to other services you use.
Writing Your First Google Script
In this Google Sheets scripting tutorial, we’ll write a script that is linked to our Google spreadsheet. This is a bounded script.
Start by creating a new Google spreadsheet.
Then, click on the menu: Extensions > Apps Script.
This will open a new tab in your browser, which is the Google Apps Script editor window.
By default, it will open with a single Google script file (code.gs) and a default code block, myFunction().
In the code window, enter the following line of code inside the curly braces after the syntax of the myFunction() function:
function myFunction() {
Browser.msgBox("Hello, world!");
}Congratulations! You have now written your first Apps Script program.
Authorizing the Google Apps Script
Google scripts have robust security protections to reduce the risks associated with unverified applications. That’s why we have to go through the authorization process when authorizing our own applications.
When you click the “Run” button for the first time, you will be prompted to authorize the application to run.
By clicking on “Review permissions”, another window opens showing the permissions your application needs to run. In this case, the application wants to view and manage your spreadsheets in Google Drive. Click “Authorize” (otherwise, your script won’t be able to interact with your spreadsheet or do anything).
When you run your Apps script for the first time, you may see the “unverified app” screen and warnings asking you if you want to continue.
In our case, since we are the creators of the application, we know it’s safe, so we want to continue. Furthermore, the application script projects described in this article are not intended to be publicly released for other users to use, so we don’t need to submit them to Google for review (but if you want to do so, here’s more information).
Click on the “Advanced” button at the bottom left of the authorization review window, then click “Go to Start Script Code (unsafe)” at the bottom of the next screen to continue. Then type the word “Continue” on the next screen, click Next, review the permissions, and click “ALLOW”, as shown in this image (showing another script in the old editor):
More information can be found in this detailed article by Martin Hawksey, a development expert at Google.
Executing a Function in Apps Script
Once you have authorized the Google Apps script, the function will execute.
If something goes wrong with your code, this is where you’ll see a warning message (instead of the yellow message, you’ll see a red box with an error message).
Go back to your Google spreadsheet, and you should see the output of your program, a popup dialog box with the classic message “Hello, world!”.
Click OK to close it.
Great job! You have now written your first Apps Script program.
Renaming Functions in Google Apps Script
We should rename our function to give it a more specific meaning.
For now, it is called myFunction, which is the generic default name generated by Google. Every time I want to call this function (i.e., execute it to do something), I type myFunction(). This is not very descriptive, so let’s rename it to helloWorld(), which gives it some context.
Modify your code in line 1 like this:
function helloWorld() {
Browser.msgBox("Hello, world!");
}Note that it is convention in Apps Script to use CamelCase naming convention, starting with a lowercase letter. Thus, we name our function helloWorld, with a lowercase h at the beginning of hello and an uppercase W at the beginning of World.
Adding a Custom Menu in Google Apps Script
In its current form, our program is quite useless for many reasons, one of which is that we can only run it from the script editor window and not from our spreadsheet.
We’ll fix that by adding a custom menu to the menu bar of our spreadsheet so that a user can run the script in the spreadsheet without needing to open the editor window.
It’s actually surprisingly easy to do and only requires a few lines of code. Add the following 6 lines of code to the editor window above the helloWorld() function we created earlier, as shown here:
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('My Custom Menu')
.addItem('Say Hello', 'helloWorld')
.addToUi();
}
function helloWorld() {
Browser.msgBox("Hello, world!");
}If you now look at the tab of your spreadsheet in the browser, nothing has changed. You don’t have the custom menu yet. We need to reopen our spreadsheet (refresh it) or run our onOpen() script first for the menu to display.
To run onOpen() from the editor window, first select and then execute the onOpen function, as shown in this image:
Now, when you go back to your spreadsheet, you will see a new menu on the right side of the Help option, called My Custom Menu. Click on it, and it will open to display a choice to run your Hello, World program.
Running Functions from Buttons in Google Sheets
Another way to execute Google scripts from your spreadsheets is by associating the function with a button in your sheet.
For example, here is an invoice template in a spreadsheet with a RESET button to clear the content:
For more information on how to do this, check out this article: Add a Google Sheets Button to Run Scripts.
Real-world Examples of Google Apps Scripts
Macros in Google Sheets
A great way to start with Google Scripts is by using macros. Macros are small programs in your Google Sheets that you record so you can reuse them (e.g., applying standard formatting to a table). They use Apps Script under the hood, making them a great starting point.
Custom Function Using Google Apps Script
Create a custom function with Apps Script and also use the Maps service. We will create a small custom function that calculates driving distance between two points based on Google Maps driving estimates.
The goal is to be able to have two location names in our spreadsheet and type the new function into a new cell to get the distance, like this:
The expected output should be:
Copy the following code into the Apps Script editor window and save it. The first time, you’ll need to run the script once from the editor window and click “Authorize” for the script to be able to interact with your spreadsheet.
function distanceBetweenPoints(start_point, end_point) {
// get directions
const directions = Maps.newDirectionFinder()
.setOrigin(start_point)
.setDestination(end_point)
.setMode(Maps.DirectionFinder.Mode.DRIVING)
.getDirections();
// get the first route and return the distance
const route = directions.routes[0];
const 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 scripting tutorial.
Suppose I want to save a copy of the data at regular intervals, like this:
In this script, I created a custom menu to execute my main function. The main function, saveData(), copies the top row of my spreadsheet (live data) and pastes it to the next empty row in my current data range with the new timestamp, effectively “saving” a snapshot in time.
The code for this example is as follows:
// custom menu
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Save Data', 'saveData')
.addToUi();
}
// function to save data
function saveData() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const url = sheet.getRange('Sheet1!A1').getValue();
const subscriber_count = sheet.getRange('Sheet1!B1').getValue();
const date = sheet.getRange('Sheet1!C1').getValue();
sheet.appendRow([url, subscriber_count, date]);
}See this article, How to Save Data in Google Sheets with Timestamp Using Apps Script, for a step-by-step guide on creating and running this script.
Google Apps Script Example in Google Docs
Google Apps Script is not limited to just spreadsheets; it can also be used in other Google Workspace tools.
Here’s a quick example in Google Docs, showing a script that inserts a specific symbol where your cursor is:
We do this by using Google Apps Scripts as follows:
- Create a new Google document.
- Open the script editor from the menu: Extensions > Apps Script.
- In the newly opened script tab, delete all the default boilerplate code (the “myFunction” code block).
- Copy the following code:
// code to add the custom menu
function onOpen() {
const ui = DocumentApp.getUi();
ui.createMenu('My Custom Menu')
.addItem('Insert Symbol', 'insertSymbol')
.addToUi();
}
// code to insert the symbol
function insertSymbol() {
// add the symbol at the cursor position
const cursor = DocumentApp.getActiveDocument().getCursor();
cursor.insertText('§§');
}- You can modify the special character in this line:
cursor.insertText('§§');to whatever you want, for example:
cursor.insertText('( ͡° ͜ʖ ͡°)');- Click “Save” and give your script project a name (this does not affect execution, you can call it whatever you want, e.g., “Insert Symbol”).
- Run the script for the first time by clicking: Run > onOpen.
- Google will recognize that the script is not yet authorized and ask if you want to continue. Click “Continue”.
- Since this is the first time running the script, Google Docs will prompt you to authorize the script (I called it “test”, as you can see below):
- Click “Authorize”.
- Now go back to your Google document.
- You’ll have a new menu option, so click on it: My Custom Menu > Insert Symbol.
- Click on “Insert Symbol”, and you should see the symbol inserted where your cursor was.
Using the Logger Class in Google Apps Script
Use the Logger class to display text messages in log files to help with code debugging.
Log files are automatically shown after running the program or by going to the Executions menu in the left sidebar (fourth symbol, below the clock symbol).
The syntax in its most basic form is Logger.log(something here). This logs the value(s) of variable(s) at different stages of your program.
For example, add this script to a code file in your editor window:
function logTimeRightNow() {
const timestamp = new Date();
Logger.log(timestamp);
}Run the script in the editor window, and you should see:
Real-world Examples from My Own Work
I have only scratched the surface of what is possible with G.A.S. to enhance the Google applications experience. Here are a few interesting projects I have worked on:
-
A spreadsheet/web app that consists of a custom web form that sends data to a Google spreadsheet (including uploading images to Drive and displaying thumbnails in the spreadsheet) and then creates a PDF copy of the data in the spreadsheet and automatically sends it via email to users. And with all the data in a Google master spreadsheet, it’s possible to perform data analysis, create real-time data dashboards, and share/collaborate with other users.
-
A dashboard that connects to a Google Analytics account, extracts social media data, checks the status of the website, and sends a daily summary screenshot via email in PDF format.
-
A scoring template that can send scores/comments to students via email and Slack with a single click from Google Sheets. Read more about it in this article: Save Time with This Custom Integration Google Sheets, Slack & Email.
My Own Experience with Google Apps Script
My friend Julian from Measure School interviewed me in May 2017 about my journey with Apps Script and my thoughts on getting started:
Resources for Google Apps Script
To learn more, I have created this list of resources to gather information and inspiration:
Courses
Documentation
Communities
Elsewhere on the Internet
For general JavaScript questions, I recommend this JavaScript tutorial page from W3Schools when you’re getting started. When you’re more comfortable with the basics of JavaScript, I recommend the comprehensive JavaScript documentation from Mozilla.
The only limits to what you can do and where you can go with GAS are your imagination and the patience to learn. I hope you feel inspired to extend your Google spreadsheets and documents, and automate those boring repetitive tasks!
