I basically want to get the last column of the first row of a spreadsheet. No matter what I try it Logs the last column with data of the whole spreadsheet. I know it's pretty simple, but no luck.

I have searched all over and everything I try doesn't work. Any help is greatly appreciated. Thanks so much! Brandon

I basically want to get the last column of the first row of a spreadsheet. No matter what I try it Logs the last column with data of the whole spreadsheet. I know it's pretty simple, but no luck.

I have searched all over and everything I try doesn't work. Any help is greatly appreciated. Thanks so much! Brandon

• javascript
• google-apps-script

• 1 please post your code – Marc Commented Feb 27, 2015 at 16:00

5 Answers 5

Here is some code to get you started:

function getLastNonEmptyCellInRow() {
  var rowToCheck = 2;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var maxColumns = sheet.getLastRow();

  var rowData = sheet.getRange(rowToCheck, 1, 1, maxColumns).getValues();
  rowData = rowData[0]; //Get inner array of two dimensional array

  var rowLength = rowData.length;

  for (var i = 0; i < rowLength; i++) {
    var thisCellContents = rowData[i];

    Logger.log('thisCellContents: ' + thisCellContents);

    if (thisCellContents === "") {
      Logger.log(i);
      return i + 1;  //Pass the count plus one, which is the last column with data
    }
  }
}

function getLastColNum(rowNum)
{
  // Get the values of the row's (unbounded by column number) range.
  var vals =
        SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
        .getRange(rowNum +":"+ rowNum)
        .getValues()[0],

      lastColNum = vals.length
  ;

  // Decrement while popping off any trailing empty text ("") cells.
  while(!vals.pop())
  { --lastColNum; }


  return(lastColNum);
}

There's no built in function to do this, but you can write a function to do it yourself.

For example, use getRange() and getLastColumn() to get the row you are interested in up to the last possible data column, then use getValues() to get all the cells in that row. Finally, loop over array of cells (returned by getValues()) and keep track of the last index which has a value.

https://developers.google./apps-script/reference/spreadsheet/sheet#getRange()

https://developers.google./apps-script/reference/spreadsheet/range#getValues()

The Sheet class supports getLastColumn() to return the last column with content in it.

https://developers.google./apps-script/reference/spreadsheet/sheet#getLastColumn()

Range has a getLastColumn() function, but it is the last column of the range and not necessarily the last column with data in it.

https://developers.google./apps-script/reference/spreadsheet/range#getLastColumn()

To get the last column with content in it, iterate over the row from the last Sheet column back to a non-empty cell.

   /**
   * Get last column with content in row
   * @param {Sheet} sheet
   * @param {integer} row
   * @return {integer} last column with content, 0 if none found
   */
   function getLatColInRow(sheet, row = 1) {
       let lastColumn = sheet.getLastColumn();
       let values = sheet.getRange(row,1,1,lastColumn).getValues()[0];
       while ((values[lastColumn-1] == "") && (lastColumn>0)) {
           lastColumn --;
       }
       return lastColumn;
   }

from excel workbook object get sheet using getSheetAt(sheetNumber); then using sheet object getRow(0); then using cellIterator() you can iterate till last column of row. Example using XSSFWorkBook XSSFWorkBook wb=new XSSFWorkBook(FileInputStream object);//object got by passing excel file path to FileInputStream Sheet sh=wb.getSheetAt(0) or wb.getSheet("sheet name"); Row row=sh.getRow(0); Iterator cit=row.cellIterator();Cell cell; while(cit.hasNext()) { cell=cit.next(); }

when you e out of the while loop you will have last cell object in 'cell' variable