Formula leads to all empty rows in Google Sheets showing in ListView

[Cami_Carrot]

Hi - am am currently working through METRIC RAT'S AIi's CRUDII Google Sheet App example and have picked up a problem of my own making.

I changed the first column of the google sheet to an index column with the following formula in cell A1

={"id";ArrayFormula(if(B2:B<>"",row(A2:A)-1,""))}

which automatically updates the ID number with each new entry. The problem I picked up, here and in other apps I am experimenting with is that as soon as you call data with ListView as that it lists all vacant rows until the bottom of the sheet which seems wasteful of resources.

Can anyone help me with a way to limit the listing of vacant rows using that or similar formulas.

[TIMAI2]

Remove all the empty rows on the spreadsheet below your data.

or use a different formula, try this:

={"id";ArrayFormula(if(filter(B2:B,B2:B<>""),row(A2:A)-1,""))}

or best practice, do both !

[Cami_Carrot]

@TIMAI2

={"id";ArrayFormula(if(filter(B2:B,B2:B<>""),row(A2:A)-1,""))}

Thanks - tried this but it brought up an error message as follows:

For the data as follows

id	first_name
#VALUE!	B
#VALUE!	Henry
#VALUE!	Ivan
#VALUE!	Carl
It gave error:
Error Function IF parameter 1 expects boolean values. But 'B' is a text and cannot be coerced to a boolean.

I'm not sure why it did this because it worked with text in the original form.

[TIMAI2]

Yes, seems no way around that one, sorry. Works OK with a numbers column.

Try this one:

={"id";ArrayFormula(sequence(MATCH(2,1/(B:B<>""),1)-1))}

[Cami_Carrot]

You are brilliant - works well and stops the empty rows being pulled into ListView.

Problem solved - thanks