How to get a websites data with the input from a cell Excel

Question

I'm trying to make a excel workbook for a game called torn city. I want users to be able to input their game key into a cell and then excel automatically pulls the data from a link with the combined key. For example this is my key(VNWZibo54fSCUM01) and link for the data is: https://api.torn.com/user/?selections=honors&key=VNWZibo54fSCUM01 Now when you go to this specific website it shows the awards that you have gotten for the game, It is in a JSON format with the two things in it being honors_awarded and honors_time. I want the honors_awarded list to be displayed when a user inputs his key into a cell.

I have been able to get the data in excel with this url(https://api.torn.com/user/?selections=honors&key=VNWZibo54fSCUM01)

How this data looks

but I want to have a more user friendly interface. I want this done by simply having users input their key into a cell the the data appears

Might be irrelevant but I also have this python code that does the get the data i want but I dont know if this will be able to be in excel

import requests

api = input("Enter your api key")

url = f"https://api.torn.com/user/?selections=honors&key={api}"
response = requests.get(url)
 
if response.status_code == 200:
    data = response.json()  # Parse JSON response into a Python dictionary
    if 'honors_awarded' in data:
        honors_awarded = data['honors_awarded']
        for data_list in honors_awarded:
            print(data_list)
    else:
        print("The key 'honors_awarded' does not exist in the response.")
else:
    print("Failed to retrieve data:", response.status_code)

Any suggestions?

Is Excel is something crucial for you, or you just need table-like representation of your data? — Alderven
– Alderven, Commented May 10, 2024 at 18:27

Alderven · Accepted Answer · 2024-05-11 05:16:17Z

Created simple solution using pandas, although not very convenient because requires several steps:

Create Excel file file.xlsx in the same dir with Python script
Enter API key into Excel's first cell
Save and close Excel file
Run script
Open Excel file, data will be there:

Code:

import requests
import pandas as pd

df = pd.read_excel('file.xlsx')
try:
    key = df.columns.values[0]
except IndexError:
    print('API key not found!')
    exit()

response = requests.get(f'https://api.torn.com/user/?selections=honors&key={key}')
if response.status_code == 200:
    data = response.json()
    if 'honors_awarded' in data:
        df = pd.DataFrame({key: data['honors_awarded']})
        try:
            df.to_excel('file.xlsx', index=False)
        except PermissionError:
            print('Permission denied! You need to close Excel file!')
    else:
        print('The key "honors_awarded" does not exist in the response.')
else:
    print('Failed to retrieve data:', response.status_code)

Collectives™ on Stack Overflow

How to get a websites data with the input from a cell Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related