add values moving down column in google sheets using apps script-CodePudding

In google sheets I have an input field for names that pops up when you press a button. I want all the names to appear in column B starting at cell B44. The names will continue to move down the column as you use the input field to add more names (B44, B45, B46...).

First I tried using appendRow, however I realized you can't specify a starting cell. I'm now using setValue and getRange('B44'). The problem with this is that each time you enter a new name into the input field it just replaces the last name and stays in cell B44.

code.gs

function addminitask() {
    var html = HtmlService.createTemplateFromFile('minitask').evaluate()
  var result = SpreadsheetApp.getUi().showModalDialog(html, ' ');
  SpreadsheetApp.getActive().toast(result);
}

function AddRecord(name) {
  
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var mainSheet = ss.getSheetByName("DAILY HUB");
  mainSheet.getRange('B44').setValue([name]);
  
}

function startForm()
{
 var form = HtmlService.createHtmlOutputFromFile('minitask');
 SpreadsheetApp.getUi().showModalDialog(form, 'Add Record');
  
  
}



function onOpen(e)
{
  
 addMenu(); 
}

minitask.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
    function AddRow()
    {
    var name = document.getElementById("name").value;
    google.script.run.AddRecord(name);
    }
    </script>
  </head>
  <body>
    Name:  <input type="text" id="name" />
    <input type="button" value="Add" onclick="AddRow()" />
  </body>
</html>

CodePudding user response：

In your script, how about the following modification?

From:

mainSheet.getRange('B44').setValue([name]);

To:

var values = mainSheet.getRange('B44:B').getDisplayValues();
var idx = values.findIndex(([b]) => !b);
var lastRow = (idx == -1 ? values.length : idx)   44;
mainSheet.getRange(`B${lastRow}`).setValue(name);

By this modification, the value is put in the 1st empty cell below cell "B44".