0
276
ViewsGoogle Sheets - Find last column with data in array
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);
}
Can anyone explain to me how this function could be changed up a bit to search for the column instead of the row?
3 answers
Answer question0
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
0
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:
0
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
