0
votes

I use a Google Form for people to request days off. An add-on called Form Approvals is used to send emails to certain people who can approve or deny the request. In the Google Sheet listing the responses, new entries keep going to the bottom.

Is there a way to make new entries from the Google Form to be sorted automatically by the date of the day off in the Google Sheet?

I found this script, but it doesn't work:

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();
  var columnToSortBy = 2;
  var tableRange = sheet.getDataRange();
  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy } );
  }
}

Also, is there a way to specify which sheet tab for the script to run on?

3

3 Answers

0
votes

Try this:

function onEdit(e){
  var sh=e.range.getSheet();
  if(sh.getName()!="Your desired sheet name")return;
  if(e.range.columnStart==2){   
    sh.getDataRange().sort({ column:2});
  }
}

A lot of new programmers try to run these onEdit(e) functions from the script editor. Unfortunately, that doesn't work because the e parameter is expecting to be populated by the event trigger. Without the event object you'll normally get an error like Cannot read property range from undefined because e has not been populated by the event trigger.

I test them by making sure I'm editing the correct sheet and correct range and I use the e.source.toast() function to provide me with feed back sort of like the console.log() does.

If you want to learn more about the event object then try adding a Logger.log(JSON.stringify(e)); to the first line after the function declaration. And then get it to run by editing the appropriate sheet in the appropriate way and go to view log to see the results.

0
votes
  • If your sheet is populated by a form and you want to sort the data every time a new form is submitted - you need to use the onFormSubmit trigger.

  • Google Forms populates the destination spreadsheet chronologically, in order to avoid interference you can use a sync sheet to which the data is transferred on every form submit and which you can sort as desired.

Sample:

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getActiveSheet();
  if(sheet.getName()=="Name of Tab to copy and sort"){
    var lastRow=sheet.getLastRow();
    var lastCol=sheet.getLastColumn();
    var range=sheet.getRange(lastRow,1,1,lastCol);
    var secondarySheetId="XXX";//Paste here the Id of the secondary spreadsheet
    var secondarySheet=SpreadsheetApp.openById(secondarySheetId).getSheetByName("Name of tab of your choice");
    secondarySheet.getRange(secondarySheet.getLastRow()+1,1,1,lastCol).setValues(range.getValues());
    SpreadsheetApp.flush();
    var secondaryRange = secondarySheet.getDataRange();
    var columnToSortBy=2; //adapt to your needs
    secondaryRange.sort( { column : columnToSortBy } );
  }
}

Atach this script to the destination spreadsheet, insert the Id of a secondary spreadsheet (which you have to create first), save the script and bind an installable onFormSubmit trigger to the script through Edit->Current project's triggers->New trigger.

0
votes

I figured it out using https://www.idiotinside.com/2018/06/08/sort-multiple-columns-google-sheets-apps-script/ :)

For my spreadsheet:

  • The name of the sheet I want to sort (not every sheet in the spreadsheet) is called "Requests".
  • Dates are in Column B which is column number 2.
  • Dates are sorted with oldest at the top.
  • Be sure to edit SHEET_NAME, SORT_DATA_RANGE, and SORT_ORDER to your needs.

Use this script with an onEdit trigger:

SHEET_NAME = "Requests";
SORT_DATA_RANGE = "A2:L1500";
SORT_ORDER = [
// {column: 1, ascending: true}, // 1 = column number for Column A, sorting by ascending order 
// {column: 3, ascending: false}, // 3 = column number for Column C, sorting by descending order
{column: 2, ascending: true},
];

function onEdit(e){
  multiSortColumns();
}
function multiSortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);
  range.sort(SORT_ORDER);
  ss.toast('Sorting by Date completed.');
}

Then go to https://script.google.com/ and create a trigger for the above script. Under "Select event type" use "On form submit".

This is working well so far :)