How can I retain cell formatting when data is inputted via a Form?

Question

I have a fuel calculation sheet, and I've created a form that I input the amount I've just filled up (as well as total price and my current mileage) using a Form on my phone while at the pump.

The issue is that I've formatted the various columns I have in specific ways (A certain number of decimal points, central alignment, wether it is Currency or Date etc), but when ever this new data is added, the formatting defaults back to the original settings and I have to reformat this new data each time.

Is there a way to make this newly submitted data conform to my already formatted columns? It looks like the Form is creating a new row with each submission, rather than filling the pre-made cells.

Answer 1

Yes, Forms work by inserting a new row with form data on each submission. I can think of two ways to get around this.

Another sheet

Create another sheet, say "Formatted Form Data", with a single command in cell A1,

 =arrayformula('Form Responses 1'!A:D)

(The range A:D depends on how many columns your form has.) Then you can format the columns of that sheet, and the formatting will apply to new data as well.

A script

Another approach is running a script triggered by form submission, which would copy the format of, say, row 2 to the newly added row(s). The method copyFormatToRange is useful here.

function copyFormat() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  sheet.getRange(2, 1, 1, lastColumn).copyFormatToRange(sheet, 1, lastColumn, 3, lastRow);  
}