Quickly format multiple Google Sheets with Apps Script

In this post, we’re going to see how we can very quickly format multiple Google Sheets with a little help from Apps Script.

The idea of this post, came from the Department of Education in Hawaii, where they have about 300 schools and a guy who works there told me he had to format about 300 Google Sheets the same way, and wondered how this could be done with Apps Script.

Easy I said! And I thought it was a typical example of how a little knowledge of Apps Script can help you speed up your work and allow you to spend less time doing boring, repetitive work, and focus more on the data itself.

Let’s see how it’s done!

Overview of the files

We have a master file, where we store the list of spreadsheet URLs and where we will run the script from. Each row is one of the spreadsheets, and it contains some information about the district, school, and grade, and the URL of the spreadsheet.

Each of the spreadsheets we’re going to format has two sheets. One called “Students Missing Reg Form” and the other “Students Attending Program”.

The sheets have the same 5 columns, so the good thing is that we’re working the same layout every time.

But as we can see the formatting on each of the sheets is different, and that is one of the things we want to standardize across all the sheets.

We are also going to freeze the first row, so that the header is always visible. We’re going to remove all the empty columns and leave just 300 rows.

Finally, we’re going to protect the sheets, so that only certain people can edit them.

The Code

Now, let’s go through the code step-by-step.

The script has five functions. The main one is called configureSheets and will call the other four. Why have I set it up like that? Well, basically it’s good practice to group parts of your code by the function it carries out, which makes it easier to read and potentially update in the future. So, the other four we have are called removeRowsColumns, formatHeaders, formatDataSet, protectSheet. Hopefully, from the names you have an idea what each one does.

OK, let’s start at the beginning.

configureSheets

1. function configureSheets() {
2.
3.  //Get data on sheet and who will be able to edit the sheets
4.  const ss = SpreadsheetApp.getActiveSpreadsheet();
5.  const sh = ss.getSheetByName("SPREADSHEETS");
6.  const data = sh.getDataRange().getValues();
7.  data.shift();
8.  const canEdit = ['baz@bazroberts.com', 'bazrobertsbooks@gmail.com'];

L1: Set up the function called configureSheets.

L4: Get the active spreadsheet, which is the Master spreadsheet you’re running the script from.

L5: Get the sheet called SPREADSHEETS, which has the list of the spreadsheet URLs.

L6: Get all the data on that sheet and store it in an array called data. Even though, we’re really only interested in just the spreadsheet URLs, I usually grab all the data, then later filter what I need. The important thing is not to make multiple calls for different data on the sheet, as this slows the script.

L7: Next, I want to get rid of the header row from the data array.

L8: Next, I’m going to define who will be able to edit the sheets. We can do that by storing their email addresses in an array.

Then we get the spreadsheet URLs from the data array.

10.  //Make array of spreadsheet URLs
11.  const ssURLs = data.map((row) => {
12.    let ssURL = row[3];
13.    return ssURL;
14.  }).filter(n => n);

L11: Now, we’re going to loop thru each ‘row’ of the data array with the map method to get the spreadsheet URLs and we’ll store them in the ssURLs variable.

L12: Around each loop, we’ll get the content of the cell in the fourth column (column D) and store it in the ssURL variable. I’m only storing it here, just so it’s clear what row[3] is.

L13: Return that URL and that will be stored in the ssURLs array.

L14: As we may have blank cells on our sheet, we need to filter those out, so we don’t try to open a spreadsheet later on without a URL. We can simply do that by filtering out blank elements in the ssURLs array using the filter method and the short cut “n => n”.

Note, this filters out not just blanks, but also other falsey elements, like zero, null, undefined, but in this case it’s a simple way for what we need.

Now, we have an array of the spreadsheet URLs, we can now loop thru them and get the sheets we want to format.

16.  //Loop thru spreadsheets
17.  ssURLs.forEach((ssURL) => {
18.    let currentSS = SpreadsheetApp.openByUrl(ssURL);
19.    let shToFormat1 = currentSS.getSheetByName("Students Attending Program");
20.    let shToFormat2 = currentSS.getSheetByName("Students Missing Reg Form");
21.    const sheetsToFormat = [shToFormat1, shToFormat2];

L17: To loop thru the array, we can use the forEach method. This will loop thru all the elements (URLs) in the array.

L18: Open the spreadsheet using the current URL.

L19-20: Let’s then get the two sheets in the current spreadsheet by their names.

L21: Finally, let’s store those two sheets in an array, so we can loop thru that to format each of them.

The final part of this function is to loop thru the sheets and to call the five functions which will format and protect the sheets.

23.    sheetsToFormat.forEach((shToFormat) => {
24.      removeRowsColumns(shToFormat);
25.      formatHeaders(shToFormat);
26.      formatDataSet(shToFormat);
27.      protectSheet(shToFormat, canEdit);
28.    });
29.  });
30.  ss.toast("Finished");
31. }

L23: Again, we use forEach to loop thru the two sheets.

L24: Call the removeRowsColumns function which will remove the rows and columns we don’t want. As we will need the current sheet we want to format, we pass the shToFormat variable as a parameter.

We’ll look at that function further down. Once that function finishes, it will come back and continue to the next line.

L25: Call the formatHeaders function to format the header row on each row.

L26: Call the formatDataSet function to format the data set on each of the sheets.

L27: Finally, call the protectSheet function to protect the sheets by only allowing certain people edit the sheets.

L28-29: Close the two forEach loops.

L30: I always like to let the user know that the process has finished, and the simplest way is to display a toast message.

L31: Close the configureSheets function.

removeRowsColumns

In this function we’re going to remove any rows after row 300, and delete any columns after column E.

35. function removeRowsColumns(shToFormat) {
36.
37.  //Remove rows from row 301 onwards
38.  let maxRows = shToFormat.getMaxRows();
39.  if (maxRows > 300) {
40.    shToFormat.deleteRows(300, maxRows - 300);
41.  }
42.
43.  //Remove columns from column F
44.  let maxColumns = shToFormat.getMaxColumns();
45.    if (maxColumns > 5) {
46.    shToFormat.deleteColumns(6, maxColumns - 5);
47.  }
48. }

L35: Set up the removeRowsColumns function and pass in the current sheet.

L38: First, let’s find out how many rows there are on the current sheet.

L39: We want to delete any rows that there may be after row 300, so first let’s check to see if there are more than 300 rows, because if there aren’t we will get an error if we try to delete rows that don’t exist. So, we simply check to see if there are more than 300 rows.

L40: If there are, then we delete the rows number of rows there are after row 300.

L41: Close the if statement.

L44: Next, we want to delete any columns that are to the right of the fifth column. First, we find out how many columns there are on our sheet.

L45: Then we check to see that there are some columns to the right of column E, by seeing if there are more than 5 columns.

L46: If there are, we delete those columns, by getting the sixth column and deleting the number of columns there are.

L47-48: Close the if statement and the function.

formatHeaders

Next, let’s freeze the first rows and format the header rows.

51. function formatHeaders(shToFormat) {
52.  shToFormat.setFrozenRows(1);
53.
54.  const headerRow = shToFormat.getRange(1, 1, 1, shToFormat.getLastColumn());
55.  headerRow.clearFormat()
56.    .setFontWeight("bold")
57.    .setFontSize(14)
58.    .setFontFamily("Calibri")
59.    .setFontColor("yellow")
60.    .setBackground("#0000ff");
61.}

L51: Set up the formatHeaders function and pass in the current sheet.

L52: Freeze the first row.

L54: Get the first row on the sheet.

L55: First, I usually clear any existing formatting. Note, here I’ve not ended the line with a semi-colon, as we’re going to chain the following formatting, i.e. connect them all to the header row range.

L56: Bold the text.

L57: Set the font size to 14.

L58: Set the font to Calibri.

L59: Set the font colour to yellow.

L60: Set the background to blue. Note, here I’m using a hex colour code to set the specific shade of blue I want.

L61: Close the function.

formatDataSet

Next, the formatDataSet function will format the data underneath the header row. We’ll also auto resize the column widths to match the data.

64. function formatDataSet(shToFormat) {
65.  const dataSetRange = shToFormat.getRange(2, 1, shToFormat.getMaxRows() - 1, shToFormat.getLastColumn());
66.  dataSetRange.clearFormat()
67.    .setFontSize(14)
68.    .setFontFamily("Calibri")
69.    .setFontColor("black")
70.    .setBackground("#ffffff");
71.  shToFormat.autoResizeColumns(1, 5);
72. }

L64: Set up the formatDataSet function and again pass in the current sheet.

L65: Get the data range from row 2 to the bottom of the sheet and to the last column.

L66-70: Similar to what we saw above, we clear the current formatting, set the font size, font, font colour, and background colour.

L71: Here, I also want to resize the column widths to match the data they contain.

protectSheet

The last part is to protect the sheets and only give certain people the ability to edit them. This we set up back on line 8.

75. function protectSheet(shToFormat, canEdit) {
76.  const protectedSheet = shToFormat.protect().setDescription('Protected');
77.  const editorsToRemove = protectedSheet.getEditors();
78.  protectedSheet.removeEditors(editorsToRemove);
79.  protectedSheet.addEditors(canEdit);
80. }

L75: Set up the protectSheet function and pass in both the current sheet and the canEdit array.

L76: Protect the sheet and give it the protection a name. The last part is optional, but useful when adding different protections.

L77: Get the editors on the sheet.

L78: Remove all the current editors.

L79: Add the editors you want to have edit access.

L80: Close the function.

Menu to run the code

The final part is to add a menu to run the script from.

1. function onOpen() {
2.  SpreadsheetApp.getUi().createMenu('MAGIC') 
3.    .addItem('Configure all sheets', 'configureSheets')
4.    .addToUi();
5. }

L1: Set up the function. I usually add these menu script in a separate script file, but you could just add it on the end of the main script.

L2: Create a menu called MAGIC.

L3: Add the item which will be clicked to run the code. Give it a name and the name of the function it will run.

L4: Add it to the UI.

L5: Close the function.

Run the script

Run the script from the menu. You may need to refresh the master spreadsheet, so the menu appears. You will need to authorize the script.

As you can see, it’s formatted the data the way we want it, frozen the top row, and deleted the unwanted rows and columns.

This is an example of how a little bit of Apps Script can help you by doing the boring, repetitive work for you, consistently and quickly.

File containing the code: FILE

There are links to the example data sheets in the master file.

A big thank you to Vladimir Sosa for the original question about how to do this! I hope this helped!

a