Last active
April 13, 2018 20:54
-
-
Save agustinscigliano/78d063ccd30548fb77f3a3e90db83a7b to your computer and use it in GitHub Desktop.
Google app script for structure creation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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