Import Shopify Data To Google Sheets

“How do I connect Shopify orders to Google Sheets?”
“Is there a way to display Shopify products in Google Sheets?”
“Can you link Shopify to Google Sheets?”

Ecommerce specialist

Ecommerce maintenance takes hours to do. You want more time to strategize, not crunch numbers. If you maintain a Shopify website, you don’t experience any efficiency. You’re juggling different things at once so you want to do reports fast.

Why Use Shopify At All?

I did a small and quick survey in the Online Genius slack. Shopify is one of the most used e-commerce platforms with WooCommerce. Though not as high, the value comes from automating reports connecting ecommerce data with other data. Integration of information across platforms into one report is a goldmine to interpret data.

og woocommerce shopify 1

In this article, learn how to use the Shopify API in Google Sheets to import products data or orders information.

Requirement: Shopify API Key

You need to go through the Shopify API to import the data into Google Sheets. First thing is to create the API key for Shopify. Note that these steps don’t require coding knowledge or programming skills at all. Luckily, creating the Shopify API is within reach and easy to do.

Enable Private App Development

1 – Go to your Shopify dashboard. Look for ‘Apps’ in the left navigation panel.

shopify google sheets1

2 – Once inside the Apps dashboard, click ‘Manage private apps’ below. The text is small and you might not see it.

shopify google sheets2

3 – If this is your first time creating an app, the disable prompt appears. In any case, click ‘Enable private app development’.

shopify google sheets3

4 – Once inside, tick the three boxes of the terms and agreements. Then click ‘Enable private app development’.

shopify google sheets4

5 – Finally, click ‘Create private app’.

shopify google sheets5

Create The API Key and Password

1 – Name your private app. And add a developer email. These are arbitrary.

shopify google sheets6

2 – Next is setting permissions. To make things seamless, click the dropdown and select ‘Read access’.

From Customer API all the way down to store content API, grant ‘Read access’.

shopify google sheets7b

3 – Click Save on the bottom of the page. Then click ‘Create app’

shopify google sheets8

4 – You have now created the API key. Note the API key and password as these are used later on.

In case you’re wondering, you can ignore Example URL and Shared Secret.

shopify google sheets9

We’re done creating the Shopify API Key! Now let’s import data to Google Sheets…

A No-Code Guide On How To Import Shopify Data To Google Sheets

To import Shopify data like orders to Google Sheets automatically, you need a Google Sheets add-on. These add-ons are most likely paid add-ons. As mentioned, you can do it manually (for free) to save money. But in perspective, you might want to spend a little bit to save time. You choose.

You’ll use Two Minute Reports for this example. TMR imports data from the Shopify API directly to Google Sheets in minutes.

Installing The Google Sheets Add-on

1 – At the top of your Google Sheet, click ‘Add-ons’. Then click ‘Get add-ons’.

tmr 2

2 – Search for Two Minute Reports. Then install it.

tmr 1

3 – You now have the add-on installed on your Google Sheets file.

tmr 3

Import Shopify Data Source

1 – Launch the add-on. A navigation pane on the right side appears.

tmr 15

2 – Click the burger icon. Then Click ‘Data Sources’. Click ‘Add +’ button.

tmr 16

3 – Name your data source. Then select ‘Shopify’ inside E-commerce under Type.

shopify google sheets10

4 – Add the API Key from the Shopify private app dashboard.

5 – Add the password from the Shopify private app dashboard.

6 – For store name, add the texts after http/https and before myshopify.

So, if your shop is https://yellow-bananas.myshopify.com, the Store Name is yellow-bananas.

shopify google sheets11
shopify google sheets9

8 – Click Test and Save. You should get a ‘Successfully authenticated’ prompt.

Import Shopify Data Query

This point in the process is the importing orders list to the spreadsheet.

1 – Click the burger icon. Then Click ‘Data Queries’. And click ‘Add +’.

tmr 11

2 – Name your data query. Then find the Shopify data source you imported earlier.

3 – ‘Save on Sheet’ is the location of the sheet where the data is imported to. The location might be ‘Sheet 1’ but it depends if you named your sheet.

4 – Add A1 under ‘Cell’.

5 – Unselect ‘Append new data’. Unselected means the data overwrites the current spreadsheet.

shopify google sheets12

6- Under ‘Query’, select the ‘Metrics’ you want to import. In this example, we use Gross sales amount, Total sales amount, and Item quantity.

7 – Select the ‘Dimensions’ you want to import. In this example, we use Store name and Product title.

8 – Select the date range with whatever you need. In this example, we use last 3 months.

shopify google sheets13

10 – Click ‘Run query’

shopify google sheets14

Viola! You imported the orders data on a Google Sheet. These are the first steps in pulling in any important data from the Shopify API. At this point, you may take the steps on accessing your required data. You can import Inventory value, Customer count, Refund amount, Order ID, and many others.

Schedule Shopify Reports

Two Minute Reports has a scheduling function. This means the spreadsheet refreshes and updates the acquired data from the API. Reports are automated even more.

1 – Click the burger icon. Then click Schedule Refresh. And click ‘New +’.

tmr fb5

2 – Once inside, you can set the terms of the report schedule.

-Name

-Repeats: This is where you set how often the data refresh happens. Two Minute Reports can do hourly refreshes.

-Timezone

tmr li3

3 – You can also set the notification terms. Two Minute Reports can email you a PDF of the data. You can also decide to have the email contain the sheet content in the email body as part of the update.

tmr fb7
tmr fb8

4 – Click ‘Save’.

Congratulations! You now know how to import Shopify orders to Google Sheets with the API. The methods above updates accessing data without manual import and export. It avoids spreadsheet headaches.

Conclusion – Build Shopify Reports Faster

Know how to use an API in Google Sheets for your ecommerce needs. Google Sheets is a web-based spreadsheet app that provides an easy way to analyze data. With the help of Google Sheets add-ons, you can use external APIs and automate reporting.

You’re wasting time manually importing and exporting data. If you’re an entrepreneur or analyst, automated reporting with updates through APIs makes lives easier.


About The Author

Francis Angelo Reyes

Francis is the owner and runs Lupage Digital. With 5 years of experience, Francis writes everything he knows about digital marketing. There’s a large knowledge gap between developers and digital marketers. Francis is the middleman between where the non-technical and technical meet.