Automatically format rows for Google Form responses

Here, we’re going to see how we can automatically format rows on a Google Sheet when a Google Form response is submitted.

When the form responses come in normally, they have the default format below.

But I want them to be formatted as below.

So, changing the cell backgrounds to red, only showing the day and month in the timestamp and it in grey, putting the name in bold, formatting the date to YYYY-MM-DD, and showing the time in hours and minutes.

We have two sheets:

• DATA – Where the form responses come in

• FORMAT – Where there is a row formatted the way we want.

We could do all this in the code, but what we’re going to see is, a quick way to copy that formatting from the FORMAT sheet and apply it to the DATA sheet.

This also has the advantage that if someone wants to change the formatting later on, they don’t have to change any code, they just change the formatting on the FORMAT sheet.

Let’s look at the code to do this.

Code

In the script editor we type the follow code.

1. function formatLatestRow() {
2.  const ss = SpreadsheetApp.getActiveSpreadsheet();
3.  const sh = ss.getActiveSheet();
4.  const shFormat = ss.getSheetByName("FORMAT");
5.

L1: the function we will call is called formatLatestRow.

L2: Get the active spreadsheet.

L3: Get the active sheet, which will be the one the form response comes into. In this this example, it’s the DATA sheet.

L4: Get the sheet where the formatted master is on. In this case, on the FORMAT sheet.

Now, we get the row with the formatting we want and the row we want to add the formatting to.

6.  //Get source formatted row and target row to be formatted
7.  const sourceRowRange = shFormat.getRange(2, 1, 1, shFormat.getLastColumn());
8.  const targetRowRange = sh.getRange(sh.getLastRow(), 1, 1, sh.getLastColumn());
9.  

L7: Get the FORMAT sheet and the row which has the formatting. Here, I’m getting the entire row, i.e. all the columns, just in case in the future columns are added or removed. We store that range in the variable sourceRowRange.

L8: Get the latest row on the form submissions sheet, DATA. Again, get the whole row. Store that range in targetRowRange.

The final part is to apply that formatting to the row.

10.  //Copy format from source row to target row
11.  sourceRowRange.copyTo(targetRowRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
12. }

L11: Get the source range then copy it to the target row range. You then paste the format to it. The false refers to whether you want the range transposed or not, but as we are copying a horizontal row to a horizontal row, we state false to not transpose it.

L12: Close the function.

And that’s it! You can see we haven’t had to code all the formatting.

The final step is to turn the “On form submit” trigger on, so that it runs the script automatically when the form response is submitted.

Automatically format rows

To set up the trigger, from the script editor, click on the clock icon.

Click “Add Trigger”.

Set up the function you want to run and select the event type to “On form submit” and click “Save”.

You will need to authorize the script – see my post on this. The trigger is now ready to automatically run your script.

When another form response is submitted, as you can see below it automatically formats the row.

Note, it doesn’t copy things like the column widths. Plus, you can only use this within the same file, so you can’t copy the formatting from one file on to another.

It’s an easy way to format the form responses and can keep your sheets looking the way you want them.

The idea for this post came from a great post by Amit Agarwal, where he formats the latest row by copying the previous row’s format.

Example file with code

Here you can make a copy of the file that contains the script and the attached form.

a