Connect with Google Sheets - How it works

It isn't supported to be a database. Behind the scene, Google doesn't provide some essential features.

For example, it lacks support for data searching. Let's say, we have a customer spreadsheet. If we need to filter customers that have John as the first name, we need to create a formula. Then produce the result into a hidden Sheet. Then delete the sheet once we got the results

Another limitation is the lack of support for record ID, which can be ambiguous in some cases. For example, when John made an order, the system will create an order record, which refers to John, and the items he bought. In case we use references like Sheet1!A2:A3, when we move John, the order will point to whoever it is in Sheet1!A2:A3.

Though, we managed to complete the essential features, by adding a helper layer. This helper stores part of the data as metadata and attached with an internal ID. Then later use to perform essential data queries.

For example, when filter customers with their name contain John, the helper will search through its internal database. Then use the internal IDs to get the latest results from Google Sheet.

To sum up, Google Sheets lacks some essential features compare with an actual database system. We've managed to build a helper layer, which uses its internal metadata, make it easy to perform data queries.

Feel free to leave your feedback, and thanks for your support.