CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Laurie Fletcher
Laurie Fletcher

Reputation: 3

Google sheets script works on some tabs and not others

I'm trying to automate a sheet to sort rows based on date, and transfer rows when a checkbox is selected. I got the transfer part figured out, but the sort function only works on the tabs I'm getting rid of, and not the tabs I'm keeping, and I don't understand why.

Here's a couple sort functions that both work, but not on the tabs I need them to work on:

function onEdit(event) {
  // assumes source data in sheet named Active
  // target sheet of move to named Completed
  // getColumn with check-boxes is currently set to colu 8 or H
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  //sort Active by oldest date at the top, using column D or 4
  
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();
  var columnToSortBy = 4;
  var tableRange = "A2:T99"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );
  }
  
}

And:

function myFunction() 
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Active");
  var rows = sheet.getLastRow();
  var columns = sheet.getLastColumn();

  var sortedValues = sheet.getRange(2, 1, rows-1, columns).getValues().sort();

  sheet.getRange(2, 1, rows-1, columns).setValues(sortedValues);
}

The sort works on the tabs with names on them, but not the tabs called Active and Completed. With the automation, I'm getting rid of the named tabs and we'll just have Active and Complete.

I've removed the transfer upon complete function as it worked just fine, and I'm just focusing on the sort, since it seems to be the issue.

Any suggestions for why the two tabs aren't sorting? Here's a copy of the spreadsheet I'm working on: https://docs.google.com/spreadsheets/d/17VeCzfqAclxUN-kVIrWZSII-7clsNfttmVVU8s0JhFs/edit?usp=sharing

EDIT: solved by Cooper below. Final version of code that worked for my needs:

function onEdit(event) {
  var sh=SpreadsheetApp.getActive().getSheetByName("Active");
  sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).sort({column:4,ascending:true});
}

Upvotes: 0

Views: 107

Answers (1)

Cooper
Cooper

Reputation: 64100

Try this for sorting by column 4:

function myFunction() {
  var sh=SpreadsheetApp.getActive().getSheetByName("Active");
  sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).sort({column:4,ascending;true});
}

Sorting

Upvotes: 1

Related Questions