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

user_9482570 0
Viewed 810

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});

}
google-apps-script google-sheets sorting
1 Answers

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});

}
user_105499580
Related