CODEWITHSUNDEEP
javascriptgoogle-apigoogle-sheetsgoogle-sheets-api
Greg
Greg

Reputation: 13

Google Spreadsheet Script - Autosort on Table Change

Using Google Apps Script, I have the following code that automatically sorts a table whenever I edit information in the table. 

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var columnToSortBy = 3;
  var tableRange = "A3:AQ11";  
  var range = sheet.getRange(tableRange);
      range.sort( { column : columnToSortBy, ascending: false } );
}

However, my entire table is populated by formulas that reference different sheets, and if I change a value in a different sheet, the tables values on the first sheet change, but it does not get sorted b/c the event did not occur on that sheet.

Is there a way to add functionality to sort the table whenever information in the table changes, rather than a manual edit to the table? I have looked into the onChange() event, but not sure how exactly it could be used.

Upvotes: 1

Views: 2106

Answers (1)

eddyparkinson
eddyparkinson

Reputation: 3700

onChange docs has an example: From : https://developers.google.com/apps-script/reference/script/spreadsheet-trigger-builder

 var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("myFunction")
   .forSpreadsheet(sheet)
   .onChange()
   .create();

how to add triggers: From http://www.labnol.org/internet/google-docs-email-form/20884/ 

/* Send Google Form Data by Email v3.0 */
/* Source: http://labnol.org/?p=20884  */

/**
* @OnlyCurrentDoc
*/

function Initialize() {

    var triggers = ScriptApp.getProjectTriggers();

    for (var i in triggers)
        ScriptApp.deleteTrigger(triggers[i]);

    ScriptApp.newTrigger("SendGoogleForm")
        .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
        .onFormSubmit().create();
}

function SendGoogleForm(e) {

    if (MailApp.getRemainingDailyQuota() < 1) return;

    // You may replace this with another email address
    var email = "[email protected]";

    // Enter your subject for Google Form email notifications
    var subject = "Google Form Submitted";

    var s = SpreadsheetApp.getActiveSheet();
    var columns = s.getRange(1, 1, 1, s.getLastColumn()).getValues()[0];
    var message = "";

    // Only include form fields that are not blank
    for (var keys in columns) {
        var key = columns[keys];
        if (e.namedValues[key] && (e.namedValues[key] !== "")) {
            message += key + ' :: ' + e.namedValues[key] + "\n\n";
        }
    }

    MailApp.sendEmail(email, subject, message);
}

/* For support, contact the develop at www.ctrlq.org */

Go to the Run menu and choose Initialize. The Google Script will now require you to authorize the script – just click the Accept button and you’re done. Or you can use an onOpen function to auto add, I think "FormEmailer" includes and example of how.

Upvotes: 1

Related Questions