Have a button to add a record

Question

I have a sheet containing all of my data.

What I want to do is to have a button to add the values in some cells in the current sheet to the first row with empty cells in the data sheet.

I want to do it in the sheets and not with the API (if it is possible to use the API in a sheet then that would work).

If the only way to add ui objects is by drawing then I know how to do that but the main problem is with the adding the row.

Answer 1

To target the first empty row in the column, use the following code:

var firstOpenRow = sheet.getLastRow() + 1;
var firstOpenRange = sheet.getRange(firstOpenRow, columnNum, numRows, numColumns);

If you want to fill that row with data from an existing row, get the data from that row and set the values of firstOpenRow:

var existingData = sheet.getRange(rowNum, columnNum, numRows, numColumns);
for (var i = 0; i < numberOfCellsToFill; i++) {
    firstOpenRange.getCell(1, i+1).setValue(existingData.getCell(1, i+1).getValue());
}

In order to run these blocks in-sheet, you will have to create a sidebar add-on which is relatively easy if you know basic HTML and CSS. Some example reference can be found here.

In that sidebar, you'll want to have an input field and button to fetch whatever dataset you are trying to target, and then another button to insert that data at the end of the sheet.

If you are looking to insert completely new data, use a form object in your sidebar to retrieve and send that data to your script. In the script, use the same principles as shown above but with the form data.