Compare MacBook Prices Worldwide with Google Sheets

Looking to buy the new Macbook Pro with M3 chips? Wondering if it would be cheaper to purchase a Macbook in your local Apple store, or ask a friend who is travelling from Singapore or Japan to bring one for you?

Here’s a Google Sheet that can help you compare prices of MacBooks in different countries. It takes the current prices of MacBooks from Apple online stores in different countries and converts them to a common currency (US Dollars). The exchange rates are fetched directly from Google Finance so the prices will update automatically when the exchange rates change.

How the Macbook Price Comparison Sheet Works

I have written a Node.js script that fetches the current prices of MacBooks from the Apple website and writes them to Google Sheets. Here’s the code that scrapes the Apple website and parses the HTML to extract the prices.

Get Macbook Prices from Apple Website

Apple uses JSON-LD to embed structured pricing data in their web pages that can be easily parsed using cheerio. If the prices were not embedded in the wepage, an headless browser like Puppeteer would have been required to scrape the data.

const fs = require('fs');
const cheerio = require('cheerio');
const regions = ['us', 'in', 'sg', 'uk', 'ae', 'jp'];

const scrapeAppleStore = async (region) => {
  const url = `https://www.apple.com/${region}/shop/buy-mac/macbook-pro`;
  const response = await fetch(url);
  const html = await response.text();
  const $ = cheerio.load(html);
  const country = $('a.as-globalfooter-mini-locale-link').text().trim();
  const data = [];
  $('script[type="application/ld+json"]').each((i, elem) => {
    const json = JSON.parse($(elem).text());
    if (json['@type'] === 'Product') {
      json.offers.forEach((offer) => {
        const { priceCurrency, price, sku } = offer;
        data.push([country, sku.substring(0, 5), price, priceCurrency]);
      });
    }
  });
  return data;
};

(async () => {
  const promises = regions.map(scrapeAppleStore);
  const values = await Promise.all(promises);
  const prices = values.filter((value) => value.length > 0);
  fs.writeFileSync('prices.json', JSON.stringify(prices, null, 4));
})();

Get Currency Exchange Rates from Google Finance

The next step is to convert the prices of MacBooks in different currencies to a common currency (US Dollars). The exchange rates are fetched from Google Finance using the GOOGLEFINANCE function of Google Sheets.

=BYROW(A1:A27, LAMBDA(e, IF(e="USD",1, GOOGLEFINANCE("CURRENCY:USD"&e))))

The function accepts the currency code of the source and target currencies and returns the exchange rate. For instance, the formula =GOOGLEFINANCE("CURRENCY:USDINR") will fetch the current exchange rate of US Dollars to Indian Rupees.

Build the Macbook Price Comparison Sheet

Now that we have prices in a common current, we can build the price comparison table using the INDEX MATCH function of Google Sheets. The lookup criteria includes two columns - the SKU of the Macbook model and the country. The relevant formula is:

=INDEX(Data!$A$1:$E$648,MATCH($A3&B$1,Data!$A:$A&Data!$C:$C,0),5)

Also see: Monitor iPhone Stock with Google Sheets

source:https://ift.tt/UlCDsqe