【colab专题-shopify订单】colab导出shopify订单详情到google sheet

• 【colab专题-数据导出】导出数据到google sheet
• 如何编写shopify简单的GraphQL语句
• 如何获取shopify后端access token来调用数据？

之前详细说明了shopify的密钥获取，GraphQL查询数据，导出数据到colab中，这次做一个整合，演示用脚本如何从shopify获取数据并导出到google sheet中。

授权并指定导出需要的google sheet

 先指定下Google sheet，然后导入我们需要的库

from google.colab import auth
auth.authenticate_user()

import gspread
from google.auth import default
creds, _ = default()
gc = gspread.authorize(creds)

import pandas as pd
import requests

sheet_url = 'XXXXX'
sheetname = '工作表xxxx'
gsheets = gc.open_by_url(sheet_url)
sheets = gsheets.worksheet(sheetname)

构建shopify GraphQL语句的http请求

 这里发起请求的URL使用

https://XXXXX.myshopify.com/admin/api/2024-07/graphql.json

配合之前拿到的密钥，接下来开始构建获取前10个订单的query的http请求

access_token = 'XXXXXXXX'
request_url = 'https://XXXXXX.myshopify.com/admin/api/2024-07/graphql.json/'

query = """
query{
orders(first:10){
  nodes{
    id
    name
  }
}
}
"""

headers = {
        'X-Shopify-Access-Token': access_token,
        'Content-Type': 'application/json'
    }

payload = {
        "query": query
    }
response = requests.post(request_url, headers=headers, json=payload)
response_data = response.json()

数据处理

 获取到http请求后处理成pandas的dataframe

datalist = []
orders = response_data['data']['orders']['nodes']
for order in orders:
 datalist.append(order)
df = pd.DataFrame(datalist)

处理后的结果

数据导出

 最后导出数据

sheets.clear()
sheets.update([df.columns.values.tolist()] + df.values.tolist())

最后表格结果如下

完整代码如下

from google.colab import auth
auth.authenticate_user()

import gspread
from google.auth import default
creds, _ = default()
gc = gspread.authorize(creds)

import pandas as pd
import requests

sheet_url = 'XXXXX'
sheetname = '工作表xxxx'
gsheets = gc.open_by_url(sheet_url)
sheets = gsheets.worksheet(sheetname)

access_token = 'XXXXXXXX'
request_url = 'https://XXXXXX.myshopify.com/admin/api/2024-07/graphql.json/'

query = """
query{
orders(first:10){
  nodes{
    id
    name
  }
}
}
"""

headers = {
        'X-Shopify-Access-Token': access_token,
        'Content-Type': 'application/json'
    }

payload = {
        "query": query
    }
response = requests.post(request_url, headers=headers, json=payload)
response_data = response.json()

datalist = []
orders = response_data['data']['orders']['nodes']
for order in orders:
 datalist.append(order)
df = pd.DataFrame(datalist)
sheets.clear()
sheets.update([df.columns.values.tolist()] + df.values.tolist())