Another request we had was for a simple script that gets your search queries, puts them into a Google Sheet, and then uses Google Translate to quickly identify any that aren’t in the main account’s language, and then easily add any of those to a Negative Keyword list.
There aren’t many parameters to change – simply add the name of the output Spreadsheet (the script will create one for you), the name of the sheet (again, the script will create it for you), and a date range for the SQR report.
Once that’s done, simply run the script – it’ll give you the URL for your new sheet and you can have a look through all the queries there. When you find a query you’d like to add to a negative list, simply add the name of the list to the negative list column (needs to be exactly as it shows up in your Google Ads account). Once that’s all done, re-run the script and it’ll add all of those keywords to your negative lists!
/*
_ ___ _ _ _____ ___ _
/_\ / __| | | |_ _/ _ \ (_)___
/ _ \ (__| |_| | | || (_) || / _ \
/_/ \_\___|\___/ |_| \___(_)_\___/
*/
var config = {
// The name of the output spreadsheet
spreadsheetName: 'SQR Translator - Acuto Script',
// The name you want to give your sheet (i.e. the tab)
sheetName: 'Translated Queries',
// The date range for the SQR report
// Valid values: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_WEEK, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH
dateRange: 'LAST_7_DAYS',
}
function main() {
sqrTranslator();
}
function sqrTranslator() {
// Get the sheet, or create a new one if it doesn't already exist
var spread;
var sheet;
try {
var spreadUrl = DriveApp.getFilesByName(config.spreadsheetName).next().getUrl();
spread = SpreadsheetApp.openByUrl(spreadUrl);
sheet = spread.getSheetByName(config.sheetName);
} catch(e) {
spread = SpreadsheetApp.create(config.spreadsheetName);
spread.insertSheet(config.sheetName);
sheet = spread.getSheetByName(config.sheetName);
spread.deleteSheet(spread.getSheetByName('Sheet1'));
var headersRange = sheet.getRange('B2:D2');
headersRange.setValues([['QUERY', 'TRANSLATION', 'NEGATIVE LIST']]);
headersRange.setBackground('#4A86E8');
headersRange.setFontWeight('bold');
headersRange.setFontColor('#ffffff');
headersRange.setHorizontalAlignment('center');
headersRange.setVerticalAlignment('middle');
sheet.setColumnWidths(2, 3, 200);
sheet.setRowHeight(2, 30);
Logger.log('Created new spreadsheet here: ' + spread.getUrl());
}
// Add any keywords to negative lists
if (sheet.getLastRow() > 2) {
var queries = sheet.getRange(3, 2, sheet.getLastRow() - 3, 3).getValues();
queries.forEach(function(row) {
var negativeList = row[2];
if (negativeList) {
var kw = row[0];
var list = AdsApp.negativeKeywordLists().withCondition('Name ="' + negativeList + '"').get().next();
list.addNegativeKeyword(kw);
}
})
}
// Get a new report and update the sheet
var query = 'SELECT Query FROM SEARCH_QUERY_PERFORMANCE_REPORT WHERE Impressions > 0 DURING ' + config.dateRange;
var report = AdsApp.report(query);
var data = [];
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var query = row.Query;
data.push([query]);
}
var formulas = data.map(function(row, i) {
var formula = '=GOOGLETRANSLATE(B' + (i + 3) + ')';
return [formula];
});
sheet.getRange('B3:D').clearContent();
if (!data[0]) {
Logger.log('No data found! Try changing your date range to a longer period');
} else {
sheet.getRange(3, 2, data.length, data[0].length).setValues(data);
sheet.getRange(3, 3, data.length, 1).setFormulas(formulas);
}
}
