0

I'm trying to create a custom function in Google Sheets that will let me grab the 6 month avg price for "new", where the function let's me input the part ID and color ID.

For example, in the following url, 98138 is the part ID and 11 is the color ID

https://www.bricklink.com/catalogPG.asp?P=98138&ColorID=11

and I want the function =BLprice(98138,11) to output $0.05 from the part in the table. (Here's the part of the table I want to grab.)

Here is a link to the google sheet I used to test it: https://docs.google.com/spreadsheets/d/1N_3jKiSfi-ENfoah4fziFf815-hTm0tbz0U_snvw6BI/edit?usp=sharing

My current code is

function BLprice(PartID,ColorID) {

const url = "https://www.bricklink.com/catalogPG.asp?P=%22&PartID&%22&ColorID="&ColorID;

const html = UrlFetchApp.fetch(url).getContentText();

return html.match(/AVG Price:(.*)</tbody>/)[1].trim();

}

I've based it on the following (and I'm very inexperienced with coding) :

Google Sheet use Importxml error could not fetch url

Problem: I want to learn how to import a number from a website to google sheets using google apps script

Currently the error is "Exception: Bad request: http://0 (line 3)." With trying to play around with this I've also gotten the error that the url could not be fetched (and that was when trying to do importxml and attempting to fetch a part of the table)

Brett Gonser
  • 1
  • 1

1 Answers1

0

Firstly, the 2nd line of your function is incorrectly formatted, use this instead:

  const url = "https://www.bricklink.com/catalogPG.asp?P=" + PartID + "&ColorID=" + ColorID;

updated code should look like this:

function BLprice(PartID,ColorID) {
  const url = "https://www.bricklink.com/catalogPG.asp?P=" + PartID + "&ColorID=" + ColorID;
  const html = UrlFetchApp.fetch(url).getContentText();
  const regex = /Avg Price:<\/TD><TD><B>GBP&nbsp;([\d\.]+)/;
  const match = regex.exec(html);
  if (match && match[1]) {
    return match[1].trim();
  } else {
    return "N/A";
  }
}

This should match and return the "0.05" price you want.

However, it seems your big problem is that the website is blocking requests from Google servers which is why =IMPORTXML/+IMPORTHTML don't seem to work either. I can get the code to work locally but not as a Google Apps Script.

One possible solution would be to setup a Google Colab python notebook that is connected to your Google Sheet to get the data you need, it won't be a UDF that you had setup but does allow you to programmatically get the data you need, here is the equivalent code that allows Colab to output to Sheets:

import requests
import gspread
from google.auth import default
from google.colab import auth
from bs4 import BeautifulSoup

# Authenticate and create the Google Sheets client
auth.authenticate_user()
creds, _ = default()
client = gspread.authorize(creds)

# Open the Google Sheet and select the first worksheet
sheet_id = '<your_sheet_id_>' #in the url of the sheet after https://docs.google.com/spreadsheets/d/
sheet = client.open_by_key(sheet_id)
worksheet = sheet.sheet1

# Define the function to fetch the price data
def fetch_price_data(part_id, color_id):
    url = f'https://www.bricklink.com/catalogPG.asp?P={part_id}&ColorID={color_id}'
    headers = {"user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36"}
    resp = requests.get(url,headers=headers)
    print(resp)
    soup = BeautifulSoup(resp.content, 'html.parser')
    avg_price_tag = soup.find('td', string='Avg Price:')
    avg_price_value = avg_price_tag.find_next_sibling('td').text.strip()
    print(avg_price_value)
    return avg_price_value

# Define the list of part IDs and color IDs to fetch data for
part_ids = ['98138']
color_ids = ['11']

# Loop through the part IDs and color IDs and update the Google Sheet
for i in range(len(part_ids)):
    part_id = part_ids[i]
    color_id = color_ids[i]
    avg_price = fetch_price_data(part_id, color_id)
    print(part_id,color_id,avg_price)
    worksheet.update_cell(i+1, 1, part_id)
    worksheet.update_cell(i+1, 2, color_id)
    worksheet.update_cell(i+1, 3, avg_price)
childnick
  • 1,361
  • 1
  • 6
  • 10