Copy a variable range depending on the number of non-empty cells

Question

From a google sheets macro, I am trying to copy a variable range of cells, which depends on the amount of non-empty cells each time. In the following sentence:

spreadsheet.getRange ('Orders! 1: 30'). copyTo (spreadsheet.getActiveRange (), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

I need that instead of the number "30" there is a variable that is defined according to how many lines are occupied in column I of the same sheet.

I was thinking of using the "for" function to scan lines in column I from the cell I1 until It finds an empty one (or not greater than 0). but I'm not sure how to do this.

---

After the help of #Iamblichus and #Tedinoz I was able to advance in the code, but now a connected and similar problem arised.

I need to paste the selected range in a different tab, but in which same variable no longer recognizes same amount. This is my code:

function Test() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("Aux");

  spreadsheet.getRange('I3:I').createFilter();
  var criteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(['0']).build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(9, criteria);

  var valuesI = spreadsheet.getRange("I1:I").getValues();
  var numRowsI = valuesI.filter(String).length; 
  var firstRow = 1;
  var firstCol = 1;
  var numCols = sheet.getLastColumn();
  var originRange = sheet.getRange(firstRow, firstCol, numRowsI, numCols); 

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Hist_D'), true);
  spreadsheet.getActiveSheet().insertRowsBefore(sheet.getActiveRange().getRow(), numRowsI);
  spreadsheet.getRange('A1').activate();
  originRange.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  originRange.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Aux'), true);
  spreadsheet.getRange('T35').activate();
  spreadsheet.getActiveSheet().getFilter().remove();  
}

"Hist_D" is a record in which the latest data is added at the top. I need to copy a variable amount X of lines from the "Aux" tab to the first lines of the "Hist_D" tab. But before that I need to add (insertRowsBefore) the same number X of lines in "Hist_D" so that the old info is not overwritten. My problem now is how to keep the amount X in the numRowsI variable when changing tabs.

The number of lines inserted through the formula .insertRowsBefore is around 100 (total number of lines in the first tab), when it should be around 20 (number of non-empty lines in the first tab). According to my interpretation, for some reason the variable numRowsI changes when changing tab.

Answer 1

Props to @Mogsdad ref and AndyE before him, for this incredibly handy method.

var Bvals = ss.getRange("B1:B").getValues();

var Blast = Bvals.filter(String).length;

The value of "Blast" is the number of cells in Column B that contain values - there is an implicit assumption that it is a contiguous range.

If you define your range using getRange(row, column, numRows, numColumns), then substitute "Blast" for "numrows".

Answer 2

Update:

Assuming that:

• For each row to be copied, you want to copy all columns with content and not just column I.
• You want to copy this to the top of another sheet, without overwriting previous data.

You can try using this:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var originSheet = ss.getSheetByName("Aux");
  var destSheet = ss.getSheetByName("Hist_D");  
  var valuesI = originSheet.getRange("I1:I").getValues(); // As far as I can see, you want to check column I, not B
  var numRowsI = valuesI.filter(String).length;
  var firstRow = 1;
  var firstCol = 1;
  var numCols = originSheet.getLastColumn();
  var originRange = originSheet.getRange(firstRow, firstCol, numRowsI, numCols);  
  destSheet.insertRows(1, numRowsI);
  var destRange = destSheet.getRange(firstRow, firstCol, numRowsI, numCols);
  originRange.copyTo(destRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES);
}

The main thing here is finding the last cell with content in a specific column, something that can be achieved using this (assuming there are no blank cells in between, which, considering your question, seems to be the case).

The number of blank rows you have to add to the top Hist_D in order not to overwrite existing data equals the number of rows that have been copied from Aux (that is, equal to numRowsI). You are having problems because getRange is a method of the Sheet class, not the Spreadsheet class.

I hope this is of any help.