Google Sheets Two-Way Databinding – How to Implement

google sheetsgoogle-apps

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?

Best Answer

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.

Related Solutions

Google-sheets – How to get Google Sheets to treat the form-submitted strings as plain text and preserve leading zeros

Part of my answer would be: this is a known bug (newly received form submissions do not respect the format of the columns that they are landing in, as they did in the old version of sheets).

Depending on what you are doing with the data afterwards, another option would be to reproduce the form submissions on another sheet, with the correct format. For example, if column E contained zip codes that should be 5 digits long and include leading zeros (if required), then you could use:

=ArrayFormula(TEXT('Form Responses'!E:E,"00000"))

on another sheet.

You would need to bring over all your other columns as well, though. Without getting too complicated, this can be reasonably easily achieved in the new version of Sheets, and format the column(s) as desired, with (eg):

=QUERY('Form Responses'!A:G,"select * format E '00000'",1)

Google Sheets – How to Import JSON Using Google Apps Script

As JPV pointed out, the line

var jsonData = ImportJSON(apiUrl);

is not valid in Apps Script (unless you have defined ImportJSON function somewhere). To fetch something from an external URL in Apps Script, one uses UrlFetchApp. So the line could be

var jsonData = JSON.parse(UrlFetchApp.fetch(apiUrl).getContentText());

After this, jsonData is a JavaScript object parsed from the JSON string returned by the server. To import it in a spreadsheet, one has to create a double array suitable for passing to setValues. How to do this depends on the structure of the object.

The pullJSON gist that you referenced gives a simple model of this process, which works when jsonData is already an array. But different APIs work differently: for example, Stack Exchange API returns an object with several "wrapper" properties, one of which is items that holds an array of objects of interest. So in this case one would loop over the elements of jsonData.items, extracting the properties of each object in the array and pushing them into a double array.

Best Answer

Related Solutions

Google-sheets – How to get Google Sheets to treat the form-submitted strings as plain text and preserve leading zeros

Google Sheets – How to Import JSON Using Google Apps Script

Related Topic