Check URLs for Out of Stock Products from a Spreadsheet

[NOER]

My apologies if this sounds like a "Do this for me", but I'm interested in learning how this works. One client I work with is interested in checking URLs for out of stock text and sending an email containing products that are out of stock and products that are currently in stock. I'm trying to modify Russell Savage's similar script. The reason that I'm searching URLs from a spreadsheet is because there are occasionally products that are advertised on services other than AdWords and the client would like a single tracking spreadsheet. I had suggested creating ads with an incredibly low bid so that they would never drive traffic so that I could pull a keyword destination URL, but they weren't interested. When I run a preview of the script I get the error "TypeError: Cannot find function hasNext in object.... (Line 18)" is this because hasNext only works with adwords attributes and not with data from a spredsheet?

Here is the code I'm using:

/**************************************************

*Out of Stock Checker

*Version 2.0

*Checks URLs from a spreadsheet for out of stock products

*

* based on http://www.freeadwordsscripts.com/2013/10/disable-ads-and-keywords-for-out-of.html

**************************************************/

var STRIP_QUERY_STRING = true; // false would leave url params

var WRAPPED_URLS = false; // I don't need this, but might later

var spreadsheet_url = "https://docs.google.com/spreadsheets/d/1YNZqLZm8DpinTF5-yLDYkG7MgAmgOfAba-6QNhySKUI/edit#gid=0";

var oos = new Array();

var active = new Array();

var OUT_OF_STOCK_TEXT = 'Expired';

function main(){

var alreadyCheckedUrls = {};

  var iter = buildSelector().get;

  while(iter.hasNext()) {

    var entity = iter.next();

    var urlCean = cleanUrl(entity);

    if(alreadyCheckedUrls[urlCean]) {

      if(alreadyCheckedUrls[urlCean] === 'out of stock') {

        entity.push(expired);

      } else {

        entity.push(avtive);

      }

} else {

var htmlCode;

try {

htmlCode = UrlFetchApp.fetch(url).getContentText();

} catch(e) {

Logger.log('There was an issue checking:'+url+', skipping.');

continue;

}

if(htmlCode.indexOf(OUT_OF_STOCK_TEXT) >= 0){

alreadyCheckedUrls[url] = 'out of stock';

entity.push(oos);

} else {

alreadyCheckedUrls[url] = 'in stock';

entity.push(active);

}

}

Logger.log('Url: '+url+' is '+alreadyCheckedUrls[url]);

var subject = "!!!!!!EXIPRED PRODUCTS!!!!!!!"

var results1 = "These URLs are expired: " + oos

var results2 = "These URLs are still good: " + active

if (expired) {

MailApp.sendEmail('MY_EMAIL_ADDRESS', subject, results1)

}

}

function cleanUrl(url) {

if(WRAPPED_URLS) {

url = url.substr(url.lastIndexOf('http'));

if(decodeURIComponent(url) !== url) {

url = decodeURIComponent(url);

}

if(STRIP_QUERY_STRING) {

if(url.indexOf('?') >=0) {

url = url.split('?')[0];

}

}

if(url.indexOf('{') >= 0) {

// lets remove value track params!

url = url.replace(/\{[0-9a-zA-Z]+\}/g,'');

}

return url;

}

}

function buildSelector() {

var spreadsheet = SpreadsheetApp.openByUrl(spreadsheet_url);

var sheet = spreadsheet.getSheetByName('Active Products');

var selector = new Array();

var sheet_values = sheet.getDataRange().getValues();

for (i = 1; i < sheet_values.length; i++){

//make sure there is some data here

if(sheet_values[i][0] == "") continue;

var selector = sheet_values[i][6]

}

return selector;

}}

[Alexander Wang]

Hi there,

You're correct that the reason it doesn't work is because AdWords iterators work differently from spreadsheet data. If you look at the SpreadsheetApp documentation, you can see that the range.getValues() method returns a 2-dimensional array (basically a grid of values) NOT an iterator.

I don't know how the spreadsheet is formatted, but if the data is in the first column, you can iterate over the values like this:

// Get the 2-dimensional array of data from the spreadsheet.
var data = sheet.getDataRange().getValues();
// Iterate over each row. (i = row currently looking at, data.length = number of rows)
for (var i = 0; i < data.length; i++) {
  // Assume that the url is in the first (i.e. "zero-th") column of each row.
  var url = data[i][0];
  // Do stuff with the url
}

Arrays are a generic Javascript construct, so if you need help/guidance on working with arrays, there are a number of great external resources you can review:

• w3schools has a small tutorial on arrays: http://www.w3schools.com/js/js_arrays.asp
  
• w3schools also has a reference page that might be handy: http://www.w3schools.com/jsref/jsref_obj_array.asp
  
• codecademy has a really great JS tutorial that covers Arrays (in its "Data Structures" section): http://www.codecademy.com/en/tracks/javascript
  

I hope this helps.

Cheers,

Alex