Google Apps Script: Format a Sheet

Format a Sheet

How do we promote STEM (Science, Technology, Engineering and Math) with students if we do not engage in these activities ourselves? Learning a little code is one way we can model the importance of these skills to our students. Also, it is really cool to manipulate Google Apps. Here is a little activity to get you going with coding!

Google Apps Script

Google Apps Script is based on JavaScript, but honestly you need to know very little JavaScript to successfully code in Google Apps Script. Read through these methods on the Google Apps Script website to see some of the cool things you can do to a Google Sheets spreadsheet.

Template

CLICK HERE to create a copy of a spreadsheet to get started.

Script Editor

Go to the Tools menu to choose “Script editor.” I have included the script for an Add-On and a sidebar menu. Locate the format function.
Format function

After defining a variable you can build on the variable. I defined the variable sheet to be the first sheet. You want to add some formatting to this sheet. On line 27 type the variable sheet and type a period. You need to define the range of cells that the formatting will be applied to.

getRange()

Use the method getRange() to define the range of cells you will be working with.

sheet.getRange(‘A1’)

In the parenthesis type the cell or range of cells that define the range. Place the range in quotations.

setValue()

Use the method setValue() to place text in the cell. Type a period after the getRange() and type setValue().

sheet.getRange(‘A1’).setValue(‘First Name’);

Remember to end each line of code with a semi colon.

Define Column Headers

On new lines use the getRange() and setValue() methods to create column headers.

sheet.getRange(‘B1’).setValue(‘Last Name’);

format code

Freeze First Row

If you want to freeze the first row in the spreadsheet add this line of code.

sheet.setFrozenRows(1);

The 1 in the parenthesis freezes one row.

Increase Font Size

There are many methods you can apply to the sheet that will format the sheet. Start with the variable sheet that you defined and pair it with a formatting method.

setFontSize()

The method setFontSize() defines the size of the font. By default the cells in the spreadsheet are font size 10. Start with the sheet variable. Type a period and use the getRange() method. To set the font on all of row 1 you can set the range to be ‘1:1’. If you would prefer to set the font size on only a single cell you can use the range ‘A1’ for example. After setting the range type a period and use the method setFontSize().

sheet.getRange(‘1:1’).setFontSize(14);

The 14 in the parenthesis for setFontSize will create a font size of 14. Remember to end each line of code with a semicolon.

setBackground()

The setBackground() method allows you to fill the background color of a cell or range of cells. When setting formatting you always need to define the range first. You can set the color to be ‘red’ or ‘yellow’ or ‘green.’ You can also use HTML color codes such as ‘#8A2BE2’.

sheet.getRange(‘1:1’).setBackground(‘green’);

setFontColor()

The method setFontColor() allows you to set the color of the font. I like to use ‘white’ when I fill in the background color.

sheet.getRange(‘1:1’).setFontColor(‘white’);

Save

Save the script file.
Click save

Add-On menu

Close the script. In the spreadsheet go to the Add-On menu. Choose the “Format this” Add-On. This should open a sidebar menu. Press the button in the sidebar to run the script. If you typed the script correctly your column headers should appear, the first row should be frozen and your other formatting options should be applied.

Digiprove sealCopyright secured by Digiprove © 2015