Search code examples
sortinggoogle-apps-scriptgoogle-sheetsvlookup

Convert Google Sheets "sort" formula with embedded VLOOKUP into equivalent app script

I have a Google Sheets "sort" formula that I want to convert into an app script equivalent so that I can apply the sorting to a set of rows whenever I click a button on the page. The difficult part is that the sort formula has an embedded VLOOKUP that is used to convert a string column into a number. Here is the formula:

=sort(D2:J,I2:I,TRUE,D2:D,TRUE,VLOOKUP(E2:E,'Frequency'!A:B,2,FALSE),TRUE,H2:H,TRUE,G2:G,TRUE)

The sort creates the output perfectly, but it does not affect the source rows. I would like the script to reorder the source rows exactly as the sort formula does. How can I accomplish this?

Name Task Type Frequency Room
Joe Task 1 Routine Weekly 203a
Jane Task 2 Security Daily 102

I have tried the usual Google searches for relevant results. The closest I could come was this:

`spreadsheet.getActiveRange()
    .sort([
      {column: 6, ascending: true}, 
      {column: 1, ascending: true}, 
      {column: 2, ascending: true}, 
      {column: 5, ascending: true}, 
      {column: 3, ascending: true}]);`

It sorts the columns, but uses only the exact values in the columns. I need my sort script to reference a lookup value first.

The VLOOKUP formula converts the text of "frequency" to a number with a key-value pair.

Frequency Days
Daily 0
Weekly 7
Biweekly 14
Monthly 30

The source rows have the word in the cell, but we need to sort by the numeric value rather than alphabetically. The data rows are all text. The sort formula uses column I, then D, then the numeric value from the VLOOPUP, then H, then finally column G. The VLOOKP sorts to daily, weekly, biweekly, then monthly instead of biweekly, daily, monthly, then weekly alphabetically. All other columns are just sorted alphabetically.

How would the formula be done using apps script?

Thanks for being awesome!

Solution

  • You have a SORT problem. Your data includes a "Frequency" text field which does not sort reliably. So you have a table that matches each text values with a number of "Days" value.

    Data on TabB is filtered and copied from raw data on TabA. You want to sort both TabA and TabB using the same sort logic.

    • TabB can be sorted with an internal SORT function because the formula can be written to include VLOOKUP (finding the "days" value for the respective "Frequency")
    • BUT Tab A cannot include the "Days" value, so sorting TabA data must be by script.

    The following script takes the TabA data as an array:

    • uses indexOf to add an element to the array for the "Days" value
    • sorts the array according to a given column sequence (editable by the user)
    • uses `pop to delete the "Days" element from the sorted array
    • uses setValues to update the TabA data range with the sorted array.
    function sortData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  // TabA contains raw data
  var tabA = ss.getSheetByName("TabA")
  var tabALR = tabA.getLastRow()
  
  // assume headers in row 1
  // assume 5 columns of data
  // assume "Frequency" is in Column D (Column 4)
  var tabARange = tabA.getRange(2,1,tabALR-1,5)
  // Logger.log("DEBUG: the data range = "+tabARange.getA1Notation())
  var tabAValues = tabARange.getValues()
  // Logger.log(tabAValues) // DEBUG

  // Sheet="Frequency" contains matching integer value for text values
  var freq = ss.getSheetByName("Frequency")
  // get the last row of Frequence data; assume that this sheet contains other data
  var Avals = freq.getRange("A1:A").getValues()
  var ALastRow = Avals.filter(String).length
  // note assumes header in row 1
  var freqValues = freq.getRange(2,1,ALastRow-1,2).getValues()
  // Logger.log(freqValues) // DEBUG

  // use indexOf to find the matching string value; the integer value will be in the adjacent column
  // assume the string "Frequency" is in Column D (zero-based= Column 3) of the data sheet
  // push the "Days" integer value onto the data array - this will be used for sorting
  for (var i=0;i<tabAValues.length;i++){
    var freqIdx = freqValues.map(r => r[0]).indexOf(tabAValues[i][3])
    var freqValue = freqValues[freqIdx][1]
    // Logger.log("DEBUG: freqIdx = "+freqIdx+", Frequency: "+tabAValues[i][3]+", number value = "+freqValues[freqIdx][1])
    // push the integer value onto the data array - this will be used for sorting
    tabAValues[i].push(freqValue)
  }

  // sort the array
  var sortSeq = [6,1,2,5,3]
  for (var s = 0;s<sortSeq.length;s++){}
    tabAValues.sort(function(x,y){
      var xp = x[s];
      var yp = y[s];
      return xp == yp ? 0 : xp < yp ? -1 : 1;
    });
  

 // make a copy of the updated data array 
  var sortArray = new Array
  sortArray = tabAValues


  // delete the "Days" value from the array
  // returns the array 
  for (p=0;p<tabAValues.length;p++){
    sortArray[p].pop()
  }

  // update the data on TabA for the sorted values
  tabARange.setValues(sortArray)

}

    BEFORE

    BEFORE

    AFTER

    after