Use changing variable to define range in loop

Question

I would like to extract multiple ranges within a range on a spreadsheet. The number of rows always changing. This is what I'm aiming to do. The start row and the number of rows will always be different.

var sourcesheet = ss.getSheetByName("sheet name");
var nameRowN = starting rows already in an array
var sor = number of rows in each array already calculated and put in an array

 var ranges = [];
   for (var m = 0; m < sourcesheet.length; m++) {
   ranges = sourcesheet.getRange(nameRowN[m],5,sor[m],29).getValues();
   }

So ranges should include multiple mini arrays. The for loop goes through m, then m+1 and so on. Am I on the right track? Is that possible to achieve what I'm looking for this way?

Edit: I can get the range outside of the loop and replace m variable:

ranges = sourcesheet.getRange(nameRowN[1],5,sor[1],29).getValues();

I had no idea, thank you! I'm quite a beginner with scripts, trying to teach myself while working. Can I use a range instead? — Vidomina Sangre
– Vidomina Sangre, Commented Jan 7, 2020 at 8:03

ziganotschka · Accepted Answer · 2020-01-07 09:47:48Z

1

If your goal is to iterate through all rows with contents - you can use the method getLastRow().

Sample:

var lastRow=sourcesheet.getLastRow();
for (var m = 0; m < lastRow; m++) {
  //do what you want
}

Another possibility would be to use getDataRange() in conjunction with getValues() and length.

Sample:

var rangeLength=spreadsheet.getDataRange().getValues().length;
for (var m = 0; m < rangeLength; m++) {
  //do what you want
}

answered Jan 7, 2020 at 9:47

ziganotschka

26.9k2 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vidomina Sangre Over a year ago

Thank you! The second method you mentioned was working just as I needed it to. Now it seems so simple yet I couldn't figure it out.

Collectives™ on Stack Overflow

Use changing variable to define range in loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related