New Bug Encountered:Data table not accessible due to "Internal Error" with all Office365 apps

[GreenFlux]

One of my accounts is getting the same error in two different apps- both using Office365/Excel as a datasource.

There are many other tables but the error only mentions one being inaccessible. I’ve tried clearing cookies/history, different browser, tablet, etc.

[pravse]

Yes, more nasty stuff associated with the old API. By reverting to that, we’ve rolled back a year and a half of improvements in user experience. But it was the only way to sort-of get the apps semi-functional again. I still haven’t heard back from Microsoft.

[Harry2]

@Appsheet-GLM A side effect of reverting to the old API is that you won’t be able to make updates to your spreadsheet via the app if the spreadsheet is also open in Excel Online. We ask for your understanding and patience as we reach out to Microsoft to resolve this.

[Engie_Peru]

@praveen
I continue to have the problem

[Engie_Peru]

and I can´t deploy too

[Harry2]

@Engie_Peru I’m still investigating the deployment check issue and will get back to you shortly.

[Harry2]

@Engie_Peru The deployment check error occurred because the data source name of a table in your app does not match the name of the Microsoft data source in your account. I have sent you the instructions on how to fix this.

[Engie_Peru]

thank you

[Harry2]

@Engie_Peru Hi, this is an unrelated problem, which I have responded to your app user Daniel. The account 537838 is a Google account, but the authentication provider of the app is set to be Microsoft. To fix this, you need to change the authentication provider to “Any”.

[pravse]

Please feel free to vote-up this issue in StackOverflow … perhaps Microsoft will notice. https://stackoverflow.com/questions/57576896/how-to-fix-bug-starting-8-20-2019-usedrange-returns-nul...

[Alejandra_Petro]

I’m having this problem too, However, not sure if this problem has something to do with my spreadsheet behaviour this morning. I got a large data and as you can see in the picture I m not able to clear the filters even though rows are jumping from 1 to 1000…

and some data, for example, the one highlighted in yellow is also missing on the app but they are showing into the sheet. not sure what is happening.

[Harry2]

@Kyle_Richardson Rows are missing from your app because a column in the app contains a multi-row sheet formula even though the column is marked as editable in AppSheet. I’ve sent you more details about the problem and how to fix it.

[Harry2]

@Alejandra_Petro Hi Alejandra, are there missing rows in your app as well? If not, most likely the problem that you saw in Excel Online did not occur in AppSheet, but in Microsoft. We believe there are some ongoing issues in Microsoft servers that affect not just third-party applications such as AppSheet, but also Microsoft’s own products such as Excel Online and Office 365. We are still waiting to hear from Microsoft about this.

[Alejandra_Petro]

Hi @Harry, yes I am missing several rows in my app. The data is on the sheet but it is not on the app.

[Harry2]

@Alejandra_Petro Could you give me the names of the affected app and table? Also, please check to make sure that there’s no column in your app that is editable and has a sheet formula at the same time. If a column contains a sheet formula, it must be read-only in the app.

[Alejandra_Petro]

I do not have sheet formulas in my table.

App’s name is LOADDATA-899227 (AGSpread 2.0) and the table’s name is Load Description 2.0.

thanks for your help!

[Harry2]

@Alejandra_Petro Could you give me the name of the table with missing rows?

[Alejandra_Petro]

The table is “Loads”. The sheet is “Load Description 2.0”

[Harry2]

@Alejandra_Petro Hi Alejandra, a lot of rows in table “Load Description 2.0” contain multi-row formulas in column “Delivery Duration”. Because the column is marked as editable, any row that has a formula in this column will be ignored.

Could you check the spreadsheet again to see if you can find the formulas? Please note that not all of the rows will have a formula in the column “Delivery Duration”.

If you want to keep the formulas, you will need to mark the column as read-only in AppSheet. If you want to allow app users to modify the column in the app, you will have to remove all of the formulas.

[Alejandra_Petro]

I can not do anything with the spreadsheet because for some reason the rows are not showing . The only rows I can see are the ones showing in the previous photo attached. Is this because of the multi-rows?

[Harry2]

@Alejandra_Petro The problems in Excel Online appear to only affect very large spreadsheets. If there are a lot of rows in your table, you might not be able to edit the spreadsheet directly in Excel Online right now. A possible workaround is to download the spreadsheet to your computer and then edit it using the desktop/offline version of Excel. Once you have finished removing the formulas from the spreadsheet, you can then upload it back into Office 365 or OneDrive.

[Alejandra_Petro]

@Harry I just fixed some of the data in column “Delivery Duration” and they are now showing on the app. Thanks a lot for that!. My question is Why is this happening now? because I have not done any changes to the sheet and I have never used formulas in it. I apply the formulas into the App no into the sheet. is this because of Microsoft?

[pravse]

Yes, we were forced to move to an older API (because the “new” API — what we’ve been using for two years) is broken. That older API has a host of other issues.

[pravse]

FYI, we have escalated the issue five times to increasingly senior Microsoft contacts over the course of the day. At this point, we have escalated to Jeff Teper, Corporate VP of the Sharepoint division. Received an ack from him and he delegated to a couple of people who have not responded back. Our last option tomorrow is to escalate to the President of the Office division. If that still doesn’t lead to an actionable result, we will advise affected customers to move at least temporarily to Google Sheets, and help make that transition as smooth as possible. We understand (and share in) the frustration you all feel. At the moment, we do not have any other solution to offer.

[Sakis_Koukouvis]

My apps are OK now, with the old API

I would like to know how easy is to move my data to Google sheets while the users log in with Office365 accounts

[pravse]

Your data can live on Google Sheets. User authentication is independent of where the data lives. If this issue persists, tomorrow we’ll write and publish an article telling you how to migrate.

[Alejandra_Petro]

yes pleasee!!! that would be very helpful

[pravse]

Brief version:
a) import your xlsx files into Google Sheets … this is a manual process. There may be some spreadsheet formulas that do not move over.
b) In your AppSheet account, add a Google Drive data source if you don’t have one (Accounts -> Data Sources)
c) in your AppSheet app, go to each table definition (Data -> Tables), click on the Source property, and change it to the corresponding Google sheet (instead of the Excel sheet)

That’s all you should need. You can also do this by copying the old Office365-based app into a new app, copying the data to your Google drive. But then you’d have a new app and have to distribute that. This might be a good/quick way to first test if your app will work with Google Sheets.

[pravse]

Btw, a response from Microsoft (from the Group Program Manager of the Office Extensibility team)… I think this is good news. They ack that there is a problem, they are aware of it, they are working on a fix, and they think it has already been broken for an unreasonably long time. So they’re taking it seriously.

Hi Praveen,

Apologies for the delay in our response; I was out of the office today.

The issue was identified this morning and the engineering team has been working throughout the day to identify the fastest possible resolution, so rest assured that folks are treating it with extremely high priority - we definitely understand the impact of API breaks, and it’s extremely rare that an issue takes this long to remediate, but folks are working even now to resolve, and we will have an update for you tomorrow morning.

[pravse]

Another update from Microsoft ,… their engineering team is starting to deploy a fix and they expect to have things back to normal in the next two hours. Unfortunately, that will be something like 1:30AM our time. We’ve decided we will not try to test and deploy a change at that time — very likely something will go wrong, we will be half asleep and will break something we shouldn’t.
Instead, we will evaluate in the morning. Assuming Microsoft gets it out as planned, the earliest AppSheet customers will see this fix is maybe around 9AM to 10AM PST.

[pravse]

FYI, Microsoft has deployed a fix. It is too late at night for us to test that change thoroughly and deploy it. Instead, it will be the first thing we work on in the morning.

[Eleshia_Robinso]

How is it going with the Microsoft fix?

[Farzad]

We’ve just finished deploying the fix to both US and EU regions.

[Eleshia_Robinso]

My users just attempted to sync for the first time since the new deployment. They indicated that it is now taking about an hour to sync 30 changes (the number 30 is showing in the sync icon). This still seems quite slow, but I’m checking with the team to determine what their average sync time was for that number of edits, prior to the issues beginning yesterday. Found out their average sync time was usually less than 15 min on 4G service on their mobile device.

[Harry2]

@Eleshia_Robinson Hi Eleshia, I was unable to find the specific log entry that took one hour in the performance log of your app, since your app has a lot of activities. Could you give me the approximate time when this happened? Also, an hour is a very long time, and normally the connection should have already been terminated, or a timeout error should have occurred before the operation could reach one hour.

I can see that most of the recent adds and updates in your app take between 5 - 15 seconds, and most of the syncs take about 25 - 30 seconds. I then compared this against the performance of your app 3 days ago before the problem in Microsoft occurred, and the performance was comparable.

Could you check with your team again to see if the app is still consistently slow? If so please let us know right away so we can escalate this to Microsoft.

[Eleshia_Robinso]

I’ll check with them and determine an approximate time for the lengthy sync.

[Eleshia_Robinso]

The long-running sync occurred around 2 or 2:15 today Eastern Time.

[Harry2]

@Eleshia_Robinson I’m looking for the slow log entries. Is your app working normally again right now?

[Harry2]

Hi @Eleshia_Robinson, was it 2:00 AM or 2:00 PM EST? Between 1:30 AM and 2:30 AM EST, there’s no activity in your app’s audit log.

Between 1:30 PM and 2:30 PM EST, I couldn’t find any operation that took more than about 40 seconds. Most of the syncs were between 20 seconds and 30 seconds, and most of the adds/updates were between 5 seconds and 15 seconds. Even if a user had 30 adds/updates queued up, syncing all of them shouldn’t have taken more than a few minutes, not an hour.

Is the app’s performance right now comparable to what it used to be before the problem occurred (2 or 3 days ago)?

[Eleshia_Robinso]

The users have not reported any further issues with slow sync times. Thank you all!

[Kyle_Richardson]

Earlier today I did what Harry recommended and was able to see all of my records

Now however, I’m just repeatedly timing out when trying to sync.

Any thoughts?