Building the COVID City Map
COVID-19 has rapidly changed how the world does, well, everything. Small businesses, in particular, have to rapidly change how they do things in order to stay afloat. As cities rapidly change lockdown rules in different directions, it can be hard to know what businesses are open and what services (delivery, curbside pick, etc.) are available. HERE Technologies, with our partners, have built an application that demonstrates how this information can be both gathered and then shared with the public. We have two examples of this now, one for Liverpool, England and one for Lyon, France.
In the screen shot above, you can see the Lyon, France map with businesses that are sharing information prominently displayed. Selecting one zooms in and gives you details:
If your French isn't quite up to snuff, the detail under Services specify that this bakery offers takeaway service, but not delivery or contactless delivery. (I cheated and just checked the Liverpool map.)
This map was built with user submissions in mind. By clicking the Submit your Business/Ajoutez votre entreprise button, any business can enter their details. These details will be reviewed, tweaked if necessary, and then published. Let's talk a bit more about how that process works.
I worked on the initial version of this demo (the current version was done by our partners and looks far nicer than mine) and initially created a solution using Google Forms and Sheets. It's possible to have a public Google Form write to a public Google Sheet that only has public read access. We tied the Google Form submissions to the Sheet and added additional columns to mark which entries were valid (in case of spam) as well as adding latitude and longitude values. We ended up moving away from this approach, but if this interests you, please read my blog post on how you can do the same.
At HERE we are big users of Sharepoint so we quickly realized that it would be better to switch to Microsoft Forms. The form was built using Microsoft's form builder:
For the most part, this worked just as easily as Google's, to the point where I almost wondered if one or the other was sharing tech. The only issue came in when we wanted to send the resources to an Excel form. Google makes this incredibly easy - you can send form submissions to Sheets with a quick setting change. Microsoft Forms did not. After some searching, I came across an excellent blog post that discussed how to handle this: How to sync Microsoft Excel with Microsoft Forms responses (using Power Automate)
Basically - you end up using another Sharepoint product, Power Automate. I had never heard of this before, but the blog post walked me through the relatively painless process. You do need to be careful if you make any changes to the form itself. Editing questions seemed harmless enough but adding or removing questions may require going back to Power Automate and updating the connection.
Unfortunately, there was no way (well, no way we felt comfortable with) to share Excel data via a public URL. Since the process required human intervention anyway (again, to check for spam as well as figuring out latitude and longitude for a business), we decided to keep it manual and simply export the results as a CSV file that ends up in the same location as the rest of the application. Initially PapaParse was used for CSV processing but that changed D3. Personally this was rather surprising to me as I had only heard of D3 being used for fancy graphics!