If you have an app’s account whether it be a Shopify or Google Analytics, moving its data to a spreadsheet program might be a bit harder than you think. For a Google Sheet or Excel, there are a few issues when it comes to handling your data coming from an application.

First, modern applications use APIs to talk to each other. The APIs are a series of JSON objects that can’t be directly inserted into a spreadsheet. Since spreadsheets are essentially relational data tables, the JSON objects need to be parsed into a tabular format as well.

Secondly, your app’s data may change and it can be difficult for a spreadsheet program to handle. If you download the app’s data as a flat CSV file, you can easily insert it into a spreadsheet. Though your app’s data may update constantly, and it may mess up your spreadsheet’s structure.

Lastly, sending data from your app to your spreadsheet can create performance issues. If the data size from your app exceeds 100k rows, it could significantly slow down your computer’s speed. If you set up multiple connections with your app from your spreadsheet, it could also crash your spreadsheet program.

How to send your data from an application to a spreadsheet then? Here are a few things to consider.

Syncing data between a database and a spreadsheet requires a data pipeline

First, how does your spreadsheet sync with your database? A database typically comes with a rigid structure and schema. This way it can update logically and regularly. A spreadsheet however is a grid of cells without a structure. When you send data from your database to a spreadsheet, you won’t be able to manipulate the data like you would with a static dataset. However, you can still use the database's data as a reference table on a spreadsheet. Most transformations are better off done via the database than a spreadsheet.

Spreadsheets can only support a limited number of data records

Second, most databases store a lot of data records. A spreadsheet program typically only supports up to a million rows. When sending data from your database to a spreadsheet, you may have to segment or shrink the table size. Again this step should be done with a database rather than on a spreadsheet.

Receiving data on a spreadsheet requires an API endpoint

The most common way to receive data on a spreadsheet from a database is via an API. On a Google Sheet, for example, each API is automatically called/requested on a fixed integral. I believe that you can set an “autorefresh” trigger on a Google Sheet. Here’s a tutorial on how to do that. When an API endpoint hits your spreadsheet, it should look like a relational table that periodically updates. You can use it as a reference table for any additional data modeling/manipulation outside of this API-powered table.

To drill down on the details, here are a few solutions to how you can send app data to your spreadsheet.

Solution 1: Build a database and create an API

If you want to connect an application’s data to a spreadsheet, having a database is critical. This way you can keep an organized collection of records for the APIs you request from the app. However, you will need to design and construct tables for the App API. This can be a complex task since each application can have a very different schema design and key columns that connect tables.

Once you have created a database, you can create an API to send data to your spreadsheet. Here I used Node.js as a backend service and MySQL for storing all the Shopify API data. The structure looks like this:

Solution 2: Use an ETL tool to migrate data

ETL stands for Extract, Transform and Load. It helps combine data from multiple data sources into a single, consistent data store that is loaded into a target system.

There are many ETL tools on the market. One core advantage of an ETL tool is that the connection between one application and another should have been established in the past. No need for you to write anything from scratch. Using an ETL tool should provide a stable, and consistent data pipeline between your application and Google Sheet.

Most ETL tools have a variety of connectors that should not take you much time to set up. After verifying your credentials, you should be able to extract the data and load it into Google Sheet fairly quickly.

However, ETL has its limitations.

1. You must be relatively familiar with database engineering in order to design a good pipeline that does not break.

2. It isn’t the fastest way to move data around. When a pipeline is required, it can take a bit of time to transport data.

3. Once it’s been set up, it’s hard to make changes retroactively.

4. When it breaks, it’s hard to debug.

Solution 3: Use a cloud database to support your spreadsheets

Another option is to connect your apps with a cloud database. A cloud database “talks” to your applications via an API and it can store, index, and back up the data permitted from your application providers.

Take Shopify as an example, here are the steps for you to send its data to a spreadsheet program.

Connect to a database

First, connect to your database by verifying your credentials. Some databases may require whitelisting the product’s IP address.

Transform and maintain your database

After connection, you will have created a database “resource”. You can use this resource to create a project. Here I have a project called “Test”.

Within the project, you can import any table from your database so you can transform and maintain it. You can use the “clock” button to request manual sync or you can schedule data sync so the data is updated every one minute/hour/day.

Send it to Google Sheets or Excel

Now you can go to the “cloud” button on the top right to export the data to Google Sheets.

Try using the =importdata(“”) function to send data to your Google Sheet.