Google Script sort troubleshooting (works as 2 functions but not combined)

Question

I'm working with a google apps script to sort data in columns A-C by a number in column D. Column D's number is dependent on the data in Columns A-C (see below).

https://docs.google.com/spreadsheets/d/11QSZPAl3nCs7nzpZIR4FzjfkPUyBBut1pD2K0919UB8/edit?usp=sharing

I'm trying to make it so that there is a blank row up at the top (in A6:C6) so new data can be entered, then upon sorting it will move down into the main list (A7:C299) and a new blank row will appear in A6:C6 again.

I tried doing this by moving the new entry to the bottom of the sheet, then having it sort, but for some reason it doesn't sort the new entry once it has been moved. If I delete the code for the sort, it moves correctly, and if I just have the code to sort (after it has copy-pasted the data from A6:C6), it also works, but when the functions are combined it doesn't. Any ideas or another way to accomplish this? Thanks!

function SortList() {

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange("A7:D299");

// Takes values in first row, moves then to end of list and clears contents in first row, then sorts list

var copyrange = sheet.getRange("A6:C6");
var pasterange = sheet.getRange("A298:C298");
var copyvalues = copyrange.getValues();


pasterange.setValues(copyvalues); 
copyrange.clearContent();

// Sorts by the values in the first column (A)
range.sort({column: 4, ascending: false});

}

Jacque · Accepted Answer · 2018-11-14 08:36:22Z

You can try adding the flush() method in between the two actions.

flush()

Applies all pending Spreadsheet changes.

Spreadsheet operations are sometimes bundled together to improve performance, such as when doing multiple calls to Range.getValue(). However, sometimes you may want to make sure that all pending changes are made right away, for instance to show users data as a script is executing.

function SortList() {

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange("A7:D299");

// Takes values in first row, moves then to end of list and clears contents in first row, then sorts list

var copyrange = sheet.getRange("A6:C6");
var pasterange = sheet.getRange("A298:C298");
var copyvalues = copyrange.getValues();

pasterange.setValues(copyvalues); 
copyrange.clearContent();

//Flush
SpreadsheetApp.flush();

// Sorts by the values in the first column (A)
range.sort({column: 4, ascending: false});

}

Collectives™ on Stack Overflow

Google Script sort troubleshooting (works as 2 functions but not combined)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related