my getFirstEmptyRow FUNCTION returning same row number of gsheet even after appending new with data using apps script

Question

I have following code to determine the last row of first column (it has timestamp) that I copied from here. When I submit web app form it keeps updating the same row, replacing the old form data.

function getFirstEmptyRow() {
    var ss= SpreadsheetApp.openByUrl("URL here");
    var dataSheet = ss.getSheetByName("Message");
    var column = dataSheet.getRange('A:A');
    var FirstEmptyRow = dataSheet.getRange('A:A').getLastRow()+1;
    var values = column.getValues(); // get all data in one call
    var ct = 0;
    while ( values[ct][0] != "" ) {
      ct++;
    }
    
    return (ct+1);
  }

And here is my code.gs that is adding the data into gsheet.

function AddRecordMsg(formData) { 
    //URL OF GOOGLE SHEET;
    var ss= SpreadsheetApp.openByUrl(URL here);
    var dataSheet = ss.getSheetByName("Form Responses");
    // var newDate = new Date(); // Get new date object
    var timezone = Session.getScriptTimeZone(); // See Apps Script documentation or like "GMT+5"
    var format = "MM-dd-yyyy HH:mm:ss"; // Set date format for output
    var timeStamp = Utilities.formatDate(new Date(), timezone, format); // This returns a string
    // dataSheet.appendRow([new Date(), '','','','Message to the CEO',formData.FormControlTextarea1]);
    var numRows = getFirstEmptyRow(); 
    dataSheet.getRange("A"+numRows).setValue(timeStamp);
    dataSheet.getRange("E"+numRows).setValue("Message to the CEO");
    dataSheet.getRange("F"+numRows).setValue(formData.FormControlTextarea1);

  }

HTML form is as below:

 <form id="msg2ceo" class="row" onsubmit="event.preventDefault();sendForm2CEO()">
          <div class="col-md-12">
            <h4 class="mb-3"></h4>
              <!-- <div class="form-group mb-3" id="user">
                <label for="mobile">Mobile Number</label>
                <input type="text" class="form-control" id="mobile" placeholder="Enter your WhatsApp number" required>
              </div> -->
              <div class="form-group mb-3">
                <label for="FormControlTextarea1">Your Message</label>
                <textarea class="form-control" id="FormControlTextarea1" rows="3" required>Enter message</textarea>
              </div>
              <hr class="mb-4">
              <!-- // -->
              <!-- // -->
              <input type="submit" class="btn btn-primary btn-lg btn-block" id="submit2ceo" value="Submit"/>
          </div>
        </form>

The JavaScript has code to handle the form submission and calling code.gs function to add data into gsheet:

<script>



var inputs = [
'FormControlTextarea1'
];

function getFormData(){
  var output = {};
  for(var i = 0; i < inputs.length; i++){
      output[inputs[i]] = $('#'+inputs[i]).val();
      // console.log(output);
  }
  return output;
}



function animateBtn($btn, color){
      $btn.value ="Message Sent.";
      $btn.css('background', color);

}

function sendForm2CEO(){
  console.log('startup')
 
    console.log(getFormData());

    google.script.run.withSuccessHandler(function(returnValue){
      console.log("returnValue :"+returnValue);
    }).AddRecordMsg(getFormData());

    animateBtn($(this),'green');


}

</script>

The important point here to note is the Google sheet URL is not the URL of the activeSpreadsheet in which the apps script code is bound to. I am trying to update webapp form data into already existing google sheet which was previously used as google form response sheet. Now I am trying to use it so that existing data connections to all other sheets used remain there.

Now I am not able to figure out why I can't get updated first empty row number after new timestamp data inserted. I have done few trial and error but seems this would not work.

score 1 · Answer 1 · answered Jul 24 '21 at 12:44

Try this:

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')
}

score 0 · Answer 2 · answered Jul 24 '21 at 14:54

I have found the problem in my function "getFirstEmptyRow". In this function I am actually looking into another tab of the google sheet. And that tab's first empty row is always fixed and that's the reason I was getting the my webapp form data in the same row, replacing the old data.I am so sorry for lossing my almost 3-4 hours of troubleshooting.

I should be looking at tab "Form Responses" and find the first empty row of the timestampp column. Now I am happy that at least I have found the problem.

my getFirstEmptyRow FUNCTION returning same row number of gsheet even after appending new with data using apps script

2 Answers2