CODEWITHSUNDEEP
google-apps-script
Vegard Smith-Johansen
Vegard Smith-Johansen

Reputation: 15

Looping through all entries in a Google Sheet with dynamic range

I'm trying to make a process which is working through a dynamic range in Google Sheet. The length of the table can vary from 1 entry to 200. My program needs to be able to work with the table no matter how many rows there are.

I have tried two different approaches. One is very static, and requires me to loop through a lot of unnecessary rows to check if they are empty.

And the other does not handle all tables lengths too well.

Is there any other approach I can use to fix this. I've read about the "getDataRanges". But I'm not sure if this could work in my situation.

Any ideas?

  1. This approach is static. In this case it gives me an array of 2 rows. I have considered using this and setting it to e.g. (2,200,3,6). In order to work with any ranges containing less than 200 rows. Then looking at each row to see if it is empty or not. If it is empty, I can do nothing, and skipt it.
var data = sheet.getRange(2,1,3,6).getValues();
  1. This approach is a bit more dynamic. As it uses Ctrl + shift + right button + down button in order to create a range of all existing data in the table. However, it doesn't work well if there is only 1 row for example. As it will select lots and lots of rows when selecting downwards.
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.NEXT).activate();
  spreadsheet.getRange('A2:V2').activate();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();

  var selection = SpreadsheetApp.getActiveSpreadsheet().getSelection();
  var activeRange = selection.getActiveRange();

  var range = '';
  var sel = SpreadsheetApp.getActive().getSelection().getActiveRangeList().getRanges();
  for(var i = 0; i < sel.length; i++){ 
  range += sel[i].getA1Notation();
    }

Upvotes: 0

Views: 403

Answers (1)

J. G.
J. G.

Reputation: 1832

var data = sheet.getDataRange().getValues();
  for(var i = 0; i < data.length; i++){ 
  if (data[i][0] = "") continue; //blank row
  range += data[i].getA1Notation();
}

Will indeed work well for you.

That said, checking for blank rows is not slow as long as you are doing it within an array and not with individual "getValue" statements. What are you hoping to accomplish here?

Upvotes: 2

Related Questions