Google Apps Script Finds Out-of-Stock Items

Once upon a digital dawn, a clever programmer named Emily lived in the bustling kingdom of Webtopia. She was known far and wide for her knack for solving intricate puzzles and automating mundane tasks. But one day, a peculiar problem landed on her virtual doorstep: the kingdom’s largest online marketplace was plagued by out-of-stock items, causing frustration among the shoppers.

To create a FREE Google Apps Script that scans all pages of your website for out-of-stock items and outputs them into a Google Sheet hourly, follow these steps:

1. Crawl Your Website
2. Identify Out-of-Stock Items
3. Log Out-of-Stock Items to Google Sheets
4. Set Up an Hourly Trigger

function scanWebsiteForOutOfStockItems() {
  const urls = ['https://yourwebsite.com/page1', 'https://yourwebsite.com/page2']; // Add all the URLs you need to scan
  const outOfStockItems = [];

  // Iterate through each URL and fetch the HTML content
  urls.forEach(url => {
    const response = UrlFetchApp.fetch(url);
    const html = response.getContentText();
    
    // Parse the HTML content
    const $ = Cheerio.load(html);
    
    // Assuming out-of-stock items are marked with a specific class, e.g., 'out-of-stock'
    $('.out-of-stock').each((index, element) => {
      const itemName = $(element).find('.item-name').text().trim(); // Adjust selector as needed
      outOfStockItems.push();
    });
  });

  // Open the target Google Sheet
  const sheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getSheetByName('OutOfStock');

  // Clear the sheet before adding new data
  sheet.clear();

  // Add headers to the sheet
  sheet.appendRow(['Item', 'Page URL']);

  // Append out-of-stock items to the sheet
  outOfStockItems.forEach(item => {
    sheet.appendRow(item);
  });
}

// Set a time-based trigger to run this function hourly
function createHourlyTrigger() {
  ScriptApp.newTrigger('scanWebsiteForOutOfStockItems')
    .timeBased()
    .everyHours(1)
    .create();
} 

Determined to restore order, Emily embarked on a quest to create a magical script—a Google Apps Script, to be precise—that would scan every nook and cranny of the marketplace’s sprawling website. Armed with her trusty keyboard and a steaming cup of virtual coffee, she set forth on her adventure.

Chapter 1: The Crawl Begins

Emily’s first task was to crawl the website. She summoned the mystical UrlFetchApp spell, which allowed her to traverse the digital highways and fetch the HTML content of each page. She delved deeper into the labyrinthine code and extracted precious product data like a digital archaeologist unearthing ancient artifacts.

Chapter 2: The Mark of Misfortune

But how would she identify the elusive out-of-stock items? Emily consulted her magical scroll, which revealed that these cursed artifacts bore a distinct mark—a class named “out-of-stock.” Armed with this knowledge, she cast her spell once more, using the powerful Cheerio library to parse the HTML content. Like a seasoned detective, she followed the breadcrumbs trail left by the forbidden class’s mischievous elements.

Chapter 3: Logging the Lost

With her inventory of out-of-stock items growing, Emily needed a safe repository. She invoked the ancient spirits of Google Sheets, opening a sacred document by its mystical ID. The sheet, named “OutOfStock,” awaited her command. She cleared its contents, leaving a pristine canvas.

Friendly Tips:

• URLs Array: Add the URLs of the website pages to be scanned.
• Fetch and Parse HTML: Use UrlFetchApp.fetch(url) and Cheerio.load(html) to fetch and parse the HTML content.
• Identify Out-of-Stock Items: Check for HTML classes like out-of-stock to identify items.
• Log to Google Sheets: Open the target Google Sheet, clear previous data, and append new out-of-stock items.
• Hourly Trigger: Set a trigger to run the script every hour.

Things to be aware of:

Compliance: Ensure compliance with robots.txt and terms of service.

Cheerio Library: Google Apps Script doesn’t natively support Cheerio. Use an equivalent library or regex-based parsing if feasible.

Deploy as a Web App: Consider deploying as a web app to handle large-scale crawling.

Website Structure: Customize HTML selectors to match your website’s structure.

Execution Limits: Be aware of execution time limits and quota. Large-scale crawling might exceed these limits.