Google Script - Get last non-empty column of row-CodePudding

I'm trying to get the last non-empty column of the first row of a Google Sheet. I find plenty of help regarding the last row of a column, but I want the other way around.

I've tried using GetLastColumn, but I must misunderstand what "containing content" means (https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#getlastcolumn).

var sheet = SpreadsheetApp.getActive().getActiveSheet();
var last_column = sheet.getRange(1,1,1,30).getLastColumn();

Logger.log(last_column)

This returns "30" instead of "16", although P1 is the last non-empty cell on row 1. I can't use var last_column = sheet.getLastColumn();, because I want to populate Q2, Q3, Q4 etc. with a value. As soon as that happens, the last column of the sheet is Q, not P.

In fact, it seems to me the solution of setting the range to an arbitrary max of columns is less then ideal, but I can't manage setting the range to an entire row (and not more than one row).

But the main question: what should I to to have a return that is "16"?

CodePudding user response：

Description

To find the first empty cell of a row use, col is the non empty cell.

let range = some row of a spreadsheet
let col = range.getNextDataCell(SpreadsheetApp.Direction.NEXT);

This is equivalent to typing CTRL Right on the keyboard.

To find the first empty cell of a column use, row is the non empty cell.

let range = some column of a spreadsheet
let row = range.getNextDataCell(SpreadsheetApp.Direction.DOWN);

This is equivalent to typing CTRL Down on the keyboard

If there is a blank cell within the range of data it will stop there

Reference

Range.getNextDataCell()

CodePudding user response：

Get Row Width

function getRowWidth(row, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var row = row || sh.getActiveCell().getRow();
  var rcA = [];
  if(sh.getLastColumn()){rcA = sh.getRange(row, 1, 1, sh.getLastColumn()).getValues().flat().reverse();}
  let s = 0;
  for (let i = 0; i < rcA.length; i  ) {
    if (rcA[i].toString().length == 0) {
      s  ;
    } else {
      break;
    }
  }
  return rcA.length - s;
}