Determining the last row in a range of columns that contain empty cells - Google Sheets

Question

I'm having trouble creating a function to find the last row with data in a specified range, in this case I have data that moves in columns A:K and functions that use that data in L:Z. I want to paste data in the first empty row in the range A:K. I have tried using the solution of finding the first empty row in a specific column:

var Avals = ss.getRange("A1:A").getValues();
var Alast = Avals.filter(String).length;

but that isn't a viable solution for me since we may have empty first values in a row, and I need it to paste in a specific range. I've tried using both getLastRow and getDataRange methods but they don't satisfy the requirements.

Answer 1

Goal:

You want to find the last row of a particular range of columns and some of these columns could contain empty cells or even the full column could be totally empty.

Explanation:

• Select the desired range of columns you want to identify the last row with content.

• forEach column, use map to get the data of this particular column.

• reverse the data array of the column so you can findtheIndex of the first non-empty cell (starting backwards).

• Deduct this row from the maximum rows in the sheet, and it will give you the last row with content of each column in the selected range. Push all these values to an array.

• The Math.max value of this array will be the last row with content of the selected range.

Code snippet:

Iterate through the columns A:K, find the last row for every column and then calculate the maximum last row:

function findLastRow() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const data = sh.getRange("A:K").getValues();
  const mR = sh.getMaxRows();
  const indexes = [];
  data[0].forEach((_,ci)=>{
   let col = data.map(d => d[ci]);
   let first_index = col.reverse().findIndex(r=>r!='');
   if(first_index!=-1){
      let max_row = mR - first_index;
      indexes.push(max_row);
   }
  });
  last_row = indexes.length > 0 ? Math.max(...indexes) : 0;
  console.log(last_row);
}

Then you can start pasting from last_row+1.

Example Sheet for code snippet:

Let's say we want to find the last row in the range A:K for which some columns might contain empty values, and there is another column that has the last row with content in the sheet. In the example sheet below, the correct answer should be 27.

Advantages:

Unlike this, the solution works:

• even if any of the columns in the range contains empty values (or the full column is empty).

• for both single columns or range of columns.

Disadvantages:

It could potentially be slow for a large number of columns. Albeit, the number of GAS API calls are at the minimum possible level, there are many JavaScript function calls that are being used iteratively.