Google-sheets – Trailing zeros in Google Spreadsheet automatically removed from form submission

It looks like this issue is due to a bug in the new Google Sheets, but you can use this simple script as a workaround.

• In your sheet, go to Tools > Script Editor. Erase the existing code in Code.gs and add this instead:

  function setCellAlign(range, alignment) {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheets()[0];
     var cell = sheet.getRange(range);
     cell.setHorizontalAlignment(alignment);
   }
  function onFormSubmit() {
     setCellAlign("A1:F500", "center");
  }
  

• In the second-to-last-line, change F500 to the expected bottom-right cell of your sheet. For example, if your rightmost column is G and you want to hold 1000 responses in your sheet, change the value to G1000 so the second-to-last line is setCellAlign("A1:G1000", "center");

• Save the script project as "AutoAlign"

• Within the Script Editor, go to Resources > Current project's triggers. Add a new trigger for onFormSubmit, From the spreadsheet, On form submit, and press "Save."

Your responses should now align automatically to the center. For whatever reason, this script fails to work 1% of the time, but if that happens the next form submission will fix the previous row.

Part of my answer would be: this is a known bug (newly received form submissions do not respect the format of the columns that they are landing in, as they did in the old version of sheets).

Depending on what you are doing with the data afterwards, another option would be to reproduce the form submissions on another sheet, with the correct format. For example, if column E contained zip codes that should be 5 digits long and include leading zeros (if required), then you could use:

=ArrayFormula(TEXT('Form Responses'!E:E,"00000"))

on another sheet.

You would need to bring over all your other columns as well, though. Without getting too complicated, this can be reasonably easily achieved in the new version of Sheets, and format the column(s) as desired, with (eg):

=QUERY('Form Responses'!A:G,"select * format E '00000'",1)