Situation
Your client needs a website where they can manage (CRUD) their products (code, name, price, category, etc.). They know that there'll be times when they need to update the prices or other properties massively, so they want a solution that allows them to do so without having to update every single product independently with the typical update form.
My thoughts
I've seen this requirement ☝️ come up in many different software projects. The most common approach to solve it was to let the client upload an Excel sheet with the updated records, then the software iterated through each row and updated the corresponding record in the database.
I also saw the excel approach fail because users messed with the format or maybe left a "not null" value empty, etc. I always thought there must be a better way.
I'm thinking maybe the Google sheets API could be used to provide a more robust solution 🤔, but I'm not familiar with it yet.
Discuss
Have you ever faced this issue? Can you think of a better solution? 🎤
Edit: Just to clarify, I'm not actually dealing with this issue. I just thought it'd be something interesting to discuss here!
Top comments (9)
Bulk data can always contain errors, whether you're parsing CSVs or Excel workbooks row by row or COPYing direct from a file or program. There isn't really anything to do about it other than perform what validation you can up front and use transactions so you can roll back if a problem slips through. If you're receiving the source data from another party, supplying a standalone validator to them could at least help cut down on the number of aborted ETLs.
You're completely right, bulk data will always be troublesome, especially when it comes from another party.
Users don't always know that. I believe it's our job to come up with some alternate solutions that solve the problem and reduce headaches from both parties.
A long time ago I worked for a software company that was maintaining a web app that had this "update by spreadsheet" feature. I can't tell you how many times users came asking for help because the software rejected their file. And usually when we opened the file the data was completely wrong. That problem could've been solved with a standalone validator like you said, but we weren't allowed to develop new features for that project, so we had to just keep fixing excel files over and over 😥
There are a couple approaches we have used in the past:
Challenges: just like with using spreadsheets, it's common to make accidental changes to the data that are not detected when it happens. You can deploy various helper strategies to mitigate this, such as making the user click on a cell before allowing it to be editable.
Challenges: it requires more upfront cost in analyzing the business and figuring out which functions will yield the best cost-to-benefit ratios.
Hope that provides you with a couple more ideas, and good luck!
Thanks for sharing your experience!
This ☝️ is the solution I always think of whenever I see this issue. It's simple and manageable. Sometimes users want/need more freedom, but even if that's the case, this solution might be good enough for an MVP.
Let's talk a little more about the problem you're trying to solve!
You could expose a Google Sheet and let users be able to edit it as they wanted. That would kind of work. But... then you'd need to turn the Google Sheet into the source of truth, rather than your database. (Otherwise, you'd run into some really awful synchronization problems! Two-way data sync is an evil, evil thing.)
Sometimes it's great to turn a Google sheet, or an Airtable database, into the source of truth for your product. That lets you bypass a lot of admin UI setup. But it also lessens the control you have. Maybe you need to kick off a background job to update the search index whenever someone updates a product! You can do this easily if folks are submitting product update forms through your webapp, but less easily if you're relying on an external source of truth for your admin UI.
Another solution to your problem: you can make a bulk updater that's failure-aware.
It sounds like the reason you're having trouble with bulk updates is that your code "expects" or "needs" the spreadsheets folks are uploading to be perfect. But as Dian Fay points out, bulk data is always going contain errors. This can be ok if you treat a Bulk Update as a first-class citizen in your app, just like Products are.
What if, when a user uploads a spreadsheet, that creates a new Bulk Update record in your database? Then, you have a chance to pre-validate the information and echo back errors to the user before you run the update. You also have a place to store information about partial update failures as you update all the rows. It gives you a lot of flexibility about how to handle errors. Do you want to roll back the update entirely and let the user retry after they've corrected their errors? Do you want to update everything you can update, and then let the user know that there are a few products they'll need to manually correct? I can't say what the correct choice for your application is. I can say that treating bulk updates as their own kind of record will give you the flexibility to find the correct choice.
Wow, thanks for your comment! Those are some really good points you made there.
Just to clarify, I'm not actually dealing with this problem (luckily), I just saw it happen a bunch of times in different projects and thought this discussion was a good fit for dev.to! Sorry I forgot to mention that on my post.
The reason I'd try to avoid Excel sheets is because I saw the frustration on some users when they uploaded a spreadsheet and the software rejected it because of some incorrect value in a specific cell.
However, this issue could be solved with a standalone solution that performs the validations while maintaining a better UX at the same time, like @dmfay and @tux0r proposed.
To answer some of your last questions, even though this is just a hypothetical situation, I believe that updating the records that "passed" the validation and then allow the user to fix the ones that didn't would be a better UX in most cases, unless the records are somehow related to one another.
When I solved this problem before, there were two pieces to the solution. First a way to filter the data down to the specific items the user is interested in bulk editing. Second, a bulk edit form that was similar to the regular edit form. But you could only set data.
The user would first perform a search to filter down the items they wanted. Then those IDs would be passed to the bulk edit form.
I later added a convenience to the form where as you made changes it would send the edits to the server in Test mode. So all validations would run but nothing would be saved. If there were any errors, they would be displayed under the item where the edit failed. That was in a browser-based client. Doing that on the server side before loading the page might significantly delay page load.
That's an interesting solution, thanks!
It would increase the cost of the software, of course, but it would definitely be easier to provide a better UX through a desktop application rather than validate everything server-side and then have the user fix the issues and re-upload the whole spreadsheet.