Skip to content

Instantly share code, notes, and snippets.

@agustinscigliano
Last active April 13, 2018 20:54
Show Gist options
  • Save agustinscigliano/78d063ccd30548fb77f3a3e90db83a7b to your computer and use it in GitHub Desktop.
Save agustinscigliano/78d063ccd30548fb77f3a3e90db83a7b to your computer and use it in GitHub Desktop.
Google app script for structure creation
function structureCreation2() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var original_sheet = ss.getSheetByName('structure_original');
var data_sheet = ss.getSheetByName('input');
var work_sheet = ss.getSheetByName('structure');
var keywords = ["{{customer}}","{{shop_url}}","{{brand}}","{{competitors}}","{{keyword}}","{{category_ad}}","{{url}}","{{match-type}}","{{campaign-type}}","{{bid}}"];
var column_data_source = [1,3,5,7,9,10,11,13,15,16];
var row_data_source_beginning = 4;
var options = {};
var options_size = {};
var total_result = [];
for (var i =0 ; i < column_data_source.length ; i++) {
var values_q_all = data_sheet.getRange(4, column_data_source[i],20,1).getValues();
var values_q = values_q_all.filter(String);
var key = keywords[i];
var newArr = []
for(var j = 0; j < values_q.length; j++) {
newArr = newArr.concat(values_q[j]);
}
options[key] = newArr;
options_size[key] = newArr.length;
}
var selection=original_sheet.getDataRange();
var columns=selection.getNumColumns();
var rows=selection.getNumRows();
var rows_range = original_sheet.getRange(2, 1, rows, columns);
var rows_info = rows_range.getValues();
for (var row=0; row < rows; row++) {
var row_info = rows_info[row];
var matched_words_array = matched_words(keywords,row_info);
var keywords_variations = combinations(matched_words_array, options);
var possible_combinations = keywords_variations.length;
for (var k = 0 ; k < possible_combinations ; k++) {
var aux = row_info.slice(0);
for (var l = 0 ; l < aux.length ; l++) {
for (var m = 0 ; m < matched_words_array.length ; m++) {
aux[l] = aux[l].replace(matched_words_array[m],keywords_variations[k][m]);
}
}
total_result.push(aux);
}
}
work_sheet.clear();
work_sheet.getRange(1, 1, total_result.length, total_result[0].length).setValues(total_result);
}
function combinations(keywords, options) {
if (keywords.length == 0) {
return [[]];
} else {
var res = [];
var opt;
for (var i = 0 ; i < options[keywords[0]].length ; i++) {
opt = options[keywords[0]][i];
var combination;
var combinations_array = combinations(keywords.slice(1,keywords.length), options);
for (var j=0 ; j < combinations_array.length ; j ++) {
combination = combinations_array[j];
res = res.concat([[opt].concat(combination)]);
}
}
return res;
}
}
function matched_words(arr1, arr2) { //Arr1 indicates words to be found, and arr2 are the containers.
var ret = [];
for(var i = 0; i < arr2.length; i += 1) {
for(var j = 0; j < arr1.length; j += 1) {
if(arr2[i].match(arr1[j]) && ret.indexOf(arr1[j]) < 0) {
ret.push(arr1[j]);
}
}
}
return ret;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment