In this Google Apps Script tutorial for beginners you’ll learn how to automate the formatting of a Google Sheets document. With just a couple lines of code, you can automate almost anything within Google’s many apps. You’re wondering what Google Apps Script is? The short answer to that question is: Google Apps Script is a scripting language based on JavaScript, that’s built in to Google apps. Have a look a this blog post for an in depth explanation.
This tutorial also comes in video form, where basic programming concepts like variables, objects, etc. are explained. Go ahead and check it out.
Imagine you receive a report every week and you find yourself formatting it every single time. Wouldn’t it be cool to simply click on a button and have the report format itself?
That’s exactly what we’ll be doing in this tutorial! Well create a straight forward automation script to format a Google Sheets file.
Look at most of the Google Apps and you’ll find your way to the script editor. In our tutorial we’re automating the formatting of a Google Sheets document. That’s why we access the script editor, where we will be writing our automation script, through Tools > Script editor in Google Sheets.
The script editor opens in a new browser tab and it contains a placeholder function by the name myFunction. You can delete this function and start writing your own.
We’ll start off by naming our function, that is the code that will run for the formatting, formatReport.
Then we will do the following:
function formatReport() {
// 1. Access our active spreadsheet and store it in a variable
let sheet = SpreadsheetApp.getActiveSpreadsheet();
// 2. & 3. Identify our header row & table and store them in two seperate variables
let headers = sheet.getRange('A1:F1');
let table = sheet.getDataRange();
} Now it’s time to define the formatting of our header. We’re changing the font weight to bold and the color to white. The last change is the background color which will be a beautiful saperis purple.
// 4. Change the format of our header row: Make the header text bold, white and the cell background purple.
headers.setFontWeight("bold");
headers.setFontColor("white");
headers.setBackground("#52489C"); Now we shift our attention to the entire table. We change the font to Roboto and align the cell content to the center.
The last line, table.setBorder… looks quite a bit complicated. To put it short we’re setting all four sides of every cell border to solid purple.
// 5. Change the format of our table: Change the font to 'Roboto', center the cell contents and add a border.
table.setFontFamily("Roboto");
table.setHorizontalAlignment("center");
table.setBorder(true, true, true, true, false, true, "#52489C", SpreadsheetApp.BorderStyle.SOLID); The last step in this function is to create a filter. We do so by calling the createFilter method on our table object.
// 6. Create a filter for our header row
table.createFilter(); We probably don’t want to have to start up the script editor every time we want to run our code. That would be a bit tedious.
Instead we want a menu to appear in Google Sheets so we can easily run our automation script.
We create a separate function named onOpen. This is the default name provided by Google for whenever you want to add a custom menu to a Google app.
function onOpen() {
let ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Formatting').addItem("Format Report", "formatReport").addToUi();
} Now we have everything we need for our automation script. Here how the script should look once you’re done putting it together.
function formatReport() {
// 1. Access our active spreadsheet and store it in a variable
let sheet = SpreadsheetApp.getActiveSpreadsheet();
// 2. & 3. Identify our header row & table and store them in two seperate variables
let headers = sheet.getRange('A1:F1');
let table = sheet.getDataRange();
// 4. Change the format of our header row: Make the header text bold, white and the cell background purple.
headers.setFontWeight("bold");
headers.setFontColor("white");
headers.setBackground("#52489C");
// 5. Change the format of our table: Change the font to 'Roboto', center the cell contents and add a border.
table.setFontFamily("Roboto");
table.setHorizontalAlignment("center");
table.setBorder(true, true, true, true, false, true, "#52489C", SpreadsheetApp.BorderStyle.SOLID);
// 6. Create a filter for our header row
table.createFilter();
}
function onOpen() {
let ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Formatting').addItem("Format Report", "formatReport").addToUi();
} Before you leave the script editor be sure to save your automation script by clicking on the Save icon and giving it a name. In our tutorial we named our script Report Formatting.
To actually run our automation script so that it formats our report, we head back to our Google Sheets document.
Reload the document in your browser and after about 15 seconds our Custom Formatting appears in the menu list. That menu was created by the second function in our script we created.
You will be asked to grant your script the rights to access your account. Confirm this. Then click again on Custom Formatting > Format Report to run it.
After about 5 seconds your report should look something like this.
That wasn’t that difficult, was it? If you’re new to automation or coding lots of things in the code above might seam strange to you. What are variables? What is a function? What are methods? And many more questions…
I encourage you to check out our Google Apps Script playlist on YouTube to learn more about this useful coding language.