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

Question

I'm having trouble finding out how to get the last row of a specific column which has data in it (like this: Determining the last row in a single column) and implementing it together with "appendRow" in my code, so that I can append data to the next blank row in that specific column.

Here is an example sheet of what I have:

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:

You can't appendRow. You have to get the range and use `setValues()` — TheMaster, Dec 12 '21 at 17:01
Have you checked out [cooper's script](https://stackoverflow.com/revisions/70324125/3) already? — TheMaster, Dec 12 '21 at 18:27

Cooper · Accepted Answer · 2021-12-12T20:39:14.253

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:

First of all thank you @Cooper for your effort! It comes close to what i'm trying to do! I will update my post so that you can see the final version of what my goal is. In this final version I want to have the dialog linked to a specific range, so that there would be no need to choose the column or range in the dialog. Is there any way? Thanks! — axis9011, Dec 13 '21 at 08:27
I've asked a new question, thanks ! -> [HERE](https://stackoverflow.com/questions/70334784/how-to-put-data-from-a-custom-dialog-into-a-specific-range-in-the-sheet-without) — axis9011, Dec 13 '21 at 12:41

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

1 Answers1

How to get the last row of a specific column

And appending data without appendRow()

Linked

Related