how to select a range from an array of unknown size?

Question

I would like to make a macro that will retrieve data in another sheet but I do not know in advance the number of lines and I would like to recover the number of lines. the DATA SHEET have a different numbers of lines every day or month so i can't define in advance the number of lines.

I’ve tried to use the function "GetRange" but there is nothing about the number of non empty cells to get the number of non empty lines.

I've tried to create filter too but i don't have the permission to whrite in the document.

function myFunction() {

   // Get the sheet with DATA active 
   var spreadsheet = SpreadsheetApp.getActive();
   spreadsheet.setActiveSheet(spreadsheet.getSheetByName('DataSheet'), true);
  
  
//how can i get the Range of the last non empty cells ??
  var range = spreadsheet.getRange("A2:Axxx"); 

//i want to have an integer (number of lines with data)
  var values = range.getValue(); 
  
  
// after i do somes operations in these data but it's not important.
  for(var i=2 ; i< values ; i++) {            
  
      spreadsheet.duplicateActiveSheet();
      spreadsheet.getActiveSheet().setName('Lettre Relance' + i);
      spreadsheet.getRange('C12').activate();
      spreadsheet.getCurrentCell().setFormula('=Relance!A'+i);
  }
}

score 1 · Answer 1 · answered Dec 09 '21 at 14:41

Here is a pretty generic function that writes your data in a Sheet without knowing the dimension of your data array in advance.

The trick is to store your data in an Array, then calculate the array size and dimension, then use these values as an amount of rows and columns.


function writeToSheet(dataToWrite){
  if (dataToWrite.length > 0){

    // Set the required range dimension
    var dimension ={};
    dimension.rows = dataToWrite.length;
    dimension.columns = dataToWrite[0].length;

    // Select the Sheet
    var document = SpreadsheetApp.getActive();
    var sheet = document.getSheetByName("SheetName"); 

    //  activate the sheet
    document.setActiveSheet(sheet);

   // it starts writing in the first cell of the second row. Update the starting row/column value below to change that.
    var range = sheet.getRange(2, 1, dimension.rows, dimension.columns);
    range.setValues(dataToWrite);
    SpreadsheetApp.flush();
  }
}

score 0 · Accepted Answer · answered Apr 17 '19 at 15:48

I normally use the code from Simple Mail Merge tutorial to read an entire sheet's data into an object. You can also use code from the Faster way to find the first empty row thread to get the first empty row and then set your range to everything before that row. I use the code from @Mogsdad response, which I have copied here:

/**
 * Mogsdad's "whole row" checker.
 */
function getFirstEmptyRowWholeRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var row = 0;
  for (var row=0; row<values.length; row++) {
    if (!values[row].join("")) break;
  }
  return (row+1);
}

how to select a range from an array of unknown size?

2 Answers2