Is it possible to ADD items (lines) to a Google sheet containing ARRAYFORMULA with undefined length (typically A2:A) ? I saw some topics on that issue but no clear answer.

The problem is that lines are added at the end of the sheet although all lines from the top line containing the formula are empty ! If yes what is/are the workaround ? use an other sheet, one to manage the items (ADD, EDIT/modify, REMOVE) and an other as copy of this sheet with ARRAYFORMULA ?

Delete the empty rows. New rows will be written after the last filled row.

Yes thanks it works ! You should say "delete ALL empty rows and DON’T add new empty ones :wink:

Now the question is, if I delete some items (lines), I’ll get empty lines. Is there a trick to remove them and compress the table ?

You would probably have to write a script for that. For the most part, I don’t worry about deleted lines, unless it happens very often. Glide will ignore rows that are empty.

Well even if it’s not today the priority, do you have any idea where I could find/adapt such script ?

Not off the top of my head. You’d have to either search the forum or Google.

If your arrayformula is creating those blank rows, you could put an If-then test in the formula to verify the row has viable data. Off the top of my head, you could do something like

=arrayformula(if(len(A2:A)>0,the rest of the formula))

It may take you a minute or two of tweaking to make it work, but once it does, it will not add those empty rows to the bottom of your sheet.

Thanks Tim for your contribution, in fact I did it already in all ARRAY FORMULA.
Problem is when Users would delete some entries (product items), this will create some empty lines.

You could not let them delete the row. Instead give them a checkbox or switch that says “no longer available” or something similar. Once selected, the row becomes hidden to customers. If they want to add the product back at a later time all they need to do is unselect it.

Thanks Tim, good idea, instead of Products deletion, I’ll see alternative like Price = 0€, or Quantity Available = 0 (or -1) or something else I could even purge (myself) quickly afterwards (and after having made a copy) if really necessary :wink:

1 Like