Google Sheets - Find last column with data in array-CodePudding

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

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

After running the findLastColumn() function

Reference:

CodePudding user response：

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

CodePudding user response：

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