[PYTHON] Write the result of keyword search with ebaysdk to Google Spread Sheets

Introduction

See Last article for what you need to do to use ebay's API. This time, we will use a library called ebaysdk to list the products listed on ebay. Use gspread to get information obtained by keyword search I would like to show you how to write to Google Spread Sheets.

Preparation

First of all, refer to this article Make the necessary preparations to access Google Spread Sheets from Python.

Library installation

Next, let's install the required libraries.

pip install ebaysdk gspread oauth2client

Reference code

• Spread_sheet_key is the URL of the spreadsheet created for recording. https://docs.google.com/spreadsheets/d/SPREADSHEET_KEY/edit#gid=0 It corresponds to the part of SPREADSHEET_KEY.

When you run this program, the retrieved values ​​will be written to the spreadsheet in this way.

ebay_sdk.py

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from ebaysdk.finding import Connection as Finding
from ebaysdk.exception import ConnectionError

appkey =Obtained APP KEY
keyword = "Marvel Spiderman"  #Keywords you want to search
jsonf = "~~~~~~~~~.json"  #The json file name saved in the preparation
spread_sheet_key = "SPREADSHEET_KEY"  #If you do not understand, please refer to the above


#Visit Google Spread Sheets
def connect_gspread(jsonf, key):
    scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
    credentials = ServiceAccountCredentials.from_json_keyfile_name(jsonf, scope)
    gc = gspread.authorize(credentials)
    worksheet = gc.open_by_key(key).sheet1
    return worksheet


#Write the information obtained by ebaysdk to a spreadsheet
def write_to_sheet(ws, response_dict):
    ws.update_title(keyword)  #Make the worksheet name the searched word
    column = ["Product ID", "Product name", "currency", "price"]
    results = response_dict["searchResult"]["item"]

    for item in (results):
        column.append(item["itemId"])
        column.append(item["title"])
        column.append(item["sellingStatus"]["currentPrice"]["_currencyId"])
        column.append(item["sellingStatus"]["currentPrice"]["value"])

    cell_list = ws.range("A1:D" + str(len(results)))  #Specify as many ranges as there are search results

    for i, cell in enumerate(cell_list):
        cell.value = column[i]

    ws.update_cells(cell_list)  #Update all at once to reduce requests


try:
    api = Finding(appid=appkey, config_file=None)
    response = api.execute("findItemsAdvanced", {
        "keywords": keyword,
        "itemFilter": [
            {"name": "MinPrice", "value": "50", "paramName": "Currency", "paramValue": "USD"},
            {"name": "MaxPrice", "value": "150", "paramName": "Currency", "paramValue": "USD"}
        ],
        "sortOrder": "CurrentPriceHighest"
    })
    response_dict = response.dict()
    write_to_sheet(connect_gspread(jsonf, spread_sheet_key), response_dict)
except ConnectionError as e:
    print(e)
    print(e.response.dict())

First of all, I was accessing the ebay api with the following code Search results by keyword are stored in response The itemFilter specifies the lowest price and the highest price, but you can also narrow down by the condition of the product (new or used).

try:
    api = Finding(appid=appkey, config_file=None)
    response = api.execute("findItemsAdvanced", {
        "keywords": keyword,
        "itemFilter": [
            {"name": "MinPrice", "value": "50", "paramName": "Currency", "paramValue": "USD"},
            {"name": "MaxPrice", "value": "150", "paramName": "Currency", "paramValue": "USD"}
        ],
        "sortOrder": "CurrentPriceHighest"
    })

Next, I was accessing the spreadsheet with connect_gspread (jsonf, spread_sheet_key) A dictionary of the acquired worksheet information and response is passed to the argument of write_to_sheet.

response_dict = response.dict()
write_to_sheet(connect_gspread(jsonf, spread_sheet_key), response_dict)

I want to put the column name in the first row of the worksheet to make it easier to understand what column it is.

column = ["Product ID",  "Product name", "currency", "price"]

It is said. Next, because I only want the product search results

results = response_dict["searchResult"]["item"]

Then, store the product information of the search result in results, and store the product information for the number of hits in the search. I try to add them to column in order.

for item in (results):
    column.append(item["itemId"])  #Product ID
    column.append(item["title"])   #Product name
    column.append(item["sellingStatus"]["currentPrice"]["_currencyId"])  #currency
    column.append(item["sellingStatus"]["currentPrice"]["value"])        #price

Please add the information you want to column. There are some things to be aware of in this case. This time, the order is [" product ID "," product name "," currency "," price "], so Information on each product must be added to column in this order.

The reason is that you can update one by one with update_acell or update_cell. With this, if there are many search results, the number of requests will increase. I'm stuck with API request limits.

So use update_cells so that you can update a lot of data at once. The argument of this update_cells must be a one-dimensional array, so I put it in order.

cell_list = ws.range("A1:D" + str(len(results)))

for i, cell in enumerate(cell_list):
    cell.value = column[i]

ws.update_cells(cell_list)

ws.range ("A1: D "+ str (len (results))) is For example, if the search result is 3 product hits The first line is the column name A1 (product ID), B1 (product name) ... The search results will be from the second line, right? A2 (product ID of search result 1), B2 (product name of search result 1) ... And since it is a hit of 3 products, I want to make it D4 (price of search result 3) at the end. It is set as " A1: D "+ str (len (results)) so that it can be set dynamically for each search result.

ws.range () gets the information of the cells in the selected range in the worksheet.

So store the acquired cell information in cell_list

for i, cell in enumerate(cell_list):
    cell.value = column[i]

Write the product information contained in column to each cell of cell_list.

ws.update_cells(cell_list)

After that, you can update at once by passing the cell_list edited with update_cells as an argument.

This time, I made it to work for the time being I would appreciate it if you could tell me if there is any other way to make it easy to understand and concise.