GOTCHA: Excel Crash with non-absolute valued properties array

[wanderslth]

Something to track as issues end-users may face with their spreadsheet experience.

In this scenario with wikipedia (using gsheets here for screenshot simplicity):

Formula DOES NOT use absolute positions ($):

=FLEX("flexiodemo/wikipedia-enrich-people",A2, B1:F1)

1. Drag down one row. Excel has the data available in the the previous row, so delivers a blank row as expected.

2. Drag down all rows. Excel has no data to work with until the previous rows are calculated. Excel freezes and requires forced shut down.

Sheets handles this with more grace -- although you can tell by watching the 'loading...' message, that their calculation engine doesn't take calculations in order -- but more 'all at the same time' so you see it working itself out as the 'loading...' bubbles around. Presume excel works similarly and just doesn't know what to do with the timeout.

TL;DR - people are going to need to be careful when they reference an array of properties.

[dzwillia]

This probably needs to be filed as an issue in the add-on repos as well. It may actually be more of an issue that needs to be fixed there — e.g. I should know (and do) if a cell is a custom function and if so, I can check the syntax and if it ‘=FLEX’, I can opt not to send the API call. This also seems like a great thing to let users know about in a section in the docs on our website (e.g. best practices: use absolute cell ranges).

…

Sent from my iPhone On Nov 8, 2019, at 5:53 PM, Ken Kaczmarek <[email protected]> wrote:  Something to track as issues end-users may face with their spreadsheet experience. In this scenario with wikipedia (using gsheets here for screenshot simplicity): [Untitled spreadsheet - Google Sheets]<https://user-images.githubusercontent.com/25911712/68515718-807f9580-0247-11ea-8318-c75498337a77.png> Formula DOES NOT use absolute positions ($): =FLEX("flexiodemo/wikipedia-enrich-people",A2, B1:F1) 1. Drag down one row. Excel has the data available in the the previous row, so delivers a blank row as expected. 2. Drag down all rows. Excel has no data to work with until the previous rows are calculated. Excel freezes and requires forced shut down. Sheets handles this with more grace -- although you can tell by watching the 'loading...' message, that their calculation engine doesn't take calculations in order -- but more 'all at the same time' so you see it working itself out as the 'loading...' bubbles around. Presume excel works similarly and just doesn't know what to do with the timeout. TL;DR - people are going to need to be careful when they reference an array of properties. — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub<#572>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAKSHDF23CWB64LPYJ2AKSDQSXUVHANCNFSM4JLABZRA>.

[wanderslth]

Yeah – this is why I titled it 'GOTCHA:' for now – there are various 'quirks' of the spreadsheet people are going to have to understand (how things refresh, whether they're getting dinged for multiple api calls every time they change the format of a header, etc). Figured we could start accumulating them and have a nice chunky docs section on it as we go (tips and tricks for using excel, etc) From: Dave Williams <[email protected]> Sent: Friday, November 8, 2019 5:00 PM To: flexiodata/flexio <[email protected]> Cc: Ken Kaczmarek <[email protected]>; Author <[email protected]> Subject: Re: [flexiodata/flexio] GOTCHA: Excel Crash with non-absolute valued properties array (#572) This probably needs to be filed as an issue in the add-on repos as well. It may actually be more of an issue that needs to be fixed there — e.g. I should know (and do) if a cell is a custom function and if so, I can check the syntax and if it ‘=FLEX’, I can opt not to send the API call. This also seems like a great thing to let users know about in a section in the docs on our website (e.g. best practices: use absolute cell ranges).

…

Sent from my iPhone On Nov 8, 2019, at 5:53 PM, Ken Kaczmarek <[email protected]> wrote:  Something to track as issues end-users may face with their spreadsheet experience. In this scenario with wikipedia (using gsheets here for screenshot simplicity): [Untitled spreadsheet - Google Sheets]<https://user-images.githubusercontent.com/25911712/68515718-807f9580-0247-11ea-8318-c75498337a77.png> Formula DOES NOT use absolute positions ($): =FLEX("flexiodemo/wikipedia-enrich-people",A2, B1:F1) 1. Drag down one row. Excel has the data available in the the previous row, so delivers a blank row as expected. 2. Drag down all rows. Excel has no data to work with until the previous rows are calculated. Excel freezes and requires forced shut down. Sheets handles this with more grace -- although you can tell by watching the 'loading...' message, that their calculation engine doesn't take calculations in order -- but more 'all at the same time' so you see it working itself out as the 'loading...' bubbles around. Presume excel works similarly and just doesn't know what to do with the timeout. TL;DR - people are going to need to be careful when they reference an array of properties. — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub<#572>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAKSHDF23CWB64LPYJ2AKSDQSXUVHANCNFSM4JLABZRA>. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#572>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AGFWDICZ4EH4J27KFO6ABXDQSXVNZANCNFSM4JLABZRA>.