How to get the last row of a specific column and append data to that row with "appendRow"-CodePudding

I'm having trouble finding out how to get the last row of a specific column which has data in it (like this:

Everytime I want to append a row in ColA via dialog, the script gets the last row of all columns, not just of ColA.

Here is an example of the code I'm using:

//dialog 1
function showDialog1() {
  
  var widget = HtmlService.createHtmlOutputFromFile("dialog1.html").setHeight(250).setWidth(700);
  SpreadsheetApp.getUi().showModalDialog(widget, "Dialog 1");
}

function appendRowFromFormSubmitDIALOG1(formdialog1) {

    SpreadsheetApp.getActiveSheet().appendRow([formdialog1.datadialog1]); 
  
    
}




//dialog 2
function showDialog2() {
  
  var widget = HtmlService.createHtmlOutputFromFile("dialog2.html").setHeight(250).setWidth(700);
  SpreadsheetApp.getUi().showModalDialog(widget, "Dialog 2");
}

function appendRowFromFormSubmitDIALOG2(formdialog2) {

    SpreadsheetApp.getActiveSheet().appendRow([,formdialog2.datadialog2]); 
  
    
}

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <script>
    function submitForm() {
      google.script.run.appendRowFromFormSubmitDIALOG1(document.getElementById("dialog1"));
      document.getElementById("form").style.display = "none";
      
    }
  </script>
</head>
<body>
  <div>
  <div id="form">
  
  <form id="dialog1">

  
    <label for="datadialog1"><b>datadialog1</b></label></br>
    <input type="text" id="datadialog1" name="datadialog1" size="60"><br><br>
    

      <input type="button" value="submit" onclick="submitForm();google.script.host.close()">
       
  </form>

</body>
</html>

So my question is how get the last row of a specific column and let "appendRow" write data from "appendRowFromFormSubmit" (see code example above) into the next blank row of that column. Like this:

CodePudding user response：

How to get the last row of a specific column

function lastRowOfCol(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;
}

And appending data without appendRow()

GS:

function postMyInput(obj) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  let col = obj.col;
  let val = obj.entry;
  let 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;
    }
  }
  let lr= rcA.length - s;
  sh.getRange(lr   1,col).setValue(val);
}

function launchPostItDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah2'),'Input Dialog')
}

HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form>
      <select name="col">
        <option value="1">Col1</option>
        <option value="2">Col2</option>
      </select>
      <input type="text" size="35" name='entry' placeholder="Enter Post Value" />
      <input type="button" value="Post" onClick="postValue(this.parentNode)" />
    </form>
    <script>
      function postValue(form) {
        google.script.run.postMyInput(form);
      }
    </script>
  </body>
</html>

Demo: