CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Softserve
Softserve

Reputation: 29

How to Sort Sheets Cells by values applied in GAS?

Column A of my Sheet has status values [Yes,Pending,No,Withdrawn] in the form of a dropdown. I would like to sort by these values but the order they will be sorted in is not alphabetical.

I currently use a helper column with IFS formulas in the sheet that applies a numeric value to each status and sorts them in that order. My assumption is that I can use a script to accomplish this without needing a helper column in my sheet.

From other somewhat similar questions I've gathered that this may use a compare function but my knowledge of GAS is fairly introductory.

Upvotes: 1

Views: 260

Answers (1)

Dustin Michels
Dustin Michels

Reputation: 3226

I think using the helper column is definitely the simplest option. You can "hide" the column so it isn't in the way. (Right click -> Hide column).

But you can definitely accomplish this with a script, using a compare function! Here is an example implementation. It's a little overly verbose, in hopes of being explanatory and adaptable to your exact use case.

Code.gs

// get active spreadsheet
var ss = SpreadsheetApp.getActive();

// define mapping of status to custom values
var mapping = {
  Yes: 1,
  No: 2,
  Pending: 3,
  Withdrawn: 4
};

// define range of values to sort & which one is "status"
var sortRange = "A2:B20";
var statusCol = 0;

/**
 * Sort defined range by status, using defined mapping
 * See: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function sortData() {
  // select sheet
  var sheet = ss.getSheets()[0];

  // select range
  var range = sheet.getRange(sortRange);

  // get values (array of arrays)
  var data = range.getValues();
  Logger.log("\ndata pre-sort: %s\n\n", data);

  // sort using custom compare function
  data.sort(sortFcn_);
  Logger.log("\ndata post-sort: %s\n\n", data);

  // write values back to spreadsheet
  range.setValues(data);
}

/**
 * Custom compare function used by sortRange
 * See: https://www.w3schools.com/jsref/jsref_sort.asp
 */
function sortFcn_(rowA, rowB) {
  // get "status" from row (array lookup by integer)
  var aStatus = rowA[statusCol];
  var bStatus = rowB[statusCol];

  // convert status msg to value (object lookup by key)
  var aValue = mapping[aStatus];
  var bValue = mapping[bStatus];

  // sort in ascending order
  return aValue - bValue;
}

Now you just need to figure out how to call "sortData" at the right times. For a few options, see:

Upvotes: 1

Related Questions