// integration of adurls and sitelink urls var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/1JxBdvdm0ZxUlZep0ALYaHtd63B1N7DaNjBY2lZMV4VE/edit?usp=sharing"; var QUOTA_CONFIG = { INIT_SLEEP_TIME: 250, BACKOFF_FACTOR: 2, MAX_TRIES: 5 }; var validCodes = ['200']; function main() { var tabs = getTabs(); for(var i in tabs) { var results = runQuery(tabs[i]); writeToSpreadsheet(tabs[i],results); //Logger.log('was in the main'); } Logger.log('Search Query report available at\n' + SPREADSHEET_URL); } function getTabs(){ var sheet_tabs = ['AdUrls','Sitelinks']; return sheet_tabs; } //Helper function to get or create the spreadsheet function getSheet(tab) { var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL); var sheet; try { sheet = s_sheet.getSheetByName(tab); if(!sheet) { sheet = s_sheet.insertSheet(tab, 0); } } catch(e) { sheet = s_sheet.insertSheet(tab, 0); } return sheet } //Function to write the rows of the report to the sheet function writeToSpreadsheet(tab,rows) { var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL); var sheet = getSheet(tab); sheet.clear(); if(tab.indexOf('AdUrls') == 0){ var report_rows = convertRowsToSpreadsheetRows( rows); var to_write = final_results(report_rows); } if(tab.indexOf('Sitelinks') == 0){ var to_write = rows; } var numRows = sheet.getMaxRows(); if(numRows < to_write.length) { sheet.insertRows(2,(to_write.length-numRows)); } var range = sheet.getRange(3,1,to_write.length,to_write[0].length); range.setValues(to_write); Logger.log("number of rows : %s", to_write.length); setFormatAndFormulas(sheet,to_write,tab); } function setFormatAndFormulas(sheet,to_write,tab){ sheet.setFrozenRows(3); var range = sheet.getRange(3,1,1,to_write[0].length); range.setFontWeight("bold"); var table = sheet.getRange(3,1,to_write.length,to_write[0].length); table.setBorder(true, true, true, true, true, true, "blue",SpreadsheetApp.BorderStyle.SOLID); table.setWrap(true); return sheet; } //A generic function used to build and run the report query function runQuery(tab) { if(tab.indexOf('AdUrls') == 0) {    var cols = getColumns( ); var report = getReport( ); //var date_range = getDateRange( ); //var where = getWhereClause(tab); var query = ['select',cols.join(','),'from',report ].join(' '); var report_iter = AdWordsApp.report(query).rows(); var rows = []; while(report_iter.hasNext()) { rows.push(report_iter.next()); } return rows; }   if(tab.indexOf('Sitelinks') == 0) {     var sitelinkIterator = AdWordsApp.extensions().sitelinks() .withLimit(5) .get(); var outputValues =[]; while(sitelinkIterator.hasNext()) { Logger.log(" was here"); var sitelink = sitelinkIterator.next(); Logger.log('Sitelink text: ' + sitelink.getLinkText() + ',' + sitelink.getDescription1()); outputValues.push([ sitelink.getLinkText(), sitelink.getDescription1()?sitelink.getDescription1():'--', sitelink.urls().getFinalUrl(), requestUrl(sitelink.urls().getFinalUrl()) ]); } Logger.log( outputValues.length); Logger.log( outputValues[0].length); return outputValues; }    } //This function will convert row data into a format easily pushed into a spreadsheet function convertRowsToSpreadsheetRows(rows) { var cols = getColumns( ); var ret_val = [cols]; for(var i in rows) { var r = rows[i]; var ss_row = []; for(var x in cols) { ss_row.push(r[cols[x]]); } ret_val.push(ss_row); } return ret_val; } function getReport( ) { return 'AD_PERFORMANCE_REPORT'; } //returns the columns to add into the report function getColumns( ) { var ret_array = []; return ret_array.concat(['Id', 'AdGroupName', 'CampaignName', 'Description', 'Headline', 'DisplayUrl', 'CreativeFinalUrls', 'Labels' ]); } function final_results(ret_val){ //we are going to change the array a bit..take the ret_val array // and add response code as a column var responseCode; var already_checked = []; var numErrors =0; var numCols = ret_val[0].length; for(var i = 0;i< ret_val.length;i++) { var row = ret_val[i]; var url = ret_val[i].CreativeFinalUrls; var url_key = { url :url, responseCode : responseCode} if(!already_checked[url]) { responseCode = requestUrl(url); if(i ==0){ row.push('ResponseCode'); }else{ row.push(responseCode); } ret_val[i] = row; already_checked[url] = true; } else{ responseCode = url_key.responseCode; row.push(responseCode); ret_val[i] = row; } } return ret_val; } function requestUrl(url) { var responseCode; var sleepTime = QUOTA_CONFIG.INIT_SLEEP_TIME; var numTries = 0; while (numTries < QUOTA_CONFIG.MAX_TRIES && !responseCode) { try { // If UrlFetchApp.fetch() throws an exception, responseCode will remain // undefined. responseCode = UrlFetchApp.fetch(url, {muteHttpExceptions: true}).getResponseCode(); if (CONFIG.THROTTLE > 0) { Utilities.sleep(CONFIG.THROTTLE); } } catch(e) { if (e.message.indexOf('Service invoked too many times in a short time:') != -1) { Utilities.sleep(sleepTime); sleepTime *= QUOTA_CONFIG.BACKOFF_FACTOR; } else if (e.message.indexOf('Service invoked too many times:') != -1) { throw EXCEPTIONS.LIMIT; } else { return e.message; } } numTries++; } if (!responseCode) { throw EXCEPTIONS.QPS; } else { return responseCode; } }