Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
var DATA_SOURCE_SPREADSHEET = "URL OF GOOGLE SHEET CONTAINING DATA";
var CUSTOM_ID_COLUMN = 'Model (text)';
var SPECIAL_COLUMNS = ['Target ad group','Target campaign','Target keyword'];
function main() {
createDataSources();
}
function createCustomizersByIdMap(source) {
try {
var customizers = source.items().get();
var customizersById = {};
while (customizers.hasNext()) {
var customizer = customizers.next();
customizersById[customizer.getAttributeValue('Custom ID')] = customizer;
}
return customizersById;
} catch(e) {
return {};
}
}
function getCustomIdIndex(headers) {
var customIdIndex = headers.indexOf(CUSTOM_ID_COLUMN);
if(customIdIndex == -1) {
throw 'Could not find custom id column in headers: '+headers;
}
return customIdIndex;
}
function addOrUpdateAdCustomizerItems(source,data,headers) {
var itemsByIdMap = createCustomizersByIdMap(source);
var attrTypesMap = source.getAttributes();
for(var r in data) {
var row = data[r];
var customIdIndex = getCustomIdIndex(headers);
var attributeObj = { 'Custom ID' : row[customIdIndex] };
var customizer = itemsByIdMap[row[customIdIndex]];
for(var c = 0; c < headers.length; c++) {
if(!row[c]) { continue; }
if(SPECIAL_COLUMNS.indexOf(headers[c]) >= 0) { continue; }
var nameAndType = parseColumnHeader(headers[c]);
attributeObj[nameAndType.name] = row[c];
if(attrTypesMap[nameAndType.name] == 'price' && typeof row[c] == 'number') {
attributeObj[nameAndType.name] = '$'+row[c];
} else if(attrTypesMap[nameAndType.name] == 'date' && typeof row[c] != 'string') {
attributeObj[nameAndType.name] = Utilities.formatDate(row[c],
AdWordsApp.currentAccount().getTimeZone(),
'yyyyMMdd HHmmss');
}
}
if(customizer) {
customizer.setAttributeValues(attributeObj);
} else {
var itemBuilder = source.adCustomizerItemBuilder().withAttributeValues(attributeObj);
if(headers.indexOf('Target ad group') >= 0) {
itemBuilder = itemBuilder.withTargetAdGroup(row[headers.indexOf('Target campaign')],
row[headers.indexOf('Target ad group')]);
} else if(headers.indexOf('Target campaign')>=0) {
itemBuilder = itemBuilder.withTargetCampaign(row[headers.indexOf('Target campaign')]);
}
if(headers.indexOf('Target keyword') >= 0) {
itemBuilder = itemBuilder.withTargetKeyword(headers.indexOf('Target keyword'));
}
itemBuilder.build();
}
}
}
function getDataSourceToNameMap() {
var sourcesMap = {};
try {
var sourcesIter = AdWordsApp.adCustomizerSources().get();
while(sourcesIter.hasNext()) {
var source = sourcesIter.next();
sourcesMap[source.getName()] = source;
}
} catch(e) {
// Sometimes there is an error
// if you don't have any sources. Log and ignore.
Logger.log(e);
}
return sourcesMap;
}
function createDataSources() {
var spreadsheet = SpreadsheetApp.openByUrl(DATA_SOURCE_SPREADSHEET);
if(!spreadsheet) { throw 'Spreadsheet could not be opened. Check your url.'; }
var dataSourceMap = getDataSourceToNameMap();
var sheets = spreadsheet.getSheets();
for(var i in sheets) {
var sheet = sheets[i];
var data = sheet.getDataRange().getValues();
if(data.length <= 1) { continue; } //ignore empty sheets
var headers = data.shift();
var adCustomizerSource = dataSourceMap[sheet.getName()];
if(!adCustomizerSource) {
var attributeMap = getAttributeMap(headers);
var sourceBuilder = AdWordsApp.newAdCustomizerSourceBuilder().withName(sheet.getName());
sourceBuilder = sourceBuilder.addAttributes(attributeMap);
var result = sourceBuilder.build();
adCustomizerSource = result.getResult();
}
addOrUpdateAdCustomizerItems(adCustomizerSource,data,headers);
}
}
function getAttributeMap(headers) {
var attributeMap = {};
for(var c in headers) {
var header = headers[c];
if(!header) { continue; }
if(SPECIAL_COLUMNS.indexOf(header) >= 0) {
if(CUSTOM_ID_COLUMN == header) {
attributeMap['Custom ID'] = 'text';
}
continue;
} else {
var nameAndType = parseColumnHeader(header);
attributeMap[nameAndType.name] = nameAndType.type;
if(CUSTOM_ID_COLUMN == header) {
attributeMap['Custom ID'] = nameAndType.type;
}
}
}
return attributeMap;
}
function parseColumnHeader(columnText) {
if(!columnText) { throw 'columnText cannot be empty'; }
var results = columnText.match(/(.+)\s\((.+)\)/);
if(results && results[1] && results[2]) {
return { name: results[1].trim(), type: results[2].trim(), original: columnText };
} else {
throw 'The columnText did not match the format "Name (type)": '+columnText;
}
}
@Markvdeng

This comment has been minimized.

Copy link

Markvdeng commented Feb 16, 2016

I would love to try this script, but after setting up the sheet with the data, and running the script to create the data source i get the following error: Invalid argument: Custom ID (line 55). I have spent some time on it, but cant figure it out, any ideas?

@yyko

This comment has been minimized.

Copy link

yyko commented Oct 10, 2016

Perhaps, data that you use for Custom ID is not unique?

@theniks1

This comment has been minimized.

Copy link

theniks1 commented Apr 10, 2018

I am getting error "Item not found" .
When I preview the script it works great but when I run it then I get this error.
Any idea whats wrong?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.