I am making a Webapp and I collect the data from a form, I write it in a Sheets and there I process it with an array formula. The problem is that a row is increased at the end of the sheet, instead of being written to the first empty row. I can delete all the empty rows, but I need another way to do it without having to delete the rows. I add the code of a very simplified version of the Webapp:
//G.S.
function doGet () {
return HtmlService.createTemplateFromFile('index').evaluate();};
function escribir (array){
var ss = SpreadsheetApp.openById('').getSheetByName('Hoja 1');
ss.getRange(ss.getLastRow()+1,1,1,array[0].length).setValues(array);};
indexHTML
<body>
<input type="number" id="numero1" value="">
<input type="number" id="numero2" value="">
<input type="button" id="btn" value="boton">
<script type="text/javascript">
btn.addEventListener('click',escribirPartida)
function escribirPartida() {
array = [[numero1.value, numero2.value]]
console.log(array)
google.script.run.escribir(array)}
</script>
</body>
This is the result, instead of writing the data in the yellow row, it is written at the bottom of the sheet.
I tried to put a condition in the array function to make all the cells below it empty. This is the array function:=ArrayFormula(SI(A2:A+B2:B =0;"";A2:A+B2:B))
I need a way to limit the ArrayFormula so that the data is not written at the end. (I know that it can be done from the Script, but I need to do it from the sheets)
The problem is caused by the use of open references such as
A2:Ain conjunction with having empty rows. To avoid having to remove empty rows, you could modify your formula to limit the number of rows it returns. One way to do this is to use itARRAY_CONSTRAINin conjunction with a way to count the required rows. Example: