Publid Forms: Force Dropdown to Refresh Data in Code?

Hello, as a workaround for not having a bespoke method for reading data from Excel or a SharePoint List in a public form, I am using a Dropdown field linked to Excel. The worksheet has a single column of JSON-encoded strings, from which I create an array of objects in the form.

Right now, I can build this array if I wait a bit of time (5 seconds or so) for the dropdown list to populate after form loads, then the data is usually there... But the duration would be highly non-deterministic, I imagine.

Is there a way to synchronously force a dropdown field to refresh its data so that I can be sure the data exists before trying to construct my object array?

Thank you in advance for any assistance.

Edit: or alternatively and better, to register an asynchronous callback function to be called when the data is filled?

Edit 2: I "solved" this by writing an interval function that repeats a check every half second until the data is loaded... It would still be nice to know if there was any other method.

Hello @chs-web,

What exactly do you want to achieve?

As I understood you don't need the data to be displayed within a dropdown field. Why do you need to create an array of objects? Why do you need it?

Thank you for the follow up Margarita.

We need to retrieve data from a server for use in a public web form. We could load the data from a script file on our web site but that would make it public-facing, and unacceptible from a privacy standpoint. So instead, we want to do so from our Microsoft 365 tenant.

In the absence of a supported way to load data from Excel or SharePoint Online in public web forms, we are using a drop down control as a work-around. It is doable if not ideal. I have read other posts here that you may be working on bringing support for Excel data in public forms. That would be our preferred solution when it is availble.

I have sinced solved my design problem by using a interval function to detect when the control has filled its data. All good for now.

Hello @chs-web,

Could you please provide more details about your case. Why do you need to create a new array of objects and not just use the dropdown filed connected to Excel file?

I will pass the information to developers team, and they'll think on possible solutions or adding new functionality.

Hi Margarita. It's a straightforward requirement: we just need to lookup data from Microsoft 365.

For example, one of our forms allows people to register for events. To do this, we need to check if they are already registered, or look up other details we have on file for them that may be relevant to their registration.

We don't need new dropdown features--the only reason we are using this is because it is the simplest way to securely retrieve data into the form. (As I said, we don't want to load data directly from our web site due to privacy/security reasons.)

What we really need from your developers is a supported way to access SharePoint lists or Excel Online spreadsheets from a public web form. I've seen the published method using Azure and Microsoft Graph, but it is too complex for us.

I hope this helps.