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

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

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. gspread.png

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.

Recommended Posts

Write the result of keyword search with ebaysdk to Google Spread Sheets
Save the results of crawling with Scrapy to the Google Data Store
How to search Google Drive with Google Colaboratory
[Image recognition] How to read the result of automatic annotation with VoTT
Feel free to write a test with nose (in the case of + gevent)
Play with Google Spread Sheets in python (OAuth)
The 15th offline real-time I tried to solve the problem of how to write with python
I want to grep the execution result of strace
Add information to the bottom of the figure with Matplotlib
Get the image of "Suzu Hirose" by Google image search.
Try to get the contents of Word with Golang
I tried to access Google Spread Sheets using Python
[Python] Save the result of web scraping the Mercari product page on Google Colab to Google Sheets and display the product image as well.
How to write offline real time I tried to solve the problem of F02 with Python
I tried to find the entropy of the image with python
I tried to find the average of the sequence with TensorFlow
How to enable Read / Write of net.Conn with context with golang
Settings to debug the contents of the library with VS Code
Try to automate the operation of network devices with Python
How to write to update Datastore to async with Google Apps Engine
Google search for the last line of the file in Python
Get the source of the page to load infinitely with python.
Try to extract the features of the sensor data with CNN
Output the result of morphological analysis with Mecab to a WEB browser compatible with Sakura server / UTF-8
Scraping the result of "Schedule-kun"
Write to csv with Python
The story of not being able to run pygame with pycharm
Repeat with While. Scripts to Tweet and search from the terminal
Save the result of the life game as a gif with python
Become familiar with (want to be) around the pipeline of spaCy
I tried to automate the watering of the planter with Raspberry Pi
How to get the ID of Type2Tag NXP NTAG213 with nfcpy
Edit Google Spread Sheets with Python (Low-cost RPA case with Google APIs and Python)
First python ② Try to write code while examining the features of python
[Introduction to StyleGAN] I played with "The Life of a Man" ♬
I want to output the beginning of the next month with Python
Output the contents of ~ .xlsx in the folder to HTML with Python
Consider the speed of processing to shift the image buffer with numpy.ndarray
I wrote the code to write the code of Brainf * ck in python
Solving the Maze with Python-Supplement to Chapter 6 of the Algorithm Quick Reference-
How to output the output result of the Linux man command to a file
When you want to save the result of the callback function somewhere
How to monitor the execution status of sqlldr with the pv command
Write a script to calculate the distance with Elasticsearch 5 system painless
I tried to expand the size of the logical volume with LVM
The strongest way to use MeCab and CaboCha with Google Colab
To improve the reusability and maintainability of workflows created with Luigi
I want to check the position of my face with OpenCV!
Convert the result of python optparse to dict and utilize it
[Introduction to SIR model] Consider the fitting result of Diamond Princess ♬
I tried to improve the efficiency of daily work with Python
Python beginners hit the unofficial API of Google Play Music to play music
PhytoMine-I tried to get the genetic information of plants with Python
How to run the practice code of the book "Creating a profitable AI with Python" on Google Colaboratory