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 Shopify?

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.

Why Pay for Automation at All?

Automations solve huge problems in organizations. So, most automation tools are paid products. Yes, you can do everything manually for free to save money. But in perspective, instead, you might want to spend a little to save time. The number of hours that you take back to avoid hellish and repetitive work is worth the price. You choose.

busy desktop

I hate a super cluttered desktop. Ever experienced a desktop (or Downloads folder) full of spreadsheet files? I hate them. It increases stress and productivity comes to an all-time low. The amount of time I clicked the Export CSV button is unbearable. Automation avoids all this.

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 on 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 on Enable private app development.

shopify google sheets3

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

shopify google sheets4

5 – Finally, click on 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 on the dropdown and select Read access.

From Customer API down to store content API, grant Read access.

shopify google sheets7b

3 – Click on Save at the bottom of the page. Then click on 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…

How To Import Shopify Data To Google Sheets

This article shows two options on how to import Shopify orders to Google Sheets. Option 1 is using a Google Sheets add-on. Option 2 is using a 3rd party integration tool. No required coding skills.

Option 1: Google Sheets Add-On

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

two minute reports 01

Installing The Google Sheets Add-on

1 – Go to your Google Drive workspace.

2 – Click on + New then create a new Google Sheets file.

tmr 0
tmr 00

3 – At the top of your file, click on Extensions. Then click on Add-ons. Then click on Get add-ons.

sheets 01

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

tmr 1

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

sheets tmr 01

Import Shopify Data Source

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

sheets tmr 02

2 – Click on the burger icon. Then click on Data Sources. Click on 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 https:// and before myshopify.

So, if your shop is https://yellowbananas.myshopify.com, the Store Name is yellowbananas.

shopify google sheets11
shopify google sheets9

8 – Click on Test and Save. You should get a Successfully authenticated notification.

Import Shopify Data Query

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

1 – Click on the burger icon. Then click on Data Queries. And click on 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 on 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 on the burger icon. Then click on Schedule Refresh. And click on 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 on Save.

Scheduling helps when you need to report on Shopify data over time. No more copy and paste every day, or every week, or every month.

Option 2: Integration Tool

You’ll use Integromat (now branded as Make) for this option. Make is a powerful integration platform that allows you to visualize, design, and automate your work in minutes. In other words, it lets apps talk to each other so that you can remove repetitive tasks.

Create a Make account and get started.

integromat 01b

Getting Started

1 – Click on Templates. The tool already has a template since this scenario is a standard case.

integromat 02b

2 – Search and click on the Shopify Google Sheets template.

shopify sheets integromat 00

3 – Click on the Create new scenario from template button from the lower left of the page.

integromat 03

Setting Up Shopify Connection

The first section is setting up the Shopify connection.

1 – Under Connection click on Add.

shopify sheets integromat 01

2 – Under Connection name, name the connection. The name is arbitrary so it’s up to you. But I suggest writing a descriptive name for later use.

3 – Under Myshopify.com subdomain, enter the subdomain of your Shopify store.

If your Shopify store is called yellowbananas.myshopify.com, enter yellowbananas.

shopify sheets integromat 02

4 – Click on Continue. A window pop-up appears that leads to your Shopify dashboard.

5 – In Shopify, click Install unlisted app. This gives Integromat access to your Shopify account. This is standard procedure. The window closes after clicking on it.

shopify sheets integromat 03

6 – Under Status, select any. Other choices include open, close, cancelled.

7 – Under Financial status, select any. Other choices are available.

8 – Under Fulfillment status, select any. Other choices are available.

9 – Under Limit, enter 10 for now.

The examples above are for the purpose of this article, so it depends on your use cases. There may be advanced settings. Ignore those for now.

shopify sheets integromat 04

9 – Click on OK.

10 – Under Choose where to start, if it appears, select All.

shopify sheets integromat 05

Done! At this point, you’re finished with the Shopify connection side. The tool prompts you if there are missing values in the fields.

Setting Up Google Sheets Connection

Next is setting up the Google Sheets connection. But first, you need to go to Google Drive and create a new Google Sheets file.

1 – Go to your Google Drive workspace.

2 – Click on + New then create a new Google Sheets file.

tmr 0
tmr 00

3 – Name the Google Sheets file. The name is arbitrary so it’s up to you. But I suggest writing a descriptive name for later use.

integromat 07 sheets

4 – Name the tab or sheet on the bottom. The name is arbitrary so it’s up to you. But I suggest writing a descriptive name for later use.

integromat 07 sheets2

5 – Name the headers. It’s helpful to map out the correct data point in the integration process later.

Column A
Column B
Column C
Column D

integromat 07 sheets3

The first steps are done! Go back to Integromat.

1 – Under Connection, click on Add.

2 – Name the connection. The name is arbitrary so it’s up to you. But I suggest writing a descriptive name for later use.

gmail to sheets integromat 12b

3 – Click on Continue. A pop-up window appears.

4 – Inside the pop-up window, select the Gmail account that stores the Google Sheets file.

integromat 04

5 – Click on Allow. Integromat asks permission to access your Google account. This is standard procedure. The window closes after clicking it.

gmail to sheets integromat 13

6 – Back in Integromat, under Mode, choose Select spreadsheet and sheet. This is to make the integration simpler.

7 – Under Spreadsheet, select the Google Sheets file you created earlier. Find the name you gave it.

8 – Under Sheet, select the Google Sheets tab from earlier. Find the name you gave it. After which, the Values are created.

integromat 07 sheets5

9 – Under Values is where it gets tricky. The headings from the Google Sheets file are the parameters where you decide the values. Once you click on the field, you’re presented with different parameters. Don’t be overwhelmed. Read the context of the parameters to know which one is mapped to which.

integromat 07 sheets7

Under (A), select Created at.
Under (B), select Order ID.
Under (C), select Fulfillment status.
Under (D), select Total price.

The parameters that appear here are from the Shopify connection. Other parameters are also available. Make sure to add an extra column and name it on the Google Sheets spreadsheet so on and so forth.

10 – Click on Continue. Now it’s time to run and process the integration.

11 – Click on Run once with a play button icon.

integromat 06

A spinning wheel and a Log on the bottom right appear. Wait for the integration to finish. How long the integration takes depends on the Maximum number of results you select. It’s finished when a) green checkmarks appear, and when b) The scenario run was completed appears in the Log.

shopify sheets integromat 06

Go to your Google Sheets file and you now see the ecommerce data successfully exported from the Shopify API. Viola! Congratulations you imported Shopify orders to Google Sheets with complete information! You may also add other parameters or fields if you need them.

Schedule Automation and Reports

Integromat can schedule the automation based on cycles. The schedule is up to you.

1 – On the screen, click on the Scheduling button and turn it on.

integromat 06

2 – Under Run scenario, select the schedule you need. What you select on this parameter dictates the next ones.

If you select At regular intervals, you need to select the minutes that Integromat processes the automation.

If you select Once, you need to select the date.

If you select Every day, you need to select the time.

If you select Days of the week or Days of the week, you need to select the days.

gmail to sheets integromat 18b

3 – Click on Activate. Ignore any advanced settings for now.

Viola! Congratulations! You now know how to import Shopify orders to Google Sheets with the API. The methods above automate 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