AutoFormat text and sort answers in google forms-CodePudding

I have a form that is filled in and i would like for the answers coming in to be formatted as follow:

Text to be Wrapped, Centred Vertically as well as horizontally within the cell. As well as for the answers to be sorted from the youngest to the oldest based on column A

I have managed to pull some scripts but cannot seem to get them working in conjunction. Please help !!

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var s= ss.getActiveSheet()
  var lr = s.getLastRow()
  var r= s.getRange(1, 1, lr,4)
  var set=r.setHorizontalAlignment("center")
  var range = e.range;
  range.setWrap(true);
}  
function sortResponses() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
  sheet.sort(1, false);
}

and second

function Alignment() {
  const spreadsheet = SpreadsheetApp.getActive();
  const sheets = spreadsheet.getSheets();
  sheets.forEach(sheet=>{
  let rg=sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
  rg.setHorizontalAlignment('center')
  .setVerticalAlignment('middle');
  });
};

Please help

CodePudding user response：

Here is a little script snippet I use for doing a similar thing.. Its not my best code but does the job.

The following function is set to run with a On Form Submit trigger,

function sort_and_format_FormResponses() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');
  
  
  //Sort Responses by Date Stamp
  var range = sheet.getRange("A2:G");
  range.sort({column: 1, ascending: false})
  range.setWrap(true);
  
  //Column Specific Formatting...
  //Format column D to Plain text
  range = sheet.getRange("D:D");
  range.setNumberFormat('@STRING@');
  range.setHorizontalAlignment('left');
}

CodePudding user response：

function sort_and_format_FormResponses() {
  var sh = SpreadsheetApp.getActive().getSheetByName('Form Responses 1');
  var rg = sh.getRange("A2:G"   sh.getLastRow());//Don't use unterminated ranges like A2:G in apps script because in generates a lot of nulls for lastrow to maxrows
  rg.sort({column: 1, ascending: false})
  rg.setWrap(true);
  rg = sh.getRange("D1:D"   sh.getLastRow());
  rg.setNumberFormat('@STRING@');
  rg.setHorizontalAlignment('left');
}