Selecting range by finding start/end row number based on value in Sheets Script?

Question

tl;dr is there an easy method to select a range by finding starting and ending row numbers of the range by looking for a value in a column?

Not very familiar with java but i know this can be done very easy in other languages but i can't find anything to help me out at the moment (e.g. which() in R - finding row number of "Personal" and adding 2 to the row number to get the right one for the starting row and finding "Work" and subtracting one to get the ending row for the first section of the example provided)

I'm currently having a script automatically sorting smaller sections/tables of my worksheet based on whether or not a value in the section is checked(yes/no) (unchecked ones at the top). Right now i have manually specified the ranges in the script, but it's a hassle if i remove or add rows section (all the ranges below break). The columns do not change, i only need to find rows. So i was wondering if there's an easy method to choose the range automatically which can take into account rows being added or removed. Below is an example of a similar setup (i have a lot more sections in the actual one).

I want to select range A3:B5 for personal and A8:B10 for work. This is how it's currently coded (incl. the sorting), where i specify it manually (note that i have a Yes/no checkbox in C1 to toggle sorting based on status or due date): date):

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("To do list")
var prange = sheet.getRange("A3:C5"); //manually edit if rows are added/deleted
var wrange = sheet.getRange("A8:C10"); //manually edit if rows are added/deleted

if(sheet.getRange("C1").getValue() == "Yes"){ //toggle checkbox in C1
prange.sort([
{column: 2, ascending: true},
{column: 3, ascending: true}]);
wrange.sort([
{column: 2, ascending: true},
{column: 3, ascending: true}])
}else {
prange.sort(
{column: 3, ascending: true});
wrange.sort(
{column: 3, ascending: true})
}
}

This is a similar worksheet and matches the code above:

Personal		Yes
Task	Completed	Due
Task 1	No	6/10/2021
Task 2	Yes	6/15/2021
Task 3	No	06/22/2021
Work
Task	Completed	Due
Task 1	Yes	6/10/2021
Task 2	Yes	6/14/2021
Task 3	No	6/19/2021
End

Edit:

Somewhat of a solution using excel commands instead: Add a new sheet called "Ranges" and paste the following to cell A1 and A2:

A1 cell:=CONCATENATE("A",MATCH("Personal",'To do list'!A:A,0)+2,":C",MATCH("Work",'To do list'!A:A,0)-1) 
A2 cell:=CONCATENATE("A",MATCH("Work",'To do list'!A:A,0)+2,":C",MATCH("End",'To do list'!A:A,0)-1)

and edit the code above to:

var sheet2 = ss.getSheetByName("Ranges");
var prange = sheet.getRange(sheet2.getRange("A1").getValue()); 
var wrange = sheet.getRange(sheet2.getRange("A2").getValue());

My initial thoughts suggest that it would be easier to separate personal tasks and work tasks on separate pages and handle in separate section of the onEdit function. — Cooper

Martí Martí · Accepted Answer · 2021-06-03T09:27:17

To start with, Java and JavaScript are different languages. I know, confusing. Since you seem to be new with it, I would strongly recommend to take a look at the tutorials on the JavaScript tag's about page.

Like Cooper said, you should probably use different pages, or, at least, different columns. This would greatly reduce the complexity of the problem.

That being said, this is how you would do it:

function onEdit(e) {
  // [...]

  const colA = sheet.getRange('A1:A')            // get the entire A column
  const colAValues = colA.getValues().flat()     // Read the values and make an simple array
  const workRow = colAValues.indexOf('Work') + 1 // Get the number of the row with "Work"
  
  const pRange = sheet.getRange(`A3:C${workRow-1}`)
  const wRange = sheet.getRange(`A${workRow+2}:C`)
  
  // [...]
}

You need to remove the last "End" so the wRange doesn't include it. You could also search for it and use its position.

Note that for workRow we need a +1 because JavaScript starts counting from 0 and Sheets starts from 1.

Selecting range by finding start/end row number based on value in Sheets Script?

1 Answers

References