Cara Menghubungkan Shopify ke Excel - Kueri Data E-Commerce dengan SQL

Hubungkan Shopify ke Excel

Dapatkan data dari Shopify ke dalam Excel

Ambil data baris langsung dari akun Shopify Anda ke Excel dan perbarui kapan pun Anda mau. Tanpa driver ODBC, tanpa ekspor, tanpa makro. Sekitar sepuluh menit untuk pertama kali, dan paket Gratis sudah mencakupnya.

Sekitar 10 menit Tidak perlu coding. Kredensial Anda akan tetap bersama Anda. Gunakan Excel di mana saja, bahkan di web.

Inilah hasil akhirnya

Lembar kerja yang menampilkan hasil langsung dari kueri SQL terhadap akun Shopify Anda, dengan tombol yang mengambil baris terbaru setiap kali data berubah. Seperti ini:

Penjualan.xlsx — Excel

BerandaMenyisipkanDataAliran Kueri

	A	B	C	D	E
1	customer_id	nama depan	nama belakang	negara	total
2	1042	Maria	Alvarez	Amerika Serikat	1,284.50
3	1043	Daniel	Okafor	Amerika Serikat	612.00
4	1047	Priya	Nair	Amerika Serikat	2,090.75
5	1051	Thomas	Berg	Amerika Serikat	338.20
6	1056	Lucía	Ferreira	Amerika Serikat	1,455.00
7	1060	Kenji	Sato	Amerika Serikat	874.10
8

Lembar 1 Siaran langsung dari Shopify

Shopify menyimpan data Anda di balik API-nya. Memasukkannya ke Excel biasanya berarti mengekspor CSV dari dasbor Shopify setiap kali Anda membutuhkan angka terbaru. Query Streams melewati proses itu: program pembantu kecil bernama Agent menjalankan kueri di tempat data Anda berada, dan add-in Excel meminta baris yang Anda inginkan. Hanya hasilnya yang dikirim, melalui tautan terenkripsi, dan Excel dapat berada di mana saja: laptop Anda di rumah, komputer kolega di seberang negara, atau Excel di web.

Yang Anda butuhkan

Akun Query Streams

Paket gratis sudah cukup untuk panduan ini. Daftar di sini. my.querystreams.com.

Agen pada komputer yang selalu menyala

Sebuah program kecil yang berjalan di latar belakang dan memanggil Shopify saat Excel memintanya. Program ini hanya membutuhkan akses internet.

Informasi login Shopify Anda

Anda akan masuk ke Shopify sekali dan mengizinkan akses baca. Tidak ada kata sandi yang dimasukkan ke dalam Query Streams.

Hubungkan dalam lima langkah

1. Buat akun Anda

   Pergi ke my.querystreams.com dan mendaftar. Pilih Gratis Rencana ini adalah rencana nyata, bukan uji coba, dan mencakup menghubungkan akun Shopify Anda dan menjalankan kueri dari Excel.

2. Instal Agen pada komputer yang selalu menyala.

   Masuk, buka Unduh, dan instal Agen pada komputer atau server yang selalu aktif dan memiliki akses internet. Prosesnya hanya membutuhkan waktu satu menit dan akan berjalan dengan sendirinya setelah itu.

   Mengapa menggunakan program kecil alih-alih driver? Agent ini merupakan instalasi tunggal untuk Windows, Mac, atau Linux (sekitar 85 MB di Windows, sedikit lebih dari 100 MB di Mac dan Linux) yang berjalan diam-diam di latar belakang. Agent ini membuat satu koneksi keluar terenkripsi ke Query Streams, sama seperti yang dilakukan browser, sehingga Anda tidak perlu membuka port firewall dan mengatur VPN. Kunci Anda tetap berada di mesin tersebut, dan baris data dikirim ke mana pun Anda berada, melalui internet jika perlu. Satu-satunya hal yang Anda perlukan adalah add-in tersebut.

   Halaman Unduhan menampilkan Kunci Akses AndaTempelkan ke dalam Agent saat diminta, dan tunggu hingga badge terbaca. Terhubung ke Aliran KueriItulah satu-satunya hal yang Anda lakukan pada Agent itu sendiri.

   Panduan lengkap jika Anda menginginkannya: Windows, Mac, Linux.

3. Arahkan ke akun Shopify Anda

   Kembali ke browser Anda, buka Agen Data (di bawah Data (di menu atas). Agen Anda terdaftar di sana. Klik Tambahkan konektor dan memilih Shopify. Need your Shopify credentials first? Open the walkthrough, then fill in:

   Create your Shopify credentials (about 10 minutes)

   Follow this with Shopify open in another tab. Query Streams keeps what you create here so your Agent can use it, and only ever reads from Shopify.

   Who can do this

   Store owner, or a staff account with the App development > Develop permission (collaborator accounts can’t use the Dev Dashboard)

   Direkomendasikan

   Dev Dashboard app (custom app) in your store’s own Shopify organization: Client ID + Client secret, read-only scopes

   Sebelum Anda mulai

   • Your Shopify store’s admin login: the store owner, or a staff account with the App development > Develop permission. Store owners have it automatically.
   • The Query Streams wizard open in another tab: Data Agents → Add connector → Shopify, with your Agent online.
   • No coding. Shopify no longer lets you copy a ready-made access token for a new app; Query Streams turns the app’s Client ID and Client secret into a short-lived token itself.

   Steps

   1. Open your store’s app settings

      Sign in to your Shopify admin. Click Settings (bottom-left), then Apps.

      On some stores this menu item reads Apps and sales channels. Use the admin of the exact store you want to connect.

   2. Click Develop apps

      On the Apps settings page, click Develop apps.

      No Develop apps button? Your staff account lacks the App development > Develop permission. Ask the store owner to grant it, or to do these steps.

   3. Open the Dev Dashboard

      Click Build apps in Dev Dashboard. The Dev Dashboard opens, usually in a new tab.

      Open it this way, from your store’s admin, rather than signing in to dev.shopify.com separately. The app must be in the same Shopify organization as the store, or Query Streams can’t connect.

   4. Click Create app

      Check that Apps is selected in the left menu, then click Create app in the top-right corner.

   5. Choose Start from Dev Dashboard

      Select Start from Dev Dashboard (not Start with Shopify CLI).

   6. Name the app

      Type a name you will recognise, for example Query Streams, and click Create. The page for the app’s first version opens.

      The name is only for you. Query Streams doesn’t need a particular name.

   7. Keep the default App URL

      In the URLs section, leave App URL as https://shopify.dev/apps/default-app-home (type it in if the box is empty). If you see an Embed app in Shopify admin option, leave it off.

      Query Streams doesn’t open inside your Shopify admin, so Shopify’s default address is the right one.

   8. Pick a Webhooks API version

      In Webhooks API version, select the newest version in the list.

      Query Streams doesn’t use webhooks, so any version works.

   9. Add the read scopes

      In the Access section, find Scopes. Paste this list, or click Select scopes and tick each one: read_orders, read_draft_orders, read_customers, read_products, read_inventory, read_locations, read_discounts, read_gift_cards, read_shipping, read_merchant_managed_fulfillment_orders, read_assigned_fulfillment_orders, read_third_party_fulfillment_orders, read_content, read_themes, read_legal_policies, read_shopify_payments_accounts, read_shopify_payments_payouts, read_shopify_payments_disputes. Leave Optional scopes empty.

      Choose only read_ scopes: Query Streams never changes anything in your store. Do not add read_all_orders. Shopify rejects it for these apps with “Contains invalid scopes” (see Limits). read_payouts and read_shopify_payments are not valid names either. Keep the three Shopify Payments scopes even if you don’t use Shopify Payments; those tables just stay empty.

   10. Click Release

       Scroll to the bottom of the page and click Release. A dialog opens with an optional version name and message.

       You can leave both boxes empty.

   11. Confirm the release

       Click Release in the dialog. On the app’s Versions page the new version now has an Active badge.

   12. Click Install app

       Open the app’s home page (click the app’s name, or Home in the app’s menu). In the Installs card, click Install app. A new tab opens.

       The same action is in the ⋯ menu on the app’s row in the Apps list.

   13. Choose your store

       If you have more than one store, select the store you want to connect.

       Pick the store whose admin you started from in step 1.

   14. Klik Instal

       Shopify shows an Install app screen listing the store data the app can view. Check it and click Install.

       Back in the Dev Dashboard, the Installs card now shows 1 install. Refresh the page if it still shows 0.

   15. Open the app’s Settings

       In the Dev Dashboard, open your app and click Settings (App settings on some screens). Find the Credentials card.

   16. Copy the Client ID

       Next to Client ID, click the copy (clipboard) icon. Paste it into Client ID in the Query Streams wizard.

       It is 32 letters and digits, for example a8ed94aa0680bc6580eea34486dcaa6b. It never changes.

   17. Copy the Client secret

       Next to Client secret (labelled Secret on some screens), click the eye icon to show it, then the copy icon. Paste it into Client secret in the Query Streams wizard.

       It starts with shpss_. Shopify keeps showing it on this page, so you can come back for it later. Don’t click Rotate unless you mean to replace it.

   18. Copy your store address

       In your Shopify admin, go to Settings → Domains and copy the address that ends in .myshopify.com, for example your-store.myshopify.com. Paste it into Store address in the Query Streams wizard.

       Your own domain (such as www.yourbrand.com) won’t work. The address in your browser bar while you’re in the admin, admin.shopify.com/store/your-store, is also accepted.

   19. Test the connection

       In the Query Streams wizard, click Test connection. It says Connected.

       If it shows a message instead, find it under Common problems.

   Permissions to grant

   Resource	Akses	Used for
   pesanan_baca	Membaca	orders, order_line_items, transactions, refunds, fulfillments, order_risks and abandoned_checkouts tables (last 60 days, see Limits)
   read_draft_orders	Membaca	draft_orders table
   baca_pelanggan	Membaca	customers and customer_addresses tables
   produk_baca	Membaca	products, variants, collections, smart_collections and custom_collections tables
   baca_inventaris	Membaca	inventory_levels and inventory_items tables
   lokasi_baca	Membaca	locations table, and the per-location rows in inventory_levels
   diskon_baca	Membaca	price_rules and discount_codes tables
   read_gift_cards	Membaca	gift_cards table
   read_shipping	Membaca	shipping_zones and carrier_services tables
   read_merchant_managed_fulfillment_orders	Membaca	fulfillment_orders table: orders you fulfil from your own locations
   read_assigned_fulfillment_orders	Membaca	fulfillment_orders table (any one of the three fulfillment-order scopes turns the table on; all three give the complete list)
   read_third_party_fulfillment_orders	Membaca	fulfillment_orders table: orders fulfilled by third-party fulfilment services
   read_content	Membaca	blogs, articles and pages tables
   read_themes	Membaca	themes table
   read_legal_policies	Membaca	policies table
   read_shopify_payments_accounts	Membaca	needed together with the next two for the payouts and disputes tables (Shopify Payments stores only)
   read_shopify_payments_payouts	Membaca	payouts table
   read_shopify_payments_disputes	Membaca	disputes table

   Paste into Query Streams

   Store address

   The .myshopify.com address from step 18 (or the admin.shopify.com/store/… address from your browser bar)

   ID Klien

   The Client ID you copied in step 16

   Client secret

   The Client secret you copied in step 17 (starts with shpss_)

   Common problems

   Test connection says “The app and the store must belong to the same Shopify organization.”

   Why: The app was created in a different Shopify organization from the store, for example after signing in to dev.shopify.com with another account or choosing another organization. Owning the store is not enough.

   Fix: Start again from the store’s own admin: Settings → Apps → Develop apps → Build apps in Dev Dashboard, and create the app there (steps 1 to 17). If you belong to several organizations, the organization ID is in the Dev Dashboard address (dev.shopify.com/dashboard/<org-id>); the app and the store must be under the same one.

   Test connection says “Client ID or Client secret is wrong, or the app isn’t installed on this store.”

   Why: A value was copied incompletely or with a space, Client ID and Client secret were swapped, the secret was rotated, or the app isn’t installed on this store (or was uninstalled).

   Fix: Copy both values again with the copy icons on the app’s Settings page; the secret starts with shpss_. Check that the app’s Installs card lists your store, and click Install app if it doesn’t. If someone clicked Rotate, use the secret marked New. Then click Test connection again.

   Test connection rejects the store address (“Use your .myshopify.com address (Settings → Domains) or the admin.shopify.com/store/… URL. Custom domains can’t be used for the API.”) or says “Store not found. Check the store address.”

   Why: Your own domain (such as www.yourbrand.com) was entered, or the store handle has a typo.

   Fix: Copy the .myshopify.com address from Settings → Domains in your Shopify admin, or the admin.shopify.com/store/… address from your browser bar.

   Test connection says “The token was accepted but has no scopes”, or some tables are switched off with a missing scope named (for example payouts needs read_shopify_payments_payouts).

   Why: The scope isn’t on the app’s active version, or a new version was released but the store hasn’t approved it yet.

   Fix: In the Dev Dashboard open your app → Versions → Create version, add the missing read_ scope and click Release. Then open the app and, in the Installs card, click Install app for your store again to approve the change: in our own test the store kept the old scopes until this was done. Click Test connection again.

   Clicking Release shows “Contains invalid scopes”.

   Why: read_all_orders was added (Shopify must approve it, and Dev Dashboard apps can’t request it), or a scope name is misspelt, such as read_payouts or read_shopify_payments.

   Fix: Remove it and use exactly the names in step 9.

   Orders older than about 60 days never appear.

   Why: Shopify serves only the last 60 days of orders to apps without read_all_orders (see Limits).

   Fix: Nothing to change in the app or in Query Streams today. Orders already loaded stay in your Query Streams cache, so your history builds up from the day you connect.

   The payouts and disputes tables stay empty with the note “Shopify Payments isn’t enabled on this store, so there is nothing to load for this table.”

   Why: The store takes payments through another provider, so Shopify has no payouts or disputes to share.

   Fix: Nothing to fix. The other tables are not affected.

   Customer names, emails, phone numbers or addresses are empty while the other columns load.

   Why: Shopify treats these fields as protected customer data. Its help center says a store on the Basic plan can’t use custom apps that read them; the Grow plan or higher is needed.

   Fix: Check which Shopify plan the store is on. On Basic, these columns stay empty until the store moves to Grow or higher; nothing in Query Streams changes this.

   There is no Develop apps button, or the Dev Dashboard won’t open for you.

   Why: Your staff account doesn’t have the App development > Develop permission, or you are signed in with a collaborator account, which can’t use the Dev Dashboard.

   Fix: Ask the store owner to grant your staff account App development > Develop, or to follow these steps themselves.

   Limits to know

   • Orders: Shopify gives apps only the orders created in the last 60 days. Older orders need the read_all_orders scope, which Shopify must approve, and the Dev Dashboard rejects it for these apps (“Contains invalid scopes: read_all_orders”, checked 2026-09-29). This covers the orders table and the tables built from it (order_line_items, transactions, refunds, fulfillments, order_risks) plus abandoned_checkouts. Products, customers, inventory and draft orders are not limited.
   • Access tokens last 24 hours. The Agent requests a fresh one from the Client ID and Client secret automatically; you never copy or renew a token.
   • Shopify limits API reading by store plan: 100 cost points per second on standard plans, 200 on Advanced, 1,000 on Shopify Plus and 2,000 on Shopify for enterprise; one request can’t cost more than 1,000 points. The first full load of a large store can take a while; when Shopify throttles, the Agent waits and continues.
   • payouts and disputes have rows only if the store uses Shopify Payments.

   Steps verified 8 October 2026. Sources: shopify.dev/docs/apps/build/dev-dashboard/create-apps-usi…, shopify.dev/docs/apps/build/authentication-authorization/…, shopify.dev/docs/apps/build/authentication-authorization/…, shopify.dev/docs/apps/build/authentication-authorization/…, shopify.dev/docs/api/usage/access-scopes, shopify.dev/docs/apps/build/apis/graphql-admin/rate-limits, shopify.dev/docs/apps/launch/protected-customer-data, help.shopify.com/en/manual/apps/install-setup-apps, help.shopify.com/en/manual/apps/about-apps, help.shopify.com/en/manual/domains, changelog.shopify.com/posts/legacy-custom-apps-can-t-be-c…, kb.wolfandbadger.com/creating-your-custom-shopify-app-usi…, docs.mydbsync.com/cloud-workflow/connectors/shopify/steps…, help.sarasanalytics.com/pulse-shopify-/shopify-app-creati….

   Nama koneksi

   Nama apa pun yang akan Anda kenali nanti, seperti basis data Penjualan.

   URL Toko

   Alamat Shopify toko Anda, seperti your-store.myshopify.com.

   ID Klien

   ID Klien dari aplikasi kustom yang Anda buat untuk toko Anda di Shopify.

   Rahasia Klien

   Rahasia Klien dari aplikasi Shopify yang sama.

   Klik Uji koneksiSaat terbaca Terhubung, klik SimpanKoneksi akan sampai ke Agen Anda dalam beberapa detik.

   Agen Data Terbuka

   Tes gagal? Usually a missing permission, or a credential from the wrong Shopify account. The walkthrough above lists the common problems and their fixes.

4. Menambahkan Aliran Kueri ke Excel

   Di Excel, buka Sisipkan → Dapatkan Add-in, cari Aliran Kuerilalu klik MenambahkanAtau buka langsung dari Aliran Kueri di Microsoft AppSourceTombol Query Streams muncul di pita menu. Masuk dengan akun yang sama.

   Berfungsi di Excel untuk Microsoft 365 di Windows dan Mac, serta di Excel versi web.

5. Tarik meja pertama Anda

   Klik tombol Query Streams, lalu pilih Mengalirkan Kueri, dan pilih koneksi yang baru saja Anda buat.

   Ketik kueri yang ingin Anda jalankan. Kueri ini mengambil beberapa kolom dari sebuah shopify.orders meja:

   Contoh · SQL

   SELECT name, financial_status, total_price, created_at FROM shopify.orders ORDER BY created_at DESC LIMIT 100;

   Di bawah Lembar Tujuan memilih Buat lembar kerja baruKemudian tekan tombol putar (panah ▶). Baris-baris tersebut akan masuk ke Excel dalam satu atau dua detik. Tekan lagi setiap kali data berubah dan lembar kerja akan diperbarui secara otomatis.

   Tidak tahu SQL? Buat kueri di portal Query Streams sebagai gantinya. Pembuat Kueri Menampilkan tabel dan kolom Anda sehingga Anda dapat mengarahkan dan mengklik. Pada paket Bisnis, Anda juga dapat meminta Nova dalam bahasa Inggris sederhana (“tunjukkan kepada saya pelanggan di AS”) dan Nova akan menuliskan SQL untuk Anda. Simpan kueri tersebut dan Anda hanya perlu satu klik untuk mengaksesnya di portal, dan di daftar Kueri Tersimpan add-in mulai dari Personal Core ke atas.

Langkah selanjutnya yang bagus

• Simpan kueri di portal sehingga Anda dapat menjalankannya lagi dengan sekali klik, dari Excel atau Google Sheets.
• Tambahkan filter misalnya rentang tanggal, sehingga kueri yang sama dapat menjawab "bulan ini" tanpa perlu diedit.
• Bagikan hasilnya bersama seorang kolega. Mereka melihat baris-barisnya, bukan kredensial Anda.

Pertanyaan yang diajukan orang

Panduan terkait

• Shopify ke Google Sheets
• Stripe ke Microsoft Excel
• HubSpot ke Microsoft Excel
• Google Analytics 4 ke Microsoft Excel
• Menjalankan SQL di Excel
• Semua panduan