All rows from spreadsheet not getting pulled ...

[Chris_Creasman]

All rows from spreadsheet not getting pulled into Table

I am not sure what has changed but when I regenerated columns now all my rows are not coming in.

When I go to tables and view data only 34 of 59 rows are shown.

I created a blank test app and connected to the same data and it still only pulled in 34 of 59 rows.

Any ideas?

When I revert back to before the regenerate then all the rows are pulled in.

Can’t see anything different the data.

[Phil]

@Chris_Creasman

Can you try the following experiment?

1. Get your app back in the failing state where some rows are not being returned. 2. In the Editor, pick one of the fields containing an ArrayFormula and clear the “Spreadsheet formula” property in the Editor.

2. Make sure that field is still marked “Read-Only” so no one can write a value to it. 4. Save you changes.

I think this should work for the following reason.

After we read the data, we check each row to ensure that it is not empty. We use the technique I described above. We skip ArrayFormula fields for the reason I described above. By clearing the “Spreadsheet formula” property you trick us into ignoring the ArrayFormula which is actually present in that field. As a result, we see the data value in that field and treat the row as a non-empty row.

I do not know how you got your app into that state, but this is essentially why version 188 worked. The “Spreadsheet formula” for field “Auction Provided Notes” was blank. As a result, we ignored the Array Formula in that field.

[tony1]

@Chris_Creasman Can you take a look at the “If Some Rows from the Sheet Are Not Visible in the App” section in this article and see if any of that applies to you? https://help.appsheet.com/troubleshooting/debugging-your-app/app-changes-are-not-captured-in-the-spr...

[Chris_Creasman]

None of that seems to apply.

None of the data is created by the app just accessed and modified.

I copied the formatting from the first row to all the other rows.

All data was showing before doing the regenerate.

I am using rows as the key right now but don’t have a better identifier at the moment.

Since that has not changed I would not think that is the issue.

[tony1]

@Chris_Creasman Can you give me the app name, the version # for the broken version and the version # for the working version? Also, could you share your spreadsheet (press the “Share” button in Google Sheets) with tony@appsheet.com

[Chris_Creasman]

App name is “Foreclosure Sale” This version will work until column is regenerated 1.000188 This is current version and not working 1.000190 I sent you edit access to the spreadsheet. Thanks

[Phil]

@Chris_Creasman

The problem started occurring when you added an array formula to column K “Auction Provided Notes” and regenerated to create version 189 of your app.

When we read your worksheet, we need to determine which rows contain valid data. For example, in your worksheet there are 2000 rows but most of them are empty. We detect empty rows by looking at each row in the worksheet and seeing if any data field in that row contains at least one non-empty field value.

If at least one non-empty field value is present we know that the row is not empty.

Array formulas complicate this. A field with an array formula can always have a value even if the row is empty. That is the nature of array formulas.

Consequently, we ignore any column having an array formula when deciding whether a row is empty. Rows 35 through 59 of your worksheet only contain non-empty values in the array formula columns.

No other fields in those rows contains a non-empty value, so we treat rows 35 through 59 as empty rows and discard them.

You can fix the problem by ensuring that at least one field in every row in your worksheet contains a non-empty value. Again, columns that contain array formulas are ignored, so make sure the non-empty value is in some other column of the row.

I have added a new article describing this problem to make it easier for the next user who encounters this issue. See https://help.appsheet.com/troubleshooting/debugging-your-app/rows-are-missing-when-reading-your-spre...

[Chris_Creasman]

@Philip_Garrett_Appsh I have not seen this in the past as most of my spreadsheets are setup this way and this app has worked correctly for several months.

Your response does make sense but do not understand how I have been getting away with it previously.

Even version 1.000188 would see the data that is no longer seen after regenerate.

I however went through and changed several array formulas to ={Data}.

This did not correct the issue either.

What is the best way to get new data from a sheet to a referenced appsheet so that the data is recognized?

Without writing a script to insert data or manually adding data to each row.