I have a azure storage account with restricted access to VNet and there is a requirement to connect to it from a public facing azure static web app.
Is there any way to achieve this, I have already tried private endpoint for the static web app but that does not help as it makes my public app private and also my app is behind a front door and it does not support private endpoint for static web app.
Unfortunately this is not possible at this point in time. As you rightly mentioned, Private Endpoints won't help with your issue. You would need VNet Integration functionality so that Service Endpoints (not Private Endpoints - see here for the difference) can be leveraged but Azure Static Web Apps do not support this right now. See this GitHub Issue for a similar request.
If using Service Endpoints is hard requirement, then leveraging an App Service Plan with a traditional App Service might be an option but that would also have a potentially significant impact on costs.