[et_pb_section bb_built=”1″ admin_label=”section”][et_pb_row admin_label=”row” background_position=”top_left” background_repeat=”repeat” background_size=”initial”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” border_style=”solid” background_position=”top_left” background_repeat=”repeat” background_size=”initial” _builder_version=”3.0.51″ custom_margin=”-100px|||”]
When working with spreadsheets we can spend quite a long time making them look good and if we have to make the same sheets over and over again, that’s a lot of wasted time. Here we’ll look at how you can format a Google Sheet via Apps Script. The focus of this post is to show you some of the common methods available to get Apps Script to format your sheets automatically. Quite often, these would be part of larger program which get the data from somewhere and create a new sheet with it, then format the way you want. However, it could also be used where you want consistency between different sheets.
Due to their simplicity, I also think it’s a great place to start if you are new to Apps Script. If you are new, please see my earlier posts, which explain how to open the Script Editor, etc.
There are 3 scripts. In the first, we’ll cover the following:
- adding borders
- changing the font and size
- setting the cell wrap status
- setting the horizontal and vertical alignment
- setting the number format
- changing to bold
- changing the cell background colour
- deleting unwanted rows and columns
- adjusting the column to specific widths and to adjust to the text
In the second and third scripts, we’ll quickly look at hiding rows and columns, to set up the sheet with the information you want in it, then be able to reset it by showing all the rows and columns again.
SCRIPT 1
As an example, we going to format a list of classes. So we are going to go from this:
To this (which will take the program about 3 seconds):
Here are all the steps one-by-one:
Let’s go through it step-by-step:
First we set up the function, here I’ve called it “formatSheet1” and we get the active Spreadsheet and the sheet we want, in this case, the sheet is “Sheet1”.
Then I want to set up some variables to store the rows and columns in the sheet.
Line 8: Here I’ve used getDataRange() to get the range that contains all the data in the sheet and stored it in the variable allCells, which we’ll be using throughout the script.
Lines 9 and 10: Here I also want to store the number of rows and columns for later use. To do so, I use the getLastColumn() and getLastRow() methods.
I also want to get a couple of specific ranges that we’ll use later.
Line 13: I get the header row by using getRange() and in the brackets I put (starting row, starting column, number of rows, number of columns). I’ve used the variable numColumns for the number of columns.
Line 14: Plus, I get the comments column, using getRange().
Now let’s start formatting the sheet.
Line 17: First, let’s add borders to all the cells which contain data. We use the setBorder() method and use it with the range stored in allCells, i.e. all the data. In the brackets, there are 6 parts: top, left, bottom, right, vertical, horizontal, which refer to the sides of the cells. We state “true” for each of these parts to add complete borders.
Now let’s change the font to Calibri and set its size to 11 to all the data.
Line 20: To set the font we use the setFontFamily method and add the name of the font in the brackets. Make sure you use a capital letter, otherwise it won’t work properly. To set this to all the data, we add it to the range stored in allCells.
Line 21: To set the font size, we use the setFontSize() and in the brackets state the size.
Line 24: In the comments column I want to set a smaller font size, so, I use setFontSize() with the commentsColumn variable, which contains the comments column range.
Line 27: Now let’s set the text so it wraps if it is long. We use the setWrap() method with the header row. To activate it, we put ‘true’ in the brackets.
Line 30: I also want to do the same, for all the comments column, so I use the same method with the commentsColumn variable.
Now, let’s set the alignment of the text, both horizontally and vertically.
Line 33: First, I want to set the horizontal alignment for all the data to “center”. We use the setHorizontalAlignment() method and state the type in the brackets.
Line 34: Then, I want to set just the horizontal alignment of the comments column to “left”.
Lines 39: We do the same for the vertical alignment, using the setVerticalAlignment() method. First, we set the vertical alignment of all the data to ‘middle’.
Line 41: Then we set the vertical alignment of the header row to ‘bottom’.
Now let’s set the number format for the class number and dates.
Line 45: First we get the class column via getRange().
Line 46: Then we use the setNumberFormat() methods to that range and state the format in the brackets. In this case, I want the class number to be two digits, so that ‘1’ will be ’01’. To do this, we use the format “00”.
Line 49: Then we get the columns which contain dates, which are from column 8 (column H) to column 18 (column R). Note, we start from column 8 and then including this one, include 11 columns to the right.
Line 50: This time I want the date format to be “dd/mm”, for example, “20/03”.
Line 53: Now let’s add bolding to the header row. We use the setFontWeight() method to do so, and in the brackets, add “bold”.
Now I want to colour header cells in different colours. First, I generally find it easier to state the hexidecimal codes beforehand and assign them to easy to recognise names. Plus, if different cells are using the same colour, it’s easy to change the colour by just updating the hexidecimal code for that colour.
In the file, I’ve added a sheet which contains all the hexidecimal codes for all the default colours in the colour palette.
Lines 56 to 63: I set up the variables as below. Remember to include the hash.
Then we are ready to set the different colours to the backgrounds of the cells.
Line 65: I want to set the first 6 cells in the header to be blue, so first I get the range.
Line 66: Then I set the background colour, by using setBackground() with that range and in the brackets refer to the colour in the variables we set up beforehand, here’s it the variable blue.
Lines 68 to 96: I continue in a similar way across the sheet.
One thing I like with Sheets, is the ability to delete unwanted rows and columns. We could hard code this, in other words, state the rows or columns we want to delete, but let’s get the code to work out which rows and columns don’t have data in them, and thus, delete those.
Line 99: First, we need to find out how many columns there are in the sheet. We use the getMaxColumns() to find this out. I’ve stored this in totalColumns.
Line 100: Then we do a bit of maths, we take the number of columns that contain data (stored in the variable numColumns) away from the total number of columns (totalColumns). I’ve stored this in numOfColumnsToDelete.
Line 101: Now we delete the empty columns. We use deleteColumns() and in the brackets there are two parts: the column position we’re going to start at, and how many columns we’re going to delete. The first is the variable numColumns + 1, as we don’t want to delete the last column with data but the one to the right of it. the second, we use the variable numOfColumnsToDelete which we worked out before.
Lines 104 to 106: We follow the same process for the rows, just we use the equivalent row methods and variables.
The final thing I want to do is change the column widths. The first 7 columns I want to adjust automatically according to the data in them.
Line 109-111: Here we can use a simple for loop to resize each of the columns in turn. I start the variable i at 1 (column 1) and it goes up by 1 each round of the loop until it reaches 7 (column 7). More info on loops here.
Line 110: Each round of the loop, it will automatically resize the column based on the widest piece of data in that column. The first time in the loop the i in brackets will be 1, which refers to column 1.
Line 114-116: I use a similar loop to adjust the widths of columns 8 to 18 to ’60’.
Line 115: This time I’m stating the exact width, so we use the setColumnWidth() method. In the brackets we need to state the column number and the width.
Line 119: Here I want to make the width of the comments column ‘120’, so I’ve used the variable numColumns to identify the comments column, as I know it’s the last column, then add the width ‘120’.
SCRIPT 2 – Hiding rows and columns
Sometimes we have a sheet which is full of data but we’re just interested in a part of it and only want the relevant parts showing. In this little script, I just want to show how easy it is, to set up a sheet showing just the rows and columns you want, by hiding the unwanted ones.
As in the first script we get the active spreadsheet and Sheet 1.
I want to only show term 1 information, so I hide columns L to R as they refer to term 2 and 3.
Line 6: First, I need to get the range of columns use getRange(). Note, the range refers to the first row, which is sufficient as it by default means it includes the columns we want.
Line 7: Then we use hideColumn() to hide those columns.
This time I want to hide the rows with the French classes, so I need to hide rows 4 and 10.
Lines 10 and 11: Similar to above, I get the ranges, and here including just column A is fine, it just needs to include the row number.
Lines 12 and 13: Then we use hideRow() to hide the respective rows.
So, potentially at a click of a button, we can hide the rows and columns we want, leaving just the relevant information.
SCRIPT 3 – Unhiding all rows and columns
This is great, but we will want to reset the sheet to display all the rows and columns.
As before we get the active spreadsheet and Sheet 1.
Line 6: First, I want to unhide all the columns, so I will need to know how many columns there are. So, here I’ve used getLastColumn().
Line 7: Then, we need to get the full range of columns, using getRange() and getting from column 1 to the number of columns in numColumns.
Line 8: Then, we use unhideColumn() to unhide all the columns in the range.
Then we do the same for the rows. The only difference is we’re referring to rows not columns, so we use the respective methods.
Here’s the link to make a copy of the sheet and code:
https://docs.google.com/spreadsheets/d/1S21Km_0R1-K9An8zX9NntXBHdQWGrm9VY1ryIHs1Gj8/copy
In the spreadsheet, you will find 4 sheets.
‘Sheet 1’: the sheet that the scripts work on
‘unformatted’: a copy of the unformatted data
‘formatted’: what the data looks like once formatted
‘ColourRefs’: As a bonus, I’ve added the hexadecimal references for all the default colours in the colour palette in sheets. In the script edittor, you’ll find a fourth script, which is what I used to get those reference numbers.
Here are the full scripts:
SCRIPT 2 – HIDING ROWS AND COLUMNS
SCRIPT 3 – UNHIDING ALL ROWS AND COLUMNS
[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row admin_label=”Row” background_position=”top_left” background_repeat=”repeat” background_size=”initial”][et_pb_column type=”4_4″][et_pb_text admin_label=”PostEnd” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid” saved_tabs=”all” global_module=”22296″ background_position=”top_left” background_repeat=”repeat” background_size=”initial”]
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store
- “Google Sheet Functions – A step-by-step guide” – iBooks Store / Kindle Store
Baz Roberts (Google+ / Flipboard / Twitter)
[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]
2 comments
Comments are closed.