使用不同长度的多个importrange [英] Use multiple importrange with different length

问题描述

我是编码的新手，但我想做一些对我来说真的很难的事...

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

我大约有50张spreedsheets，我想从那时的每个数据中导入一些数据.我成功使用了while来自动导入数据，但问题是，每个电子表格都有其自己的长度，所有电子表格都始于单元格A20并一直到列T，但是最后一个数字不同.我使用了固定的数字(250)，但是它在数据之间创建了空白行.

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++;
  }  
}

推荐答案

答案:

有多种方法可以让您获取Google表格中数据的最后一行.

• 方法1: getDataRange()

可能最简单的方法是使用 Sheet 类的 getDataRange()方法.这将返回一个范围，该范围等于存在数据的维度:

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();

• 方法2: getNumRows()

从上一个方法开始，如果您不想获取范围，而只想获取行数，则可以调用 Range getNumRows()方法>返回的对象:

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();

• 方法3: range.getLastRow()

您还可以使用 getLastRow()方法获取该范围最后一行的整数值-此方法返回范围的最后一行-但请注意，就像getNumRows()，而不是返回像 getDataRange()这样的范围，它返回一个整数，因此您仍然必须在 getRange()您使用的方法:

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();

• 方法4: sheet.getLastRow()

此方法与上一个基本相同-尽管这是 Sheet 类的方法，而不是 Range 类.它的用例是相同的，并且以相同的方式返回工作表中最后一行的整数值.如果您只想要最后一行的值，因为定义的值较少，而不必从返回的范围中提取它，则这可能是最好的选择:

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();

参考文献:

• Sheets API参考: Sheet.getDataRange()
• Sheets API参考: Range.getNumRows()
• Sheets API参考: Range.getLastRow()
• Sheets API参考: Sheet.getLastRow()

这篇关于使用不同长度的多个importrange的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！