Google-sheets – How to automatically align incoming form responses in Google Sheets

google sheetsgoogle-formsgoogle-sheets-form-submissions

I like to have each of my form responses aligned in the center of their cells. However when I submit any new information through Google Forms, they are always automatically aligned to the left, and so I am forced to constantly go back and highlight all of these cells and click Format → Align → Center.

Is there someway I can have my form responses automatically aligned in the center after each new submission? Or do I always have to go back and align them myself later?

Best Answer

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.

Related Solutions

Google-sheets – How to get Google Sheets to treat the form-submitted strings as plain text and preserve leading zeros

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)

Google-sheets – An answer in column B on Google Sheets generated from a Google form sends email to collaborators including all data in that row

Here is how it could work. The script does the following:

Get the sheet with form responses
Get the last (nonempty) row there
Pick the entry from column B. To make matching more robust, it trims the entry (removing any whitespace left or right of text), and converts to lower case.
If the result is 'yes', an email is sent. The body is the content of the submitted form (I'm assuming here that it has at most 10 items; otherwise adjust the range).

function emailMe() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');
  var height = sheet.getDataRange().getHeight();
  var lastEntry = sheet.getRange(height, 2).getValue().trim().toLowerCase();
  if (lastEntry == 'yes') {
    var subject = 'Someone said yes';
    var body = sheet.getRange(height, 1, 1, 10).getValues()[0].join('\n');
    GmailApp.sendEmail('youremail@gmail.com', subject, body);
  }
}

To cc: someone else, change the sending line to

GmailApp.sendEmail('youremail@gmail.com', subject, body, {cc: 'first@aol.com,second@yahoo.com'});

Best Answer

Related Solutions

Google-sheets – How to get Google Sheets to treat the form-submitted strings as plain text and preserve leading zeros

Google-sheets – An answer in column B on Google Sheets generated from a Google form sends email to collaborators including all data in that row

Related Topic