0

Let's say the column of interest is Column A and I always want to add values in the first row after the last row with values or in the first row if there are no values in the column of interest..

Spreadsheets can deliver several scenarios, they are (if there are others that I haven't listed, remind me in the comments):

  1. No values in any column (all empty cells):
    enter image description here
  2. Non-empty cells in other columns but not in the column that interest you:
    enter image description here
  3. Non-empty cells in other columns and in the column that interest you:
    enter image description here
  4. Non-empty cells in sequence from the first row in the column you are interested in:
    enter image description here
  5. Non-empty cells from the first row in the column you are interested in but with empty cells between the non-empty cells:
    enter image description here
  6. First cell of the column that interests you being empty but other non-empty cells existing in that same column:
    enter image description here

To avoid the crashes that happen in each of these scenarios I've put together these two models that work correctly.

Using the Google Sheets API:

(Need enable the Google Sheets API advanced service)

function gsapi() {
  var sheet_id = 'XXXXXXXXXX';
  var sheet = SpreadsheetApp.openById(sheet_id);
  var sheet_page = sheet.getSheetByName('STACKTEST');

  first_row = 1;
  
  var Avals = Sheets.Spreadsheets.Values.get(sheet_id, 'STACKTEST!A' + first_row + ':A').values;
  if (Avals != undefined) {
    var Alast = Avals.length + 1;
  } else {
    var Alast = first_row;
  }
  var to_sheet = [
    ['A','B','C','D','E']
  ];
  sheet_page.getRange(Alast, 1, to_sheet.length, to_sheet[0].length).setValues(to_sheet);
}

Using Basic Google Apps Script:

function gasbasic() {
  var sheet_id = 'XXXXXXXXXX';
  var sheet = SpreadsheetApp.openById(sheet_id);
  var sheet_page = sheet.getSheetByName('STACKTEST');

  first_row = 1;
  
  const lrow = sheet_page.getLastRow();

  if (lrow > 0) {
    var last = lrow;
  } else {
    var last = 1;
  }
  
  const Avals = sheet_page.getRange("A" + first_row + ":A" + last).getValues();
  const reverse = Avals.reverse().findIndex(c=>c[0]!='');
  
  if (reverse != -1) {
    var Alast  = last - reverse + first_row;
  } else {
    var Alast  = first_row;
  }

  var to_sheet = [
    ['A','B','C','D','E']
  ];
  sheet_page.getRange(Alast, 1, to_sheet.length, to_sheet[0].length).setValues(to_sheet);
}

Is there a method that manages to circumvent all these possible scenarios in a more professional way?

It seems a bit archaic to need if or try + catch to work around failures.

Digital Farmer
  • 1,705
  • 5
  • 17
  • 67
  • Why shouldn't I close this as duplicate of https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column ? – TheMaster Jul 02 '22 at 22:29
  • To be honest what was the matter with just `const lrow = sheet_page.getLastRow();`. Regardless of the sparseness of the table it will always get the last row with data in any cell. And if there is no data it will return 0. – TheWizEd Jul 02 '22 at 22:48
  • 2
    I believe specifically [this answer](https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column/51473352#51473352) would satisfy all your conditions and probably the fastest. Also worth looking into Tanaike's benchmark answer there. – TheMaster Jul 02 '22 at 22:52
  • Hi @TheWizEd , because the ```0``` range returned in error when he was going to get the range for ```getValues()``` – Digital Farmer Jul 02 '22 at 22:58
  • You are correct @TheMaster , my stack was classifying the answers in a way that this answer was very low and the other options above (including the one marked as a solution) did not correspond to the need, in this case it does, just needing to add a detail to more when all the cells in the column are filled. – Digital Farmer Jul 02 '22 at 23:03
  • 1
    Try the "Date modified(newest first)" filter. – TheMaster Jul 02 '22 at 23:05

1 Answers1

1

Try getColumnHeight() plus one

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).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;
  //const h = Utilities.formatString('col: %s len: %s', col, rcA.length - s);
  //Logger.log(h);
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(h).setWidth(150).setHeight(100), 'Col Length')
}
Cooper
  • 59,616
  • 6
  • 23
  • 54