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.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
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.