Cannot sort a range with variables (GAS script on Google Sheets)

Question

My full script offers a sidebar to users, for them to choose the four columns they wish to sort and the sort order of each in a Sheets table called "sortTemp". Their responses are stored in a sheet called "tempVar". All is well until I get to the actual sorting. I gather the variables from tempVar, but when it gets to the sort command, it does nothing. No error. No sorting.

I'm putting here the portion of the code that is failing.

function testSort() {
  // Sort tempVar
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getActiveRange().getDataRegion().activate();
  currentCell.activateAsCurrentCell();

  // Map tempVar to sortTempColumns
  var col1 = spreadsheet.getRange('tempVar!B5').getValue();
  var col1dir = spreadsheet.getRange('tempVar!B6').getValue();
  var col2 = spreadsheet.getRange('tempVar!B7').getValue();
  var col2dir = spreadsheet.getRange('tempVar!B8').getValue();
  var col3 = spreadsheet.getRange('tempVar!B10').getValue();
  var col3dir = spreadsheet.getRange('tempVar!B11').getValue();
  var col4 = spreadsheet.getRange('tempVar!B12').getValue();
  var col4dir = spreadsheet.getRange('tempVar!B13').getValue();

  spreadsheet.getActiveRange().sort([{column: col1, ascending: col1dir}, {column: col2, ascending: col2dir}, {column: col3, ascending: col3dir}, {column: col4, ascending: col4dir}]);
  spreadsheet.getRange('A1').activate();
}

Here is a sample of the data being sorted:
[[table with sample data]][1]

Here is the sheet that the variables are being drawn from:
[tempVar][2]

The user's sort choices are stored in column A and reconfigured for use by the sort script in column B.

The column numbers are being calculated like this:
=VLOOKUP(A5,C$1:D$8,2,false)

The booleans are being calculated with this:
=lower(if(A6<="Ascending",true,false))

What I've Tried

I've tested replacing the variables with real numbers and booleans for the ascending part, to determine if I had written the code incorrectly. It works without using variables--breaks with them.
I've tested populating the variables with numbers and booleans as part of the script here, rather than gathering them from another sheet in the file. Setting "col1 = 4" for example. It did nothing.
I've tested populating the variables with numbers and booleans as part of the script here, rather than gathering them from another sheet in the file. Setting "col1 = 4" for example. It did nothing.
I've used ui alerts to ensure the variables are coming over, and they are. I've even done math with the variables in the alert to be sure they're real numbers, and they are.
I've tried putting "Number()" around the script that's populating the variables, to ensure they're converted to numbers. It didn't help.
I've let a macro memorize the sort steps, then plugged in the variables, to ensure I have the code that is supposed to work. No help.

I am new to GAS script and I'm hoping my error will be obvious to someone who is skilled.

What am I doing wrong?

[1]: sortTemp table

|12|1|Thomas   |Hannah |Jr.   |              |Spartanburg District|SC|1790|
|13|1|Tom      |Hannah |M.D.  |Smithville    |Spartanburg County  |SC|1800|
|14|1|J. T.    |Hannah |Junior|              |Renfroe             |SC|1810|
|15|1|Robert   |Hanna  |Jr    |              |Spartanburg District|SC|1820|
|16|1|D. C.    |Baker  |      |              |Tuscaloosa County   |AL|1830|
|17|1|Donna Cox|Baker  |      |Birmingham    |Jefferson           |AL|1860|
|18|1|John     |Maloney|      |Eastern Valley|Taylor County       |FL|1860|

[2]: tempVar table

| Census Worksheet           | GAS Value | Key           |   |
| Copy of Census Worksheet 5 |           | Given Name(s) | 3 |
| 6                          |           | Surname       | 4 |
| 19                         |           | Suffix        | 5 |
| Surname                    | 4         | Community     | 6 |
|                            | true      | County        | 7 |
| Given Name(s)              | 3         | State         | 8 |
|                            | true      | Census Year   | 9 |
|                            |           |               |   |
| State                      | 8         |               |   |
|                            | true      |               |   |
| County                     | 7         |               |   |
|                            | true      |               |   |
| Copy to new sheet          |           |               |

Use code fences to format your code. Test formatting before posting questions. — TheMaster
– TheMaster, Commented Feb 2, 2022 at 9:22
Thank you, TheMaster. I've revised the question with the code fences and tested it. I've hopefully also made the explanation more clear and offered links to a spreadsheet with the essential pieces. I appreciate any help you can offer. — Donna Cox Baker
– Donna Cox Baker, Commented Feb 5, 2022 at 0:30
Your What I've tried is confusing. It would be better if you provided code for the first three. Regardless, on the sort line, try replacing spreadsheet.getActiveRange() with spreadsheet.getSheet('tempVar').getRange('A1notation range') — TheMaster
– TheMaster, Commented Feb 5, 2022 at 8:43
Sidenote: Use tables to show your data structure. If you share spreadsheets, do note that your email address can be accessed by the public. — TheMaster
– TheMaster, Commented Feb 5, 2022 at 8:45
Many thanks, @TheMaster. I've replaced the sheets with tables, and I'm grateful you let me know of the insecurity. I attempted the code you mentioned but got an error that "getSheets" was not a function. But I did a Debug and realized the boolean values were coming through as strings. I've posted the answer. Thanks, again! I am sure I'll be a better "poster" in future, thanks to your patient guidance. — Donna Cox Baker
– Donna Cox Baker, Commented Feb 6, 2022 at 2:37

Donna Cox Baker · Accepted Answer · 2022-02-06 02:31:09Z

The problem turned out to be in the boolean variables. They were coming through as strings and preventing the sort from happening. I have changed the script to convert the "true" and "false" strings to booleans using JSON.parse(), and the script runs fine.

Here is the new code:

function testSort() {
  // Sort temp
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getActiveRange().getDataRegion().activate();
  currentCell.activateAsCurrentCell();

  // Map tempVar to sortTempColumns
  var col1 = spreadsheet.getRange('tempVar!B5').getValue();
  var col1dir = JSON.parse(spreadsheet.getRange('tempVar!B6').getValue());
  var col2 = spreadsheet.getRange('tempVar!B7').getValue();
  var col2dir = JSON.parse(spreadsheet.getRange('tempVar!B8').getValue());
  var col3 = spreadsheet.getRange('tempVar!B10').getValue();
  var col3dir = JSON.parse(spreadsheet.getRange('tempVar!B11').getValue());
  var col4 = spreadsheet.getRange('tempVar!B12').getValue();
  var col4dir = JSON.parse(spreadsheet.getRange('tempVar!B13').getValue());

  spreadsheet.getActiveRange().sort([{column: col1, ascending: col1dir}, {column: col2, ascending: col2dir}, {column: col3, ascending: col3dir}, {column: col4, ascending: col4dir}]);
  spreadsheet.getRange('A1').activate();
}

Collectives™ on Stack Overflow

Cannot sort a range with variables (GAS script on Google Sheets)

What I've Tried

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What I've Tried

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related