norisk-marketing/HeroConf London 2018: Automating Search Query Processing.md

## HeroConf London 2018: Automating Search Query Processing.md

      
    Raw
  

              HeroConf London 2018: Automating Search Query Processing.md
            
          
Dear HeroConf London Attendants,
Thank for your listening to our talk "Automating Search Query Processing" at HeroConf London 2018. We hope you enjoyed the presentation.
This github-gist page aims to bring all related resources, links and code into one documentation.
The slideshare deck can be found here: slideshare.com/norisk
For any further questions, please reach out to cgutknecht@noriskshop.de or philippmainka@gmail.com (related to Sixt slides).
Thanks,
Christopher & Philipp

EntityFinder Script (Slide 14)

This Google Ads script identifies product feed entities in your new queries that have not yet been added to the account. Find the script below:
https://gist.github.com/norisk-marketing/afb26844fa3b68f2de9440bf7e006f4b#file-newqueryentityextractor-js
Copy and paste the logger output into a JSON-beautifier for better readibility: https://beautifier.io/
Note: this is a reduced version of a full automation workflow for demonstration purposes.
The example output video can be found here: https://www.youtube.com/watch?v=Rf4UKYcZMTk&feature=youtu.be

AskGoogle 2: Validate Geo Entities (Slide 18)

This is an example call of the knowledge graph API. You can retrieve your API_KEY in the Google cloud console.
https://kgsearch.googleapis.com/v1/entities:search?query=munich&types=City&languages=de&limit=10&indent=true&key=API_KEY
Here is the shown example sheet with a built-in Apps Script which translates geo-entity names:
https://docs.google.com/spreadsheets/d/1SF1q9ccN60rN3T2zRDBJ5mWM4cdYVgWeD22UmlYUJ5k/edit?usp=sharing

Ask Google 3 & 4: Typo, Plural & Synonym Recognition (Slide 18)

This helper Google Ads script uses the Google suggest API and a letter change distance algorithm, also called "Levenshtein distance", to calculate the closeness of two terms. Our Levenshtein calculation is based on a javascript implementation by Nico Ziemba (https://github.com/dziemba), but divides the letter change value by the string length of the reference keyword.
First, make a copy of this spreadsheet: https://docs.google.com/spreadsheets/d/1U_pkd-fglQTL7oy98sq4mrWthJSdUR4U4SXoDixHOAQ/edit#gid=0
Then use the following Ads script and specify your base keywords:
https://gist.github.com/norisk-marketing/afb26844fa3b68f2de9440bf7e006f4b#file-suggestrelatedkeywordfetcher-js
NOTE: The script will run into a timeout if lots of generic keywords are added, so start with five more specific terms. The script doesn't add the same term again, so it can run multiple times and e.g. reverse the order if needed.

Ask Google 5: Landinge Page Finder Script (Slide 18)

Our landing page finder script uses the Data4Seo scraping service to retrieve SERP results for different countries. You can easily create an account by logging in with your Google account and you'll get a 1000 credits for free: https://my.dataforseo.com/register
See the full script here or below:
https://gist.github.com/norisk-marketing/afb26844fa3b68f2de9440bf7e006f4b#file-landingpagefinder-js
The approach is useful if on-site search is too tolerant, bad quality or slow.
Alternatives:

The UI Tool "Landing Page Finder" by OneProSeo has a 100 keyword limit and one request limit per day. Working with Proxy Extensions such as Luminati.io should help bypass the request limit per day. Link: https://www.advertising.de/oneproseo/landingpagefinder/
The direct Google scrape typically runs into a captcha limit after about 70-80 requests, especially when using site: queries
The python library GoogleScraper offers an easy-to-use API for SERP extraction, but can not be localized yet: https://github.com/NikolaiT/GoogleScraper


Python: Get PartialRatio via Cloud Functions (Slide 25)

Google Cloud Functions is a serverless execution environment for node.js and Python3 (since July 2017) and serves as a simple web API wrapper for smaller scripts. The web server is based on Flask, a popular Python web framework. For introductory articles see more here:

Official Docs: https://cloud.google.com/functions/docs/concepts/python-runtime
Tutorial: https://itnext.io/writing-google-cloud-functions-with-python-3-49ac2e5c8cb3

Our cloud function uses the fuzzywuzzy package to partially compare strings and return a similarity value. This is very similar to 2. above, but a little more advanced. The original github repo can be found here: https://github.com/seatgeek/fuzzywuzzy
Here is the complete Google Ads script you can use to compare two strings. The order of the string doesn't matter.
var KEYWORDS = ["salomon mens shoes", "salomon shoes for men"]; 

function main() {
  getPartialRatio(KEYWORDS);    
}

function getPartialRatio(keywords){
  var formData = {
    "term1" : keywords[0],
    "term2" : keywords[1]
  };

  var options = {
    "method" : 'post',
    "contentType" : "application/json",
    "payload" : JSON.stringify(formData),
    "muteHttpExceptions" : true
  };

  var response = UrlFetchApp.fetch('https://europe-west1-adwords-scripts-big-query.cloudfunctions.net/partialStringRatio', options);
  Logger.log(response);
  return response;                             
}
If you wish to build your own cloud function, here is the Python script to use in your main.py file. Add 'fuzzywuzzy' in the requirements-txt file to add the package via Pip.
from flask import Flask
from flask import request
from fuzzywuzzy import fuzz
from fuzzywuzzy import process

#app = Flask(__name__)

#@app.route('/partialStringRatio', methods=['POST'])
def partialStringRatio(request):
    print(request.is_json)
    content = request.get_json()
    print(content)

    term1 = content.get('term1')
    term2 = content.get('term2')
    print (term1)
    print (term2)

    return str(fuzz.partial_ratio(term1, term2))
If you want to further explore Google Cloud Platform's (GCP) capabilities for PPC-related machine learning, check out these resource:

Google Ads Data Transfer: https://cloud.google.com/bigquery/docs/adwords-transfer
BigQueryML: https://www.analyticspros.com/blog/bigquery/introduction-to-bigquery-ml-bqml/
AutoML for NLP: https://cloud.google.com/natural-language/automl/docs/beginners-guide

If for some reason you're not a fan of GCP, here are some Amazon Web Service references:

AWS Comprehend: https://github.com/ziniman/aws-comprehend-demo


Conclusion

We hope there was something actionable that you can use for future PPC projects!
For any further questions, please reach out to cgutknecht@noriskshop.de.
 

## landingPageFinder.js
//******************************************//
//********** START CONFIGURATION ***********//
//******************************************//

var URL_LOOKUP_CONFIG = {
	site : "norisk.group",
	se_name : "google.de",
	se_language : "German",

  // Choose a value from Googles Location Table:
  // https://fusiontables.google.com/DataSource?docid=1Jlxrqc1dU3a9rsNW2l5xxlmQEKUu0dIPusImi41B#rows:id=1
	loc_name_canonical : "Berlin,Germany",

  // your brand name not removed when comparing
	brand_name : "norisk",

  // Make a COPY of this DEMO spreadsheet and remove the values:
  // https://docs.google.com/spreadsheets/d/1xxiQeesxTZG7LgrbNWGjAkzAYZTYo_lMBsfuXmh_Szc/edit#gid=0
	ad_spreadsheet_id : "1xxiQeesxTZG7LgrbNWGjAkzAYZTYo_lMBsfuXmh_Szc",
};

// Create a Data4SEO Account and add your API credentials here:
var URL_LOOKUP_USERNAME = "name@gmail.com";
var URL_LOOKUP_PASSWORD = "ybnk239fK7d2d2S";

//******************************************//
//********** END CONFIGURATION *************//
//******************************************//


// DON'T (!) CHANGE THESE SETTINGS
var API_SET_URL = 'https://api.dataforseo.com/v2/srp_tasks_post';
var API_GET_URL = 'https://api.dataforseo.com/v2/srp_tasks_get';
var AUTH_HEADER = 'Basic ' + Utilities.base64Encode(URL_LOOKUP_USERNAME + ':' + URL_LOOKUP_PASSWORD);


function main() {
	var queries = ["norisk marketing","norisk online","norisk gtm","norisk adwords scripts"];
	var lookUp_StorageHandler = new SpreadsheetHandler();
	dataHandler = new Data4SeoHandler(lookUp_StorageHandler);

	// load task ids of which the result hasn't been retrieved from the data4seo API
	var taskIds = lookUp_StorageHandler.loadUnFinishedTaskIds();

	// retrieve data from api and store it inside the results property of the Data4SeoHandler
	dataHandler.getTaskResults(taskIds);
	dataHandler.createNewTasks(queries);
}

/**
 * Data4SeoHandler Object
 * @param {UrlLookupStorageHandler} storageHandler Handler that manages the BigQuery API.
 */

function Data4SeoHandler(storageHandler) {
	this.results = {};
	this.storageHandler = storageHandler;
}

/**
 * Submit new queries to API
 * @param  {Array} queries Array that contains strings representing new queries. If queries already exist in the database, they won't be submitted.
 */
Data4SeoHandler.prototype.createNewTasks = function(queries) {

	// fetch existing queries
	var temp_existingQueries = this.storageHandler.getExistingQueries();
	var existingQueries = {};

	Logger.log("Queries to be checked: " + JSON.stringify(queries));
	Logger.log("Existing queries: " + JSON.stringify(temp_existingQueries));

	for (var j = 0; j < temp_existingQueries.length; j++) {
		if (!existingQueries[temp_existingQueries[j][0]]) {
			existingQueries[temp_existingQueries[j][0]] = temp_existingQueries[j][0];
		}
	}

	for (var k = 0; k < queries.length; k++) {
		query = queries[k];
		if (existingQueries[query]) {
			queries.splice(k, 1);
			k--;
		}
	}

	Logger.log("New queries: " + JSON.stringify(queries));

	if (queries.length === 0) {
		Logger.log("No new queries to be submitted to D4S API.");
		return;
	}
	Logger.log("Submitting new tasks to API.");
	Logger.log("Queries: " + JSON.stringify(queries));

	var data = {
		data: {}
	};

	// Creating Data4SEO data objects per query
	for (var i = 0; i < queries.length; i++) {
		query = URL_LOOKUP_CONFIG.site + ' ' + queries[i]; // removed "site:"+ to skip higer credit cost
		var object = {
			'priority': '1',
			'site': URL_LOOKUP_CONFIG.site,
			'se_name': URL_LOOKUP_CONFIG.se_name,
			'se_language': URL_LOOKUP_CONFIG.se_language,
			'loc_name_canonical': URL_LOOKUP_CONFIG.loc_name_canonical,
			'key': query
		};
		data.data[query] = object;
	} // END For queries

	var options = {
		'method': 'post',
		'contentType': 'application/json',
		'headers': {
			'Authorization': AUTH_HEADER
		},
		'muteHttpExceptions': true,
		'payload':JSON.stringify(data)
	};

	var response = JSON.parse(UrlFetchApp.fetch(API_SET_URL, options));

	for (var result in response.results) {
		var site_string = URL_LOOKUP_CONFIG.site + " "; // removed "site:"+ to skip higer credit cost
		var sanitizedQuery = response.results[result].post_key.replace(site_string, "");
		this.storageHandler.pushDataToCache(response.results[result].task_id, sanitizedQuery);
	}

	if (this.storageHandler.cache.length > 0) this.storageHandler.flush();
};

/**
 * retrieves the static URL by query
 * @param  {string} query
 * @return {object}       returns an object. The url property contains a string with the extracted url. The similarityValue property contains a float representing the similarity value of query and retrieved url.
 */
Data4SeoHandler.prototype.getStaticUrl = function(query) {

	/* var db_query = this.storageHandler.buildSelectQuery_(["url","similarityValue"], [{inputField: "query", operator: "=", value: query}]);
	var results = this.storageHandler.queryDataTable_(db_query);
	*/

	var results = this.storageHandler.getUrlAndSimValueForQuery(query);
	if (results.length === 0) return {
		url: "",
		similarityValue: 0
	};
	var url = results[0][0];
	var simValue = results[0][1];

	return {
		url: url,
		similarityValue: simValue
	};
};

/*
 * @param {string} query
 * @param {string} url
 * @return {float} simValue
 */
Data4SeoHandler.prototype.calculateSimilarityValue = function(query, url) {
	if (!url || !query) return 0;
	var match;
	var regex = /^h?t?t?p?s?\:?\/?\/?www\.[\w|\d|\-|\_]*\.\w*\/(.*)$/gi;

	match = url.split(URL_LOOKUP_CONFIG.site + "/")[1];

	if (typeof(match) == "undefined" || !match) return 0;

	var validatedUrl = match.replace(/\//g, " ");
	validatedUrl = validatedUrl.replace(/^\s/, "").replace(/\s$/, "").replace(/_/g, " ");
	validatedUrl = validatedUrl.split("-").join(" ");
	validatedUrl = validatedUrl.replace(URL_LOOKUP_CONFIG.brand_name, "").split(" ").sort().join(" ");

	query = query.split("-").join(" ");
	query = query.replace(URL_LOOKUP_CONFIG.brand_name + " ", "").split(" ").sort().join(" ");
	//Logger.log("strippedQuery :" + query + " | url-slug : " + validatedUrl);

	var wordDistance = this._calculateLetterChanges(validatedUrl, query);
	var simValue = (1 - wordDistance / validatedUrl.length).toFixed(2);

	return simValue;
};

/**
 * retrieves the related searches by query
 * @param  {string} query String that contains the query
 * @return {object}       returns an object that contains the related searches as array, the intersection_value and the intersection_word
 */
Data4SeoHandler.prototype.getRelatedSearches = function(query) {

	var relatedSearches = this.storageHandler.getRelatedSearchesForQuery(query);
	if (relatedSearches.length === 0) {
		return {
			relatedSearches: [],
			intersection_word: "",
			intersection_value: 0
		};
	}
	return {
		relatedSearches: relatedSearches[0][0].split(","),
		intersection_word: this._computeWordIntersection(relatedSearches[0][0].split(",")).total.max_intersect_word,
		intersection_value: this._computeWordIntersection(relatedSearches[0][0].split(",")).total.max_intersect_value
	};
};

/**
 * Method that retrieves the results of a data 4 seo api and stores it in BigQuery
 * @param  {array} taskIds
 */
Data4SeoHandler.prototype.getTaskResults = function(taskIds) {

	this.taskIds = taskIds;
	var options =  {
		'method': 'get',
		'contentType': 'application/json',
		'headers': {
			'Authorization': AUTH_HEADER
		},
		'muteHttpExceptions': true
	};
	// var results = [];

	for (var i = 0; i < taskIds.length; i++) {
		var taskId = taskIds[i];

		var fetch_target = API_GET_URL.charAt(API_GET_URL.length - 1) == "/" ? API_GET_URL : API_GET_URL + "/";
		fetch_target += taskId;
		var result = JSON.parse(UrlFetchApp.fetch(fetch_target, options));
		var url, relatedSearches;

		var query = result.results.organic[0].post_key.replace(URL_LOOKUP_CONFIG.site, "");

		try {
			var bestUrl = {
				"simValue": 0,
				"url": ""
			};

			for (var j = 0; j < 3; j++) {
				url = result.results.organic[j].result_url;
				var resultRootDomain = url.replace("https://", "").replace("www.//", "");

				// Get simValue if url contains domain AND is not home
				if (url.indexOf(URL_LOOKUP_CONFIG.site) != -1 && URL_LOOKUP_CONFIG.site !== resultRootDomain) {
					var similarityValue = this.calculateSimilarityValue(query, url);
					if (similarityValue > bestUrl.simValue) {
						bestUrl.url = url;
						bestUrl.simValue = similarityValue;
					}
				}
			} // END For loop	j = url results

			relatedSearches = typeof result.results.extra.related !== "undefined" && typeof result.results.extra.related[j] !== "undefined"? result.results.extra.related[j].join() : relatedSearches = "";

			this.storageHandler.setStatusDone(taskId, bestUrl.url, relatedSearches, bestUrl.simValue);
			this.results[taskId] = result;

		} catch (e) {
			Logger.log("TaskResultException: Task not finished or no url found. Error:" + e);
			Logger.log(e.stack);
			throw e;
		}
	} // END FOR loop taskIds
};

/**
 * computes the intersection of an array of strings
 * @param  {array} array array containing the strings to be computed
 * @return {object}
 * object = { total: { max_intersect_word: "", max_intersect_value: 0, word_count: 0 }, combinations: {}};
 */
Data4SeoHandler.prototype._computeWordIntersection = function(array) {
	var object = {
		total: {
			max_intersect_word: "",
			max_intersect_value: 0,
			word_count: 0
		},
		combinations: {}
	};
	for (var i = 0; i < array.length; i++) {
		var value_array = array[i].split(" ");
		value_array = this._getCombinations(value_array);

		for (var j = 0; j < value_array.length; j++) {
			var value = value_array[j];
			if (object.combinations[value]) {
				continue;
			}
			object.combinations[value] = {
				hit_count: 0,
				inbetween_count: 0,
				inbeginning_count: 0,
				atend_count: 0,
				word_count: value.split(" ").length
			};
			for (var k = 0; k < array.length; k++) {
				if (array[k].match(new RegExp(".*" + value + ".*", "g"))) {
					object.combinations[value].hit_count++;
				}
				if (array[k].match(new RegExp(".*\\s" + value + "\\s.*", "g"))) {
					object.combinations[value].inbetween_count++;
				}
				if (array[k].match(new RegExp("^" + value + "\\s.*", "g"))) {
					object.combinations[value].inbeginning_count++;
				}
				if (array[k].match(new RegExp(".*\\s" + value + "$", "g"))) {
					object.combinations[value].atend_count++;
				}
			}
			object.combinations[value].appearing_index = object.combinations[value].hit_count / array.length;
			object.combinations[value].appearing_fullWord_index = (object.combinations[value].inbetween_count + object.combinations[value].inbeginning_count + object.combinations[value].atend_count) / array.length;

			if (object.combinations[value].appearing_fullWord_index > object.total.max_intersect_value) {
				object.total.max_intersect_value = object.combinations[value].appearing_fullWord_index;
				object.total.max_intersect_word = value;
				object.total.word_count = object.combinations[value].word_count;
			} else if (object.combinations[value].appearing_fullWord_index == object.total.max_intersect_value && object.combinations[value].word_count > object.total.word_count) {
				object.total.max_intersect_value = object.combinations[value].appearing_fullWord_index;
				object.total.max_intersect_word = value;
				object.total.word_count = object.combinations[value].word_count;
			}
		}

	}
	return object;
};

/**
 * generates combinations of characters
 * @param  {array} chars strings
 * @return {array}       combinations
 */
Data4SeoHandler.prototype._getCombinations = function(chars) {
	var result = [];
	var f = function(prefix, chars) {
		for (var i = 0; i < chars.length; i++) {
			result.push(prefix + chars[i]);
			f(prefix + chars[i] + " ", chars.slice(i + 1));
		}
	};
	f('', chars);
	return result;
};

/**
 * calculate letter changes between to words
 * @param  {string} a first word
 * @param  {string} b second word
 * @return {number}
 */
Data4SeoHandler.prototype._calculateLetterChanges = function(a, b) {
	var tmp;
	if (a.length === 0) {
		return b.length;
	}
	if (b.length === 0) {
		return a.length;
	}
	if (a.length > b.length) {
		tmp = a;
		a = b;
		b = tmp;
	}

	var i, j, res, alen = a.length,
		blen = b.length,
		row = Array(alen);
	for (i = 0; i <= alen; i++) {
		row[i] = i;
	}

	for (i = 1; i <= blen; i++) {
		res = i;
		for (j = 1; j <= alen; j++) {
			tmp = row[j - 1];
			row[j - 1] = res;
			res = b[i - 1] === a[j - 1] ? tmp : Math.min(tmp + 1, Math.min(res + 1, row[j] + 1));
		}
	}
	return res;
};

/**
 * @constructor SpreadsheetHandler
 */
function SpreadsheetHandler() {
	var spreadsheet_id = URL_LOOKUP_CONFIG.ad_spreadsheet_id;
	var sheetName = "(urls)";
	this.timeStamp = this.getTimeStamp();
	this.cache = [];

	try {
		this.controlSpreadsheet = SpreadsheetApp.openById(URL_LOOKUP_CONFIG.ad_spreadsheet_id).getSheetByName(sheetName);
	} catch (e) {
		SpreadsheetApp.openById(URL_LOOKUP_CONFIG.ad_spreadsheet_id).insertSheet(sheetName);
		try {
			this.controlSpreadsheet = SpreadsheetApp.openById(URL_LOOKUP_CONFIG.ad_spreadsheet_id).getSheetByName(sheetName);
		}
		catch (e2) {
			throw new Error("SheetNotFoundException: Please add the sheet '(urls)' to your ad template. This is needed for storing url lookup data. error: " + e2 + ". stack : " + e2.stack);
		}
	}
}


SpreadsheetHandler.prototype.pushDataToCache = function(task_id, query) {
	this.cache.push([task_id, query]);
};

SpreadsheetHandler.prototype.flush = function() {
	Logger.log(JSON.stringify(this.cache));
	for (var i = 0; i < this.cache.length; i++) {
		try {
			this.controlSpreadsheet.appendRow([this.cache[i][0], this.cache[i][1], "", "no", "", "", ""]);
		} catch (e) {
			throw new Error("Error appending new row to spreadsheet. " + e.stack);
		}
	}
	this.cache = [];
};


/**
 * function that loads values from a spreadsheet
 * @return {Array}
 */
SpreadsheetHandler.prototype.loadUnFinishedTaskIds = function() {
	var existingValues = [];
	existingValues = this.controlSpreadsheet.getRange("A2:F" + this.controlSpreadsheet.getLastRow()).getValues();
	var finalArray = [];

	for (var i = 0; i < existingValues.length; i++) {
		// TBD set exact index of status
		if (existingValues[i][3] == "no") {
			finalArray.push(existingValues[i][0]);
		}
	}
	return finalArray;
};


SpreadsheetHandler.prototype.setStatusDone = function(task_id, url, relatedSearches, similarityValue) {
	var existingIds = this.controlSpreadsheet.getRange("A2:A" + this.controlSpreadsheet.getLastRow()).getValues();
	var rowIndex = existingIds.findIndex(findIndexCallback, task_id) + 2;

	if (rowIndex < 2) return;

	this.controlSpreadsheet.getRange(rowIndex, 3).setValue(url);
	this.controlSpreadsheet.getRange(rowIndex, 4).setValue("yes");
	this.controlSpreadsheet.getRange(rowIndex, 5).setValue(relatedSearches);
	this.controlSpreadsheet.getRange(rowIndex, 6).setValue(similarityValue);
	this.controlSpreadsheet.getRange(rowIndex, 7).setValue(this.timeStamp);

};

SpreadsheetHandler.prototype.getExistingQueries = function() {
	return this.controlSpreadsheet.getRange("B2:B" + this.controlSpreadsheet.getLastRow()).getValues();
};

SpreadsheetHandler.prototype.getUrlAndSimValueForQuery = function(query) {
	var existingQueries = this.controlSpreadsheet.getRange("B2:B" + this.controlSpreadsheet.getLastRow()).getValues();
	var rowIndex = existingQueries.findIndex(findIndexCallback, query) + 2;
	if (rowIndex < 2) return [];

	return [
		[this.controlSpreadsheet.getRange(rowIndex, 3).getValue(), this.controlSpreadsheet.getRange(rowIndex, 6).getValue()]
	];
};

SpreadsheetHandler.prototype.getRelatedSearchesForQuery = function(query) {
	var existingQueries = this.controlSpreadsheet.getRange("B2:B" + this.controlSpreadsheet.getLastRow()).getValues();
	var rowIndex = existingQueries.findIndex(findIndexCallback, query) + 2;

	if (rowIndex < 2) return [];

	return [
		[this.controlSpreadsheet.getRange(rowIndex, 5).getValue()]
	];
};

function findIndexCallback(element) {
	return element[0] == this;
}

/*
 * @return string dateTime
 */
SpreadsheetHandler.prototype.getTimeStamp = function() {

	var currentdate = new Date();
	var currrentHourGmc = currentdate.getUTCHours() + 1;

	var dateTime =
		(currentdate.getDate() < 10 ? '0' + currentdate.getDate().toString() : currentdate.getDate()) + "." +
		(currentdate.getMonth() + 1) + "." +
		currentdate.getFullYear() + " , " +
		currrentHourGmc + ":" +
		(currentdate.getMinutes() < 10 ? '0' + currentdate.getMinutes().toString() : currentdate.getMinutes());

	return dateTime; // target format = '24.2.2017 , 12:09'
};


// https://tc39.github.io/ecma262/#sec-array.prototype.findIndex
if (!Array.prototype.findIndex) {
	Object.defineProperty(Array.prototype, 'findIndex', {
		value: function(predicate) {
			// 1. Let O be ? ToObject(this value).
			if (this == null) {
				throw new TypeError('"this" is null or not defined');
			}

			var o = Object(this);

			// 2. Let len be ? ToLength(? Get(O, "length")).
			var len = o.length >>> 0;

			// 3. If IsCallable(predicate) is false, throw a TypeError exception.
			if (typeof predicate !== 'function') {
				throw new TypeError('predicate must be a function');
			}

			// 4. If thisArg was supplied, let T be thisArg; else let T be undefined.
			var thisArg = arguments[1];

			// 5. Let k be 0.
			var k = 0;

			// 6. Repeat, while k < len
			while (k < len) {
				// a. Let Pk be ! ToString(k).
				// b. Let kValue be ? Get(O, Pk).
				// c. Let testResult be ToBoolean(? Call(predicate, T, « kValue, k, O »)).
				// d. If testResult is true, return k.
				var kValue = o[k];
				if (predicate.call(thisArg, kValue, k, o)) {
					return k;
				}
				// e. Increase k by 1.
				k++;
			}

			// 7. Return -1.
			return -1;
		},
		configurable: true,
		writable: true
	});
}

## newQueryEntityExtractor.js
///////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////
//
// >>>>>  NEW QUERY ENTITY EXTRACTOR -  START CONFIG    >>>>
//
///////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////

// Add your shopping feed here. To improve results: create a copy with only the core product titles in the title column
var FEED_URL = "$$CSV-FEED$$";
var COLUMN_SEPARATOR = ",";  // ONLY "," allowed! Make sure you change this!

var FEED_PARSER_CONFIG = {
  "brandColumnValue": "brand",
  "categoryColumnValue": "product_type",
  "titleColumnValue": "title"
};
var SQA_REQUIRED_COLUMNS = ["brand", "product_type", "title", "gender"]; // ONLY extend! Don't reduce
var SQA_EXTRA_COLUMNS = ["size", "color"];

var CUSTOM_WORDS = {
  "buy": ["buy", "shop", "online"],
  "sale": ["cheap", "outlet", "reduced", "sale", "clearance", "low price"],
  "year": ["2017", "2018", "2019"],
  "UK": ["UK", "United Kingdom", "2019"],
  "plusSize": ["plus size", "oversize", "plussize", "long size"],
  "size": ["size","sizes"],
  "occasions": ["wedding", "bridesmaid", "mother of bride", "bridal"],
  "fill_words": ["by", "with", "for", "at", "in", "up to"],
};

var CORECAT_ARRAY_SINGULAR = ['suit', 'blouse', 'shirt', 'jacket', 'dress', 'coat'];
var CORECAT_ARRAY_PLURAL = ['suits', 'blousens', 'shirts', 'jackets', 'dresses', 'coats'];


var NEW_PAID_QUERY_CONFIG = {
  "searchCampaignOnly": 0,
  "campaignExclude": "Brand",
  "queryExclude" : ["your brand", "discount", "coupon"],
  "timeSpan": "LAST_7_DAYS",
  "queryInclude_TermSimilarity": "standard",  // Eligible values: open, standard, strict

  "checkAgainst_Matchtypes" : "exactOnly", // "exactOnly"
  "checkAgainst_CampaignStatus" : "nonRemoved", // "enabledOnly", "nonRemoved"
  "checkAgainst_AdGroupStatus"  : "nonRemoved",  // "enabledOnly", "nonRemoved"
  "checkAgainst_KeywordStatus"  : "nonRemoved",  // "enabledOnly", "nonRemoved"

  "kpiThresholds": [{
    metric: "Conversions",
    operator: ">",
    value: 0.9
  }, {
    metric: "Impressions",
      operator: ">",
      value: 9
    }, {
      metric: "ConversionValue",
      operator: ">",
      value: 20
    },
   {"minRoas" : 2}],
};


var NEW_ORGANIC_QUERY_CONFIG = {
  "loadOrganicQueries": 0,
  "campaignExclude": "B__Brand",
  "timeSpan": "LAST_30_DAYS",
  "kpiThresholds": [{
    metric: "OrganicClicks",
    operator: ">",
    value: 5,
  }, {
    metric: "OrganicAveragePosition",
    operator: ">",
    value: 4,
  }, {
    metric: "CombinedAdsOrganicClicks",
    operator: ">",
    value: 5,
  }]
}


///////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////
//
// <<<<<<      END CONFIG    <<<<<<
//
///////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////

var DEBUG_MODE = 0;
var API_KEY = "aDfUSTUqn45D2WJJ8LrxnMcPQSLVcf8y";
var INPUT_SOURCE_MODE = "SQA";

function main(){
  try{
    var scriptfile_name = "https://scripts.adserver.cc/getScript.php?package=nrSearchqueryAutomator&version=unstable&script=nrSearchqueryAutomator.js&aid=123-456-7890&key="+API_KEY;
    var scriptFile_raw = UrlFetchApp.fetch(scriptfile_name).getContentText();

  try{
    eval(scriptFile_raw);
    nrSearchqueryAutomator();
  } catch (e) {try {if(AdWordsApp.getExecutionInfo().isPreview() === false) Logger.log("Error in Script: Exception: "+e.message+"\r\nStacktrace:\r\n"+e.stack);} catch (e2) {Logger.log(e2.stack);} throw e;}
  } catch(e3){ Logger.log(e3.stack); throw e3; }
}

## suggestRelatedKeywordFetcher.js
/**********************************************************************************************************************
* Combined Suggest & Related Searches Scraper
* By norisk Group (Chris Gutknecht & Alex Groß)
*
* Leverages the Google Autocomplete feature to find potential keyword opportunities and negative keywords
* Uses a DerekMartin script to scrape related searches. Reference: MixedMarketingArtist.com
**********************************************************************************************************************/

/*** START CONFIG ***/

var SHEET_URL = "https://docs.google.com/spreadsheets/d/1U_pkd-fglQTL7oy98sq4mrWthJSdUR4U4SXoDixHOAQ/edit#gid=0";
var targetKeywords = ["st barth avocado öl", "la girafe beißring", "nike air max"]; // this is the keyword that you want to know about
var DEBUG_MODE = 0;

/*** END CONFIG ***/

function main() {

  for(var i=0; i< targetKeywords.length; i++) {
    Logger.log("\nStarting to fetch suggest + related keywords for '" + targetKeywords[i] + "'");
    var keywordSuggestions = {"baseKeyword" : targetKeywords[i], "synonyms": {}, "allRelatedAndSuggestKeywords": [], "keywordObjects" : [], "intersections" : {}};

    keywordSuggestions = getSuggestKeywords(targetKeywords[i], keywordSuggestions);
    keywordSuggestions = getRelatedKeywords(targetKeywords[i], keywordSuggestions);
    keywordSuggestions.intersections = computeWordIntersection(keywordSuggestions.allRelatedAndSuggestKeywords);
    keywordSuggestions.synonyms = getSynonyms(targetKeywords[i], keywordSuggestions);
    if(DEBUG_MODE === 1) Logger.log("keywordSuggestions:\n" + JSON.stringify(keywordSuggestions));
    writeToSheet(keywordSuggestions);
  }
  Logger.log("\n\nDone.");
}

function getSuggestKeywords(keyword, keywordSuggestions){

  var keyword = keyword.replace(/ /g,"+").replace(/-/g,"+").replace(/_/g,"+");
  var requestUrl = "https://suggestqueries.google.com/complete/search?output=chrome&hl=en&q=" + keyword;
  var response = JSON.parse(UrlFetchApp.fetch(requestUrl));

  if(typeof response[1][0] != "undefined") {

    for(var j=0;j<10;j++) {
      if(j === 0 || response[4]["google:suggestrelevance"][j-1] - response[4]["google:suggestrelevance"][j] < 10) {

        var levenshteinDist = dziemba_levenshtein(response[0].toLowerCase(), response[1][j]);
        var simMetric = (1-levenshteinDist/response[1][j].length).toFixed(2);

        keywordSuggestions.allRelatedAndSuggestKeywords.push(response[1][j]);
        keywordSuggestions.keywordObjects.push({
          "term" : response[1][j],
          "simValue" : simMetric,
          "type" : (simMetric > 0.84 && levenshteinDist < 2) ? "typo/plural" : ""
        });
      }
    } // END FOR Loop
  }

  return keywordSuggestions;
}


function getRelatedKeywords(keyword, keywordSuggestions){

  buildKeywordList(keyword);
  brandKeywordList.sort();

  for(var i=0; i< brandKeywordList.length; i++){
    if(keywordSuggestions.allRelatedAndSuggestKeywords.indexOf(brandKeywordList[i]) == -1 && brandKeywordList[i] !== keyword) {

      var levenshteinDist = dziemba_levenshtein(keyword.split(" ").sort().join(","), brandKeywordList[i].split(" ").sort().join(","));
      var wordDistance = (1-levenshteinDist/brandKeywordList[i].length).toFixed(2);

      keywordSuggestions.allRelatedAndSuggestKeywords.push(brandKeywordList[i]);
      keywordSuggestions.keywordObjects.push({
        "term": brandKeywordList[i],
        "simValue" : wordDistance,
        "type" : ""
      });
    }
  }

  return keywordSuggestions;
}

function getSynonyms(keyword, keywordSuggestions){
  var synonyms = [];

  for(var i=0; i<Object.keys(keywordSuggestions.intersections.combinations).reverse().length; i++){
    var combinationKey = Object.keys(keywordSuggestions.intersections.combinations)[i];

    if(combinationKey.split(" ").length > 1 && keywordSuggestions.intersections.combinations[combinationKey].hit_count > 3 && keyword.indexOf(combinationKey) == -1) {
      synonyms.push({"synonym" : combinationKey, "hitCount" : keywordSuggestions.intersections.combinations[combinationKey].hit_count});
    }
  }
  return synonyms;
}

function writeToSheet(keywordSuggestions){

  var spreadsheet = SpreadsheetApp.openByUrl(SHEET_URL);
  var sheet = spreadsheet.getActiveSheet();

  var headerRange = sheet.getRange(1, 1, 1, 5);
  headerRange.setValues([["baseKeyword", "type", "term", "simValue", "detail"]]);
  headerRange.setBackground("yellow").setFontWeight("bold").setHorizontalAlignment("center");

  var suggestionsArray = convertSuggestionsToArray(keywordSuggestions);

  var firstFreeRow = getLastReportRow(sheet);
  var destinationRange = sheet.getRange(firstFreeRow, 1, suggestionsArray.length, suggestionsArray[0].length);

  var firstColumnValues = sheet.getRange(2,1,firstFreeRow-1,1).getValues();
  var suggestionsAlreadyCopied = false;

  for(var i=0; i< firstColumnValues.length;i++){
    if(firstColumnValues[i][0] === suggestionsArray[0][0]) suggestionsAlreadyCopied = true;
  }

  if(suggestionsAlreadyCopied === false) {
    destinationRange.setValues(suggestionsArray);
    Logger.log("suggestionsArray printed to sheet-URL: " + SHEET_URL);
  } else Logger.log("suggestionsArray already contained in sheet-URL: " + SHEET_URL);

}

function convertSuggestionsToArray(keywordSuggestions) {
  var suggestionsArray = [];
  suggestionsArray.push([keywordSuggestions.baseKeyword, "baseKeyword", keywordSuggestions.baseKeyword, "1", ""]);

  for(var i=0; i< keywordSuggestions.synonyms.length; i++){
    suggestionsArray.push([keywordSuggestions.baseKeyword, "synonym", keywordSuggestions.synonyms[i].synonym, "", "synHits:"+keywordSuggestions.synonyms[i].hitCount]);
  }

  for(var i=0; i< keywordSuggestions.keywordObjects.length; i++){
    suggestionsArray.push([keywordSuggestions.baseKeyword, "suggestion", keywordSuggestions.keywordObjects[i].term, keywordSuggestions.keywordObjects[i].simValue, keywordSuggestions.keywordObjects[i].type]);
  }

  return suggestionsArray;
}

function getLastReportRow(singleSheet) {
  var column = singleSheet.getRange('A:A');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] !== "" ) {
    ct++;
  }
  return (ct+1);
}


function dziemba_levenshtein(a, b){
  var tmp;
  if (a.length === 0) { return b.length; }
  if (b.length === 0) { return a.length; }
  if (a.length > b.length) { tmp = a; a = b; b = tmp; }

  var i, j, res, alen = a.length, blen = b.length, row = Array(alen);
  for (i = 0; i <= alen; i++) { row[i] = i; }

  for (i = 1; i <= blen; i++) {
    res = i;
    for (j = 1; j <= alen; j++) {
      tmp = row[j - 1];
      row[j - 1] = res;
      res = b[i - 1] === a[j - 1] ? tmp : Math.min(tmp + 1, Math.min(res + 1, row[j] + 1));
    }
  }
  return res;
}


/**
 * computes the intersection of an array of strings
 * @param  {array} array array containing the strings to be computed
 * @return {object}
 * object = { total: { max_intersect_word: "", max_intersect_value: 0, word_count: 0 }, combinations: {}};
 */
function computeWordIntersection(array) {
  var object = {
    total: {
      max_intersect_word: "",
      max_intersect_value: 0,
      word_count: 0
    },
    combinations: {}
  };
  for (var i = 0; i < array.length; i++) {
    var value_array = array[i].split(" ");
    value_array = getCombinations(value_array);

    for (var j = 0; j < value_array.length; j++) {
      var value = value_array[j];
      if (object.combinations[value]) {
        continue;
      }
      object.combinations[value] = {
        hit_count: 0,
        inbetween_count: 0,
        inbeginning_count: 0,
        atend_count: 0,
        word_count: value.split(" ").length
      };
      for (var k = 0; k < array.length; k++) {
        try{
          if (array[k].match(new RegExp(".*" + value + ".*", "g"))) {
            object.combinations[value].hit_count++;
          }
          if (array[k].match(new RegExp(".*\\s" + value + "\\s.*", "g"))) {
            object.combinations[value].inbetween_count++;
          }
          if (array[k].match(new RegExp("^" + value + "\\s.*", "g"))) {
            object.combinations[value].inbeginning_count++;
          }
          if (array[k].match(new RegExp(".*\\s" + value + "$", "g"))) {
            object.combinations[value].atend_count++;
          }
        } catch(e){}
      }
      object.combinations[value].appearing_index = object.combinations[value].hit_count / array.length;
      object.combinations[value].appearing_fullWord_index = (object.combinations[value].inbetween_count + object.combinations[value].inbeginning_count + object.combinations[value].atend_count) / array.length;

      if (object.combinations[value].appearing_fullWord_index > object.total.max_intersect_value) {
        object.total.max_intersect_value = object.combinations[value].appearing_fullWord_index;
        object.total.max_intersect_word = value;
        object.total.word_count = object.combinations[value].word_count;
      } else if (object.combinations[value].appearing_fullWord_index == object.total.max_intersect_value && object.combinations[value].word_count > object.total.word_count) {
        object.total.max_intersect_value = object.combinations[value].appearing_fullWord_index;
        object.total.max_intersect_word = value;
        object.total.word_count = object.combinations[value].word_count;
      }
    }

  }
  return object;
};

/**
 * generates combinations of characters
 * @param  {array} chars strings
 * @return {array}       combinations
 */
function getCombinations(chars) {
  var result = [];
  var f = function(prefix, chars) {
    for (var i = 0; i < chars.length; i++) {
      result.push(prefix + chars[i]);
      f(prefix + chars[i] + " ", chars.slice(i + 1));
    }
  };
  f('', chars);
  return result;
};

var brandKeywordList = ["someValue"];
var hashMapResults = {};
var numOfKeywords = 0;
var doWork = false;
var keywordsToQuery = new Array();
var keywordsToQueryIndex = 0;
var queryflag = false;


function buildKeywordList(keyword) {

  // get the first set of keywords related to the term and add to list
  brandKeywordList = queryKeyword(keyword);

  // iterate through alphabet and build keyword list for initial keyword
  for(var j = 0; j < 26; j++) {
    var chr = String.fromCharCode(97 + j);

    keywordVariation = keyword + ' '+ chr;
    var alphaList = {};
    alphaList = queryKeyword(keywordVariation);

    for (var x = 0; x < alphaList.length; x++) {
      if (x !== 0) { brandKeywordList.push(alphaList[x]); }
    }
  }

  for(var n = 0; n <= 9; n++) {
    keywordVariation = keyword + ' '+ n;

    var numberList = {};
    numberList = queryKeyword(keywordVariation);

    for (var y = 0; y < numberList.length; y++) {
     if (y !== 0) {brandKeywordList.push(numberList[y]);}
    }
  }

  ////////////////////////////////////
  ///// START CASE MULTIPLE WORDS ////
  ////////////////////////////////////

  // Split keyword up if possible and look for different variations
  var keywordPieces = _.str.words(keyword);

  if (keywordPieces.length > 1) {

    // iterate through alphabet and build keyword list for the variation: [keywordPiece1] + [a-z][0-9] + [keywordPiece2]
    // Checking for the variation [keywordPiece1] + [a-z][0-9] + [keywordPiece2]...
    // Variation: ' + keywordPieces[0] + ' '+ keywordPieces[1] + keywordPieces[2])
    for(var j = 0; j < 26; j++) {
      var chr = String.fromCharCode(97 + j);
      keywordVariation = keywordPieces[0] + ' '+ chr + ' ' + keywordPieces[1]+ ' ' + keywordPieces[2];
      var alphaList = {};
      alphaList = queryKeyword(keywordVariation);

      for (var x = 0; x < alphaList.length; x++) {
       if (x !== 0) { brandKeywordList.push(alphaList[x]); }
      }
    }

    for(var n = 0; n <= 9; n++) {
      keywordVariation = keywordPieces[0] + ' '+ n + ' ' + keywordPieces[1] +' '+  keywordPieces[2];
      var numberList = {};
      numberList = queryKeyword(keywordVariation);

      for (var y = 0; y < numberList.length; y++) {
       if (y !== 0) { brandKeywordList.push(numberList[y]); }
      }
    }

    Utilities.sleep(2000);

    /* CHECK FOR THE VARIATION [keywordPiece1] + [a-z][0-9] + [keywordPiece0] */
    // warn('now checking for the variation [keywordPiece2] + [a-z][0-9] + [keywordPiece1]...');
    // warn('variation: ' + keywordPieces[1] + ' '+ keywordPieces[0]);

    for(var j = 0; j < 26; j++) {
        var chr = String.fromCharCode(97 + j);

        keywordVariation = keywordPieces[1] + ' ' + keywordPieces[2] + ' '+ chr + ' ' + keywordPieces[0];

        var alphaList = {};
        alphaList = queryKeyword(keywordVariation);

        for (var x = 0; x < alphaList.length; x++) {

         if (x !== 0) {brandKeywordList.push(alphaList[x]);}
      }
    }

    for(var n = 0; n <= 9; n++) {
      keywordVariation = keywordPieces[1] + ' ' + keywordPieces[2] + ' '+ n + ' ' + keywordPieces[0];
      var numberList = {};
      numberList = queryKeyword(keywordVariation);

      for (var y = 0; y < numberList.length; y++) {
       if (y !== 0) { brandKeywordList.push(numberList[y]); }
      }
    }

    Utilities.sleep(2000);

    /* last variation: [a-z][0-9] [keyword1] [keyword2] */
    /* CHECK FOR THE VARIATION [keywordPiece1] + [a-z][0-9] + [keywordPiece0] */
    // info('now checking for the variation [a-z][0-9] + [keywordPiece1]  + [keywordPiece2]...');
    // warn('variation: ' + keywordPieces[0] + ' '+ keywordPieces[1]);
    for(var j = 0; j < 26; j++) {
      var chr = String.fromCharCode(97 + j);
      keywordVariation =  chr + ' ' + keywordPieces[0] + ' ' + keywordPieces[1] + keywordPieces[2];

      var alphaList = {};
      alphaList = queryKeyword(keywordVariation);

      for (var x = 0; x < alphaList.length; x++) {
       if (x !== 0) { brandKeywordList.push(alphaList[x]); }
      }
    }

    for(var n = 0; n <= 9; n++) {
      keywordVariation = n + ' ' + keywordPieces[0] + ' ' + keywordPieces[1] + keywordPieces[2];

      var numberList = {};
      numberList = queryKeyword(keywordVariation);

      for (var y = 0; y < numberList.length; y++) {
       if (y !== 0) { brandKeywordList.push(numberList[y]); }
      }
    }

  } // END IF Keyword Length > 1

  ////////////////////////////////////
  ///// END CASE MULTIPLE WORDS //////
  ////////////////////////////////////
}


function createSpreadsheet(results) {
  var newSS = SpreadsheetApp.create('searchtermreport', results.length, 26);

  var sheet = newSS.getActiveSheet();

  var columnNames = ["Campaign Name", "AdGroup", "Keyword", "Match Type"];

  var headersRange = sheet.getRange(1, 1, 1, columnNames.length);

  for (i = 0; i < results.length; i++) {

    headersRange.setValues([columnNames]);

     var resultKw;
     resultKw = results[i].toString();

    sheet.appendRow(["Your Campaign", "Your AdGroup", resultKw,'Phrase']);

    // Sets the first column to a width which fits the text
    sheet.setColumnWidth(1, 300);

  }

  return newSS.getUrl();
}

function sendAnEmail (results, fileUrl) {

  var data = Utilities.parseCsv(results, '\t');
  var today = new Date();

  var filename = 'search-results' + today;

  // Send an email with Search list attachment
   var blob = Utilities.newBlob(results, 'text/html', '');

  MailApp.sendEmail(emailAddress, 'Google Autocomplete Results ', 'You can find the results at the following URL:' + fileUrl, {
     name: 'Google Autocomplete Search Results'
 });
}

/* Utility Functions */

function warn(msg) { Logger.log('WARNING: '+msg); }
function info(msg) { Logger.log(msg); }

function queryKeyword(keyword) {
  var querykeyword = encodeURIComponent(keyword);

  var queryresult = '';
  queryflag = true;

  Utilities.sleep(1000);
  var response = UrlFetchApp.fetch("https://www.google.com/s?gs_rn=18&gs_ri=psy-ab&cp=7&gs_id=d7&xhr=t&q=" + querykeyword);

  var retval = response.getContentText();

  var test = _.str.stripTags(retval);

  var retList = ScrapePage(retval, '["', '",');

  queryflag = false;

  return retList;
}


function ScrapePage(page, left, right) {
  var i = 0;
  var retVal = new Array();
  var firstIndex = page.indexOf(left);
  while (firstIndex != -1)
  {
    firstIndex += left.length;
    var secondIndex = page.indexOf(right, firstIndex);
    if (secondIndex != -1)
    {
      var val = page.substring(firstIndex, secondIndex);
      val = val.replace("\\u003cb\\u003e", "");
      val = val.replace("\\u003c\\/b\\u003e", "");
      val = val.replace("\\u003c\\/b\\u003e", "");
      val = val.replace("\\u003cb\\u003e", "");
      val = val.replace("\\u003c\\/b\\u003e", "");
      val = val.replace("\\u003cb\\u003e", "");
      val = val.replace("\\u003cb\\u003e", "");
      val = val.replace("\\u003c\\/b\\u003e", "");
      val = val.replace("\\u0026amp;", "&");
      val = val.replace("\\u003cb\\u003e", "");
      val = val.replace("\\u0026", "");
      val = val.replace("\\u0026#39;", "'");
      val = val.replace("#39;", "'");
      val = val.replace("\\u003c\\/b\\u003e", "");
      val = val.replace("\\u2013", "2013");
      retVal[i] = val;
      i++;
      firstIndex = page.indexOf(left, secondIndex);
    }
    else
    {
      return retVal;
    }
  }
  return retVal;
}


!function(e,t){"use strict";var n=t.prototype.trim;var r=t.prototype.trimRight;var i=t.prototype.trimLeft;var s=function(e){return e*1||0};var o=function(e,t){if(t<1)return"";var n="";while(t>0){if(t&1)n+=e;t>>=1,e+=e}return n};var u=[].slice;var a=function(e){if(e==null)return"\\s";else if(e.source)return e.source;else return"["+p.escapeRegExp(e)+"]"};var f={lt:"<",gt:">",quot:'"',apos:"'",amp:"&"};var l={};for(var c in f){l[f[c]]=c}var h=function(){function e(e){return Object.prototype.toString.call(e).slice(8,-1).toLowerCase()}var n=o;var r=function(){if(!r.cache.hasOwnProperty(arguments[0])){r.cache[arguments[0]]=r.parse(arguments[0])}return r.format.call(null,r.cache[arguments[0]],arguments)};r.format=function(r,i){var s=1,o=r.length,u="",a,f=[],l,c,p,d,v,m;for(l=0;l<o;l++){u=e(r[l]);if(u==="string"){f.push(r[l])}else if(u==="array"){p=r[l];if(p[2]){a=i[s];for(c=0;c<p[2].length;c++){if(!a.hasOwnProperty(p[2][c])){throw new Error(h('[_.sprintf] property "%s" does not exist',p[2][c]))}a=a[p[2][c]]}}else if(p[1]){a=i[p[1]]}else{a=i[s++]}if(/[^s]/.test(p[8])&&e(a)!="number"){throw new Error(h("[_.sprintf] expecting number but found %s",e(a)))}switch(p[8]){case"b":a=a.toString(2);break;case"c":a=t.fromCharCode(a);break;case"d":a=parseInt(a,10);break;case"e":a=p[7]?a.toExponential(p[7]):a.toExponential();break;case"f":a=p[7]?parseFloat(a).toFixed(p[7]):parseFloat(a);break;case"o":a=a.toString(8);break;case"s":a=(a=t(a))&&p[7]?a.substring(0,p[7]):a;break;case"u":a=Math.abs(a);break;case"x":a=a.toString(16);break;case"X":a=a.toString(16).toUpperCase();break}a=/[def]/.test(p[8])&&p[3]&&a>=0?"+"+a:a;v=p[4]?p[4]=="0"?"0":p[4].charAt(1):" ";m=p[6]-t(a).length;d=p[6]?n(v,m):"";f.push(p[5]?a+d:d+a)}}return f.join("")};r.cache={};r.parse=function(e){var t=e,n=[],r=[],i=0;while(t){if((n=/^[^\x25]+/.exec(t))!==null){r.push(n[0])}else if((n=/^\x25{2}/.exec(t))!==null){r.push("%")}else if((n=/^\x25(?:([1-9]\d*)\$|\(([^\)]+)\))?(\+)?(0|'[^$])?(-)?(\d+)?(?:\.(\d+))?([b-fosuxX])/.exec(t))!==null){if(n[2]){i|=1;var s=[],o=n[2],u=[];if((u=/^([a-z_][a-z_\d]*)/i.exec(o))!==null){s.push(u[1]);while((o=o.substring(u[0].length))!==""){if((u=/^\.([a-z_][a-z_\d]*)/i.exec(o))!==null){s.push(u[1])}else if((u=/^\[(\d+)\]/.exec(o))!==null){s.push(u[1])}else{throw new Error("[_.sprintf] huh?")}}}else{throw new Error("[_.sprintf] huh?")}n[2]=s}else{i|=2}if(i===3){throw new Error("[_.sprintf] mixing positional and named placeholders is not (yet) supported")}r.push(n)}else{throw new Error("[_.sprintf] huh?")}t=t.substring(n[0].length)}return r};return r}();var p={VERSION:"2.3.0",isBlank:function(e){if(e==null)e="";return/^\s*$/.test(e)},stripTags:function(e){if(e==null)return"";return t(e).replace(/<\/?[^>]+>/g,"")},capitalize:function(e){e=e==null?"":t(e);return e.charAt(0).toUpperCase()+e.slice(1)},chop:function(e,n){if(e==null)return[];e=t(e);n=~~n;return n>0?e.match(new RegExp(".{1,"+n+"}","g")):[e]},clean:function(e){return p.strip(e).replace(/\s+/g," ")},count:function(e,n){if(e==null||n==null)return 0;return t(e).split(n).length-1},chars:function(e){if(e==null)return[];return t(e).split("")},swapCase:function(e){if(e==null)return"";return t(e).replace(/\S/g,function(e){return e===e.toUpperCase()?e.toLowerCase():e.toUpperCase()})},escapeHTML:function(e){if(e==null)return"";return t(e).replace(/[&<>"']/g,function(e){return"&"+l[e]+";"})},unescapeHTML:function(e){if(e==null)return"";return t(e).replace(/\&([^;]+);/g,function(e,n){var r;if(n in f){return f[n]}else if(r=n.match(/^#x([\da-fA-F]+)$/)){return t.fromCharCode(parseInt(r[1],16))}else if(r=n.match(/^#(\d+)$/)){return t.fromCharCode(~~r[1])}else{return e}})},escapeRegExp:function(e){if(e==null)return"";return t(e).replace(/([.*+?^=!:${}()|[\]\/\\])/g,"\\$1")},splice:function(e,t,n,r){var i=p.chars(e);i.splice(~~t,~~n,r);return i.join("")},insert:function(e,t,n){return p.splice(e,t,0,n)},include:function(e,n){if(n==="")return true;if(e==null)return false;return t(e).indexOf(n)!==-1},join:function(){var e=u.call(arguments),t=e.shift();if(t==null)t="";return e.join(t)},lines:function(e){if(e==null)return[];return t(e).split("\n")},reverse:function(e){return p.chars(e).reverse().join("")},startsWith:function(e,n){if(n==="")return true;if(e==null||n==null)return false;e=t(e);n=t(n);return e.length>=n.length&&e.slice(0,n.length)===n},endsWith:function(e,n){if(n==="")return true;if(e==null||n==null)return false;e=t(e);n=t(n);return e.length>=n.length&&e.slice(e.length-n.length)===n},succ:function(e){if(e==null)return"";e=t(e);return e.slice(0,-1)+t.fromCharCode(e.charCodeAt(e.length-1)+1)},titleize:function(e){if(e==null)return"";return t(e).replace(/(?:^|\s)\S/g,function(e){return e.toUpperCase()})},camelize:function(e){return p.trim(e).replace(/[-_\s]+(.)?/g,function(e,t){return t.toUpperCase()})},underscored:function(e){return p.trim(e).replace(/([a-z\d])([A-Z]+)/g,"$1_$2").replace(/[-\s]+/g,"_").toLowerCase()},dasherize:function(e){return p.trim(e).replace(/([A-Z])/g,"-$1").replace(/[-_\s]+/g,"-").toLowerCase()},classify:function(e){return p.titleize(t(e).replace(/_/g," ")).replace(/\s/g,"")},humanize:function(e){return p.capitalize(p.underscored(e).replace(/_id$/,"").replace(/_/g," "))},trim:function(e,r){if(e==null)return"";if(!r&&n)return n.call(e);r=a(r);return t(e).replace(new RegExp("^"+r+"+|"+r+"+$","g"),"")},ltrim:function(e,n){if(e==null)return"";if(!n&&i)return i.call(e);n=a(n);return t(e).replace(new RegExp("^"+n+"+"),"")},rtrim:function(e,n){if(e==null)return"";if(!n&&r)return r.call(e);n=a(n);return t(e).replace(new RegExp(n+"+$"),"")},truncate:function(e,n,r){if(e==null)return"";e=t(e);r=r||"...";n=~~n;return e.length>n?e.slice(0,n)+r:e},prune:function(e,n,r){if(e==null)return"";e=t(e);n=~~n;r=r!=null?t(r):"...";if(e.length<=n)return e;var i=function(e){return e.toUpperCase()!==e.toLowerCase()?"A":" "},s=e.slice(0,n+1).replace(/.(?=\W*\w*$)/g,i);if(s.slice(s.length-2).match(/\w\w/))s=s.replace(/\s*\S+$/,"");else s=p.rtrim(s.slice(0,s.length-1));return(s+r).length>e.length?e:e.slice(0,s.length)+r},words:function(e,t){if(p.isBlank(e))return[];return p.trim(e,t).split(t||/\s+/)},pad:function(e,n,r,i){e=e==null?"":t(e);n=~~n;var s=0;if(!r)r=" ";else if(r.length>1)r=r.charAt(0);switch(i){case"right":s=n-e.length;return e+o(r,s);case"both":s=n-e.length;return o(r,Math.ceil(s/2))+e+o(r,Math.floor(s/2));default:s=n-e.length;return o(r,s)+e}},lpad:function(e,t,n){return p.pad(e,t,n)},rpad:function(e,t,n){return p.pad(e,t,n,"right")},lrpad:function(e,t,n){return p.pad(e,t,n,"both")},sprintf:h,vsprintf:function(e,t){t.unshift(e);return h.apply(null,t)},toNumber:function(e,n){if(e==null||e=="")return 0;e=t(e);var r=s(s(e).toFixed(~~n));return r===0&&!e.match(/^0+$/)?Number.NaN:r},numberFormat:function(e,t,n,r){if(isNaN(e)||e==null)return"";e=e.toFixed(~~t);r=r||",";var i=e.split("."),s=i[0],o=i[1]?(n||".")+i[1]:"";return s.replace(/(\d)(?=(?:\d{3})+$)/g,"$1"+r)+o},strRight:function(e,n){if(e==null)return"";e=t(e);n=n!=null?t(n):n;var r=!n?-1:e.indexOf(n);return~r?e.slice(r+n.length,e.length):e},strRightBack:function(e,n){if(e==null)return"";e=t(e);n=n!=null?t(n):n;var r=!n?-1:e.lastIndexOf(n);return~r?e.slice(r+n.length,e.length):e},strLeft:function(e,n){if(e==null)return"";e=t(e);n=n!=null?t(n):n;var r=!n?-1:e.indexOf(n);return~r?e.slice(0,r):e},strLeftBack:function(e,t){if(e==null)return"";e+="";t=t!=null?""+t:t;var n=e.lastIndexOf(t);return~n?e.slice(0,n):e},toSentence:function(e,t,n,r){t=t||", ";n=n||" and ";var i=e.slice(),s=i.pop();if(e.length>2&&r)n=p.rtrim(t)+n;return i.length?i.join(t)+n+s:s},toSentenceSerial:function(){var e=u.call(arguments);e[3]=true;return p.toSentence.apply(p,e)},slugify:function(e){if(e==null)return"";var n="ąàáäâãåæćęèéëêìíïîłńòóöôõøùúüûñçżź",r="aaaaaaaaceeeeeiiiilnoooooouuuunczz",i=new RegExp(a(n),"g");e=t(e).toLowerCase().replace(i,function(e){var t=n.indexOf(e);return r.charAt(t)||"-"});return p.dasherize(e.replace(/[^\w\s-]/g,""))},surround:function(e,t){return[t,e,t].join("")},quote:function(e){return p.surround(e,'"')},exports:function(){var e={};for(var t in this){if(!this.hasOwnProperty(t)||t.match(/^(?:include|contains|reverse)$/))continue;e[t]=this[t]}return e},repeat:function(e,n,r){if(e==null)return"";n=~~n;if(r==null)return o(t(e),n);for(var i=[];n>0;i[--n]=e){}return i.join(r)},levenshtein:function(e,n){if(e==null&&n==null)return 0;if(e==null)return t(n).length;if(n==null)return t(e).length;e=t(e);n=t(n);var r=[],i,s;for(var o=0;o<=n.length;o++)for(var u=0;u<=e.length;u++){if(o&&u)if(e.charAt(u-1)===n.charAt(o-1))s=i;else s=Math.min(r[u],r[u-1],i)+1;else s=o+u;i=r[u];r[u]=s}return r.pop()}};p.strip=p.trim;p.lstrip=p.ltrim;p.rstrip=p.rtrim;p.center=p.lrpad;p.rjust=p.lpad;p.ljust=p.rpad;p.contains=p.include;p.q=p.quote;if(typeof exports!=="undefined"){if(typeof module!=="undefined"&&module.exports){module.exports=p}exports._s=p}else if(typeof define==="function"&&define.amd){define("underscore.string",[],function(){return p})}else{e._=e._||{};e._.string=e._.str=p}}(this,String)
	//******************************************//
	//******** START CONFIGURATION *********//
	//******************************************//

	var URL_LOOKUP_CONFIG = {
	site : "norisk.group",
	se_name : "google.de",
	se_language : "German",

	// Choose a value from Googles Location Table:
	// https://fusiontables.google.com/DataSource?docid=1Jlxrqc1dU3a9rsNW2l5xxlmQEKUu0dIPusImi41B#rows:id=1
	loc_name_canonical : "Berlin,Germany",

	// your brand name not removed when comparing
	brand_name : "norisk",

	// Make a COPY of this DEMO spreadsheet and remove the values:
	// https://docs.google.com/spreadsheets/d/1xxiQeesxTZG7LgrbNWGjAkzAYZTYo_lMBsfuXmh_Szc/edit#gid=0
	ad_spreadsheet_id : "1xxiQeesxTZG7LgrbNWGjAkzAYZTYo_lMBsfuXmh_Szc",
	};

	// Create a Data4SEO Account and add your API credentials here:
	var URL_LOOKUP_USERNAME = "name@gmail.com";
	var URL_LOOKUP_PASSWORD = "ybnk239fK7d2d2S";

	//******************************************//
	//******** END CONFIGURATION ***********//
	//******************************************//


	// DON'T (!) CHANGE THESE SETTINGS
	var API_SET_URL = 'https://api.dataforseo.com/v2/srp_tasks_post';
	var API_GET_URL = 'https://api.dataforseo.com/v2/srp_tasks_get';
	var AUTH_HEADER = 'Basic ' + Utilities.base64Encode(URL_LOOKUP_USERNAME + ':' + URL_LOOKUP_PASSWORD);


	function main() {
	var queries = ["norisk marketing","norisk online","norisk gtm","norisk adwords scripts"];
	var lookUp_StorageHandler = new SpreadsheetHandler();
	dataHandler = new Data4SeoHandler(lookUp_StorageHandler);

	// load task ids of which the result hasn't been retrieved from the data4seo API
	var taskIds = lookUp_StorageHandler.loadUnFinishedTaskIds();

	// retrieve data from api and store it inside the results property of the Data4SeoHandler
	dataHandler.getTaskResults(taskIds);
	dataHandler.createNewTasks(queries);
	}

	/**
	* Data4SeoHandler Object
	* @param {UrlLookupStorageHandler} storageHandler Handler that manages the BigQuery API.
	*/

	function Data4SeoHandler(storageHandler) {
	this.results = {};
	this.storageHandler = storageHandler;
	}

	/**
	* Submit new queries to API
	* @param {Array} queries Array that contains strings representing new queries. If queries already exist in the database, they won't be submitted.
	*/
	Data4SeoHandler.prototype.createNewTasks = function(queries) {

	// fetch existing queries
	var temp_existingQueries = this.storageHandler.getExistingQueries();
	var existingQueries = {};

	Logger.log("Queries to be checked: " + JSON.stringify(queries));
	Logger.log("Existing queries: " + JSON.stringify(temp_existingQueries));

	for (var j = 0; j < temp_existingQueries.length; j++) {
	if (!existingQueries[temp_existingQueries[j][0]]) {
	existingQueries[temp_existingQueries[j][0]] = temp_existingQueries[j][0];
	}
	}

	for (var k = 0; k < queries.length; k++) {
	query = queries[k];
	if (existingQueries[query]) {
	queries.splice(k, 1);
	k--;
	}
	}

	Logger.log("New queries: " + JSON.stringify(queries));

	if (queries.length === 0) {
	Logger.log("No new queries to be submitted to D4S API.");
	return;
	}
	Logger.log("Submitting new tasks to API.");
	Logger.log("Queries: " + JSON.stringify(queries));

	var data = {
	data: {}
	};

	// Creating Data4SEO data objects per query
	for (var i = 0; i < queries.length; i++) {
	query = URL_LOOKUP_CONFIG.site + ' ' + queries[i]; // removed "site:"+ to skip higer credit cost
	var object = {
	'priority': '1',
	'site': URL_LOOKUP_CONFIG.site,
	'se_name': URL_LOOKUP_CONFIG.se_name,
	'se_language': URL_LOOKUP_CONFIG.se_language,
	'loc_name_canonical': URL_LOOKUP_CONFIG.loc_name_canonical,
	'key': query
	};
	data.data[query] = object;
	} // END For queries

	var options = {
	'method': 'post',
	'contentType': 'application/json',
	'headers': {
	'Authorization': AUTH_HEADER
	},
	'muteHttpExceptions': true,
	'payload':JSON.stringify(data)
	};

	var response = JSON.parse(UrlFetchApp.fetch(API_SET_URL, options));

	for (var result in response.results) {
	var site_string = URL_LOOKUP_CONFIG.site + " "; // removed "site:"+ to skip higer credit cost
	var sanitizedQuery = response.results[result].post_key.replace(site_string, "");
	this.storageHandler.pushDataToCache(response.results[result].task_id, sanitizedQuery);
	}

	if (this.storageHandler.cache.length > 0) this.storageHandler.flush();
	};

	/**
	* retrieves the static URL by query
	* @param {string} query
	* @return {object} returns an object. The url property contains a string with the extracted url. The similarityValue property contains a float representing the similarity value of query and retrieved url.
	*/
	Data4SeoHandler.prototype.getStaticUrl = function(query) {

	/* var db_query = this.storageHandler.buildSelectQuery_(["url","similarityValue"], [{inputField: "query", operator: "=", value: query}]);
	var results = this.storageHandler.queryDataTable_(db_query);
	*/

	var results = this.storageHandler.getUrlAndSimValueForQuery(query);
	if (results.length === 0) return {
	url: "",
	similarityValue: 0
	};
	var url = results[0][0];
	var simValue = results[0][1];

	return {
	url: url,
	similarityValue: simValue
	};
	};

	/*
	* @param {string} query
	* @param {string} url
	* @return {float} simValue
	*/
	Data4SeoHandler.prototype.calculateSimilarityValue = function(query, url) {
	if (!url \|\| !query) return 0;
	var match;
	var regex = /^h?t?t?p?s?\:?\/?\/?www\.[\w\|\d\|\-\|\_]\.\w\/(.*)$/gi;

	match = url.split(URL_LOOKUP_CONFIG.site + "/")[1];

	if (typeof(match) == "undefined" \|\| !match) return 0;

	var validatedUrl = match.replace(/\//g, " ");
	validatedUrl = validatedUrl.replace(/^\s/, "").replace(/\s$/, "").replace(/_/g, " ");
	validatedUrl = validatedUrl.split("-").join(" ");
	validatedUrl = validatedUrl.replace(URL_LOOKUP_CONFIG.brand_name, "").split(" ").sort().join(" ");

	query = query.split("-").join(" ");
	query = query.replace(URL_LOOKUP_CONFIG.brand_name + " ", "").split(" ").sort().join(" ");
	//Logger.log("strippedQuery :" + query + " \| url-slug : " + validatedUrl);

	var wordDistance = this._calculateLetterChanges(validatedUrl, query);
	var simValue = (1 - wordDistance / validatedUrl.length).toFixed(2);

	return simValue;
	};

	/**
	* retrieves the related searches by query
	* @param {string} query String that contains the query
	* @return {object} returns an object that contains the related searches as array, the intersection_value and the intersection_word
	*/
	Data4SeoHandler.prototype.getRelatedSearches = function(query) {

	var relatedSearches = this.storageHandler.getRelatedSearchesForQuery(query);
	if (relatedSearches.length === 0) {
	return {
	relatedSearches: [],
	intersection_word: "",
	intersection_value: 0
	};
	}
	return {
	relatedSearches: relatedSearches[0][0].split(","),
	intersection_word: this._computeWordIntersection(relatedSearches[0][0].split(",")).total.max_intersect_word,
	intersection_value: this._computeWordIntersection(relatedSearches[0][0].split(",")).total.max_intersect_value
	};
	};

	/**
	* Method that retrieves the results of a data 4 seo api and stores it in BigQuery
	* @param {array} taskIds
	*/
	Data4SeoHandler.prototype.getTaskResults = function(taskIds) {

	this.taskIds = taskIds;
	var options = {
	'method': 'get',
	'contentType': 'application/json',
	'headers': {
	'Authorization': AUTH_HEADER
	},
	'muteHttpExceptions': true
	};
	// var results = [];

	for (var i = 0; i < taskIds.length; i++) {
	var taskId = taskIds[i];

	var fetch_target = API_GET_URL.charAt(API_GET_URL.length - 1) == "/" ? API_GET_URL : API_GET_URL + "/";
	fetch_target += taskId;
	var result = JSON.parse(UrlFetchApp.fetch(fetch_target, options));
	var url, relatedSearches;

	var query = result.results.organic[0].post_key.replace(URL_LOOKUP_CONFIG.site, "");

	try {
	var bestUrl = {
	"simValue": 0,
	"url": ""
	};

	for (var j = 0; j < 3; j++) {
	url = result.results.organic[j].result_url;
	var resultRootDomain = url.replace("https://", "").replace("www.//", "");

	// Get simValue if url contains domain AND is not home
	if (url.indexOf(URL_LOOKUP_CONFIG.site) != -1 && URL_LOOKUP_CONFIG.site !== resultRootDomain) {
	var similarityValue = this.calculateSimilarityValue(query, url);
	if (similarityValue > bestUrl.simValue) {
	bestUrl.url = url;
	bestUrl.simValue = similarityValue;
	}
	}
	} // END For loop j = url results

	relatedSearches = typeof result.results.extra.related !== "undefined" && typeof result.results.extra.related[j] !== "undefined"? result.results.extra.related[j].join() : relatedSearches = "";

	this.storageHandler.setStatusDone(taskId, bestUrl.url, relatedSearches, bestUrl.simValue);
	this.results[taskId] = result;

	} catch (e) {
	Logger.log("TaskResultException: Task not finished or no url found. Error:" + e);
	Logger.log(e.stack);
	throw e;
	}
	} // END FOR loop taskIds
	};

	/**
	* computes the intersection of an array of strings
	* @param {array} array array containing the strings to be computed
	* @return {object}
	* object = { total: { max_intersect_word: "", max_intersect_value: 0, word_count: 0 }, combinations: {}};
	*/
	Data4SeoHandler.prototype._computeWordIntersection = function(array) {
	var object = {
	total: {
	max_intersect_word: "",
	max_intersect_value: 0,
	word_count: 0
	},
	combinations: {}
	};
	for (var i = 0; i < array.length; i++) {
	var value_array = array[i].split(" ");
	value_array = this._getCombinations(value_array);

	for (var j = 0; j < value_array.length; j++) {
	var value = value_array[j];
	if (object.combinations[value]) {
	continue;
	}
	object.combinations[value] = {
	hit_count: 0,
	inbetween_count: 0,
	inbeginning_count: 0,
	atend_count: 0,
	word_count: value.split(" ").length
	};
	for (var k = 0; k < array.length; k++) {
	if (array[k].match(new RegExp("." + value + ".", "g"))) {
	object.combinations[value].hit_count++;
	}
	if (array[k].match(new RegExp(".\\s" + value + "\\s.", "g"))) {
	object.combinations[value].inbetween_count++;
	}
	if (array[k].match(new RegExp("^" + value + "\\s.*", "g"))) {
	object.combinations[value].inbeginning_count++;
	}
	if (array[k].match(new RegExp(".*\\s" + value + "$", "g"))) {
	object.combinations[value].atend_count++;
	}
	}
	object.combinations[value].appearing_index = object.combinations[value].hit_count / array.length;
	object.combinations[value].appearing_fullWord_index = (object.combinations[value].inbetween_count + object.combinations[value].inbeginning_count + object.combinations[value].atend_count) / array.length;

	if (object.combinations[value].appearing_fullWord_index > object.total.max_intersect_value) {
	object.total.max_intersect_value = object.combinations[value].appearing_fullWord_index;
	object.total.max_intersect_word = value;
	object.total.word_count = object.combinations[value].word_count;
	} else if (object.combinations[value].appearing_fullWord_index == object.total.max_intersect_value && object.combinations[value].word_count > object.total.word_count) {
	object.total.max_intersect_value = object.combinations[value].appearing_fullWord_index;
	object.total.max_intersect_word = value;
	object.total.word_count = object.combinations[value].word_count;
	}
	}

	}
	return object;
	};

	/**
	* generates combinations of characters
	* @param {array} chars strings
	* @return {array} combinations
	*/
	Data4SeoHandler.prototype._getCombinations = function(chars) {
	var result = [];
	var f = function(prefix, chars) {
	for (var i = 0; i < chars.length; i++) {
	result.push(prefix + chars[i]);
	f(prefix + chars[i] + " ", chars.slice(i + 1));
	}
	};
	f('', chars);
	return result;
	};

	/**
	* calculate letter changes between to words
	* @param {string} a first word
	* @param {string} b second word
	* @return {number}
	*/
	Data4SeoHandler.prototype._calculateLetterChanges = function(a, b) {
	var tmp;
	if (a.length === 0) {
	return b.length;
	}
	if (b.length === 0) {
	return a.length;
	}
	if (a.length > b.length) {
	tmp = a;
	a = b;
	b = tmp;
	}

	var i, j, res, alen = a.length,
	blen = b.length,
	row = Array(alen);
	for (i = 0; i <= alen; i++) {
	row[i] = i;
	}

	for (i = 1; i <= blen; i++) {
	res = i;
	for (j = 1; j <= alen; j++) {
	tmp = row[j - 1];
	row[j - 1] = res;
	res = b[i - 1] === a[j - 1] ? tmp : Math.min(tmp + 1, Math.min(res + 1, row[j] + 1));
	}
	}
	return res;
	};

	/**
	* @constructor SpreadsheetHandler
	*/
	function SpreadsheetHandler() {
	var spreadsheet_id = URL_LOOKUP_CONFIG.ad_spreadsheet_id;
	var sheetName = "(urls)";
	this.timeStamp = this.getTimeStamp();
	this.cache = [];

	try {
	this.controlSpreadsheet = SpreadsheetApp.openById(URL_LOOKUP_CONFIG.ad_spreadsheet_id).getSheetByName(sheetName);
	} catch (e) {
	SpreadsheetApp.openById(URL_LOOKUP_CONFIG.ad_spreadsheet_id).insertSheet(sheetName);
	try {
	this.controlSpreadsheet = SpreadsheetApp.openById(URL_LOOKUP_CONFIG.ad_spreadsheet_id).getSheetByName(sheetName);
	}
	catch (e2) {
	throw new Error("SheetNotFoundException: Please add the sheet '(urls)' to your ad template. This is needed for storing url lookup data. error: " + e2 + ". stack : " + e2.stack);
	}
	}
	}


	SpreadsheetHandler.prototype.pushDataToCache = function(task_id, query) {
	this.cache.push([task_id, query]);
	};

	SpreadsheetHandler.prototype.flush = function() {
	Logger.log(JSON.stringify(this.cache));
	for (var i = 0; i < this.cache.length; i++) {
	try {
	this.controlSpreadsheet.appendRow([this.cache[i][0], this.cache[i][1], "", "no", "", "", ""]);
	} catch (e) {
	throw new Error("Error appending new row to spreadsheet. " + e.stack);
	}
	}
	this.cache = [];
	};


	/**
	* function that loads values from a spreadsheet
	* @return {Array}
	*/
	SpreadsheetHandler.prototype.loadUnFinishedTaskIds = function() {
	var existingValues = [];
	existingValues = this.controlSpreadsheet.getRange("A2:F" + this.controlSpreadsheet.getLastRow()).getValues();
	var finalArray = [];

	for (var i = 0; i < existingValues.length; i++) {
	// TBD set exact index of status
	if (existingValues[i][3] == "no") {
	finalArray.push(existingValues[i][0]);
	}
	}
	return finalArray;
	};


	SpreadsheetHandler.prototype.setStatusDone = function(task_id, url, relatedSearches, similarityValue) {
	var existingIds = this.controlSpreadsheet.getRange("A2:A" + this.controlSpreadsheet.getLastRow()).getValues();
	var rowIndex = existingIds.findIndex(findIndexCallback, task_id) + 2;

	if (rowIndex < 2) return;

	this.controlSpreadsheet.getRange(rowIndex, 3).setValue(url);
	this.controlSpreadsheet.getRange(rowIndex, 4).setValue("yes");
	this.controlSpreadsheet.getRange(rowIndex, 5).setValue(relatedSearches);
	this.controlSpreadsheet.getRange(rowIndex, 6).setValue(similarityValue);
	this.controlSpreadsheet.getRange(rowIndex, 7).setValue(this.timeStamp);

	};

	SpreadsheetHandler.prototype.getExistingQueries = function() {
	return this.controlSpreadsheet.getRange("B2:B" + this.controlSpreadsheet.getLastRow()).getValues();
	};

	SpreadsheetHandler.prototype.getUrlAndSimValueForQuery = function(query) {
	var existingQueries = this.controlSpreadsheet.getRange("B2:B" + this.controlSpreadsheet.getLastRow()).getValues();
	var rowIndex = existingQueries.findIndex(findIndexCallback, query) + 2;
	if (rowIndex < 2) return [];

	return [
	[this.controlSpreadsheet.getRange(rowIndex, 3).getValue(), this.controlSpreadsheet.getRange(rowIndex, 6).getValue()]
	];
	};

	SpreadsheetHandler.prototype.getRelatedSearchesForQuery = function(query) {
	var existingQueries = this.controlSpreadsheet.getRange("B2:B" + this.controlSpreadsheet.getLastRow()).getValues();
	var rowIndex = existingQueries.findIndex(findIndexCallback, query) + 2;

	if (rowIndex < 2) return [];

	return [
	[this.controlSpreadsheet.getRange(rowIndex, 5).getValue()]
	];
	};

	function findIndexCallback(element) {
	return element[0] == this;
	}

	/*
	* @return string dateTime
	*/
	SpreadsheetHandler.prototype.getTimeStamp = function() {

	var currentdate = new Date();
	var currrentHourGmc = currentdate.getUTCHours() + 1;

	var dateTime =
	(currentdate.getDate() < 10 ? '0' + currentdate.getDate().toString() : currentdate.getDate()) + "." +
	(currentdate.getMonth() + 1) + "." +
	currentdate.getFullYear() + " , " +
	currrentHourGmc + ":" +
	(currentdate.getMinutes() < 10 ? '0' + currentdate.getMinutes().toString() : currentdate.getMinutes());

	return dateTime; // target format = '24.2.2017 , 12:09'
	};


	// https://tc39.github.io/ecma262/#sec-array.prototype.findIndex
	if (!Array.prototype.findIndex) {
	Object.defineProperty(Array.prototype, 'findIndex', {
	value: function(predicate) {
	// 1. Let O be ? ToObject(this value).
	if (this == null) {
	throw new TypeError('"this" is null or not defined');
	}

	var o = Object(this);

	// 2. Let len be ? ToLength(? Get(O, "length")).
	var len = o.length >>> 0;

	// 3. If IsCallable(predicate) is false, throw a TypeError exception.
	if (typeof predicate !== 'function') {
	throw new TypeError('predicate must be a function');
	}

	// 4. If thisArg was supplied, let T be thisArg; else let T be undefined.
	var thisArg = arguments[1];

	// 5. Let k be 0.
	var k = 0;

	// 6. Repeat, while k < len
	while (k < len) {
	// a. Let Pk be ! ToString(k).
	// b. Let kValue be ? Get(O, Pk).
	// c. Let testResult be ToBoolean(? Call(predicate, T, « kValue, k, O »)).
	// d. If testResult is true, return k.
	var kValue = o[k];
	if (predicate.call(thisArg, kValue, k, o)) {
	return k;
	}
	// e. Increase k by 1.
	k++;
	}

	// 7. Return -1.
	return -1;
	},
	configurable: true,
	writable: true
	});
	}
	///////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////
	//
	// >>>>> NEW QUERY ENTITY EXTRACTOR - START CONFIG >>>>
	//
	///////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////

	// Add your shopping feed here. To improve results: create a copy with only the core product titles in the title column
	var FEED_URL = "$$CSV-FEED$$";
	var COLUMN_SEPARATOR = ","; // ONLY "," allowed! Make sure you change this!

	var FEED_PARSER_CONFIG = {
	"brandColumnValue": "brand",
	"categoryColumnValue": "product_type",
	"titleColumnValue": "title"
	};
	var SQA_REQUIRED_COLUMNS = ["brand", "product_type", "title", "gender"]; // ONLY extend! Don't reduce
	var SQA_EXTRA_COLUMNS = ["size", "color"];

	var CUSTOM_WORDS = {
	"buy": ["buy", "shop", "online"],
	"sale": ["cheap", "outlet", "reduced", "sale", "clearance", "low price"],
	"year": ["2017", "2018", "2019"],
	"UK": ["UK", "United Kingdom", "2019"],
	"plusSize": ["plus size", "oversize", "plussize", "long size"],
	"size": ["size","sizes"],
	"occasions": ["wedding", "bridesmaid", "mother of bride", "bridal"],
	"fill_words": ["by", "with", "for", "at", "in", "up to"],
	};

	var CORECAT_ARRAY_SINGULAR = ['suit', 'blouse', 'shirt', 'jacket', 'dress', 'coat'];
	var CORECAT_ARRAY_PLURAL = ['suits', 'blousens', 'shirts', 'jackets', 'dresses', 'coats'];


	var NEW_PAID_QUERY_CONFIG = {
	"searchCampaignOnly": 0,
	"campaignExclude": "Brand",
	"queryExclude" : ["your brand", "discount", "coupon"],
	"timeSpan": "LAST_7_DAYS",
	"queryInclude_TermSimilarity": "standard", // Eligible values: open, standard, strict

	"checkAgainst_Matchtypes" : "exactOnly", // "exactOnly"
	"checkAgainst_CampaignStatus" : "nonRemoved", // "enabledOnly", "nonRemoved"
	"checkAgainst_AdGroupStatus" : "nonRemoved", // "enabledOnly", "nonRemoved"
	"checkAgainst_KeywordStatus" : "nonRemoved", // "enabledOnly", "nonRemoved"

	"kpiThresholds": [{
	metric: "Conversions",
	operator: ">",
	value: 0.9
	}, {
	metric: "Impressions",
	operator: ">",
	value: 9
	}, {
	metric: "ConversionValue",
	operator: ">",
	value: 20
	},
	{"minRoas" : 2}],
	};


	var NEW_ORGANIC_QUERY_CONFIG = {
	"loadOrganicQueries": 0,
	"campaignExclude": "B__Brand",
	"timeSpan": "LAST_30_DAYS",
	"kpiThresholds": [{
	metric: "OrganicClicks",
	operator: ">",
	value: 5,
	}, {
	metric: "OrganicAveragePosition",
	operator: ">",
	value: 4,
	}, {
	metric: "CombinedAdsOrganicClicks",
	operator: ">",
	value: 5,
	}]
	}


	///////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////
	//
	// <<<<<< END CONFIG <<<<<<
	//
	///////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////

	var DEBUG_MODE = 0;
	var API_KEY = "aDfUSTUqn45D2WJJ8LrxnMcPQSLVcf8y";
	var INPUT_SOURCE_MODE = "SQA";

	function main(){
	try{
	var scriptfile_name = "https://scripts.adserver.cc/getScript.php?package=nrSearchqueryAutomator&version=unstable&script=nrSearchqueryAutomator.js&aid=123-456-7890&key="+API_KEY;
	var scriptFile_raw = UrlFetchApp.fetch(scriptfile_name).getContentText();

	try{
	eval(scriptFile_raw);
	nrSearchqueryAutomator();
	} catch (e) {try {if(AdWordsApp.getExecutionInfo().isPreview() === false) Logger.log("Error in Script: Exception: "+e.message+"\r\nStacktrace:\r\n"+e.stack);} catch (e2) {Logger.log(e2.stack);} throw e;}
	} catch(e3){ Logger.log(e3.stack); throw e3; }
	}