Google Sheets - Find last column with data in array

Question

I'm trying to find the last column in a range, but the problem is I can't specify the script to only check within an array instead of the entire sheet. I was able to use this bit of code to find the last row but I'm having trouble understanding how this can be changed to find the column instead of 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);
}

Source for this code

Can anyone explain to me how this function could be changed up a bit to search for the column instead of the row?

Answer 1

Description

Finding the last row or column containing data of a range can be simplified with the use of Range.getNextDataCell().

Here is a spreadsheet with sparcely populated cells.

Code.gs

function test() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
    let range = sheet.getRange("A1:D10");
    let lastRow = range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    console.log("lastRow = "+lastRow);
    range = sheet.getRange("A1:E5");
    lastColumn = range.getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();
    console.log("lastColumn = "+lastColumn);
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

6:14:50 PM  Notice  Execution started
6:14:51 PM  Info    lastRow = 4
6:14:51 PM  Info    lastColumn = 4
6:14:51 PM  Notice  Execution completed

Reference

• Range.getNextDataCell()

Answer 2

In your situation, how about the following modification?

Modified script:

function findLasColumn() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const range = sh.getRange("A:K");
  const offset = range.getColumn();
  const maxColumn = Math.max(...range.getDisplayValues().map(r => r.reduceRight((n, c, i) => {
    if (!n && c) n = i;
    return n
  }, null)));
  const lastColumn = maxColumn + offset;
  console.log(lastColumn);
}

• When this script is run, from "A:K", the last column number is returned.
• If you use "B:K", the last column number is returned as the valid column number.

Reference:

• reduceRight()

Answer 3

Alternative Approach

You can also use the transpose method to be able to get the last column in your range.

Tweaked Script:

function findLastColumn() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const range = sh.getRange("C1:H18");
  const data = transpose(range).map((values,index) => values[0].length ? index+range.getColumn() : null).filter(d => d); //The 'index+range.getColumn()' part returns the number of a non-empty col
  console.log(data[data.length-1]);
}

function transpose(range){
  return range.getValues()[0].map((_, colIndex) => range.getValues().map(row => row[colIndex]));
}

Sample Sheet:

• After running the findLastColumn() function

Reference:

• Transpose an Array in JavaScript
• JavaScript Array map()

Answer 4

Based on your question here, and I want to paste data in the first empty row in the range A:K. Here is a simple solution for finding the first blank row and column.

const data = sh.getRange(`A:K`).getDisplayValues()

const firstBlankRow = data.findIndex(row => row.every(cell => cell === ``))+1
const firstBlankCol = data[0].map((_, index) => data.flatMap(i => i[index]))
                             .findIndex(col => col.every(cell => cell === ``))+1

Answer 5

You can prototype a function as follows

Object.prototype.getLastDataColumn = function(row) {
  var lastCol = this.getLastColumn();
  if (row==null){row=1}
  var range = this.getRange(row,lastCol);
  if (range.getValue() !== "") {
    return lastCol;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();
  }              
}

and

Object.prototype.getLastDataRow = function(col){ // col in letter
  var lastRow = this.getLastRow();
  if (col == null){col='A'}
  var range = this.getRange(col + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }  
}

and then use as

const lastRow = sheet.getLastDataRow('B') // for column B, can be omitted

and

const lastCol = sheet.getLastDataColumn(5) // for row#5 can be omitted