I'm trying to create a Python script that requests information from a website and I need this information to be sent to gsheets

I'm new to Python and I was hoping you guys could help me ... I'm using BeautifulSoup to get information from the website, this works the way I need it, I also need to send this information to a Google sheet, i already enabled the API and made it work via cloud console, I tested it and I was able to send some information to it, but when I try to send this dataframe as .json file I get this error code :

> raise HttpError(resp, content, uri=self.uri) googleapiclient.errors.HttpError: <HttpError 400 when requesting
> https: ... valueInputOption=USER_ENTERED&alt=json returned "Invalid
> value at 'data.values[0]'

What am I doing wrong?

import requests
from bs4 import BeautifulSoup
import pandas as pd
from googleapiclient.discovery import build
from google.oauth2 import service_account

    url0 = "https://www.hockey-reference.com/leagues/NHL_2023_skaters.html"
    soup = BeautifulSoup(requests.get(url0).content,"html.parser")

    table = soup.find('table', {'class': 'stats_table'})

    df = pd.read_html(str(table))[0]
    df.columns = df.columns.droplevel(0)
    df = df[df["Player"] !="Player"]
    df = df [["Player","Pos","GP","G","A","+/-","S%","PS","FO%"]]
    df.fillna(0,inplace=True)

    SERVICE_ACCOUNT_FILE = 'keys.json'
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

    creds = None
    creds = service_account.Credentials.from_service_account_file(
            SERVICE_ACCOUNT_FILE, scopes=SCOPES)

    gsheet = '******'
    service = build('sheets', 'v4', credentials=creds)
    sheet = service.spreadsheets()
    var2 = [df.to_json()]
    request = sheet.values().update(spreadsheetId=gsheet, range="MasterTest!A1",valueInputOption="USER_ENTERED",body={"values":var2}).execute()   

Thanks

BeautifulSoup is fine, the API has been enabled and it is able to get and update info.

The error is showing because the API is waiting for a 2D array, var2 needs to look similar to:

[['2023-01-18', '1234', 'ABC', 1234], 
['2023-01-17', '5678', 'DEF', 5678], 
['2023-01-16', '9012', 'ABC', 9012]]

Sheets will then add each inner Array on its own row. I made the following function to transform your data from a dictionary to a 2D array that the API can use:

def transformData(dict):
    firstkey = list(dict)[0]
    lastInnerKey = list(dict[firstkey])[-1]
    main_db = []
    row = []

    for i in range(0, lastInnerKey + 2):
        if i == 0:
            for x in dict:
                row.append("'" + x) # Appending ' to the start of a 
                # value tells Google Sheets that this is text and not a formula.
        else:
            for x in dict:
                if (i - 1) in dict[x]:
                    row.append(dict[x][i - 1])
        if len(row) > 0:
            main_db.append(row)
        row = []

    return main_db

Then you just need to update the declaration of var2:

var2 = transformData(df.to_dict())

Reference

• Using ListValue format for the body