Google Sheets Two-Way Databinding?

Question

I want to make a spreadsheet in Google Sheets with a user friendly editable front end and a back end with an arbitrary number of rows containing the data to be displayed. There should be a dropdown on the front end that allows me to select which row (or a new row) in the back end is displayed in the rest of the front end fields; and any edits on the front end should be saved to the back end.

I know vlookup can be used to show data from the backend on the frontend, but I'm pretty sure it's not going to send edits back to the backend. Is there a way to accomplish this using Google Sheets, either alone or with other Google web apps?

Answer 1

Google Sheets formulas (and indeed, all spreadsheet apps' formulas) just take input, do calculations and manipulations on it, and display output. That sounds obvious, but there's a subtlety to it: formulas don't do "actions". Any time you type in a cell, you are overwriting it—not sending data elsewhere. Any time a formula does a sort, filter, or lookup, the original values are untouched. A cell's value should never be the input and output for the same formula.

So your request is just a bit at odds with the universal base of how normalized spreadsheets work. Thus, if you need a two-way data flow, you'll need to extend native Sheets functionality. I have no specific recommendations, but perhaps check if there are Add-Ons or Apps Script solutions for such needs.