Google Sheets: Move a cell value to next empty position in Range on sheet-CodePudding

Looking to select multiple choice options from a drop down box.

I have this sample spreadsheet https://docs.google.com/spreadsheets/d/1BKWZWHRvdLIc8k3MzT4ARENe2s-qD-AW5vegOMs_jqw/edit?usp=sharing

The Clear and Submit buttons should not be a problem.

I am having issues that when I click on "Add another ATA" button, I would like an app script to move the value in D7 to the next available blank cell in the range J2:J13 and then remove the value in D7 to await a new value, then click "Add another ATA" and move the new value to the next available blank cell in the range J2:J13 etc...

I have succeeded in moving the value from D7 to J2 for example.

I cant figure out how to establish via app script how to calculate what cells are blank or not so I know where to insert the code.

I have tried putting the Range J2:13 in an array and cycling through it but I wasn't successful identifying the next available blank cell range.

CodePudding user response：

You can do that with the appendRows_() utility function. Paste it in your script project and call it like this:

  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getRange('D7').getValue();
  const result = appendRows_(sheet, data, 10);

...where 10 means column J.

CodePudding user response：

ThankYou Doubleunary - The appendRows_() formula was exactly what I was looking for...

Hopefully - I can figure out the rest - MUCH appreciated!!!