Automação Google Ads: Capturando Dados de Terceiros

Nessa série especial, reunimos alguns scripts úteis para você automatizar e aumentar o retorno de suas campanhas no Google Ads. Confira abaixo como automatizar o Google Ads para “Captura de dados de terceiros”.

 

1. Lances pela previsão de tempo – por Google Ads. Esse script do Google permite que seus lances mudem de acordo com o API do OpenWeatherMap. Caso você anuncie sorvetes artesanais você pode aumentar seus lances nos dias de sol e diminuir nos dias de chuva.

 

// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
* @name Bid By Weather
*
* @overview The Bid By Weather script adjusts campaign bids by weather
* conditions of their associated locations. See
* https://developers.google.com/adwords/scripts/docs/solutions/weather-based-campaign-management#bid-by-weather
* for more details.
*
* @author AdWords Scripts Team [[email protected]]
*
* @version 1.2.2
*
* @changelog
* - version 1.2.2
* - Add support for video and shopping campaigns.
* - version 1.2.1
* - Added validation for external spreadsheet setup.
* - version 1.2
* - Added proximity based targeting. Targeting flag allows location
* targeting, proximity targeting or both.
* - version 1.1
* - Added flag allowing bid adjustments on all locations targeted by
* a campaign rather than only those that match the campaign rule
* - version 1.0
* - Released initial version.
*/

// Register for an API key at http://openweathermap.org/appid
// and enter the key below.
var OPEN_WEATHER_MAP_API_KEY = 'INSERT_OPEN_WEATHER_MAP_API_KEY_HERE';

// Create a copy of https://goo.gl/A59Uuc and enter the URL below.
var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';

// A cache to store the weather for locations already lookedup earlier.
var WEATHER_LOOKUP_CACHE = {};

// Flag to pick which kind of targeting "LOCATION", "PROXIMITY", or "ALL".
var TARGETING = 'ALL';

/**
* The code to execute when running the script.
*/
function main() {
validateApiKey();
// Load data from spreadsheet.
var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
var campaignRuleData = getSheetData(spreadsheet, 1);
var weatherConditionData = getSheetData(spreadsheet, 2);
var geoMappingData = getSheetData(spreadsheet, 3);

// Convert the data into dictionaries for convenient usage.
var campaignMapping = buildCampaignRulesMapping(campaignRuleData);
var weatherConditionMapping =
buildWeatherConditionMapping(weatherConditionData);
var locationMapping = buildLocationMapping(geoMappingData);

// Apply the rules.
for (var campaignName in campaignMapping) {
applyRulesForCampaign(campaignName, campaignMapping[campaignName],
locationMapping, weatherConditionMapping);
}
}

/**
* Retrieves the data for a worksheet.
*
* @param {Object} spreadsheet The spreadsheet.
* @param {number} sheetIndex The sheet index.
* @return {Array} The data as a two dimensional array.
*/
function getSheetData(spreadsheet, sheetIndex) {
var sheet = spreadsheet.getSheets()[sheetIndex];
var range =
sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
return range.getValues();
}

/**
* Builds a mapping between the list of campaigns and the rules
* being applied to them.
*
* @param {Array} campaignRulesData The campaign rules data, from the
* spreadsheet.
* @return {Object.<string, Array.<Object>> } A map, with key as campaign name,
* and value as an array of rules that apply to this campaign.
*/
function buildCampaignRulesMapping(campaignRulesData) {
var campaignMapping = {};
for (var i = 0; i < campaignRulesData.length; i++) {
// Skip rule if not enabled.

if (campaignRulesData[i][5].toLowerCase() == 'yes') {
var campaignName = campaignRulesData[i][0];
var campaignRules = campaignMapping[campaignName] || [];
campaignRules.push({
'name': campaignName,

// location for which this rule applies.
'location': campaignRulesData[i][1],

// the weather condition (e.g. Sunny).
'condition': campaignRulesData[i][2],

// bid modifier to be applied.
'bidModifier': campaignRulesData[i][3],

// whether bid adjustments should by applied only to geo codes
// matching the location of the rule or to all geo codes that
// the campaign targets.
'targetedOnly': campaignRulesData[i][4].toLowerCase() ==
'matching geo targets'
});
campaignMapping[campaignName] = campaignRules;
}
}
Logger.log('Campaign Mapping: %s', campaignMapping);
return campaignMapping;
}

/**
* Builds a mapping between a weather condition name (e.g. Sunny) and the rules
* that correspond to that weather condition.
*
* @param {Array} weatherConditionData The weather condition data from the
* spreadsheet.
* @return {Object.<string, Array.<Object>>} A map, with key as a weather
* condition name, and value as the set of rules corresponding to that
* weather condition.
*/
function buildWeatherConditionMapping(weatherConditionData) {
var weatherConditionMapping = {};

for (var i = 0; i < weatherConditionData.length; i++) {
var weatherConditionName = weatherConditionData[i][0];
weatherConditionMapping[weatherConditionName] = {
// Condition name (e.g. Sunny)
'condition': weatherConditionName,

// Temperature (e.g. 50 to 70)
'temperature': weatherConditionData[i][1],

// Precipitation (e.g. below 70)
'precipitation': weatherConditionData[i][2],

// Wind speed (e.g. above 5)
'wind': weatherConditionData[i][3]
};
}
Logger.log('Weather condition mapping: %s', weatherConditionMapping);
return weatherConditionMapping;
}

/**
* Builds a mapping between a location name (as understood by OpenWeatherMap
* API) and a list of geo codes as identified by AdWords scripts.
*
* @param {Array} geoTargetData The geo target data from the spreadsheet.
* @return {Object.<string, Array.<Object>>} A map, with key as a locaton name,
* and value as an array of geo codes that correspond to that location
* name.
*/
function buildLocationMapping(geoTargetData) {
var locationMapping = {};
for (var i = 0; i < geoTargetData.length; i++) {
var locationName = geoTargetData[i][0];
var locationDetails = locationMapping[locationName] || {
'geoCodes': [] // List of geo codes understood by AdWords scripts.
};

locationDetails.geoCodes.push(geoTargetData[i][1]);
locationMapping[locationName] = locationDetails;
}
Logger.log('Location Mapping: %s', locationMapping);
return locationMapping;
}

/**
* Applies rules to a campaign.
*
* @param {string} campaignName The name of the campaign.
* @param {Object} campaignRules The details of the campaign. See
* buildCampaignMapping for details.
* @param {Object} locationMapping Mapping between a location name (as
* understood by OpenWeatherMap API) and a list of geo codes as
* identified by AdWords scripts. See buildLocationMapping for details.
* @param {Object} weatherConditionMapping Mapping between a weather condition
* name (e.g. Sunny) and the rules that correspond to that weather
* condition. See buildWeatherConditionMapping for details.
*/
function applyRulesForCampaign(campaignName, campaignRules, locationMapping,
weatherConditionMapping) {
for (var i = 0; i < campaignRules.length; i++) {
var bidModifier = 1;
var campaignRule = campaignRules[i];

// Get the weather for the required location.
var locationDetails = locationMapping[campaignRule.location];
var weather = getWeather(campaignRule.location);
Logger.log('Weather for %s: %s', locationDetails, weather);

// Get the weather rules to be checked.
var weatherConditionName = campaignRule.condition;
var weatherConditionRules = weatherConditionMapping[weatherConditionName];

// Evaluate the weather rules.
if (evaluateWeatherRules(weatherConditionRules, weather)) {
Logger.log('Matching Rule found: Campaign Name = %s, location = %s, ' +
'weatherName = %s,weatherRules = %s, noticed weather = %s.',
campaignRule.name, campaignRule.location,
weatherConditionName, weatherConditionRules, weather);
bidModifier = campaignRule.bidModifier;

if (TARGETING == 'LOCATION' || TARGETING == 'ALL') {
// Get the geo codes that should have their bids adjusted.
var geoCodes = campaignRule.targetedOnly ?
locationDetails.geoCodes : null;
adjustBids(campaignName, geoCodes, bidModifier);
}

if (TARGETING == 'PROXIMITY' || TARGETING == 'ALL') {
var location = campaignRule.targetedOnly ? campaignRule.location : null;
adjustProximityBids(campaignName, location, bidModifier);
}

}
}
return;
}

/**
* Converts a temperature value from kelvin to fahrenheit.
*
* @param {number} kelvin The temperature in Kelvin scale.
* @return {number} The temperature in Fahrenheit scale.
*/
function toFahrenheit(kelvin) {
return (kelvin - 273.15) * 1.8 + 32;
}

/**
* Evaluates the weather rules.
*
* @param {Object} weatherRules The weather rules to be evaluated.
* @param {Object.<string, string>} weather The actual weather.
* @return {boolean} True if the rule matches current weather conditions,
* False otherwise.
*/
function evaluateWeatherRules(weatherRules, weather) {
// See http://bugs.openweathermap.org/projects/api/wiki/Weather_Data
// for values returned by OpenWeatherMap API.
var precipitation = 0;
if (weather.rain && weather.rain['3h']) {
precipitation = weather.rain['3h'];
}
var temperature = toFahrenheit(weather.main.temp);
var windspeed = weather.wind.speed;

return evaluateMatchRules(weatherRules.temperature, temperature) &&
evaluateMatchRules(weatherRules.precipitation, precipitation) &&
evaluateMatchRules(weatherRules.wind, windspeed);
}

/**
* Evaluates a condition for a value against a set of known evaluation rules.
*
* @param {string} condition The condition to be checked.
* @param {Object} value The value to be checked.
* @return {boolean} True if an evaluation rule matches, false otherwise.
*/
function evaluateMatchRules(condition, value) {
// No condition to evaluate, rule passes.
if (condition == '') {
return true;
}
var rules = [matchesBelow, matchesAbove, matchesRange];

for (var i = 0; i < rules.length; i++) {
if (rules[i](condition, value)) {
return true;
}
}
return false;
}

/**
* Evaluates whether a value is below a threshold value.
*
* @param {string} condition The condition to be checked. (e.g. below 50).
* @param {number} value The value to be checked.
* @return {boolean} True if the value is less than what is specified in
* condition, false otherwise.
*/
function matchesBelow(condition, value) {
conditionParts = condition.split(' ');

if (conditionParts.length != 2) {
return false;
}

if (conditionParts[0] != 'below') {
return false;
}

if (value < conditionParts[1]) {
return true;
}
return false;
}

/**
* Evaluates whether a value is above a threshold value.
*
* @param {string} condition The condition to be checked. (e.g. above 50).
* @param {number} value The value to be checked.
* @return {boolean} True if the value is greater than what is specified in
* condition, false otherwise.
*/
function matchesAbove(condition, value) {
conditionParts = condition.split(' ');

if (conditionParts.length != 2) {
return false;
}

if (conditionParts[0] != 'above') {
return false;
}

if (value > conditionParts[1]) {
return true;
}
return false;
}

/**
* Evaluates whether a value is within a range of values.
*
* @param {string} condition The condition to be checked (e.g. 5 to 18).
* @param {number} value The value to be checked.
* @return {boolean} True if the value is in the desired range, false otherwise.
*/
function matchesRange(condition, value) {
conditionParts = condition.replace('\w+', ' ').split(' ');

if (conditionParts.length != 3) {
return false;
}

if (conditionParts[1] != 'to') {
return false;
}

if (conditionParts[0] <= value && value <= conditionParts[2]) {
return true;
}
return false;
}

/**
* Retrieves the weather for a given location, using the OpenWeatherMap API.
*
* @param {string} location The location to get the weather for.
* @return {Object.<string, string>} The weather attributes and values, as
* defined in the API.
*/
function getWeather(location) {
if (location in WEATHER_LOOKUP_CACHE) {
Logger.log('Cache hit...');
return WEATHER_LOOKUP_CACHE[location];
}

var url = Utilities.formatString(
'http://api.openweathermap.org/data/2.5/weather?APPID=%s&q=%s',
encodeURIComponent(OPEN_WEATHER_MAP_API_KEY),
encodeURIComponent(location));
var response = UrlFetchApp.fetch(url);
if (response.getResponseCode() != 200) {
throw Utilities.formatString(
'Error returned by API: %s, Location searched: %s.',
response.getContentText(), location);
}

var result = JSON.parse(response.getContentText());

// OpenWeatherMap's way of returning errors.
if (result.cod != 200) {
throw Utilities.formatString(
'Error returned by API: %s, Location searched: %s.',
response.getContentText(), location);
}

WEATHER_LOOKUP_CACHE[location] = result;
return result;
}

/**
* Adjusts the bidModifier for a list of geo codes for a campaign.
*
* @param {string} campaignName The name of the campaign.
* @param {Array} geoCodes The list of geo codes for which bids should be
* adjusted. If null, all geo codes on the campaign are adjusted.
* @param {number} bidModifier The bid modifier to use.
*/
function adjustBids(campaignName, geoCodes, bidModifier) {
// Get the campaign.
var campaign = getCampaign(campaignName);
if (!campaign) return null;

// Get the targeted locations.
var locations = campaign.targeting().targetedLocations().get();
while (locations.hasNext()) {
var location = locations.next();
var currentBidModifier = location.getBidModifier().toFixed(2);

// Apply the bid modifier only if the campaign has a custom targeting
// for this geo location or if all locations are to be modified.
if (!geoCodes || (geoCodes.indexOf(location.getId()) != -1 &&
currentBidModifier != bidModifier)) {
Logger.log('Setting bidModifier = %s for campaign name = %s, ' +
'geoCode = %s. Old bid modifier is %s.', bidModifier,
campaignName, location.getId(), currentBidModifier);
location.setBidModifier(bidModifier);
}
}
}

/**
* Adjusts the bidModifier for campaigns targeting by proximity location
* for a given weather location.
*
* @param {string} campaignName The name of the campaign.
* @param {string} weatherLocation The weather location for which bids should be
* adjusted. If null, all proximity locations on the campaign are adjusted.
* @param {number} bidModifier The bid modifier to use.
*/
function adjustProximityBids(campaignName, weatherLocation, bidModifier) {
// Get the campaign.
var campaign = getCampaign(campaignName);
if(campaign === null) return;

// Get the proximity locations.
var proximities = campaign.targeting().targetedProximities().get();
while (proximities.hasNext()) {
var proximity = proximities.next();
var currentBidModifier = proximity.getBidModifier().toFixed(2);

// Apply the bid modifier only if the campaign has a custom targeting
// for this geo location or if all locations are to be modified.
if (!weatherLocation ||
(weatherNearProximity(proximity, weatherLocation) &&
currentBidModifier != bidModifier)) {
Logger.log('Setting bidModifier = %s for campaign name = %s, with ' +
'weatherLocation = %s in proximity area. Old bid modifier is %s.',
bidModifier, campaignName, weatherLocation, currentBidModifier);
proximity.setBidModifier(bidModifier);
}
}
}

/**
* Checks if weather location is within the radius of the proximity location.
*
* @param {Object} proximity The targeted proximity of campaign.
* @param {string} weatherLocation Name of weather location to check within
* radius.
* @return {boolean} Returns true if weather location is within radius.
*/
function weatherNearProximity(proximity, weatherLocation) {
// See https://en.wikipedia.org/wiki/Haversine_formula for details on how
// to compute spherical distance.
var earthRadiusInMiles = 3960.0;
var degreesToRadians = Math.PI / 180.0;
var radiansToDegrees = 180.0 / Math.PI;
var kmToMiles = 0.621371;

var radiusInMiles = proximity.getRadiusUnits() == 'MILES' ?
proximity.getRadius() : proximity.getRadius() * kmToMiles;

// Compute the change in latitude degrees for the radius.
var deltaLat = (radiusInMiles / earthRadiusInMiles) * radiansToDegrees;
// Find the radius of a circle around the earth at given latitude.
var r = earthRadiusInMiles * Math.cos(proximity.getLatitude() *
degreesToRadians);
// Compute the change in longitude degrees for the radius.
var deltaLon = (radiusInMiles / r) * radiansToDegrees;

// Retrieve weather location for lat/lon coordinates.
var weather = getWeather(weatherLocation);
// Check if weather condition is within the proximity boundaries.
return (weather.coord.lat >= proximity.getLatitude() - deltaLat &&
weather.coord.lat <= proximity.getLatitude() + deltaLat &&
weather.coord.lon >= proximity.getLongitude() - deltaLon &&
weather.coord.lon <= proximity.getLongitude() + deltaLon);
}

/**
* Finds a campaign by name, whether it is a regular, video, or shopping
* campaign, by trying all in sequence until it finds one.
*
* @param {string} campaignName The campaign name to find.
* @return {Object} The campaign found, or null if none was found.
*/
function getCampaign(campaignName) {
var selectors = [AdWordsApp.campaigns(), AdWordsApp.videoCampaigns(),
AdWordsApp.shoppingCampaigns()];
for(var i = 0; i < selectors.length; i++) {
var campaignIter = selectors[i].
withCondition('CampaignName = "' + campaignName + '"').
get();
if (campaignIter.hasNext()) {
return campaignIter.next();
}
}
return null;
}

/**
* DO NOT EDIT ANYTHING BELOW THIS LINE.
* Please modify your spreadsheet URL and API key at the top of the file only.
*/

/**
* Validates the provided spreadsheet URL to make sure that it's set up
* properly. Throws a descriptive error message if validation fails.
*
* @param {string} spreadsheeturl The URL of the spreadsheet to open.
* @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
* @throws {Error} If the spreadsheet URL hasn't been set
*/
function validateAndGetSpreadsheet(spreadsheeturl) {
if (spreadsheeturl == 'INSERT_SPREADSHEET_URL_HERE') {
throw new Error('Please specify a valid Spreadsheet URL. You can find' +
' a link to a template in the associated guide for this script.');
}
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
return spreadsheet;
}

/**
* Validates the provided API key to make sure that it's not the default. Throws
* a descriptive error message if validation fails.
*
* @throws {Error} If the configured API key hasn't been set.
*/
function validateApiKey() {
if (OPEN_WEATHER_MAP_API_KEY == 'INSERT_OPEN_WEATHER_MAP_API_KEY_HERE') {
throw new Error('Please specify a valid API key for OpenWeatherMap. You ' +
'can acquire one here: http://openweathermap.org/appid');
}
}

 

 

2. Gerencie anúncios com base nos horários de vôos – por Russell Savage. Atrasos de vôo? Seus anúncios são ativados. Excelente scripts para hotéis e restaurantes próximos a aeroportos.

 

/*********************************************
* Pause/Enable Ads Based On Airport Delays
* Version 1.1
* ChangeLog v1.1
* - Added ability to completely pause non-delay ads
* Created By: Russ Savage
* FreeAdWordsScripts.com
*********************************************/
// For this to work, you need to add a label to all
// the ads for each airport. For example, PIT_normal
// or SFO_normal
var PAUSE_NORMAL_ADS_DURING_DELAY = false;
var DELAY_SUFFIX = '_delay'; //the suffix on the label for delayed ads
var NORMAL_SUFFIX = '_normal'; //the suffix on the label for normal ads

var AIR_CODES = ["ATL","ANC","AUS","BWI","BOS","CLT","MDW","ORD","CVG","CLE",
"CMH","DFW","DEN","DTW","FLL","RSW","BDL","HNL","IAH","HOU",
"IND","MCI","LAS","LAX","MEM","MIA","MSP","BNA","MSY","JFK",
"LGA","EWR","OAK","ONT","MCO","PHL","PHX","PIT","PDX","RDU",
"SMF","SLC","SAT","SAN","SFO","SJC","SNA","SEA","STL","TPA",
"IAD","DCA"];

function main() {
var faaUrl = "http://services.faa.gov/airport/status/";
var args = "?format=application/json";
for(var i in AIR_CODES) {
try{
var resp = UrlFetchApp.fetch(faaUrl + AIR_CODES[i] + args);
if( resp.getResponseCode() == 200 ) {
var json = Utilities.jsonParse(resp.getContentText());
if(json.delay == "false") {
Logger.log("No delays at "+json.IATA+". Pausing delay ads if any are running.");
turnOffDelayAds(json.IATA);
if(PAUSE_NORMAL_ADS_DURING_DELAY) {
turnOnNonDelayAds(json.IATA);
}
} else {
Logger.log("Delays in "+json.IATA+" Reason: "+json.status.reason);
Logger.log("Turning on delay ads if there are any.");
turnOnDelayAds(json.IATA);
if(PAUSE_NORMAL_ADS_DURING_DELAY) {
turnOffNonDelayAds(json.IATA);
}
}
}
}
catch(e) {
Logger.log("Error: " + e.message);
}
}
}

function turnOffDelayAds(airportCode) {
var labelName = airportCode + DELAY_SUFFIX;
var adIter = AdWordsApp.ads()
.withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
.withCondition("CampaignStatus = ENABLED")
.withCondition("AdGroupStatus = ENABLED")
.withCondition("Status = ENABLED")
.get();
while(adIter.hasNext()) {
adIter.next().pause();
}
}

function turnOffNonDelayAds(airportCode) {
var labelName = airportCode + NORMAL_SUFFIX;
var adIter = AdWordsApp.ads()
.withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
.withCondition("CampaignStatus = ENABLED")
.withCondition("AdGroupStatus = ENABLED")
.withCondition("Status = ENABLED")
.get();
while(adIter.hasNext()) {
adIter.next().pause();
}
}

function turnOnDelayAds(airportCode) {
var labelName = airportCode + DELAY_SUFFIX;
var adIter = AdWordsApp.ads()
.withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
.withCondition("CampaignStatus = ENABLED")
.withCondition("AdGroupStatus = ENABLED")
.withCondition("Status = PAUSED")
.get();
while(adIter.hasNext()) {
adIter.next().enable();
}
}

function turnOnNonDelayAds(airportCode) {
var labelName = airportCode + NORMAL_SUFFIX;
var adIter = AdWordsApp.ads()
.withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
.withCondition("CampaignStatus = ENABLED")
.withCondition("AdGroupStatus = ENABLED")
.withCondition("Status = PAUSED")
.get();
while(adIter.hasNext()) {
adIter.next().enable();
}
}

 

3. Recurso auto-completar do Amazon para encontrar palavras-chave – por Derek Martin. Exportando os resultados do recurso da Amazon de auto-completar você pode encontrar novas palavras-chave para sua conta no Google Ads.

 

/**********************************************************************************************************************
* Amazon Autocomplete Tool
* Leverage the Amazon Autocomplete feature to find highly commercial keyword opportunities.
* Export the results for efficient importing into Google Adwords
* Version 1.0
* Created By: Derek Martin
* DerekMartinLA.com or MixedMarketingArtist.com
**********************************************************************************************************************/

var hashMapResults = {};
var numOfKeywords = 0;
var doWork = false;
var keywordsToQuery = new Array();
var keywordsToQueryIndex = 0;
var queryflag = false;

var brandKeywordList = [];
var targetKeyword = "srixon"; // this is the keyword that you want to know about
var emailAddress ="[email protected]"; // this is where the final report will be sent
var iterate = false; // true means go through initial results and find opportunities; false means return the intial list

function main() {
// Get the brand name from the account
var accountName = AdWordsApp.currentAccount().getName().split('-');

var clientName = accountName[0];

info('Now starting Amazon Autocomplete Analysis..');
info("");
// iterate through alphabet and build keyword list for initial keyword
info("Targeted Keyword: " + targetKeyword);
info("");
info("Results:");

var initial_list = [];
var list = []
initial_list = buildKeywordList(targetKeyword);

list.push(initial_list);

if (iterate == true) {

for (i = 1; i < initial_list.length; i++) {
Utilities.sleep(100);
if (initial_list[i] != targetKeyword) {

var res = buildKeywordList(initial_list[i]);

if (res != null) {
list.push(res);
}
}
}
}
list = flatten(list);
list = _.uniq(list);
list.sort()

// let user know that search has completed
info('Amazon Autocomplete Search has completed, expect an email with search term results momentarily');
info("");
info(list.length + ' searches were found. ');

var fileUrl = createSpreadsheet(list);

info('Or you can find it here: ' + fileUrl);
sendAnEmail(clientName, list.toString(), fileUrl);
}

function flatten(arr) {
return arr.reduce(function (flat, toFlatten) {
return flat.concat(Array.isArray(toFlatten) ? flatten(toFlatten) : toFlatten);
}, []);
}

function buildKeywordList(keyword) {
var result = [];
// get the first set of keywords related to the term and add to list
brandKeywordList = queryKeyword(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) {

var myRe = /(.+)\"],.+/;
var testRe = myRe.exec(alphaList[x]);

if (testRe == null) {
info(alphaList[x]);
result.push(alphaList[x]);

} else {
info(testRe[1]);
result.push(testRe[1]);
}
}
}
}

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) {

var myRe = /(.+)\"],.+/;
var testRe = myRe.exec(numberList[y]);

if (testRe == null) {
info(numberList[y]);
result.push(numberList[y]);
} else {
info(testRe[1]);
result.push(testRe[1]);
}

}
}
}
return result;
} // end of buildKeywordList

function createSpreadsheet(results) {
var newSS = SpreadsheetApp.create('amazon-keywords', 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 = 'keyword-research-results' + today;

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

MailApp.sendEmail(emailAddress, 'Amazon Autocomplete Results ', 'You can find the results at the following URL:' + fileUrl, {
name: 'Amazon 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(200);
var response = UrlFetchApp.fetch("http://completion.amazon.com/search/complete?mkt=1&search-alias=aps&x=updateAmazon&q=" + querykeyword);

var resStr = "\"[completion = \"";
var retval = response.getContentText().replace(resStr,"");

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;
}

// Underscore.js 1.6.0
// http://underscorejs.org
// (c) 2009-2014 Jeremy Ashkenas, DocumentCloud and Investigative Reporters & Editors
// Underscore may be freely distributed under the MIT license.
(function(){var n=this,t=n._,r={},e=Array.prototype,u=Object.prototype,i=Function.prototype,a=e.push,o=e.slice,c=e.concat,l=u.toString,f=u.hasOwnProperty,s=e.forEach,p=e.map,h=e.reduce,v=e.reduceRight,g=e.filter,d=e.every,m=e.some,y=e.indexOf,b=e.lastIndexOf,x=Array.isArray,w=Object.keys,_=i.bind,j=function(n){return n instanceof j?n:this instanceof j?void(this._wrapped=n):new j(n)};"undefined"!=typeof exports?("undefined"!=typeof module&&module.exports&&(exports=module.exports=j),exports._=j):n._=j,j.VERSION="1.6.0";var A=j.each=j.forEach=function(n,t,e){if(null==n)return n;if(s&&n.forEach===s)n.forEach(t,e);else if(n.length===+n.length){for(var u=0,i=n.length;i>u;u++)if(t.call(e,n[u],u,n)===r)return}else for(var a=j.keys(n),u=0,i=a.length;i>u;u++)if(t.call(e,n[a[u]],a[u],n)===r)return;return n};j.map=j.collect=function(n,t,r){var e=[];return null==n?e:p&&n.map===p?n.map(t,r):(A(n,function(n,u,i){e.push(t.call(r,n,u,i))}),e)};var O="Reduce of empty array with no initial value";j.reduce=j.foldl=j.inject=function(n,t,r,e){var u=arguments.length>2;if(null==n&&(n=[]),h&&n.reduce===h)return e&&(t=j.bind(t,e)),u?n.reduce(t,r):n.reduce(t);if(A(n,function(n,i,a){u?r=t.call(e,r,n,i,a):(r=n,u=!0)}),!u)throw new TypeError(O);return r},j.reduceRight=j.foldr=function(n,t,r,e){var u=arguments.length>2;if(null==n&&(n=[]),v&&n.reduceRight===v)return e&&(t=j.bind(t,e)),u?n.reduceRight(t,r):n.reduceRight(t);var i=n.length;if(i!==+i){var a=j.keys(n);i=a.length}if(A(n,function(o,c,l){c=a?a[--i]:--i,u?r=t.call(e,r,n[c],c,l):(r=n[c],u=!0)}),!u)throw new TypeError(O);return r},j.find=j.detect=function(n,t,r){var e;return k(n,function(n,u,i){return t.call(r,n,u,i)?(e=n,!0):void 0}),e},j.filter=j.select=function(n,t,r){var e=[];return null==n?e:g&&n.filter===g?n.filter(t,r):(A(n,function(n,u,i){t.call(r,n,u,i)&&e.push(n)}),e)},j.reject=function(n,t,r){return j.filter(n,function(n,e,u){return!t.call(r,n,e,u)},r)},j.every=j.all=function(n,t,e){t||(t=j.identity);var u=!0;return null==n?u:d&&n.every===d?n.every(t,e):(A(n,function(n,i,a){return(u=u&&t.call(e,n,i,a))?void 0:r}),!!u)};var k=j.some=j.any=function(n,t,e){t||(t=j.identity);var u=!1;return null==n?u:m&&n.some===m?n.some(t,e):(A(n,function(n,i,a){return u||(u=t.call(e,n,i,a))?r:void 0}),!!u)};j.contains=j.include=function(n,t){return null==n?!1:y&&n.indexOf===y?n.indexOf(t)!=-1:k(n,function(n){return n===t})},j.invoke=function(n,t){var r=o.call(arguments,2),e=j.isFunction(t);return j.map(n,function(n){return(e?t:n[t]).apply(n,r)})},j.pluck=function(n,t){return j.map(n,j.property(t))},j.where=function(n,t){return j.filter(n,j.matches(t))},j.findWhere=function(n,t){return j.find(n,j.matches(t))},j.max=function(n,t,r){if(!t&&j.isArray(n)&&n[0]===+n[0]&&n.length<65535)return Math.max.apply(Math,n);var e=-1/0,u=-1/0;return A(n,function(n,i,a){var o=t?t.call(r,n,i,a):n;o>u&&(e=n,u=o)}),e},j.min=function(n,t,r){if(!t&&j.isArray(n)&&n[0]===+n[0]&&n.length<65535)return Math.min.apply(Math,n);var e=1/0,u=1/0;return A(n,function(n,i,a){var o=t?t.call(r,n,i,a):n;u>o&&(e=n,u=o)}),e},j.shuffle=function(n){var t,r=0,e=[];return A(n,function(n){t=j.random(r++),e[r-1]=e[t],e[t]=n}),e},j.sample=function(n,t,r){return null==t||r?(n.length!==+n.length&&(n=j.values(n)),n[j.random(n.length-1)]):j.shuffle(n).slice(0,Math.max(0,t))};var E=function(n){return null==n?j.identity:j.isFunction(n)?n:j.property(n)};j.sortBy=function(n,t,r){return t=E(t),j.pluck(j.map(n,function(n,e,u){return{value:n,index:e,criteria:t.call(r,n,e,u)}}).sort(function(n,t){var r=n.criteria,e=t.criteria;if(r!==e){if(r>e||r===void 0)return 1;if(e>r||e===void 0)return-1}return n.index-t.index}),"value")};var F=function(n){return function(t,r,e){var u={};return r=E(r),A(t,function(i,a){var o=r.call(e,i,a,t);n(u,o,i)}),u}};j.groupBy=F(function(n,t,r){j.has(n,t)?n[t].push(r):n[t]=[r]}),j.indexBy=F(function(n,t,r){n[t]=r}),j.countBy=F(function(n,t){j.has(n,t)?n[t]++:n[t]=1}),j.sortedIndex=function(n,t,r,e){r=E(r);for(var u=r.call(e,t),i=0,a=n.length;a>i;){var o=i+a>>>1;r.call(e,n[o])<u?i=o+1:a=o}return i},j.toArray=function(n){return n?j.isArray(n)?o.call(n):n.length===+n.length?j.map(n,j.identity):j.values(n):[]},j.size=function(n){return null==n?0:n.length===+n.length?n.length:j.keys(n).length},j.first=j.head=j.take=function(n,t,r){return null==n?void 0:null==t||r?n[0]:0>t?[]:o.call(n,0,t)},j.initial=function(n,t,r){return o.call(n,0,n.length-(null==t||r?1:t))},j.last=function(n,t,r){return null==n?void 0:null==t||r?n[n.length-1]:o.call(n,Math.max(n.length-t,0))},j.rest=j.tail=j.drop=function(n,t,r){return o.call(n,null==t||r?1:t)},j.compact=function(n){return j.filter(n,j.identity)};var M=function(n,t,r){return t&&j.every(n,j.isArray)?c.apply(r,n):(A(n,function(n){j.isArray(n)||j.isArguments(n)?t?a.apply(r,n):M(n,t,r):r.push(n)}),r)};j.flatten=function(n,t){return M(n,t,[])},j.without=function(n){return j.difference(n,o.call(arguments,1))},j.partition=function(n,t){var r=[],e=[];return A(n,function(n){(t(n)?r:e).push(n)}),[r,e]},j.uniq=j.unique=function(n,t,r,e){j.isFunction(t)&&(e=r,r=t,t=!1);var u=r?j.map(n,r,e):n,i=[],a=[];return A(u,function(r,e){(t?e&&a[a.length-1]===r:j.contains(a,r))||(a.push(r),i.push(n[e]))}),i},j.union=function(){return j.uniq(j.flatten(arguments,!0))},j.intersection=function(n){var t=o.call(arguments,1);return j.filter(j.uniq(n),function(n){return j.every(t,function(t){return j.contains(t,n)})})},j.difference=function(n){var t=c.apply(e,o.call(arguments,1));return j.filter(n,function(n){return!j.contains(t,n)})},j.zip=function(){for(var n=j.max(j.pluck(arguments,"length").concat(0)),t=new Array(n),r=0;n>r;r++)t[r]=j.pluck(arguments,""+r);return t},j.object=function(n,t){if(null==n)return{};for(var r={},e=0,u=n.length;u>e;e++)t?r[n[e]]=t[e]:r[n[e][0]]=n[e][1];return r},j.indexOf=function(n,t,r){if(null==n)return-1;var e=0,u=n.length;if(r){if("number"!=typeof r)return e=j.sortedIndex(n,t),n[e]===t?e:-1;e=0>r?Math.max(0,u+r):r}if(y&&n.indexOf===y)return n.indexOf(t,r);for(;u>e;e++)if(n[e]===t)return e;return-1},j.lastIndexOf=function(n,t,r){if(null==n)return-1;var e=null!=r;if(b&&n.lastIndexOf===b)return e?n.lastIndexOf(t,r):n.lastIndexOf(t);for(var u=e?r:n.length;u--;)if(n[u]===t)return u;return-1},j.range=function(n,t,r){arguments.length<=1&&(t=n||0,n=0),r=arguments[2]||1;for(var e=Math.max(Math.ceil((t-n)/r),0),u=0,i=new Array(e);e>u;)i[u++]=n,n+=r;return i};var R=function(){};j.bind=function(n,t){var r,e;if(_&&n.bind===_)return _.apply(n,o.call(arguments,1));if(!j.isFunction(n))throw new TypeError;return r=o.call(arguments,2),e=function(){if(!(this instanceof e))return n.apply(t,r.concat(o.call(arguments)));R.prototype=n.prototype;var u=new R;R.prototype=null;var i=n.apply(u,r.concat(o.call(arguments)));return Object(i)===i?i:u}},j.partial=function(n){var t=o.call(arguments,1);return function(){for(var r=0,e=t.slice(),u=0,i=e.length;i>u;u++)e[u]===j&&(e[u]=arguments[r++]);for(;r<arguments.length;)e.push(arguments[r++]);return n.apply(this,e)}},j.bindAll=function(n){var t=o.call(arguments,1);if(0===t.length)throw new Error("bindAll must be passed function names");return A(t,function(t){n[t]=j.bind(n[t],n)}),n},j.memoize=function(n,t){var r={};return t||(t=j.identity),function(){var e=t.apply(this,arguments);return j.has(r,e)?r[e]:r[e]=n.apply(this,arguments)}},j.delay=function(n,t){var r=o.call(arguments,2);return setTimeout(function(){return n.apply(null,r)},t)},j.defer=function(n){return j.delay.apply(j,[n,1].concat(o.call(arguments,1)))},j.throttle=function(n,t,r){var e,u,i,a=null,o=0;r||(r={});var c=function(){o=r.leading===!1?0:j.now(),a=null,i=n.apply(e,u),e=u=null};return function(){var l=j.now();o||r.leading!==!1||(o=l);var f=t-(l-o);return e=this,u=arguments,0>=f?(clearTimeout(a),a=null,o=l,i=n.apply(e,u),e=u=null):a||r.trailing===!1||(a=setTimeout(c,f)),i}},j.debounce=function(n,t,r){var e,u,i,a,o,c=function(){var l=j.now()-a;t>l?e=setTimeout(c,t-l):(e=null,r||(o=n.apply(i,u),i=u=null))};return function(){i=this,u=arguments,a=j.now();var l=r&&!e;return e||(e=setTimeout(c,t)),l&&(o=n.apply(i,u),i=u=null),o}},j.once=function(n){var t,r=!1;return function(){return r?t:(r=!0,t=n.apply(this,arguments),n=null,t)}},j.wrap=function(n,t){return j.partial(t,n)},j.compose=function(){var n=arguments;return function(){for(var t=arguments,r=n.length-1;r>=0;r--)t=[n[r].apply(this,t)];return t[0]}},j.after=function(n,t){return function(){return--n<1?t.apply(this,arguments):void 0}},j.keys=function(n){if(!j.isObject(n))return[];if(w)return w(n);var t=[];for(var r in n)j.has(n,r)&&t.push(r);return t},j.values=function(n){for(var t=j.keys(n),r=t.length,e=new Array(r),u=0;r>u;u++)e[u]=n[t[u]];return e},j.pairs=function(n){for(var t=j.keys(n),r=t.length,e=new Array(r),u=0;r>u;u++)e[u]=[t[u],n[t[u]]];return e},j.invert=function(n){for(var t={},r=j.keys(n),e=0,u=r.length;u>e;e++)t[n[r[e]]]=r[e];return t},j.functions=j.methods=function(n){var t=[];for(var r in n)j.isFunction(n[r])&&t.push(r);return t.sort()},j.extend=function(n){return A(o.call(arguments,1),function(t){if(t)for(var r in t)n[r]=t[r]}),n},j.pick=function(n){var t={},r=c.apply(e,o.call(arguments,1));return A(r,function(r){r in n&&(t[r]=n[r])}),t},j.omit=function(n){var t={},r=c.apply(e,o.call(arguments,1));for(var u in n)j.contains(r,u)||(t[u]=n[u]);return t},j.defaults=function(n){return A(o.call(arguments,1),function(t){if(t)for(var r in t)n[r]===void 0&&(n[r]=t[r])}),n},j.clone=function(n){return j.isObject(n)?j.isArray(n)?n.slice():j.extend({},n):n},j.tap=function(n,t){return t(n),n};var S=function(n,t,r,e){if(n===t)return 0!==n||1/n==1/t;if(null==n||null==t)return n===t;n instanceof j&&(n=n._wrapped),t instanceof j&&(t=t._wrapped);var u=l.call(n);if(u!=l.call(t))return!1;switch(u){case"[object String]":return n==String(t);case"[object Number]":return n!=+n?t!=+t:0==n?1/n==1/t:n==+t;case"[object Date]":case"[object Boolean]":return+n==+t;case"[object RegExp]":return n.source==t.source&&n.global==t.global&&n.multiline==t.multiline&&n.ignoreCase==t.ignoreCase}if("object"!=typeof n||"object"!=typeof t)return!1;for(var i=r.length;i--;)if(r[i]==n)return e[i]==t;var a=n.constructor,o=t.constructor;if(a!==o&&!(j.isFunction(a)&&a instanceof a&&j.isFunction(o)&&o instanceof o)&&"constructor"in n&&"constructor"in t)return!1;r.push(n),e.push(t);var c=0,f=!0;if("[object Array]"==u){if(c=n.length,f=c==t.length)for(;c--&&(f=S(n[c],t[c],r,e)););}else{for(var s in n)if(j.has(n,s)&&(c++,!(f=j.has(t,s)&&S(n[s],t[s],r,e))))break;if(f){for(s in t)if(j.has(t,s)&&!c--)break;f=!c}}return r.pop(),e.pop(),f};j.isEqual=function(n,t){return S(n,t,[],[])},j.isEmpty=function(n){if(null==n)return!0;if(j.isArray(n)||j.isString(n))return 0===n.length;for(var t in n)if(j.has(n,t))return!1;return!0},j.isElement=function(n){return!(!n||1!==n.nodeType)},j.isArray=x||function(n){return"[object Array]"==l.call(n)},j.isObject=function(n){return n===Object(n)},A(["Arguments","Function","String","Number","Date","RegExp"],function(n){j["is"+n]=function(t){return l.call(t)=="[object "+n+"]"}}),j.isArguments(arguments)||(j.isArguments=function(n){return!(!n||!j.has(n,"callee"))}),"function"!=typeof/./&&(j.isFunction=function(n){return"function"==typeof n}),j.isFinite=function(n){return isFinite(n)&&!isNaN(parseFloat(n))},j.isNaN=function(n){return j.isNumber(n)&&n!=+n},j.isBoolean=function(n){return n===!0||n===!1||"[object Boolean]"==l.call(n)},j.isNull=function(n){return null===n},j.isUndefined=function(n){return n===void 0},j.has=function(n,t){return f.call(n,t)},j.noConflict=function(){return n._=t,this},j.identity=function(n){return n},j.constant=function(n){return function(){return n}},j.property=function(n){return function(t){return t[n]}},j.matches=function(n){return function(t){if(t===n)return!0;for(var r in n)if(n[r]!==t[r])return!1;return!0}},j.times=function(n,t,r){for(var e=Array(Math.max(0,n)),u=0;n>u;u++)e[u]=t.call(r,u);return e},j.random=function(n,t){return null==t&&(t=n,n=0),n+Math.floor(Math.random()*(t-n+1))},j.now=Date.now||function(){return(new Date).getTime()};var T={escape:{"&":"&amp;","<":"&lt;",">":"&gt;",'"':"&quot;","'":"&#x27;"}};T.unescape=j.invert(T.escape);var I={escape:new RegExp("["+j.keys(T.escape).join("")+"]","g"),unescape:new RegExp("("+j.keys(T.unescape).join("|")+")","g")};j.each(["escape","unescape"],function(n){j[n]=function(t){return null==t?"":(""+t).replace(I[n],function(t){return T[n][t]})}}),j.result=function(n,t){if(null==n)return void 0;var r=n[t];return j.isFunction(r)?r.call(n):r},j.mixin=function(n){A(j.functions(n),function(t){var r=j[t]=n[t];j.prototype[t]=function(){var n=[this._wrapped];return a.apply(n,arguments),z.call(this,r.apply(j,n))}})};var N=0;j.uniqueId=function(n){var t=++N+"";return n?n+t:t},j.templateSettings={evaluate:/<%([\s\S]+?)%>/g,interpolate:/<%=([\s\S]+?)%>/g,escape:/<%-([\s\S]+?)%>/g};var q=/(.)^/,B={"'":"'","\\":"\\","\r":"r","\n":"n"," ":"t","\u2028":"u2028","\u2029":"u2029"},D=/\\|'|\r|\n|\t|\u2028|\u2029/g;j.template=function(n,t,r){var e;r=j.defaults({},r,j.templateSettings);var u=new RegExp([(r.escape||q).source,(r.interpolate||q).source,(r.evaluate||q).source].join("|")+"|$","g"),i=0,a="__p+='";n.replace(u,function(t,r,e,u,o){return a+=n.slice(i,o).replace(D,function(n){return"\\"+B[n]}),r&&(a+="'+\n((__t=("+r+"))==null?'':_.escape(__t))+\n'"),e&&(a+="'+\n((__t=("+e+"))==null?'':__t)+\n'"),u&&(a+="';\n"+u+"\n__p+='"),i=o+t.length,t}),a+="';\n",r.variable||(a="with(obj||{}){\n"+a+"}\n"),a="var __t,__p='',__j=Array.prototype.join,"+"print=function(){__p+=__j.call(arguments,'');};\n"+a+"return __p;\n";try{e=new Function(r.variable||"obj","_",a)}catch(o){throw o.source=a,o}if(t)return e(t,j);var c=function(n){return e.call(this,n,j)};return c.source="function("+(r.variable||"obj")+"){\n"+a+"}",c},j.chain=function(n){return j(n).chain()};var z=function(n){return this._chain?j(n).chain():n};j.mixin(j),A(["pop","push","reverse","shift","sort","splice","unshift"],function(n){var t=e[n];j.prototype[n]=function(){var r=this._wrapped;return t.apply(r,arguments),"shift"!=n&&"splice"!=n||0!==r.length||delete r[0],z.call(this,r)}}),A(["concat","join","slice"],function(n){var t=e[n];j.prototype[n]=function(){return z.call(this,t.apply(this._wrapped,arguments))}}),j.extend(j.prototype,{chain:function(){return this._chain=!0,this},value:function(){return this._wrapped}}),"function"==typeof define&&define.amd&&define("underscore",[],function(){return j})}).call(this);
//# sourceMappingURL=underscore-min.map

// Underscore.string
!function(e,t){"use strict";var n=t.prototype.trim,r=t.prototype.trimRight,i=t.prototype.trimLeft,s=function(e){return e*1||0},o=function(e,t){if(t<1)return"";var n="";while(t>0)t&1&&(n+=e),t>>=1,e+=e;return n},u=[].slice,a=function(e){return e==null?"\\s":e.source?e.source:"["+p.escapeRegExp(e)+"]"},f={lt:"<",gt:">",quot:'"',apos:"'",amp:"&"},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,r=function(){return r.cache.hasOwnProperty(arguments[0])||(r.cache[arguments[0]]=r.parse(arguments[0])),r.format.call(null,r.cache[arguments[0]],arguments)};return 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 p[1]?a=i[p[1]]: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()}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)throw new Error("[_.sprintf] huh?");if(n[2]){i|=1;var s=[],o=n[2],u=[];if((u=/^([a-z_][a-z_\d]*)/i.exec(o))===null)throw new Error("[_.sprintf] huh?");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)throw new Error("[_.sprintf] huh?");s.push(u[1])}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)}t=t.substring(n[0].length)}return r},r}(),p={VERSION:"2.3.0",isBlank:function(e){return e==null&&(e=""),/^\s*$/.test(e)},stripTags:function(e){return e==null?"":t(e).replace(/<\/?[^>]+>/g,"")},capitalize:function(e){return e=e==null?"":t(e),e.charAt(0).toUpperCase()+e.slice(1)},chop:function(e,n){return e==null?[]:(e=t(e),n=~~n,n>0?e.match(new RegExp(".{1,"+n+"}","g")):[e])},clean:function(e){return p.strip(e).replace(/\s+/g," ")},count:function(e,n){return e==null||n==null?0:t(e).split(n).length-1},chars:function(e){return e==null?[]:t(e).split("")},swapCase:function(e){return e==null?"":t(e).replace(/\S/g,function(e){return e===e.toUpperCase()?e.toLowerCase():e.toUpperCase()})},escapeHTML:function(e){return e==null?"":t(e).replace(/[&<>"']/g,function(e){return"&"+l[e]+";"})},unescapeHTML:function(e){return e==null?"":t(e).replace(/\&([^;]+);/g,function(e,n){var r;return n in f?f[n]:(r=n.match(/^#x([\da-fA-F]+)$/))?t.fromCharCode(parseInt(r[1],16)):(r=n.match(/^#(\d+)$/))?t.fromCharCode(~~r[1]):e})},escapeRegExp:function(e){return e==null?"":t(e).replace(/([.*+?^=!:${}()|[\]\/\\])/g,"\\$1")},splice:function(e,t,n,r){var i=p.chars(e);return i.splice(~~t,~~n,r),i.join("")},insert:function(e,t,n){return p.splice(e,t,0,n)},include:function(e,n){return n===""?!0:e==null?!1:t(e).indexOf(n)!==-1},join:function(){var e=u.call(arguments),t=e.shift();return t==null&&(t=""),e.join(t)},lines:function(e){return e==null?[]:t(e).split("\n")},reverse:function(e){return p.chars(e).reverse().join("")},startsWith:function(e,n){return n===""?!0:e==null||n==null?!1:(e=t(e),n=t(n),e.length>=n.length&&e.slice(0,n.length)===n)},endsWith:function(e,n){return n===""?!0:e==null||n==null?!1:(e=t(e),n=t(n),e.length>=n.length&&e.slice(e.length-n.length)===n)},succ:function(e){return e==null?"":(e=t(e),e.slice(0,-1)+t.fromCharCode(e.charCodeAt(e.length-1)+1))},titleize:function(e){return e==null?"":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){return e==null?"":!r&&n?n.call(e):(r=a(r),t(e).replace(new RegExp("^"+r+"+|"+r+"+$","g"),""))},ltrim:function(e,n){return e==null?"":!n&&i?i.call(e):(n=a(n),t(e).replace(new RegExp("^"+n+"+"),""))},rtrim:function(e,n){return e==null?"":!n&&r?r.call(e):(n=a(n),t(e).replace(new RegExp(n+"+$"),""))},truncate:function(e,n,r){return e==null?"":(e=t(e),r=r||"...",n=~~n,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);return s.slice(s.length-2).match(/\w\w/)?s=s.replace(/\s*\S+$/,""):s=p.rtrim(s.slice(0,s.length-1)),(s+r).length>e.length?e:e.slice(0,s.length)+r},words:function(e,t){return p.isBlank(e)?[]:p.trim(e,t).split(t||/\s+/)},pad:function(e,n,r,i){e=e==null?"":t(e),n=~~n;var s=0;r?r.length>1&&(r=r.charAt(0)):r=" ";switch(i){case"right":return s=n-e.length,e+o(r,s);case"both":return s=n-e.length,o(r,Math.ceil(s/2))+e+o(r,Math.floor(s/2));default:return s=n-e.length,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){return t.unshift(e),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?e.indexOf(n):-1;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?e.lastIndexOf(n):-1;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?e.indexOf(n):-1;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();return e.length>2&&r&&(n=p.rtrim(t)+n),i.length?i.join(t)+n+s:s},toSentenceSerial:function(){var e=u.call(arguments);return e[3]=!0,p.toSentence.apply(p,e)},slugify:function(e){if(e==null)return"";var n="ąàáäâãåæćęèéëêìíïîłńòóöôõøùúüûñçżź",r="aaaaaaaaceeeeeiiiilnoooooouuuunczz",i=new RegExp(a(n),"g");return e=t(e).toLowerCase().replace(i,function(e){var t=n.indexOf(e);return r.charAt(t)||"-"}),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++)o&&u?e.charAt(u-1)===n.charAt(o-1)?s=i:s=Math.min(r[u],r[u-1],i)+1: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,typeof exports!="undefined"?(typeof module!="undefined"&&module.exports&&(module.exports=p),exports._s=p):typeof define=="function"&&define.amd?define("underscore.string",[],function(){return p}):(e._=e._||{},e._.string=e._.str=p)}(this,String);

 

4. Lances atrelados ao mercado financeiro – por Russell Savage. O script utiliza o API da Yahoo! Finanças para determinar o valor dos lances de suas contas. Determine uma planilha para inserir os dados do mercado em tempo real e ajuste os lances de sua conta de acordo com os dados.

 

/******************************************
* Yahoo Finance API Class Example
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function main() {
var sheetUrl = 'ENTER A GOOGLE SHEET URL HERE';

var yfa = new YahooFinanceAPI({
symbols: ['^GSPC','VTI','^IXIC','BTCUSD=X'],
f: 'snl1' // or something longer like this 'sl1abb2b3d1t1c1ohgv'
});
for(var key in yfa.results) {
Logger.log(Utilities.formatString('Name: "%s", Symbol: "%s", Last Trade Price: $%s',
yfa.results[key].name,
key,
yfa.results[key].last_trade_price_only));
}

var includeColumnHeaders = true;
var sheetData = yfa.toGoogleSheet(includeColumnHeaders);
var ss = SpreadsheetApp.openByUrl(sheetUrl).getActiveSheet();
for(var i in sheetData) {
ss.appendRow(sheetData[i]);
}
}

 

Basta copiar o código a seguir na parte inferior do seu script do Google AdWords e estará pronto.

 

/******************************************
* Yahoo Finance API Class
* Use this to pull stock market quotes from Yahoo Finance
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function YahooFinanceAPI(configVars) {
var QUERY_URL_BASE = 'https://query.yahooapis.com/v1/public/yql';
var FINANCE_URL_BASE = 'http://download.finance.yahoo.com/d/quotes.csv';
this.configVars = configVars;

/*************
* The results are stored here in a
* map where the key is the ticker symbol
* { 'AAPL' : { ... }, 'GOOG' : { ... }
*************/
this.results = {};

/************
* Function used to refresh the results
* from Yahoo! Finance API. Called automatically
* during object reaction.
************/
this.refresh = function() {
var queryUrl = getQueryUrl(this.configVars);
var resp = UrlFetchApp.fetch(queryUrl,{muteHttpExceptions:true});
if(resp.getResponseCode() == 200) {
var jsonResp = JSON.parse(resp.getContentText());
if(jsonResp.query.count == 1) {
var row = jsonResp.query.results.row;
this.results[row.symbol] = row;
} else if(jsonResp.query.count > 1) {
for(var i in jsonResp.query.results.row) {
var row = jsonResp.query.results.row[i];
this.results[row.symbol] = row;
}
}
} else {
throw resp.getContentText();
}
}

/************
* Translates the results into a 2d array
* to make it easier to add into a Google Sheet.
* includeColumnHeaders - true or false if you want
* headers returned in the results.
************/
this.toGoogleSheet = function(includeColumnHeaders) {
if(!this.results) { return [[]]; }
var retVal = [];
var headers = null;
for(var key in this.results) {
if(!headers) {
headers = Object.keys(this.results[key]).sort();
}
var row = [];
for(var i in headers) {
row.push(this.results[key][headers[i]]);
}
retVal.push(row);
}
if(includeColumnHeaders) {
return [headers].concat(retVal);
} else {
return retVal;
}
}

// Perform a refresh on object creation.
this.refresh();

// Private functions

/************
* Builds Yahoo Finance Url
************/
function getFinanceUrl(configVars) {
var financeUrlParams = {
s : configVars.symbols.join(','),
f : configVars.f,
e : '.json'
}
return FINANCE_URL_BASE + serialize(financeUrlParams);
}

/************
* Builds Yahoo Query Url
************/
function getQueryUrl(configVars) {
var financeUrl = getFinanceUrl(configVars);
var cols = fToCols(configVars.f);
var queryTemplate = "select * from csv where url='%s' and columns='%s'";
var query = Utilities.formatString(queryTemplate, financeUrl,cols.join(','));
var params = {
q : query,
format : 'json'
}
var finalRestUrl = QUERY_URL_BASE + serialize(params);
return finalRestUrl;
}

/************
* This function translates the f parameter
* into actual field names to use for columns
************/
function fToCols(f) {
var cols = [];
var chunk = '';
var fBits = f.split('').reverse();
for(var i in fBits) {
chunk = (fBits[i] + chunk);
if(fLookup(chunk)) {
cols.push(fLookup(chunk));
chunk = '';
}
}
return cols.reverse();
}

/************
* Copied from: http://stackoverflow.com/a/18116302
* This function converts a hash into
* a url encoded query string.
************/
function serialize( obj ) {
return '?'+
Object.keys(obj).reduce(
function(a,k) {
a.push(k+'='+encodeURIComponent(obj[k]));
return a
},
[]).join('&');
}

/************
* Adapted from http://www.jarloo.com/yahoo_finance/
* This function maps f codes into
* friendly column names.
************/
function fLookup(f){
return{
a:'ask',b:'bid',b2:'ask realtime',b3:'bid realtime',p:'previous close',o:'open',
y:'dividend yield',d:'dividend per share',r1:'dividend pay date',
q:'ex-dividend date',c1:'change',c:'change & percent change',c6:'change realtime',
k2:'change percent realtime',p2:'change in percent',d1:'last trade date',
d2:'trade date',t1:'last trade time',c8:'after hours change realtime',
c3:'commission',g:'days low',h:'days high',k1:'last trade realtime with time',
l:'last trade with time',l1:'last trade price only',t8:'1 yr target price',
m5:'change from 200 day moving average',m6:'percent change from 200 day moving average',
m7:'change from 50 day moving average',m8:'percent change from 50 day moving average',
m3:'50 day moving average',m4:'200 day moving average',w1:'days value change',
w4:'days value change realtime',p1:'price paid',m:'days range',m2:'days range realtime',
g1:'holdings gain percent',g3:'annualized gain',g4:'holdings gain',
g5:'holdings gain percent realtime',g6:'holdings gain realtime',t7:'ticker trend',
t6:'trade links',i5:'order book realtime',l2:'high limit',l3:'low limit',
v1:'holdings value',v7:'holdings value realtime',s6: 'revenue',k:'52 week high',
j:'52 week low',j5:'change from 52 week low',k4:'change from 52 week high',
j6:'percent change from 52 week low',k5:'percent change from 52 week high',
w:'52 week range',v:'more info',j1:'market capitalization',j3:'market cap realtime',
f6:'float shares',n:'name',n4:'notes',s:'symbol',s1:'shares owned',x:'stock exchange',
j2:'shares outstanding',v:'volume',a5:'ask size',b6:'bid size',k3:'last trade size',
a2:'average daily volume',e:'earnings per share',e7:'eps estimate current year',
e8:'eps estimate next year',e9:'eps estimate next quarter',b4:'book value',j4:'ebitda',
p5:'price sales',p6:'price book',r:'pe ratio',r2:'pe ratio realtime',r5:'peg ratio',
r6:'price eps estimate current year',r7:'price eps estimate next year',s7:'short ratio'
}[f];
}
}

 

5. Script para receber dados do OAuth – por Russel Savage. O OAuth é um serviço de autenticação necessário para acessar os APIs de algumas empresa, como o Twitter por exemplo. Esse script permite que você faça o login e acesse o API para retirar os dados de uma URL.

 

/******************************************
* Yahoo Finance API Class Example
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function main() {
var sheetUrl = 'ENTER A GOOGLE SHEET URL HERE';

var yfa = new YahooFinanceAPI({
symbols: ['^GSPC','VTI','^IXIC','BTCUSD=X'],
f: 'snl1' // or something longer like this 'sl1abb2b3d1t1c1ohgv'
});
for(var key in yfa.results) {
Logger.log(Utilities.formatString('Name: "%s", Symbol: "%s", Last Trade Price: $%s',
yfa.results[key].name,
key,
yfa.results[key].last_trade_price_only));
}

var includeColumnHeaders = true;
var sheetData = yfa.toGoogleSheet(includeColumnHeaders);
var ss = SpreadsheetApp.openByUrl(sheetUrl).getActiveSheet();
for(var i in sheetData) {
ss.appendRow(sheetData[i]);
}
}

 

Basta copiar o código a seguir na parte inferior do seu script do Google AdWords e estará pronto.

 

/******************************************
* Yahoo Finance API Class
* Use this to pull stock market quotes from Yahoo Finance
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function YahooFinanceAPI(configVars) {
var QUERY_URL_BASE = 'https://query.yahooapis.com/v1/public/yql';
var FINANCE_URL_BASE = 'http://download.finance.yahoo.com/d/quotes.csv';
this.configVars = configVars;

/*************
* The results are stored here in a
* map where the key is the ticker symbol
* { 'AAPL' : { ... }, 'GOOG' : { ... }
*************/
this.results = {};

/************
* Function used to refresh the results
* from Yahoo! Finance API. Called automatically
* during object reaction.
************/
this.refresh = function() {
var queryUrl = getQueryUrl(this.configVars);
var resp = UrlFetchApp.fetch(queryUrl,{muteHttpExceptions:true});
if(resp.getResponseCode() == 200) {
var jsonResp = JSON.parse(resp.getContentText());
if(jsonResp.query.count == 1) {
var row = jsonResp.query.results.row;
this.results[row.symbol] = row;
} else if(jsonResp.query.count > 1) {
for(var i in jsonResp.query.results.row) {
var row = jsonResp.query.results.row[i];
this.results[row.symbol] = row;
}
}
} else {
throw resp.getContentText();
}
}

/************
* Translates the results into a 2d array
* to make it easier to add into a Google Sheet.
* includeColumnHeaders - true or false if you want
* headers returned in the results.
************/
this.toGoogleSheet = function(includeColumnHeaders) {
if(!this.results) { return [[]]; }
var retVal = [];
var headers = null;
for(var key in this.results) {
if(!headers) {
headers = Object.keys(this.results[key]).sort();
}
var row = [];
for(var i in headers) {
row.push(this.results[key][headers[i]]);
}
retVal.push(row);
}
if(includeColumnHeaders) {
return [headers].concat(retVal);
} else {
return retVal;
}
}

// Perform a refresh on object creation.
this.refresh();

// Private functions

/************
* Builds Yahoo Finance Url
************/
function getFinanceUrl(configVars) {
var financeUrlParams = {
s : configVars.symbols.join(','),
f : configVars.f,
e : '.json'
}
return FINANCE_URL_BASE + serialize(financeUrlParams);
}

/************
* Builds Yahoo Query Url
************/
function getQueryUrl(configVars) {
var financeUrl = getFinanceUrl(configVars);
var cols = fToCols(configVars.f);
var queryTemplate = "select * from csv where url='%s' and columns='%s'";
var query = Utilities.formatString(queryTemplate, financeUrl,cols.join(','));
var params = {
q : query,
format : 'json'
}
var finalRestUrl = QUERY_URL_BASE + serialize(params);
return finalRestUrl;
}

/************
* This function translates the f parameter
* into actual field names to use for columns
************/
function fToCols(f) {
var cols = [];
var chunk = '';
var fBits = f.split('').reverse();
for(var i in fBits) {
chunk = (fBits[i] + chunk);
if(fLookup(chunk)) {
cols.push(fLookup(chunk));
chunk = '';
}
}
return cols.reverse();
}

/************
* Copied from: http://stackoverflow.com/a/18116302
* This function converts a hash into
* a url encoded query string.
************/
function serialize( obj ) {
return '?'+
Object.keys(obj).reduce(
function(a,k) {
a.push(k+'='+encodeURIComponent(obj[k]));
return a
},
[]).join('&');
}

/************
* Adapted from http://www.jarloo.com/yahoo_finance/
* This function maps f codes into
* friendly column names.
************/
function fLookup(f){
return{
a:'ask',b:'bid',b2:'ask realtime',b3:'bid realtime',p:'previous close',o:'open',
y:'dividend yield',d:'dividend per share',r1:'dividend pay date',
q:'ex-dividend date',c1:'change',c:'change & percent change',c6:'change realtime',
k2:'change percent realtime',p2:'change in percent',d1:'last trade date',
d2:'trade date',t1:'last trade time',c8:'after hours change realtime',
c3:'commission',g:'days low',h:'days high',k1:'last trade realtime with time',
l:'last trade with time',l1:'last trade price only',t8:'1 yr target price',
m5:'change from 200 day moving average',m6:'percent change from 200 day moving average',
m7:'change from 50 day moving average',m8:'percent change from 50 day moving average',
m3:'50 day moving average',m4:'200 day moving average',w1:'days value change',
w4:'days value change realtime',p1:'price paid',m:'days range',m2:'days range realtime',
g1:'holdings gain percent',g3:'annualized gain',g4:'holdings gain',
g5:'holdings gain percent realtime',g6:'holdings gain realtime',t7:'ticker trend',
t6:'trade links',i5:'order book realtime',l2:'high limit',l3:'low limit',
v1:'holdings value',v7:'holdings value realtime',s6: 'revenue',k:'52 week high',
j:'52 week low',j5:'change from 52 week low',k4:'change from 52 week high',
j6:'percent change from 52 week low',k5:'percent change from 52 week high',
w:'52 week range',v:'more info',j1:'market capitalization',j3:'market cap realtime',
f6:'float shares',n:'name',n4:'notes',s:'symbol',s1:'shares owned',x:'stock exchange',
j2:'shares outstanding',v:'volume',a5:'ask size',b6:'bid size',k3:'last trade size',
a2:'average daily volume',e:'earnings per share',e7:'eps estimate current year',
e8:'eps estimate next year',e9:'eps estimate next quarter',b4:'book value',j4:'ebitda',
p5:'price sales',p6:'price book',r:'pe ratio',r2:'pe ratio realtime',r5:'peg ratio',
r6:'price eps estimate current year',r7:'price eps estimate next year',s7:'short ratio'
}[f];
}
}

 

 

6. Acrescente estatísticas de crime em seus criativos – por Russel Savage. Utilizar dados aumentam a credibilidade de seus anúncios. Sendo assim, esse script trabalha com dados APIs que alguns sites governamentais disponibilizam e atualiza os parâmetros das palavras-chave de anúncios com o número de tipos de crimes diferentes.

 

//-----------------------------------
// Put Chicago Crime Stats in Your Creatives
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
// You can get this link by going here: http://goo.gl/tfNgM
// Apply some filters and then click export > api
// This end point is good for all of 2013
var DATA_ENDPOINT = "http://data.cityofchicago.org/resource/pga9-zdiw.json";
var CAMPAIGN_PREFIX = 'Crime_Data_Chicago_'; //All your campaigns start with this
var AD_PARAM = 1; // 1 or 2

try {
var json = Utilities.jsonParse(UrlFetchApp.fetch(DATA_ENDPOINT).getContentText());
var summary = summarizeCrimeStats(json);
//logCrimeSummary(summary);

for(var i in summary) {
var total = totalPrimaryDescription(i,summary);
var kw_iter = AdWordsApp.keywords()
.withCondition("CampaignName CONTAINS_IGNORE_CASE '"+CAMPAIGN_PREFIX+i+"'")
.get();
while(kw_iter.hasNext()) {
var kw = kw_iter.next();
kw.setAdParam(AD_PARAM, total);
}
}
}catch(e) {
}
}

// A helper function to aggregate the data by primary description
function totalPrimaryDescription(key,summary) {
var tot = 0;
for(var i in summary[key]) {
tot += summary[key][i];
}
return tot;
}

//This function takes in a json formatted object and stores the count of instances
//in a 2 dimentional hash of [Primary Description][Secondary Description]
function summarizeCrimeStats(json) {
var crime_summary = {};
for(var i in json) {
var crime = json[i];
if(crime_summary[crime._primary_decsription]) {
if(crime_summary[crime._primary_decsription][crime._secondary_description]) {
crime_summary[crime._primary_decsription][crime._secondary_description]++;
}else{
crime_summary[crime._primary_decsription][crime._secondary_description] = 1;
}
}else{
crime_summary[crime._primary_decsription] = {};
crime_summary[crime._primary_decsription][crime._secondary_description] = 1;
}
}
return crime_summary;
}

//Just a helper function to print out the summary info so that
//I can find the data I'm interested in.
function logCrimeSummary(crime_summary) {
for(var i in crime_summary) {
for(var x in crime_summary[i]) {
Logger.log([i,x,crime_summary[i][x]].join(', '));
}
}
}

 

7. Connecte o Zoho CRM com o Google Ads – por Russell Savage. Utilize os dados do Google Ads (clique, impressões…) e da sua plataforma de CRM (Leads, contatos, oportunidadees…) para gerar um relatório integrado. Utilizando-se das duas fontes de dados é possível ter uma visão mais apurada de suas campanhas.

 

/******************************************
* Zoho CRM Get API Class
* Use it to pull data out of Zoho CRM
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
//For more info about the Zoho CRM API, see here:
// https://www.zoho.com/crm/help/api/
function ZohoApi(authToken) {
var ZOHO_AUTH_TOKEN = authToken;
var ZOHO_BASE_URL = 'https://crm.zoho.com/crm/private/json/';
var METHODS = ['getMyRecords','getRecords','getRecordById','getCVRecords',
'getSearchRecords','getSearchRecordsByPDC','getRelatedRecords',
'getFields','getUsers','downloadFile','downloadPhoto'];
var OBJECTS = ['Leads','Accounts','Contacts','Potentials',
'Campaigns','Cases','Soultions','Products',
'PriceBooks','Quotes','Invoices','SalesOrders',
'Vendors','PurchaseOrders','Events','Tasks','Calls'];

for(var i in OBJECTS) {
// Creating getPotentials() functions
this['get'+OBJECTS[i]] = new Function('additionalParams',
'return this.get("'+OBJECTS[i]+'","getRecords",additionalParams);');
// Creating getMyPotentials() functions
this['getMy'+OBJECTS[i]] = new Function('additionalParams',
'return this.get("'+OBJECTS[i]+'","getMyRecords",additionalParams);');
// Creating getPotentialsById(id) functions
this['get'+OBJECTS[i]+'ById'] = new Function('id',
'return this.get("'+OBJECTS[i]+'","getRecordById",{ id : id });');
// Creating searchPotentials(searchCondition) functions
this['search'+OBJECTS[i]] = new Function('criteria',
'return this.get("'+OBJECTS[i]+'","getSearchRecords",criteria);');
// Creating getPotentialsFields() functions
this['get'+OBJECTS[i]+'Fields'] = new Function('return this.get("'+OBJECTS[i]+'","getFields",{});');
}

// You can use any Zoho OBJECT and METHOD and
// put any additional parameters as a map {param : val, param2: val2}
this.get = function(zohoObj,zohoMethod,additionalParams) {
validateParams(zohoObj,zohoMethod);
additionalParams = addColumnsIfNeeded(this,zohoObj,zohoMethod,additionalParams);
var url = buildUrl(zohoObj,zohoMethod,additionalParams);
Logger.log(url);
var resp = UrlFetchApp.fetch(url).getContentText();
try {
var jsonObj = JSON.parse(resp);
if(jsonObj['response'] && jsonObj['response']['nodata']) {
Logger.log('Code: '+jsonObj['response']['nodata']['code']+
' Message: '+ERRORS[jsonObj['response']['nodata']['code']]);
return [];
}
if(jsonObj['response'] && jsonObj['response']['error']) {
throw 'Code: '+jsonObj['response']['error']['code']+
' Message: '+jsonObj['response']['error']['message'];
}

if(jsonObj['response'] && jsonObj['response']['result']) {
return parseResponseObject(zohoObj,jsonObj);
}
if(jsonObj[zohoObj] && jsonObj[zohoObj]['section']) {
return parseFieldsObject(zohoObj,jsonObj);
}
return jsonObj;
}catch(e){
throw 'There was an issue parsing the response. '+e;
}
};

function parseResponseObject(zohoObj,jsonObj) {
if(jsonObj['response'] && jsonObj['response']['result']) {
var rows = jsonObj['response']['result'][zohoObj]['row'];
if(typeof rows[0] === 'undefined') {
return [mapValToContent(rows)];
} else {
var retVal = [];
for(var i in rows) {
retVal.push(mapValToContent(rows[i]));
}
return retVal;
}
}
return [];
}

function parseFieldsObject(zohoObj,jsonObj) {
if(jsonObj[zohoObj] && jsonObj[zohoObj]['section']) {
var fields = [];
for(var i in jsonObj[zohoObj]['section']) {
var elem = jsonObj[zohoObj]['section'][i];
if(elem['FL'] && elem['FL'][0]) {
for(var x in elem['FL']) {
var field = elem['FL'][x];
if(field['dv']) {
fields.push(field['dv']);
}
}
} else if(elem['FL'] && elem['FL']['dv']) {
fields.push(elem['FL']['dv']);
}
}
return fields;
}
return [];
}

function validateParams(zohoObj,zohoMethod) {
if(!zohoObj || OBJECTS.indexOf(zohoObj) == -1) {
throw 'Get must be called with a proper ZOHO object. Object given: "'+
zohoObj+'" Available Objects:'+OBJECTS.join(',');
}
if(!zohoMethod || METHODS.indexOf(zohoMethod) == -1) {
throw 'Get must be called with a proper ZOHO method. Method given: "'+
zohoObj+'" Available Methods:'+METHODS.join(',');
}
}

function addColumnsIfNeeded(self,zohoObj,zohoMethod,additionalParams) {
var searchConditionRequired = ['getSearchRecords','getSearchRecordsByPDC'];
if(searchConditionRequired.indexOf(zohoMethod) >= 0) {
if(!additionalParams['selectColumns']) {
additionalParams['selectColumns'] = zohoObj+'('+self['get'+zohoObj+'Fields']().join(',')+')';
}
}
return additionalParams;
}

function buildUrl(zohoObj,zohoMethod,additionalParams) {
var url = ZOHO_BASE_URL+zohoObj+'/'+zohoMethod+
'?authtoken='+ZOHO_AUTH_TOKEN+'&scope=crmapi&newFormat=1';
for(var key in additionalParams) {
url += '&' + key + '=' + encodeURIComponent(additionalParams[key]);
}
return url;
}

function mapValToContent(obj) {
var retVal = {};
if(obj.FL) {
for(var i in obj.FL) {
var elem = obj.FL[i];
var key = elem.val;
var cleanKey = key.toLowerCase().replace(/ /g,'_');
retVal[cleanKey] = elem.content;
}
}
return retVal;
}

var ERRORS = {'4000':'Please use Authtoken, instead of API ticket and APIkey.',
'4500':'Internal server error while processing this request',
'4501':'API Key is inactive',
'4502':'This module is not supported in your edition',
'4401':'Mandatory field missing',
'4600':'Incorrect API parameter or API parameter value. Also check the method '+
'name and/or spelling errors in the API url.',
'4820':'API call cannot be completed as you have exceeded the "rate limit".',
'4831':'Missing parameters error',
'4832':'Text value given for an Integer field',
'4834':'Invalid ticket. Also check if ticket has expired.',
'4835':'XML parsing error',
'4890':'Wrong API Key',
'4487':'No permission to convert lead.',
'4001':'No API permission',
'401':'No module permission',
'401.1':'No permission to create a record',
'401.2':'No permission to edit a record',
'401.3':'No permission to delete a record',
'4101':'Zoho CRM disabled',
'4102':'No CRM account',
'4103':'No record available with the specified record ID.',
'4422':'No records available in the module',
'4420':'Wrong value for search parameter and/or search parameter value.',
'4421':'Number of API calls exceeded',
'4423':'Exceeded record search limit',
'4807':'Exceeded file size limit',
'4424':'Invalid File Type',
'4809':'Exceeded storage space limit'};

}

 

E aqui está um exemplo muito simples de como você pode combinar dados de conversão de várias origens em um único relatório da planilha do Google.

 

/ ** ****************************************
* Combine os dados de conversão do Google AdWords e do Zoho CRM
* Versão 1.0
* Criado por: Russ Savage
* FreeAdWordsScripts.com
***************************************** * /
var ZOHO_AUTH_TOKEN = 'YOUR ZOHO AUTH TOKEN';
var SPREADSHEET_URL = 'THE URL OF AN EMPTY SPREADSHEET';

function main() {
var acctStats = AdWordsApp.currentAccount().getStatsFor('YESTERDAY');
var adWordsImps = parseFloat(acctStats.getImpressions());
var adWordsClicks = parseFloat(acctStats.getClicks());
var adWordsCtr = parseFloat(acctStats.getCtr());
var adWordsConv = parseFloat(acctStats.getConversions());
var crmConv = getCRMConversions().count;
var totalConv = (crmConv + adWordsConv);
var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
if(!sheet.getActiveRange().getValue()) {
sheet.appendRow(['Date','Account Name',
'Impressions','Clicks','Ctr',
'AdWords Conversions','Conv Rate',
'CRM Wins','Win %']);
}
var toAppend = [
Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd'),
AdWordsApp.currentAccount().getName(),
adWordsImps,
adWordsClicks,
adWordsCtr,
adWordsConv,
(adWordsClicks === 0) ? 0 : Math.round((adWordsConv/adWordsClicks)*100)/100,
crmConv,
(adWordsConv === 0) ? 0 : Math.round((crmConv/adWordsConv)*100)/100];
sheet.appendRow(toAppend);
}

function getCRMConversions(dateRange) {
var zoho = new ZohoApi(ZOHO_AUTH_TOKEN);
var closedWonCount = 0;
var closedWonRevenue = 0;
var yesterday = new Date();
yesterday.setDate(yesterday.getDate()-1);
var yesterdayStr = Utilities.formatDate(yesterday, AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
var potentials = zoho.searchPotentials({'searchCondition':'(Closing Date|=|'+yesterdayStr+')'});
for(var i in potentials) {
var potential = potentials[i];
if(potential.stage === 'Closed Won' && potential.campaign_source === 'AdWords') {
closedWonCount++;
closedWonRevenue += parseFloat(potential.amount);
}
}
return { count: closedWonCount, revenue: closedWonRevenue };
}

function ZohoApi(authToken) { throw 'Fill this code in from the blog post!'; }

 

8. Extraia dados do Salesforce e do Google Ads – por Russel Savage. Semelhante ao do Zoho CRM, esse script permite exportar dados do Salesforce para criar um relatório integrado com o Google Ads.

 

Primeiro, precisaremos configurar algumas credenciais do OAuth e, para isso, você precisa configurar um novo aplicativo conectado no Salesforce. Está em lugares um pouco diferentes em cada versão, mas usando a versão do desenvolvedor, eu consegui encontrá-la em Setup> Build> Create> Apps. De lá, até o final, você pode ver uma seção dos Aplicativos conectados.

 

 

Se, por algum motivo, você não conseguir encontrá-lo na sua instância do Salesforce, talvez o seu administrador não tenha lhe fornecido acesso. Espero que eles possam ajudá-lo.

Depois de clicar no novo botão, você precisará preencher alguns campos obrigatórios e selecionar a opção “Ativar configurações do Oauth”. Você precisará inserir um URL de retorno de chamada, mas não o usaremos para inserir qualquer URL que comece com https. Para “Escopos”, eu acabei de dizer “Acesso total”, mas talvez você precise conversar com o administrador do Salesforce sobre isso. Nós só estaremos lendo do Salesforce, então não deve ser um problema.

 

Isso é tudo que você precisa preencher e você deve ter um novo aplicativo criado. O importante aqui é a “Chave do Consumidor” e o “Segredo do Consumidor” que você precisará para o script se conectar à sua instância do Salesforce.
Consumidor Salesforce e chaves secretas

 

 

A última coisa que você precisará da sua instância do Salesforce é um token de segurança. Você pode já ter um, nesse caso, você pode pular isso. Mas se não, você pode redefini-lo em Minhas Configurações> Pessoal> Redefinir meu Token de Segurança. Ele lhe enviará um novo token.
Redefina seu token de segurança

 

 

Ok, agora estamos finalmente prontos para acessar o código de scripts do Google AdWords. O código a seguir configurará um novo objeto SalesforceAPI e consultará as Oportunidades mais recentes que foram fechadas para que você possa usar essa receita em sua conta do Google AdWords.

 

 

/******************************************
* Get Won Opportunity Revenue Amounts
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function main() {
var CONSUMER_KEY = "YOUR APP CONSUMER KEY";
var CONSUMER_SECRET = "YOUR APP SECRET KEY";
var USERNAME = "YOUR SALESFORCE USERNAME";
var PASSWORD = "YOUR SALESFORCE PASSWORD";
var SECURITY_TOKEN = "YOUR SECURITY TOKEN";

// Create a new SalesforceAPI object
var sf = new SalesforceAPI({
client_id : CONSUMER_KEY,
client_secret : CONSUMER_SECRET,
username : USERNAME,
password : PASSWORD,
token: SECURITY_TOKEN
});

// Query the results
var results = sf.query("SELECT Name \
, StageName \
, ExpectedRevenue \
FROM Opportunity \
WHERE IsWon = True \
AND CloseDate < THIS_WEEK");
// Log the results
for(var i in results) {
Logger.log(['Name:',results[i].Name,
'Stage Name:',results[i].StageName,
'Expected Revenue:',results[i].ExpectedRevenue,
'Url:',sf.getFullUrl(results[i].attributes.url)].join(' '));
}
}

 

É simples assim. Se você deseja obter todas as informações sobre um determinado objeto depois de consultá-lo, você pode usar a função getObjectByUrl () e enviar a url dos resultados da consulta. Para saber mais sobre a sintaxe da consulta, confira a documentação do Salesforce SOQL .

Existem algumas ressalvas para este código. Toda instalação do Salesforce é única, então não há como realmente solucionar problemas com sua instalação específica. Esse código foi testado em uma nova conta do Salesforce for Developers para que seus resultados possam variar. Você provavelmente terá mais sorte em entrar em contato com seu administrador do Salesforce do que deixar um comentário aqui. Além disso, você pode perceber que o código está usando a opção menos segura para entrar no Salesforce. Esse código com seu nome de usuário e senha estará acessível a todos os usuários de sua conta do Google AdWords, portanto, tenha cuidado. Talvez seja melhor criar um usuário especial do Salesforce com permissões muito limitadas para algo assim.

 

/******************************************
* Salesforce.com CRM API Class
* Use it to query data out of Salesforce CRM
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function SalesforceAPI(configVars) {
this.login = function(configVars) {
var LOGIN_ENDPOINT = "https://login.salesforce.com/services/oauth2/token";
var options = {
muteHttpExceptions : false,
method: "POST",
payload: {
grant_type : "password",
client_id : configVars.client_id,
client_secret : configVars.client_secret,
username : configVars.username,
password : configVars.password+configVars.token
}
};
var resp = UrlFetchApp.fetch(LOGIN_ENDPOINT, options);
if(resp.getResponseCode() == 200) {
var jsonResp = JSON.parse(resp.getContentText());
this.id = jsonResp.id;
this.instanceUrl = jsonResp.instance_url;
this.signature = jsonResp.signature;
this.accessToken = jsonResp.access_token;
Logger.log('Successfully logged in user with id: '+this.id);
}
}

this.getServices = function() {
if(this.serviceUrls) { return this.serviceUrls };
var ENDPOINT_URL = this.instanceUrl+"/services/data/v26.0/.json";
var options = getBasicOptions(this.accessToken);
var resp = UrlFetchApp.fetch(ENDPOINT_URL, options);
if(resp.getResponseCode() == 200) {
var jsonResp = JSON.parse(resp.getContentText());
this.serviceUrls = jsonResp;
return this.serviceUrls;
}
}

this.query = function(queryStr) {
if(!this.serviceUrls.query) { throw "Query service is not enabled in this SF instance."; }
var ENDPOINT_URL = this.instanceUrl+this.serviceUrls.query+'.json';
var url = ENDPOINT_URL + '?q=' + encodeURIComponent(queryStr);
var options = getBasicOptions(this.accessToken);
var resp = UrlFetchApp.fetch(url, options);
if(resp.getResponseCode() == 200) {
var jsonResp = JSON.parse(resp.getContentText());
if(jsonResp.done) {
return jsonResp.records;
} else {
var retVal = jsonResp.records;
while(!jsonResp.done) {
resp = UrlFetchApp.fetch(jsonResp.nextRecordsUrl, options);
if(resp.getResponseCode() == 200) {
jsonResp = JSON.parse(resp.getContentText());
retVal = retVal.concat(jsonResp.records);
}
}
return retVal;
}
}
}

this.getObjectByUrl = function(url) {
var url = this.instanceUrl + url + '.json';
var options = getBasicOptions(this.accessToken);
var resp = UrlFetchApp.fetch(url, options);
Logger.log(resp.getContentText());
if(resp.getResponseCode() == 200) {
return JSON.parse(resp.getContentText());
}
}

this.getFullUrl = function(url) {
return this.instanceUrl + url;
}

this.login(configVars);
this.getServices();

function getBasicOptions(token) {
return {
muteHttpExceptions : false,
method: 'GET',
headers: {
Authorization : "Bearer " + token
}
};
}
}