Getting all your Historical data into Google Sheets

Exports to Google Sheets come with a row limit. This usually isn't a problem, but if your business handles large amounts of data, you might need some workarounds. See the steps below.

Managing data across multiple sheets

If your export reaches the row limit defined for your plan, you can still achieve the goal of maintaining all your historical and current data in a single sheet. This is possible through the use of the Google Sheets function IMPORTRANGE, which allows you to reference rows from previous exported sheets.

Each sheet is automatically organized by Shopify ID, sorted chronologically, so the oldest data always appears at the top. This strategy ensures continuous access of data across multiple sheets.

To begin retrieving the oldest data, some manual steps are required. The initial exports should be filtered by date, allowing us to batch the data effectively according to the plan's limit.

1. Run your Initial Export

Conduct your initial export using the default settings. In this example, the store’s order line items will be capped at 15,000, which is the maximum allowed under the current plan:

We can name this export "The Book Store - Order Line Items - 1" to better organize and order our exports:

After the export is complete, we'll see it limited to 15,000 rows:

2. Run the next exports

For our next export, we need to start from where the last record ended. Open the previous export sheet to locate this starting point:

As we can see, the Created At (Order) date is 2024-01-26, which is in UTC. For the next export, we can use the day before this to account for any differences in time zones, ensuring no orders are overlooked.

Let's name this export "The Book Store - Order Line Items - 2":

This export again reaches the maximum limit of 15,000 rows:

Let's review the content of this spreadsheet and ensure that the same items aren't included in both of the sheets we just exported.

Notice that order #4328 appears twice: at the end of the first export sheet and at the beginning of the second export sheet.

We should remove it from one of the two to avoid duplication:

Repeat this process until you reach the more recent items and the export no longer hits the 15,000-row limit:

Once you find that an export stays under this limit, go ahead and schedule it, so the spreadsheet is auto-updated with new changes:

3. Combine all sheets into one automatically

Now it’s time to bring all the data together into one sheet for a unified view.

Create a new spreadsheet and utilize the IMPORTRANGE function to pull data from the export sheets we’ve previously created.

={
    IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BAhGXYHC-NbFsLqUSdf2JHUUn9QWOog9lhjmt4IIm4Q/edit#gid=0", "Data!A3:Z")
}

Make sure to allow access to this sheet:

Next, add the remaining sheets to the formula to combine all the data together:

={
    IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BAhGXYHC-NbFsLqUSdf2JHUUn9QWOog9lhjmt4IIm4Q/edit#gid=0", "Data!A3:Z");
    IMPORTRANGE("https://docs.google.com/spreadsheets/d/1NQ8xXJtBF-e7EwHSJJ8QQ0s1nPQyfpvCd00QUvRwYJU/edit?gid=0#gid=0", "Data!A3:Z")
}

If you encounter the following error, first add the URL of that specific sheet to the IMPORTRANGE formula and authorize access:

That's it. If you're approaching the store limit again, simply create a new export starting from today and integrate it into this historical unified sheet.

Ready to get started? Begin exporting your data today.