I’m trying to make a google sheet which monitors the average price of certain figures on Bricklink, and started with a code that chatgpt gave me. The sheet would have dates in the A column and from the B column the scraped average prices to certain figures.
my code right now (with comments from chatgpt):
function scrapeMultipleWebsites() {
// Array of URLs to scrape numbers from
const urls = [
"https://www.bricklink.com/catalogPG.asp?M=sw1363&ColorID=0",
"https://www.bricklink.com/catalogPG.asp?M=sw1325&ColorID=0",
"https://www.bricklink.com/catalogPG.asp?M=sw1377&ColorID=0",
// Add up to 20 URLs in total
];
// Regular expression to extract numbers (adjust this if needed for each URL)
const regex = /<TD>Avg Price:</TD><TD><B>HUF [0-9]*,d+.d+/;
// Target Sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("'24 SW Figure price monitor"); // Update sheet name
const lastRow = sheet.getLastRow();
const nextRow = Math.max(3, lastRow + 1); // Ensure the script starts writing from Row 3
// Add current date in Column A
const currentDate = new Date();
sheet.getRange(nextRow, 1).setValue(currentDate);
// Loop through URLs and scrape numbers
for (let i = 0; i < urls.length; i++) {
const html = UrlFetchApp.fetch(urls[i]).getContentText();
const match = html.match(regex);
Logger.log(match);
if (match) {
const number = match[1]; // Extract matched number
sheet.getRange(nextRow, i + 2).setValue(number); // Start at Column B (index 2)
} else {
sheet.getRange(nextRow, i + 2).setValue("N/A"); // If no match, add "N/A"
}
}
}
The html looks the same with all bricklink price guide links (see above in the code), so the same regex for all the different links should work in theory. It searches for the simple average price (so not the qty avg) of a new figure in the last 6 months. Since I live in Hungary, a returned value in HUF should look something like this: 12,438.02
Whatever regex I have put in I never got anything but the N/A back. I tried many longer and way more specific regexes as well with no success. In fact I tried so many versions I started to believe the problem might not be with that part. But still, when I tried it with Logger.log(match), it said there were no matches.
Here’s an example of a more detailed regex I tried which has only one correct returned value: (nevertheless it didn’t help the code finding the price)
/[0-9]*,d+.d+(?=</B></TD></TR><TR ALIGN="RIGHT"><TD>Qty Avg Price:</TD><TD><B>HUF [0-9]*,d+.d+</B></TD></TR><TR ALIGN="RIGHT"><TD>Max Price:</TD><TD><B>HUF [0-9]*,d+.d+</B></TD></TR></TABLE></TD></TR></TABLE></TD><TD VALIGN="TOP"><TABLE BORDER="0" WIDTH="100%" CELLPADDING="5" CELLSPACING="0"><TR><TD><TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" CLASS="fv"><TR ALIGN="RIGHT"><TD WIDTH="50%">Times Sold: )/
I have tried checkers like regexr.com and regex101.com but there my regexes worked and the correct prices were always found.
(I’m not a very experienced programmer, but I know some things and can learn quickly.)
Ben Sword is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Here’s a very simplified version you can try, it does seem to work in regex101 but not sure how it will perform in your implementation: />Avg Price:.+?(HUF[^<]+)/
The idea being:
>Avg Price:Start matching when you hit the text “Avg Price:” as the first thing in atd(to rule out qty avg).+?Non-greedy match to consume anything in the way until you hit the part you want(HUF[^<]+)Capture starting with HUF and take everything up until the table cell closes by only matching non-<characters
Also, commenting above made me realize that might be a better approach for you. If you can get the text content of the page instead of the HTML, you can just use regex to look for the string you want without needing to adjust for HTML tags
This script contains an API (Free Currency Exchange Rates API) posted on Github that automatically gets the USD currency on the last parameter and then returns it as an object. I also modified your regex syntax based on your needs. It displays all the average prices of a new figure in the last 6 months in HUF currency. You can read more about regex here.
Here’s a modified version of your script that should work:
function scrapeMultipleWebsites() {
const usdJson = JSON.parse(UrlFetchApp.fetch(("https://cdn.jsdelivr.net/npm/@fawazahmed0/currency-api@latest/v1/currencies/usd.json")).getContentText());
// Array of URLs to scrape numbers from
const urls = [
"https://www.bricklink.com/catalogPG.asp?M=sw1363&ColorID=0",
"https://www.bricklink.com/catalogPG.asp?M=sw1325&ColorID=0",
"https://www.bricklink.com/catalogPG.asp?M=sw1377&ColorID=0"
// Add up to 20 URLs in total
];
// Regular expression to extract numbers (adjust this if needed for each URL)
const regex = /<td>Avg Price:</td><td><b>US $(d+(.d+)?)/i;
// Target Sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("'24 SW Figure price monitor"); // Update sheet name
const lastRow = sheet.getLastRow();
const nextRow = Math.max(3, lastRow + 1); // Ensure the script starts writing from Row 3
// Add current date in Column A
const currentDate = new Date();
sheet.getRange(nextRow, 1).setValue(currentDate);
// Loop through URLs and scrape numbers
for (let i = 0; i < urls.length; i++) {
const html = UrlFetchApp.fetch(urls[i]).getContentText();
const match = html.match(regex);
Logger.log(match);
if (match) {
const number = match[1]; // Extract matched number
sheet.getRange(nextRow, i + 2).setValue((usdJson.usd.huf * number).toFixed(2)); // Start at Column B (index 2)
} else {
sheet.getRange(nextRow, i + 2).setValue("N/A"); // If no match, add "N/A"
}
}
}
Reference:
- exchange-api
2