CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheetsimportrange
Antonio Tebas
Antonio Tebas

Reputation: 15

Use multiple importrange with different length

I'm new with coding but i'm trying to do somenthing really hard to me...

I have about 50 spreedsheets and i want to import some data from each of then. I had success using while to automaticly import the data, the problem is, each spreadsheet has its own lenght, all starts at cell A20 and goes until colum T, but the last number is different. I used a fixed number (250), but it creates blanks rows between the data.

Is it possible to personalize the range selection so i can avoid the empty space between the datas? Or there is some code to delete the empty rows between the data?

My code so far...

function funil_usando_FOR () {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A3').activate();

  i = 3
  while (true) {
    var range = spreadsheet.getRange("Z" + i);
    var value = range.getValue();

    if(value == "") {
      break;
    }

    spreadsheet.getCurrentCell().setFormula('=importrange("'+value+'", "NOVO FUNIL!A20:T250")');
    spreadsheet.getCurrentCell().offset(251, 0).activate();
    i++;
  }  
}

Upvotes: 1

Views: 244

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

Answer:

There are multiple ways which allow you to obtain the last row in which data is present in a Google Sheet.

Methods:

Probably the simplest method is to use the getDataRange() method of the Sheet class. This returns a range equal to the dimensions in which data is present:

var range = spreadsheet.getDataRange();

Following on from the previous method, if you don't want to get the range and only the number of rows you can call the getNumRows() method of the Range object returned:

var range = spreadsheet.getDataRange();
var lastRow = range.getNumRows();

You can also get the integer value of the last row of this range using the getLastRow() method - this method returns the last row in the range - but be aware though as like getNumRows(), rather than returning a range like getDataRange(), it returns an integer and so you will still have to then use it in the getRange() method you were using:

var range = spreadsheet.getDataRange();
var lastRow = range.getLastRows();

This method is basically the same as the last - though this is a method of the Sheet class rather than the Range class. Its use case is the same and returns an integer value of the last row in the sheet in the same way. This is likely the best to use if you only want the value of the last row as there is less to define and you don't have to extract it from the returned range:

var lastRow = spreadsheet.getLastRow();

References:

Upvotes: 2

Related Questions