Check product availability using Google Cloud Function and Google Ads Script

Would you like to check the stock status of your products without using any extra software and manage your Google Ads campaigns according to these results? You can check your product stocks using only Google Cloud Function and Google Ads Script via our solution below.

Create Google Cloud Function

Begin by creating a Google Cloud Function using NodeJS and the Puppeteer library to monitor product stock. This function will visit the given URLs in parallel with Headless Chrome, check the stock status and return the result.

1. Go to Google Cloud Console.
2. Select your project.
3. Create new Google Cloud Function.
4. Set settings for new function as below:
   • Name: checkProductAvailability
   • Trigger: HTTP
   • Memory allocated: 2 GB
   • Timeout: 540
   • Authentication: Allow unauthenticated invocations
5. Use files above for source code.
   • Index.js
   • Package.json
6. Save the new Google Cloud Function.
7. Get the URL of created function which you will need for later.

Package.json

{
    "name": "checkproductavailability",
    "version": "1.0.0",
    "description": "",
    "main": "index.js",
    "engines": {
        "node": ">=16.0.0"
    },
    "scripts": {
        "test": "echo \"Error: no test specified\" && exit 1",
        "start": "functions-framework --target=checkProductAvailability --trace-warning"
    },
    "author": "",
    "license": "ISC",
    "dependencies": {
        "@google-cloud/storage": "^5.16.1",
        "@google-cloud/functions-framework": "^2.1.0",
        "pending-xhr-puppeteer": "^2.3.3",
        "puppeteer": "^13.3.2",
        "puppeteer-cluster": "^0.23.0",
        "puppeteer-extra": "^3.2.3",
        "puppeteer-extra-plugin-adblocker": "^2.12.0",
        "puppeteer-extra-plugin-stealth": "^2.9.0"
    }
}

index.js

const puppeteer = require('puppeteer-extra');
const { Storage } = require('@google-cloud/storage')
const _StealthPlugin = require('puppeteer-extra-plugin-stealth');
const _AdblockerPlugin = require('puppeteer-extra-plugin-adblocker');
const { Cluster } = require('puppeteer-cluster');


puppeteer.use(_StealthPlugin());
puppeteer.use(_AdblockerPlugin());

exports.checkProductAvailability = async(req, res) => {


    var body
    var urlList
    var keywordList
    var elementList
    var hasErr = null
    try {
        body = req.body
        elementList = body.elements || ['body']
        urlList = body.urls || []
        keywordList = body.keywords

    } catch (err) {
        hasErr = err
        console.log(err)
    }

    if (!Array.isArray(urlList) && urlList) {
        urlList = [urlList]
    }
    if (!Array.isArray(keywordList) && keywordList) {
        keywordList = [keywordList]
    }
    if (!Array.isArray(elementList) && elementList) {
        elementList = [elementList]
    }

    if (!hasErr) {

        var data = {}
        const cluster = await Cluster.launch({
            concurrency: Cluster.CONCURRENCY_CONTEXT,
            maxConcurrency: 5,
            puppeteer
        });

        await cluster.task(async({ page, data: url }) => {
            await page.setViewport({ width: 1200, height: 1000 });
            await page.goto(url);
            const title = await page.title();


            let foundText = "-"
            let foundSelector = "-"
            let hasFound = false
            for (const f of elementList) {

                try {

                    const element = await page.waitForSelector(f, { timeout: 2000 }); // select the element
                    if (element) {

                        const value = await element.evaluate(el => el.textContent); // grab the textContent from the element, by evaluating this function in the browser context


                        for (const key of keywordList) {
                            if (value.toLocaleLowerCase().indexOf(key.toLowerCase()) >= 0) {
                                foundText = key
                                foundSelector = f
                                hasFound = true
                            }
                        }
                    } else {

                        foundText = ""
                        foundSelector = ""
                        hasFound = false
                    }
                } catch (errElem) {

                    console.log(errElem)
                    foundText = ""
                    foundSelector = ""
                    hasFound = false
                }

            }




            data[url] = {
                foundKeyword: foundText,
                foundSelector: foundSelector,
                pageUrl: url,
                pageTitle: title,
                returnType: hasFound,
                date: new Date()
            }
            console.log(data[url])


        });


        urlList.forEach(((f, i) => {
            cluster.queue(f);
        }))




        await cluster.idle();
        await cluster.close();

        res.send(JSON.stringify(data))
    } else {
        res.send("err")
    }



};

Create New Google SpreadSheet

1. Go to Google SpreadSheets.
2. Create a new blank spreadsheet.
3. Name the sheet as URL List.
4. Set header “URL” for first row.
5. Place your URL list (one URL for each row).
6. Note the URL of SpreadSheet (you’ll need it later).

Create Google Ads Script

After creating Google Cloud Function, we need a script that will send URLs to this function and store the results for processing. We must have a schedule in order to do this on a regular basis.

1. Go to Google Ads panel.
2. Create a new script.
3. Copy & paste script.
4. Remember to edit necessary fields such as functionURL and sheetURL.
5. Schedule script to run hourly.

Script code

//Edit spreadsheet URL above
const sheetURL = "your spreadsheet URL"
const spreadsheet = SpreadsheetApp.openByUrl(sheetURL)
const getUrlFromSheet = true
var urlList = ['']
//Keyword to lookup in page for product availability
const keywords = ['Out of stock', 'No stock']
//Selectors to look for in page - leave empty if not needed then will work for whole body
const selectors = ['.error-message']
//const selectors = ['']
const functionURL = "your function URL"
var today = new Date().toDateString()
//max Count of process that will run at once 
var maxProcess = 20

function main() {
    if (getUrlFromSheet) {
        urlList = getUrls()
    }
    getResultsSheet()
    urlList = removeExistsUrls()
    if (urlList.length > 0) {
        var requestArr = []
        for (let i = 0; i < urlList.length; i += maxProcess) {
            const chunk = urlList.slice(i, i + maxProcess);
            var data = {
                "urls": chunk,
                "keywords": keywords,
                "elements": selectors
            };
            var request = {
                "url": functionURL,
                'method': 'post',
                'contentType': 'application/json',
                'payload': JSON.stringify(data)
            };
            requestArr.push(request)



        }
        var resAll = fetchApi(requestArr)
        var resCsv = Object.assign({}, ...resAll);
        writeToSheet(resCsv)
    }
}

function writeToSheet(res) {
    var resJSON = res
    var sheet = spreadsheet.getSheetByName("Results");
    var csv = []
    for (const item in resJSON) {
        var row = []
        row.push(resJSON[item].pageUrl)
        row.push(resJSON[item].pageTitle)
        row.push(resJSON[item].returnType)
        row.push(getDateStr(new Date()))
        csv.push(row)
    }
    var rowCount = csv.length
    var colCount = 4
    sheet.getRange(sheet.getLastRow() + 1, 1, rowCount, colCount).setValues(csv);
}

function getResultsSheet() {
    spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
    var sheet = spreadsheet.getSheetByName("Results");
    if (sheet) {
        var row = sheet.getLastRow();
        if (row > 0) {} else {
            sheet.appendRow(["URL", "Page Title", "Out of Stock", "Date"])
            freezeRows()
        }
    } else {
        spreadsheet.insertSheet("Results");
        sheet = spreadsheet.getSheetByName("Results");
        sheet.appendRow(["URL", "Page Title", "Out of Stock", "Process Time"])
        freezeRows()
    }
}

function isToday(date) {
    if (date) {
        const today = getDateStr(new Date())
        const dateTime = new Date(date)
        const dateStr = dateTime.getFullYear() + '-' + (parseInt(dateTime.getMonth()) + 1) + '-' + dateTime.getDate()
        console.log(today)
        console.log(dateStr)
        return today == dateStr
    } else {
        return false
    }
}

function getDateStr(date) {
    const today = new Date()
    return today.getFullYear() + '-' + (parseInt(today.getMonth()) + 1) + '-' + today.getDate()
}

function removeExistsUrls() {
    const timeZone = AdsApp.currentAccount().getTimeZone();
    var sheet = spreadsheet.getSheetByName("Results");
    var row = sheet.getLastRow();
    if (row > 1) {
        var values = sheet.getRange(2, 1, row, 4).getValues().map(item => ({ url: item[0], dateStr: Utilities.formatDate(new Date(item[3]), timeZone, 'MMMM dd, yyyy 12:00:00 Z') }))
        var newUrlList = []
        urlList.forEach((item) => {
            if (item) {
                var found = values.filter(v => v.url == item && isToday(v.dateStr))
                if (found.length === 0) {
                    newUrlList.push(item)
                }
            }
        })
        return newUrlList
    } else {
        return urlList
    }
}

function getUrls() {
    sheet = spreadsheet.getSheetByName("URL List");
    var row = sheet.getLastRow();
    var values = sheet.getRange(2, 1, row - 1, 1).getValues()
    var urls = []
    values.forEach((item) => {
        urls.push(item[0])
    })
    return urls
}

function fetchApi(requests) {
    var responses = UrlFetchApp.fetchAll(requests);
    var res = responses.map(function(e) { return JSON.parse(e.getContentText()) });
    return res

}



function freezeRows() {
    var sheet = spreadsheet.getSheetByName("Results");
    sheet.setColumnWidth(1, 311);
    sheet.setColumnWidth(2, 265);
    sheet.setColumnWidth(3, 118);
    sheet.setColumnWidth(4, 202);
    sheet.setFrozenRows(1);

}

The diagram below explains how the whole process works together.

Do you need a custom solution?

Perfect! We know one-size-fits-all solutions often miss specific issues, so we love building custom scripts for our companies. Let’s collaborate to transform your needs int a practical solution. Contact us anytime.