Is it possible for a two-way edit relationship in Google Sheets?

3
votes

We know spreadsheets always had one-way referencing.

One good thing for Google Sheets is that it is scriptable.

Is it possible in Google Sheets to have a database-type two-way edit relationship on cells/ranges?

Specific case:

I have a Sheet with raw data. I have another using the FILTER function to show the necessary "searched for" information. It is of course not directly possible to edit the output of FILTER. Could JavaScript/workaround be created to allow editing the outputted information and reflect the changes on the source Sheet?

This would be a great feature, a database-like ability in the simplicity of a spreadsheet GUI.

1
javascriptgoogle-apps-scriptgoogle-sheets
I am not sure if this is possible, but what can I give you is some tutorials that I think can give you an idea on how to work Google Sheets in a Database. Try to check this one, It explains here how to work Google Sheets as a Database with the help of Apps Script and Execution API. It also gives sample code and screenshot for you to follow. You can also check this blog. – KENdi
Thanks @KENdi. I'll check it out and let you know what I'm able to do with it. Good day! – Majal
You might want to look at: developers.google.com/drive/v2/web/push – user4426017

1 Answers

3
votes

Short answer

Yes, it's possible by using Google Apps Script.

Explanation

I posted an answer that shows on way to do this on a similar question on Stack Exchange Web Applications site. The basic idea is to have a on edit event triggered function on each spreadsheet that sends the changes on the spreadsheet to the other spreadsheet.

IMPORTANT NOTE

The on edit event triggers work on a "best effort basis", meaning that sometimes the an edit will not fire the trigger.

References

Extend Google Docs, Sheets, and Forms with Apps Script - Docs editors Help