Leveraging the Power of the Google Sheets Database
In this guide, we’ll step through how to create a Google Sheets database, without any coding necessary. At the end of the guide, we provide a link to a free Azuqua workflow that allows you to get started using a Google Sheets database in less than five minutes (which is pretty awesome). This workflow allows you to read information from the Google Sheets database through an Azuqua workflow.
If you’re only interested in the implementation details, skip ahead to Implementing A Google Sheets Database to get up and running on Azuqua with a new Google Sheets database. Otherwise, read on to learn more about the advantages and disadvantages of using Google Sheets to house your data.
Why use a Google Sheets Database?
- Google’s powerful, built-in spreadsheet functions and tools.
- Data transparency, data visualization, and ease-of-access.
- Simple graphical user interface for viewing and manipulating the data.
When to use a Google Sheets Database?
- Simple relationships between the data. If you require complex relationships, it’s better to use a relational database system, such as PostgreSQL, MySQL or MS SQL.
- Read-only records, or if speed doesn’t matter. One of the reasons to use a traditional database is for quickness of transactions. As you are interacting with your Google Sheet database via an API, this is necessarily a longer and more costly operation.
- Creating powerful, constantly evolving reports and dashboards. By placing data inside a Google Sheet, you natively get access to all the reporting, visualization, and data capabilities that Google Sheets has to offer. With the database constantly being populated by new data, these reports and dashboards can be updated in real-time to reflect what’s occurring with the newest information available.
Implementing A Google Sheets Database
When we talk about creating a Google Sheets database, what we’re actually talking about is creating a persistent data store, with definable columns and tables, where you can safely store important information as rows. This data can then be referenced by other workflows, processes, or services that may need access to the data.
Azuqua offers a free workflow that allows you to read data from a Google Sheets database, and return that data to any other external services. Additional functionality (like inserting, updating, and deleting data) can be created in Azuqua, as well.
There are a few steps outside of Azuqua that are necessary before we step into that workflow, though.
- Create a new spreadsheet inside Google Sheet.
- Define the column names. These will be where your values are stored. Note: this is a very important step. Defining a column name is how you will define the data schema of your Google Sheets database. While the column names can be changed, this may result in unsanitary data inside a specific column, reducing the overall value/integrity of the data.
- Name and save your sheet. Here’s an example of how you might structure a Google Sheets database:
That takes care of the outside of Azuqua steps. Next, we have to configure the workflow that will allow you to insert specific data into a Google Sheet. This workflow is built inside of Azuqua.
- Click here to get started with the Google Sheets Database widget inside Azuqua. This widget will create a new process flow for you, containing example logic for getting up and running on Google Sheets.
- Once you’re inside Azuqua, click on the ‘Google Sheets Database’ FLO that is contained within the My FLOs area of the dashboard.
- Once the FLO has loaded, you will see a series of cards that contain both input fields, and draggable output fields. Azuqua uses an event-driven flow paradigm, meaning that certain events will cause the workflow to trigger. With this flow, we’ve chosen to trigger the workflow whenever a URL associated with the flow is hit. You can find the URL for this flow by clicking ‘Endpoint Settings’, located at the bottom of the API Endpoint card.
- Now, we’re going to need to configure the Google Sheets – Search Column card and the Google Sheets – Read Row card to have access to the Google Sheet you created earlier. On these cards, you will notice that there are three tabs available. The first tab on the card is where you can authenticate your Google Apps account, allowing data to be inserted into the Google Sheet you specify.
- Authenticate your Google Apps account by clicking ‘Add Account’ on the first tab of the card.
- After authentication, you will be given a choice of spreadsheet and worksheet to select. Choose the spreadsheet and worksheet where you’ve established your database, as defined in the previous steps.
- On any subsequent Google Sheets cards you may add, the authentication will have already been added for you, but not selected. You must go through the process of selecting the newly created Google Apps authentication, and choosing your spreadsheet and worksheet for every Google Sheet card you have inside the FLO.
- Once each of these cards is configured, you’re done! You now have a workflow that can read data from your Google Sheet, based on a specified value. Test it out by clicking ‘Test’ inside Azuqua, and supplying a search value to the “value” input field. This will emulate the API endpoint URL being hit, and a specific value being provided as a query parameter inside the URL.
- You can create additional flows that allow you to perform other actions on your database, such as inserting, deleting, or updating the data.
- If you have any questions about how these process flows work, or how you can create additional functionality using Azuqua, you can contact us at firstname.lastname@example.org, visit our [help documentation], or visit the Azuqua Community to ask a question! We are very active, and would love to field any questions you may have.
Azuqua Tables, an (Awesome!) Alternative to Google Sheets Databases
Azuqua Tables is a native feature of Azuqua. This means it is much, much faster than working with an integration like Google Sheets. Azuqua Tables is a great alternative to Google Sheets when you’re looking to persist data in a simple, intuitive way.
We found ourselves using a number of different Google Sheets databases for a variety of projects, especially projects centered around metrics, reporting, and analytics dashboards. We realized that while the Google Sheets method was effective, it was ultimately limited by being an external service. No matter what, we’d be limited by the speed and rates from the Google API.
Simultaneous to realizing this issue, our team was internally developing Azuqua Tables, which allows our users to persist data across various workflows. This in and of itself was a powerful feature, but we also realized that Azuqua Tables could easily (and more powerfully!) fulfill the same role as our Google Sheets databases.