I am running this code and getting same error after trying different data values. please help me resolve this error.
function expiredsubsalt() // Function to Move expired members from Active Memberships to Expired Membership Sheet
// put the variable to pick the last row and last column and put it into expired sheet
{
var schedSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Active Memberships");
var archive=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expired Memberships");
var clno = schedSheet.getLastColumn();
for(r=schedSheet.getLastRow();r>1; r--) //For loop started for all rows
{
if (schedSheet.getRange(r,9).getValue()== "Expired")
{
archive.getRange(2,1,archive.getLastRow(),archive.getLastColumn());
archive.appendRow(schedSheet.getRange(r,1,1,clno).getValues()[0]);
schedSheet.deleteRow(r);
}
if (schedSheet.getRange(r,2).getValue()==""){schedSheet.deleteRow(r)}
}
Logger.log(r)
Logger.log(clno)
// archive.getRange('A2:I').removeDuplicates();
// archive.getRange().sort([{column: 1, ascending: false}, {column: 2, ascending: true}]);
archive.getRange(r,clno).removeDuplicates();
archive.getRange(r,clno).sort([{column: 1, ascending: false}, {column: 2, ascending: true}]);
}
line no 824 is - archive.getRange(r,clno).sort([{column: 1, ascending: false}, {column: 2, ascending: true}]);
In these two lines:
archive.getRange(r,clno).removeDuplicates();
archive.getRange(r,clno).sort([{column: 1, ascending: false}, {column: 2, ascending: true}]);
the r variable is being used is the same that was previously used to loop through the first file, and it has a value of 2 regardless of your spreadsheets. Moreover, as it has been said,your code is trying to sort only one cell.
As you probably want to sort and remove duplicates the whole archive spreadsheet, I would suggest changing the ranges to something like:
.getRange(1,1,archive.getLastRow(), archive.getLastColumn())
You can read more about how Google Sheets deals with ranges here and how you can provide different inputs here.