Master.gs

function transpose(e){var t=e.length?e.length:0,a=e[0]instanceof Array?e[0].length:0;if(0===a||0===t)return[];var r,n,o=[];for(r=0;r<a;r++)for(o[r]=[],n=0;n<t;n++)o[r][n]=e[n][r];return o}function test_intersection(){Logger.log(arrayIntersection([1,2,3,"a"],[1,"a",2],["a",1])),Logger.log(arrayIntersection([1],[1,6,7,8]))}var arrayContains=Array.prototype.indexOf?function(e,t){return e.indexOf(t)>-1}:function(e,t){for(var a=e.length;a--;)if(e[a]===t)return!0;return!1};function arrayIntersection(){var e,t,a,r,n,o,i=[],s=Array.prototype.slice.call(arguments);if(a=s.pop())for(n=a.length,t=s.length;n--;){for(e=a[n],o=!1,r=t;!o&&r--;)arrayContains(s[r],e)||(o=!0);o||i.push(e)}return i}function test_getFilled2DArray(){Logger.log(getFilled2DArray("max",3,4))}function getFilled2DArray(e,t,a){for(var r=[],n=[],o=0;o<a;o++){n=[];for(var i=0;i<t;i++)n.push(e);r.push(n)}return r}function makeDvFromList(e,t,a){var r=SpreadsheetApp.newDataValidation();r.requireValueInList(t,!0),""!==a&&r.setHelpText(a),r.setAllowInvalid(!1),e.setDataValidation(r)}function getEditObject(e){return{user:Session.getActiveUser().getEmail(),source:SpreadsheetApp.getActiveSpreadsheet(),range:e,value:e.getValue(),authMode:"LIMITED"}}function ObjectOnEdit(e){var t=this,a=e.range,r=a.getWidth(),n=a.getHeight(),o=!0;(n>1||r>1)&&(o=!1);var i=a.getRow(),s=a.getColumn(),g=[];if(o)g.push(s);else for(var l=s;l<=s+r-1;l++)g.push(parseInt(l));t.range=a,t.numCols=r,t.numRows=n,t.numRow=i,t.numCol=s,t.boolCell=o,t.columns=g,t.getLastRow=a.getLastRow(),t.getLastCol=a.getLastColumn(),this.getValues=function(){if(!t.boolCell)return a.getValues();var r=e.value;return null!=r&&"object"!=typeof r||(r=a.getValue()),[[r]]};var u=e.oldValue;null==u&&(u=""),"object"==typeof u&&(u=""),t.oldValue=u;var S=a.getSheet(),h=S.getParent(),c=h.getId(),p=S.getName(),f=S.getSheetId();t.sheet=S,t.file=h,t.idFile=c,t.nameSheet=p,t.idSheet=f}function checkRowDv(e,t){return e<=t?0:1}function checkColDv(e,t,a){if("number"==typeof t)return t==e?1:0;var r=[];return Array.isArray(t)&&(r=t),"string"==typeof t&&(r=(r=t.split(a)).map(Number)),-1!=r.indexOf(e)?1:0}var NUM_MAX_BITE_SIZEOFPROPERTY=9e3,NUM_MAX_BITE_SIZEOFPROPERTIES=25e3,STR_PREFIX_NUMOFPARTS="NUM_OF_PARTS";function setPropertyFromNamedRange(e,t,a){a=a||t;var r=(e=e||SpreadsheetApp.getActiveSpreadsheet()).getRangeByName(t).getValue();return setUserProperty(r,a)}function setUserProperty(e,t){var a=PropertiesService.getUserProperties(),r=byteCount(e);if(r>NUM_MAX_BITE_SIZEOFPROPERTIES)return"Set Properties -- error. String size is more than "+NUM_MAX_BITE_SIZEOFPROPERTIES+" bites.";var n=Math.ceil(r/NUM_MAX_BITE_SIZEOFPROPERTY),o=chunkStringParts(e,n);a.setProperty(t+STR_PREFIX_NUMOFPARTS,n);for(var i=0;i<n;i++)e=o[i],a.setProperty(t+i,e);return"Set Properties -- ok!"}function getUserProperty(e){for(var t=PropertiesService.getUserProperties(),a="",r=t.getProperty(e+STR_PREFIX_NUMOFPARTS),n=0;n<r;n++)a+=t.getProperty(e+n);return a}function getPropertyAsArray(e){for(var t=PropertiesService.getUserProperties(),a="",r=t.getProperty(e+STR_PREFIX_NUMOFPARTS),n=0;n<r;n++)a+=t.getProperty(e+n);return JSON.parse(a)}function test_getRangeValuesMapR1C1(){var e=SpreadsheetApp.getActive().getActiveSheet().getRange("B2:C12");Logger.log(getRangeMapR1C1(e))}function getRangeMapR1C1(e,t){t=t||e.getValues();for(var a=e.getColumn(),r=e.getRow(),n=e.getWidth()+a-1,o=e.getHeight()+r-1,i={rowNums:[]},s={},g=r;g<=o;g++){i.rowNums.push(g);for(var l=a;l<=n;l++)(s={}).value=t[g-r][l-a],s.range=e.offset(g-r,l-a,1,1),i["R"+g+"C"+l]=s}return i}function test_getSmartSort(){var e=getSmartSort([["Earth","Europe","Britain","London"],["Earth","Europe","Britain","Manchester"],["Earth","Europe","Britain","Liverpool"],["Earth","Europe","France","Paris"],["Earth","Europe","France","Lion"],["Earth","Europe","Italy","Rome"],["Earth","Europe","Italy","Milan"],["Earth","Europe","Greece","Athenes"],["Earth","Asia","China","Pekin"],["Earth","Africa","Algeria","Algiers"],["Earth","America","USA","Dallas"],["Earth","America","USA","New York"],["Earth","America","USA","Chicago"],["Tatooine","Yulab","Putesh","ASU"],["Tatooine","Yulab","Putesh","Niatirb"],["Tatooine","Yulab","Zalip","Duantan"],["Tatooine","Asia","Solo","Lion"],["Tatooine","Asia","Solo","To"],["Earth","America","USA","San Francisco"],["Tatooine","Yulab","Koko","Traiwau"],["Venus","Yoo","Van","Derzar"],["Tatooine","Chendoo","org","Eccel"]]);Logger.log(e)}function getSmartSort(e){var t=Object.create(null);return e.map(function(e,a){var r=t;return e.map(function(e){return r[e]=r[e]||{_:a},(r=r[e])._})}).sort(function(e,t){var a;return e.some(function(e,r){return a=e-t[r]}),a}).map(function(t){return e[t[t.length-1]]})}function chunkString(e,t){return e.match(new RegExp(".{1,"+t+"}","g"))}function TESTchunkString(){Logger.log(chunkString("12312312",2))}function chunkStringParts(e,t){var a=e.length,r=Math.ceil(a/t);return chunkString(e,r)}function TESTchunkStringParts(){Logger.log(chunkStringParts("12312312",3))}function byteCount(e){return encodeURI(e).split(/%..|./).length-1}function replaceAll(e,t,a){return e.replace(new RegExp(t,"g"),a)}var OBJ_TASKS_DV={},C_TASKS_FUNCTIONS={clearContents:makeTasksClearContents,clearDv:makeTasksClearDataValidation,makeDv:makeTasksDataValidation,setVal:makeTasksSetValue};function makeTasksClearContents(e){e.clear({contentsOnly:!0})}function makeTasksClearDataValidation(e){e.clear({validationsOnly:!0})}function makeTasksDataValidation(e,t,a){makeDvFromList(e,t,a)}function makeTasksSetValue(e,t,a){var r=e.getWidth(),n=e.getHeight();1===r&&1===n?e.setValue(a):e.setValues(getFilled2DArray(a,r,n))}function test_addTask(){var e=[4,5,7];addTask("clearContents","",3,e),Logger.log(JSON.stringify(OBJ_TASKS_DV));addTask("makeBaby","Lu-Max",3,e,["ooo","oah","aaah"]),Logger.log(JSON.stringify(OBJ_TASKS_DV))}function addTask(e,t,a,r,n){OBJ_TASKS_DV[e]||(OBJ_TASKS_DV[e]={}),OBJ_TASKS_DV[e][t]||(OBJ_TASKS_DV[e][t]={}),OBJ_TASKS_DV[e][t].numRows||(OBJ_TASKS_DV[e][t].numRows=[]),OBJ_TASKS_DV[e][t].numColumnss||(OBJ_TASKS_DV[e][t].numColumnss=[]),OBJ_TASKS_DV[e][t].numRows.push(a),OBJ_TASKS_DV[e][t].numColumnss.push(r),n&&(OBJ_TASKS_DV[e][t].data||(OBJ_TASKS_DV[e][t].data=n))}function makeTasks(e){for(var t,a=getTaskMaker(OBJ_TASKS_DV),r=Object.keys(a),n="",o={},i=[],s={},g="",l=0,u=r.length;l<u;l++){n=r[l],t=C_TASKS_FUNCTIONS[n],o=a[n];for(var S=0,h=(i=Object.keys(o)).length;S<h;S++)s=o[g=i[S]],makeTasksFunctions(t,e,s.rangesA1,s.data,g)}return OBJ_TASKS_DV={},0}function makeTasksFunctions(e,t,a,r,n){for(var o={},i=0,s=a.length;i<s;i++)o=a[i],e(t.getRange(o.numRow,o.numCol,o.numRows,o.numCols),r,n)}function getTaskMaker(e){for(var t={},a=Object.keys(e),r="",n={},o={},i="",s={},g={},l=[],u=0,S=a.length;u<S;u++){n=e[r=a[u]],s={};for(var h=0,c=(l=Object.keys(n)).length;h<c;h++)o=n[i=l[h]],g=getSubTask(o),s[i]=g;t[r]=s}return t}function getSubTask(e){var t={};e.data&&(t.data=e.data);var a=getSubTaskRows({numRows:e.numRows,numColumnss:e.numColumnss}),r=getSubTaskA1Ranges(a);return t.rangesA1=r,t}function getSubTaskRows(e){for(var t={},a={},r={},n=e.numRows,o=0,i=e.numColumnss,s=[],g=[],l=0,u=n.length;l<u;l++)s=(r=a[o=n[l]]||{}).listCols||[],g=i[l],s=s.concat(g),s=getUniqueLine(s).sort(function(e,t){return e-t}),r.listCols=s,a[o]=r;return t.rows=a,t}function test_getSubTaskA1Ranges(){Logger.log(getSubTaskA1Ranges({rows:{11:{listCols:[2]},12:{listCols:[2]},13:{listCols:[2]},6:{listCols:[2]},7:{listCols:[2]},8:{listCols:[2]},9:{listCols:[2]},10:{listCols:[2]}}}))}function getSubTaskA1Ranges(e){var t,a={},r=null,n="",o=[],i=[],s=-1,g=0,l=0;(t=(t=Object.keys(e.rows)).map(function(e){return parseInt(e)})).sort(function(e,t){return e-t});for(var u=0,S=t.length;u<S;u++){l=t[u];for(var h=0,c=(i=e.rows[l].listCols).length;h<c;h++)(g=i[h])-s!=1?(r&&(a[n]=r),n="R"+l+"C"+g,o.push(n),(r={}).numRow=l,r.numCol=g,r.numCols=1):r.numCols=r.numCols+1,a[n]=r,s=g;r=null}var p={},f={},d="";for(u=0,S=o.length;u<S;u++)(f=p[d="C"+(r=a[n=o[u]]).numCol+"W"+r.numCols]||{}).numCol=r.numCol,f.numCols=r.numCols,f.rows=f.rows||[],f.rows.push(r.numRow),p[d]=f;var m=[],v=null,E=Object.keys(p),R=[],_=-1;for(l=0,u=0,S=E.length;u<S;u++){(R=(f=p[d=E[u]]).rows).sort(function(e,t){return e-t}),_=-1,v=null;for(h=0,c=R.length;h<c;h++)(l=R[h])-_!=1?(v&&m.push(v),(v=v||{}).numRow=l,v.numCol=f.numCol,v.numCols=f.numCols,v.numRows=v.numRows||1):v.numRows=v.numRows+1,_=l;m.push(v)}return m}var C_SOURCE_TRIGGER="SPREADSHEETS";function setTriggerOnEdit(e){if(checkTriggerExists(e,C_SOURCE_TRIGGER))return-1;var t=SpreadsheetApp.getActive();ScriptApp.newTrigger(e).forSpreadsheet(t).onEdit().create()}function checkTriggerExists(e,t){for(var a=ScriptApp.getProjectTriggers(),r={},n=0;n<a.length;n++)if((r=a[n]).getHandlerFunction()==e&&r.getTriggerSource()==t)return!0;return!1}function test_uniqueArray(){var e=["boo","1",1,"true",!0];Logger.log(getUniqueLine(e)),e=[[5],[3,5],[5],[3,5],[5],[3,5]],Logger.log(getUniqueLine(e))}function getUniqueLine(e){var t={};return e.forEach(function(e){t[e+"::"+typeof e]=e}),Object.keys(t).map(function(e){return t[e]})}function test_getUniquePairs(){Logger.log(getUniquePairs([["one","two","fri","fri"],[1,2,3,3]]))}function getUniquePairs(e){var t=transpose(e);return getUniqueLine(t)}var C_USER_SETTINGS_SHEET="_Dv_Ini_",C_SORT_DV_DATA=!1,C_DELIMETERS=[">","=>","->",">>","|"," |","--\x3e",">>>"],C_USER_PROPERTY_DV="Dv_my_property_1984",C_SAMPLE_DATA_SHEET="Data Sample",C_SAMPLE_WORK_SHEET="Work Sample",C_USER_SETTINGS_KEYS=["Work Sheet","Data Sheet","Source File Id","Header Row","Columns"],C_USER_SETTINGS_HIDDEN=[0,0,1,1,1],C_USER_SETTINGS_NOTES=["Name of sheet to make dependent drop-downs","Name of sheet with data relating to the Work Sheet","[Optional] Id of file with data. Please omit if Data Sheet is in current file","[Optional] Header row of Work Sheet. Data Validations come after this row. Leave this field blank to make trigger automatically find matching headers from Data Sheet in Work Sheet.","[Optional] Column numbers for Data Validation, [ "+(C_NUMBER_DELIMETER=",")+" ] separated list. Data Validations come after this row. Leave this field blank to make trigger automatically find matching headers from Data Sheet in Work Sheet."],C_CUSTOM_ERROR_KEY="Oh, Boy!";function test_getDvSettings(){Logger.log(getDvSettings())}function getDvSettings(){var e=SpreadsheetApp.getActiveSpreadsheet(),t=this;t.values={},t.values.file=e;var a=getSettingsSheet(C_USER_SETTINGS_SHEET,C_USER_SETTINGS_KEYS,C_USER_SETTINGS_HIDDEN,C_USER_SETTINGS_NOTES),r=a.getDataRange().getValues();1==r.length&&(r=getSampleSheetsIni(C_SAMPLE_WORK_SHEET,C_SAMPLE_DATA_SHEET,a));for(var n=[],o=[],i=[],s=[],g=[],l=[],u=1,S=r.length;u<S;u++)l=r[u],n.push(l[0]),o.push(l[1]),i.push(l[2]),s.push(l[3]),g.push(l[4]);t.values.namesWork=n,t.values.namesData=o,t.values.idsSource=""!==i&&i,t.values.rowsHeader=""!==s&&s,t.values.columns=""!==g&&g;var h=[];for(u=0,S=g.length;u<S;u++)if(g){h=g[u].split(C_NUMBER_DELIMETER);for(var c=0;c<h.length;c++)h[c]=+h[c];g[u]=h}return t.values}function test_getSettingsSheet(){Logger.log(getSettingsSheet(C_USER_SETTINGS_SHEET,C_USER_SETTINGS_KEYS,C_USER_SETTINGS_HIDDEN,C_USER_SETTINGS_NOTES))}function getSampleSheetsIni(e,t,a){var r=getSampleDataSheet(t);getSampleWorkSheet(e);a.getRange(2,1).setValue(e),a.getRange(2,2).setValue(t),a.getRange(2,3).setValue(a.getParent().getId());var n=getConnection(e,r.getDataRange().getValues()[0]);return a.getRange(2,4).setValue(n.r),a.getRange(2,5).setValue(n.c.join(",")),a.getDataRange().getValues()}function getSettingsSheet(e,t,a,r){var n=SpreadsheetApp.getActive(),o=n.getSheetByName(e);if(null!=o)return o;n.insertSheet(e),o=n.getSheetByName(e);for(var i=0,s=a.length;i<s;i++)1==a[i]&&o.hideColumns(i+1);var g=s+1,l=o.getMaxColumns()-s-1;o.deleteColumns(g,l),o.appendRow(t),o.setFrozenRows(1),o.getRange(1,1,1,s).setFontWeight("bold").setNotes([r]),l=o.getMaxRows()-30;return o.deleteRows(31,l),o}function test_getSampleWorkSheet(){Logger.log(getSampleWorkSheet(C_SAMPLE_WORK_SHEET))}function getSampleDataSheet(e){var t=SpreadsheetApp.getActive(),a=t.getSheetByName(e);if(null!=a)return a;t.insertSheet(e),a=t.getSheetByName(e);var r=[["Planet","Mainland","Country","City"],["Earth","Europe","Britain","London"],["Earth","Europe","Britain","Manchester"],["Earth","Europe","Britain","Liverpool"],["Earth","Europe","France","Paris"],["Earth","Europe","France","Lion"],["Earth","Europe","Italy","Rome"],["Earth","Europe","Italy","Milan"],["Earth","Europe","Greece","Athenes"],["Earth","Asia","China","Pekin"],["Earth","Africa","Algeria","Algiers"],["Earth","America","USA","Dallas"],["Earth","America","USA","New York"],["Earth","America","USA","San Francisco"],["Earth","America","USA","Chicago"],["Tatooine","Yulab","Putesh","ASU"],["Tatooine","Yulab","Putesh","Niatirb"],["Tatooine","Yulab","Zalip","Duantan"],["Tatooine","Asia","Solo","Lion"],["Tatooine","Asia","Solo","To"]],n=r[0].length,o=r.length;a.getRange(1,1,o,n).setValues(r),a.setFrozenRows(1),a.getRange(1,1,1,n).setFontWeight("bold");var i=n+1,s=a.getMaxColumns()-n-1;a.deleteColumns(i,s),s=a.getMaxRows()-o-30;var g=o+31;return a.deleteRows(g,s),a}function getSampleWorkSheet(e){var t=SpreadsheetApp.getActive(),a=t.getSheetByName(e);if(null!=a)return a;t.insertSheet(e),a=t.getSheetByName(e);var r=[["Dependent Drop-Down Lists","","","",""],["Please try entering the following:","","","",""],["Planet → Mainland → Country → City","","","",""],["","","","",""],["Planet","Mainland","Country","Code","City"]],n=r[0].length,o=r.length;a.getRange(1,1,o,n).setValues(r),a.setFrozenRows(5),a.getRange(5,1,1,n).setFontWeight("bold"),a.getRange(1,1).setFontSize(14).setFontWeight("bold"),howMany=a.getMaxRows()-o-300;var i=o+301;return a.deleteRows(i,howMany),a}function getDvObject(e){var t=getPropertyAsArray(C_USER_PROPERTY_DV),a=Object.keys(t.w),r=e.nameSheet;if(-1===a.indexOf(r))return null;var n={},o=t.w[r],i=getListDvConnections(e,o);if(null===i)return null;(n={}).connections=i;var s=getDvDataSets(i,t.d);return n.dataSets=s,n}function getListDvConnections(e,t){for(var a=Object.keys(t),r=[],n="",o=0,i=a.length;o<i;o++){var s=t[n=a[o]],g=getDvConnection(s,e);null!==g&&(g.name=n,r.push(g))}return 0===r.length?null:r}function getDvConnection(e,t){if(t.getLastRow<=e.r)return null;var a;return 0===(a=arrayIntersection(e.c,t.columns).sort(function(e,t){return e-t})).length?null:(Logger.log(1e3),e.columnsChanged=a,e)}function getDvDataSets(e,t){for(var a={},r="",n=0,o=e.length;n<o;n++)a[r=e[n].name]=t[r];return a}function makeFirstDvRules(e){for(var t=e.w,a=Object.keys(t),r="",n={},o={},i={},s=[],g="",l=0,u=a.length;l<u;l++){o=t[r=a[l]];for(var S=0,h=(s=Object.keys(o)).length;S<h;S++){i=o[g=s[S]],n=e.d[g];var c=getFirstDvRuleRange(r,i),p=getDvRuleList(n,"");makeDvFromList(c,p,"")}}}function getFirstDvRuleRange(e,t){var a=SpreadsheetApp.getActive().getSheetByName(e),r=a.getMaxRows(),n=t.r+1,o=t.c[0],i=r-n+1;return a.getRange(n,o,i)}function test_getDvRuleList(){var e={n:"data",d:{Earth:{Europe:{Britain:["London","Manchester","Liverpool"],France:["Paris","Lion"],Italy:["Rome","Milan"],Greece:["Athenes"]},Asia:{China:["Pekin"]},Africa:{Algeria:["Algiers"]},America:{USA:["Dallas","New York","San Francisco","Chicago"]}},Tatooine:{Yulab:{Putesh:["ASU","Niatirb"],Zalip:["Duantan"]},Asia:{Solo:["Lion","To"]}}},h:["Planet","Mainland","Country","City"],l:4,s:">",f:0},t="Earth>Europe>France>Paris";Logger.log(getDvRuleList(e,t));t="Earth>Europe>France";Logger.log(getDvRuleList(e,t));t="Earth";Logger.log(getDvRuleList(e,t));t="Earth>Europe";Logger.log(getDvRuleList(e,t));t="";Logger.log(getDvRuleList(e,t));t="Booooooo";Logger.log(getDvRuleList(e,t));t="Earth>Boooooooooo";Logger.log(getDvRuleList(e,t));t="Earth>Europe>France>Paris>Rue";Logger.log(getDvRuleList(e,t));t=">>>>>>>>>>>>>>>>>>>>>>>>";Logger.log(getDvRuleList(e,t))}function getDvRuleList(e,t){if(""===t)return Object.keys(e.d);var a=t.split(e.s),r=a.length;if(r>e.l)return null;r===e.l&&r--;var n=e.d;if(void 0===n)return null;for(var o=0;o<r;o++)n=n[a[o]];return Array.isArray(n)?n:void 0===n?null:Object.keys(n)}function makeSmartDataValidation(e,t,a){for(var r=e.connections,n={},o={},i=0,s=r.length;i<s;i++)n=r[i],o=e.dataSets[n.name],makeSmartDVConnection(t,n,o);makeTasks(a)}function makeSmartDVConnection(e,t,a){for(var r=t.columnsChanged,n=r[r.length-1],o=e.rowNums,i={},s=0,g=o.length;s<g;s++)o[s]>t.r&&(i=getObjDvChange(n,o[s],t.c,r,e),makeSmartDVRow(i,a,e))}function getObjDvChange(e,t,a,r,n){var o={};o.numRow=t,o.numCol=e,o.addR1C1="R"+t+"C"+e,o.columns=a,o.columnsChanged=r;var i=n[o.addR1C1];return o.value=i.value,o.range=i.range,o}function makeSmartDVRow(e,t,a){var r;r=e.columns.indexOf(e.numCol)===t.l-1;var n=!1;""===e.value&&(n=!0);var o=a[e.addR1C1],i=o.range.getDataValidation();if(i)var s=i.getHelpText(),g=s+t.s+e.value;else n=!0;s||(g=o.value);var l=getDvRuleList(t,g);if(l||(n=!0),n)return dealWithBadValue(e,a,t),0;if(r)return 0;var u=e.columns,S=u.indexOf(e.numCol)+1;if(S===t.l)return 0;var h=u[S];addTask("makeDv",g,e.numRow,[h],l),1===l.length&&dealWithOnlyValue(l[0],g,t.s,e.numRow,h,u,t)}function dealWithOnlyValue(e,t,a,r,n,o,i){var s,g=o.indexOf(n);s=g===i.l-1;var l="setVal";if(addTask(l,e,r,[n]),!s){l="makeDv";var u=getDvRuleList(i,t=t+a+e);n=o[g+1],addTask(l,t,r,[n],u),1===u.length&&(e=u[0],dealWithOnlyValue(e,t,a,r,n,o,i))}}function dealWithBadValue(e,t,a){var r=e.columns.filter(function(t){return t>e.numCol});r&&r.length&&addTask("clearDv","",e.numRow,r);var n=e.columns.filter(function(t){return t>=e.numCol});n&&n.length&&addTask("clearContents","",e.numRow,n);var o=e.columns.indexOf(e.numCol),i=e.columnsChanged.indexOf(e.numCol);if(0===o){var s=getDvRuleList(a,"");return addTask("makeDv","",e.numRow,[e.numCol],s),0}if(0===i)return 0;var g=e.columnsChanged[i-1],l=getObjDvChange(g,e.numRow,e.columns,e.columnsChanged,t);makeSmartDVRow(l,a,t)}function test_cellsProps(){var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(1,1);Logger.log(e.getDataSourceUrl()),Logger.log(e.getGridId());var t=e.getDataValidation();if(null!=t){var a=t.getCriteriaType(),r=t.getCriteriaValues();Logger.log("The data-validation rule is %s %s",a,r)}else Logger.log("The cell does not have a data-validation rule.")}function test_makeTestDataAndProps(){var e=[];e.push(["Name","Sum"]);for(var t=[],a=0;a<1e3;a++)(t=[]).push("Name "+a),t.push(parseInt(1e4*Math.random())),e.push(t);var r=JSON.stringify(e),n="test_data",o=setUserProperty(r,n);Logger.log(o);var i=new Date;if("Set Properties -- ok!"==o)getPropertyAsArray(n);Logger.log(new Date-i)}function test_dataValidationCapacity(){var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tooMuchData"),t=e.getDataRange().getValues();Logger.log(t);var a=e.getRange("B1"),r=SpreadsheetApp.newDataValidation().requireValueInList(t,!1).build();a.setDataValidation(r)}function test_dataValidationRangeFromOtherSheet(){var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tooMuchData").getDataRange(),t=SpreadsheetApp.openById("1_CKplszLjCnuCj8B5r-xFww5Ys-SjL-qD6BEl7cr9CA").getSheetByName("Каталог").getRange("L3"),a=SpreadsheetApp.newDataValidation().requireValueInRange(e,!1).build();t.setDataValidation(a)}function test_trigger(){var e=ScriptApp.getAuthorizationInfo(ScriptApp.AuthMode.FULL),t=e.getAuthorizationStatus(),a=e.getAuthorizationUrl();Logger.log(t),Logger.log(a);for(var r=[],n=DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);n.hasNext();)file=n.next(),r.push([file.getName(),file.getUrl(),file.getDateCreated(),file.getLastUpdated()]);Logger.log(r)}function test_getTournamentMatches(){var e=["Brazil","USA","Argentina","Ukraine"];Logger.log(getTournamentMatches(e)),e.push("Vietnam"),Logger.log(getTournament_(e))}function test_getTournament(){Logger.log(getTournament([["Brazil"],["USA"],["Argentina"],["Ukraine"]]))}function getTournament(e){var t=[];return e.forEach(function(e){e.forEach(function(e){t.push(e)})}),getTournament_(t)}function getTournament_(e){for(var t=0,a=e.length,r=[],n=[],o=0;o<a;o++)for(var i=++t;i<a;i++)(n=[]).push(e[o]),n.push(e[i]),r.push(n);return r}function setDv(){var e=getDvSettings(),t=new SetsDV(e);setUserProperty(JSON.stringify(t),C_USER_PROPERTY_DV),makeFirstDvRules(t),Logger.log(t),setTriggerOnEdit("dvOnEdit")}function SetsDV(e,t){namesWork=e.namesWork,namesData=e.namesData,idsFileData=e.idsSource,rowsHeader=e.rowsHeader,columnsList=e.columns;var a=namesWork.length;idsFileData||(idsFileData=getFilledArray(!1,a)),rowsHeader||(rowsHeader=getFilledArray(!1,a)),columnsList||(columnsList=getFilledArray(!1,a));for(var r={},n={},o={},i={},s="",g="",l="",u="",S=0;S<a;S++)(s=namesWork[S])in r||(r[s]={}),o=r[s],(u=idsFileData[S]||"0")==SpreadsheetApp.getActive().getId()&&(u="0"),(g=u+(l=namesData[S])+"-"+columnsList[S].join(";"))in n||("0"==u?SpreadsheetApp.getActive():SpreadsheetApp.openById(u),n[g]=new SheetData(l,idsFileData[S],rowsHeader[S],columnsList[S])),i=n[g],g in o||(o[g]=getConnection(s,i.h,rowsHeader[S],columnsList[S]));this.w=r,this.d=n}function getFilledArray(e,t){for(var a=[],r=0;r<t;r++)a.push(e);return a}function test_getConnection(){var e={};e["0data"]=getConnection("Work Sample",["Planet","Mainland","Country","City"]),Logger.log(JSON.stringify(e))}function getConnection(e,t,a,r){var n={};if(a&&r)return n.r=a,n.c=r,n;for(var o=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e).getDataRange().getValues(),i=[],s=t.length,g=0,l=[],u=0,S=0,h=o.length;S<h;S++){i=o[S],l=[];for(var c=0;c<s&&0!=(g=i.indexOf(t[c])+1);c++)if(l.push(g),c==s-1)return u=S+1,n.r=u,n.c=l,n}return-1}function test_sheetData(){var e=new SheetData("Data Sample","1W2M_SardPuzP...0Z2fhL18");Logger.log(JSON.stringify(e))}function SheetData(e,t){var a=this;a.n=e,t=t||SpreadsheetApp.getActiveSpreadsheet().getId();var r=SpreadsheetApp.openById(t).getSheetByName(a.n);if(null===r)throw C_CUSTOM_ERROR_KEY+": No sheet called '"+a.n+"'. Please review your settings. ... You may find this in a script by key: '"+C_CUSTOM_ERROR_KEY+". Error #1'";var n=r.getDataRange(),o=getSmartSort(n.getDisplayValues());return C_SORT_DV_DATA&&n.setValues(o),a.d=getDvData(o),a.h=o[0],a.l=o[0].length,a.s=getDelimeter(o,C_DELIMETERS),t==SpreadsheetApp.getActiveSpreadsheet().getId()&&(t=0),a.f=t,0}function test_getDelimeter(){var e=C_DELIMETERS,t=[["One","Two","Fri"]];Logger.log(getDelimeter(t,C_DELIMETERS)),t[0].push(e[0]),Logger.log(getDelimeter(t,C_DELIMETERS)),t[0].push(e.join("")),Logger.log(getDelimeter(t,C_DELIMETERS)),t[0].push("(1)"),t[0].push("(2)"),Logger.log(getDelimeter(t,C_DELIMETERS))}function getDelimeter(e,t,a){var r=JSON.parse(JSON.stringify(t)),n=[],o="",i="";a>=0&&(r.push("("+a+")"),a++);for(var s=0,g=e.length;s<g;s++)for(var l=0,u=(n=e[s]).length;l<u;l++){o=n[l];for(var S=r.length-1;S>=0;S--)i=r[S],o.indexOf(i)>-1&&r.splice(S,1)}return r&&r.length?r[0]:(a||(a=1),getDelimeter(e,r,a))}function test_getDvData(){var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Sample").getDataRange().getValues(),t=getDvData(e);Logger.log(JSON.stringify(t))}function getDvData(e){for(var t,a={},r={},n={},o="",i=e[0].length,s="",g=1,l=e.length;g<l;g++){if(o=e[g][0],s=e[g][i-1],i>2){n=r=a[o]||{};for(var u=1;u<i-2;u++)r[t=e[g][u]]=r[t]||{},r=r[t];r[t=e[g][i-2]]=r[t]||[],(r=r[t]).push(s)}else(n=a[o]||[]).push(s);a[o]=n}return a}function transpose(e){var t=e.length?e.length:0,a=e[0]instanceof Array?e[0].length:0;if(0===a||0===t)return[];var r,n,o=[];for(r=0;r<a;r++)for(o[r]=[],n=0;n<t;n++)o[r][n]=e[n][r];return o}function test_intersection(){Logger.log(arrayIntersection([1,2,3,"a"],[1,"a",2],["a",1])),Logger.log(arrayIntersection([1],[1,6,7,8]))}arrayContains=Array.prototype.indexOf?function(e,t){return e.indexOf(t)>-1}:function(e,t){for(var a=e.length;a--;)if(e[a]===t)return!0;return!1};function arrayIntersection(){var e,t,a,r,n,o,i=[],s=Array.prototype.slice.call(arguments);if(a=s.pop())for(n=a.length,t=s.length;n--;){for(e=a[n],o=!1,r=t;!o&&r--;)arrayContains(s[r],e)||(o=!0);o||i.push(e)}return i}function test_getFilled2DArray(){Logger.log(getFilled2DArray("max",3,4))}function getFilled2DArray(e,t,a){for(var r=[],n=[],o=0;o<a;o++){n=[];for(var i=0;i<t;i++)n.push(e);r.push(n)}return r}function makeDvFromList(e,t,a){var r=SpreadsheetApp.newDataValidation();r.requireValueInList(t,!0),""!==a&&r.setHelpText(a),r.setAllowInvalid(!1),e.setDataValidation(r)}function getEditObject(e){return{user:Session.getActiveUser().getEmail(),source:SpreadsheetApp.getActiveSpreadsheet(),range:e,value:e.getValue(),authMode:"LIMITED"}}function ObjectOnEdit(e){var t=this,a=e.range,r=a.getWidth(),n=a.getHeight(),o=!0;(n>1||r>1)&&(o=!1);var i=a.getRow(),s=a.getColumn(),g=[];if(o)g.push(s);else for(var l=s;l<=s+r-1;l++)g.push(parseInt(l));t.range=a,t.numCols=r,t.numRows=n,t.numRow=i,t.numCol=s,t.boolCell=o,t.columns=g,t.getLastRow=a.getLastRow(),t.getLastCol=a.getLastColumn(),this.getValues=function(){if(!t.boolCell)return a.getValues();var r=e.value;return null!=r&&"object"!=typeof r||(r=a.getValue()),[[r]]};var u=e.oldValue;null==u&&(u=""),"object"==typeof u&&(u=""),t.oldValue=u;var S=a.getSheet(),h=S.getParent(),c=h.getId(),p=S.getName(),f=S.getSheetId();t.sheet=S,t.file=h,t.idFile=c,t.nameSheet=p,t.idSheet=f}function checkRowDv(e,t){return e<=t?0:1}function checkColDv(e,t,a){if("number"==typeof t)return t==e?1:0;var r=[];return Array.isArray(t)&&(r=t),"string"==typeof t&&(r=(r=t.split(a)).map(Number)),-1!=r.indexOf(e)?1:0}NUM_MAX_BITE_SIZEOFPROPERTY=9e3,NUM_MAX_BITE_SIZEOFPROPERTIES=25e3,STR_PREFIX_NUMOFPARTS="NUM_OF_PARTS";function setPropertyFromNamedRange(e,t,a){return a=a||t,setUserProperty((e=e||SpreadsheetApp.getActiveSpreadsheet()).getRangeByName(t).getValue(),a)}function setUserProperty(e,t){var a=PropertiesService.getUserProperties(),r=byteCount(e);if(r>NUM_MAX_BITE_SIZEOFPROPERTIES)return"Set Properties -- error. String size is more than "+NUM_MAX_BITE_SIZEOFPROPERTIES+" bites.";var n=Math.ceil(r/NUM_MAX_BITE_SIZEOFPROPERTY),o=chunkStringParts(e,n);a.setProperty(t+STR_PREFIX_NUMOFPARTS,n);for(var i=0;i<n;i++)e=o[i],a.setProperty(t+i,e);return"Set Properties -- ok!"}function getUserProperty(e){for(var t=PropertiesService.getUserProperties(),a="",r=t.getProperty(e+STR_PREFIX_NUMOFPARTS),n=0;n<r;n++)a+=t.getProperty(e+n);return a}function getPropertyAsArray(e){for(var t=PropertiesService.getUserProperties(),a="",r=t.getProperty(e+STR_PREFIX_NUMOFPARTS),n=0;n<r;n++)a+=t.getProperty(e+n);return JSON.parse(a)}function test_getRangeValuesMapR1C1(){var e=SpreadsheetApp.getActive().getActiveSheet().getRange("B2:C12");Logger.log(getRangeMapR1C1(e))}function getRangeMapR1C1(e,t){t=t||e.getValues();for(var a=e.getColumn(),r=e.getRow(),n=e.getWidth()+a-1,o=e.getHeight()+r-1,i={rowNums:[]},s={},g=r;g<=o;g++){i.rowNums.push(g);for(var l=a;l<=n;l++)(s={}).value=t[g-r][l-a],s.range=e.offset(g-r,l-a,1,1),i["R"+g+"C"+l]=s}return i}function test_getSmartSort(){var e=getSmartSort([["Earth","Europe","Britain","London"],["Earth","Europe","Britain","Manchester"],["Earth","Europe","Britain","Liverpool"],["Earth","Europe","France","Paris"],["Earth","Europe","France","Lion"],["Earth","Europe","Italy","Rome"],["Earth","Europe","Italy","Milan"],["Earth","Europe","Greece","Athenes"],["Earth","Asia","China","Pekin"],["Earth","Africa","Algeria","Algiers"],["Earth","America","USA","Dallas"],["Earth","America","USA","New York"],["Earth","America","USA","Chicago"],["Tatooine","Yulab","Putesh","ASU"],["Tatooine","Yulab","Putesh","Niatirb"],["Tatooine","Yulab","Zalip","Duantan"],["Tatooine","Asia","Solo","Lion"],["Tatooine","Asia","Solo","To"],["Earth","America","USA","San Francisco"],["Tatooine","Yulab","Koko","Traiwau"],["Venus","Yoo","Van","Derzar"],["Tatooine","Chendoo","org","Eccel"]]);Logger.log(e)}function getSmartSort(e){var t=Object.create(null);return e.map(function(e,a){var r=t;return e.map(function(e){return r[e]=r[e]||{_:a},(r=r[e])._})}).sort(function(e,t){var a;return e.some(function(e,r){return a=e-t[r]}),a}).map(function(t){return e[t[t.length-1]]})}function chunkString(e,t){return e.match(new RegExp(".{1,"+t+"}","g"))}function TESTchunkString(){Logger.log(chunkString("12312312",2))}function chunkStringParts(e,t){var a=e.length;return chunkString(e,Math.ceil(a/t))}function TESTchunkStringParts(){Logger.log(chunkStringParts("12312312",3))}function byteCount(e){return encodeURI(e).split(/%..|./).length-1}function replaceAll(e,t,a){return e.replace(new RegExp(t,"g"),a)}OBJ_TASKS_DV={},C_TASKS_FUNCTIONS={clearContents:makeTasksClearContents,clearDv:makeTasksClearDataValidation,makeDv:makeTasksDataValidation,setVal:makeTasksSetValue};function makeTasksClearContents(e){e.clear({contentsOnly:!0})}function makeTasksClearDataValidation(e){e.clear({validationsOnly:!0})}function makeTasksDataValidation(e,t,a){makeDvFromList(e,t,a)}function makeTasksSetValue(e,t,a){var r=e.getWidth(),n=e.getHeight();1===r&&1===n?e.setValue(a):e.setValues(getFilled2DArray(a,r,n))}function test_addTask(){var e=[4,5,7];addTask("clearContents","",3,e),Logger.log(JSON.stringify(OBJ_TASKS_DV)),addTask("makeBaby","Lu-Max",3,e,["ooo","oah","aaah"]),Logger.log(JSON.stringify(OBJ_TASKS_DV))}function addTask(e,t,a,r,n){OBJ_TASKS_DV[e]||(OBJ_TASKS_DV[e]={}),OBJ_TASKS_DV[e][t]||(OBJ_TASKS_DV[e][t]={}),OBJ_TASKS_DV[e][t].numRows||(OBJ_TASKS_DV[e][t].numRows=[]),OBJ_TASKS_DV[e][t].numColumnss||(OBJ_TASKS_DV[e][t].numColumnss=[]),OBJ_TASKS_DV[e][t].numRows.push(a),OBJ_TASKS_DV[e][t].numColumnss.push(r),n&&(OBJ_TASKS_DV[e][t].data||(OBJ_TASKS_DV[e][t].data=n))}function makeTasks(e){for(var t,a=getTaskMaker(OBJ_TASKS_DV),r=Object.keys(a),n="",o={},i=[],s={},g="",l=0,u=r.length;l<u;l++){n=r[l],t=C_TASKS_FUNCTIONS[n],o=a[n];for(var S=0,h=(i=Object.keys(o)).length;S<h;S++)makeTasksFunctions(t,e,(s=o[g=i[S]]).rangesA1,s.data,g)}return OBJ_TASKS_DV={},0}function makeTasksFunctions(e,t,a,r,n){for(var o={},i=0,s=a.length;i<s;i++)o=a[i],e(t.getRange(o.numRow,o.numCol,o.numRows,o.numCols),r,n)}function getTaskMaker(e){for(var t={},a=Object.keys(e),r="",n={},o="",i={},s={},g=[],l=0,u=a.length;l<u;l++){n=e[r=a[l]],i={};for(var S=0,h=(g=Object.keys(n)).length;S<h;S++)s=getSubTask(n[o=g[S]]),i[o]=s;t[r]=i}return t}function getSubTask(e){var t={};e.data&&(t.data=e.data);var a=getSubTaskA1Ranges(getSubTaskRows({numRows:e.numRows,numColumnss:e.numColumnss}));return t.rangesA1=a,t}function getSubTaskRows(e){for(var t={},a={},r={},n=e.numRows,o=0,i=e.numColumnss,s=[],g=[],l=0,u=n.length;l<u;l++)s=(r=a[o=n[l]]||{}).listCols||[],g=i[l],s=getUniqueLine(s=s.concat(g)).sort(function(e,t){return e-t}),r.listCols=s,a[o]=r;return t.rows=a,t}function test_getSubTaskA1Ranges(){Logger.log(getSubTaskA1Ranges({rows:{11:{listCols:[2]},12:{listCols:[2]},13:{listCols:[2]},6:{listCols:[2]},7:{listCols:[2]},8:{listCols:[2]},9:{listCols:[2]},10:{listCols:[2]}}}))}function getSubTaskA1Ranges(e){var t,a={},r=null,n="",o=[],i=[],s=-1,g=0,l=0;(t=(t=Object.keys(e.rows)).map(function(e){return parseInt(e)})).sort(function(e,t){return e-t});for(var u=0,S=t.length;u<S;u++){l=t[u];for(var h=0,c=(i=e.rows[l].listCols).length;h<c;h++)(g=i[h])-s!=1?(r&&(a[n]=r),n="R"+l+"C"+g,o.push(n),(r={}).numRow=l,r.numCol=g,r.numCols=1):r.numCols=r.numCols+1,a[n]=r,s=g;r=null}var p={},f={},d="";for(u=0,S=o.length;u<S;u++)(f=p[d="C"+(r=a[n=o[u]]).numCol+"W"+r.numCols]||{}).numCol=r.numCol,f.numCols=r.numCols,f.rows=f.rows||[],f.rows.push(r.numRow),p[d]=f;var m=[],v=null,E=Object.keys(p),R=[],_=-1;for(l=0,u=0,S=E.length;u<S;u++){for((R=(f=p[d=E[u]]).rows).sort(function(e,t){return e-t}),_=-1,v=null,h=0,c=R.length;h<c;h++)(l=R[h])-_!=1?(v&&m.push(v),(v=v||{}).numRow=l,v.numCol=f.numCol,v.numCols=f.numCols,v.numRows=v.numRows||1):v.numRows=v.numRows+1,_=l;m.push(v)}return m}C_SOURCE_TRIGGER="SPREADSHEETS";function setTriggerOnEdit(e){if(checkTriggerExists(e,C_SOURCE_TRIGGER))return-1;var t=SpreadsheetApp.getActive();ScriptApp.newTrigger(e).forSpreadsheet(t).onEdit().create()}function checkTriggerExists(e,t){for(var a=ScriptApp.getProjectTriggers(),r={},n=0;n<a.length;n++)if((r=a[n]).getHandlerFunction()==e&&r.getTriggerSource()==t)return!0;return!1}function test_uniqueArray(){var e=["boo","1",1,"true",!0];Logger.log(getUniqueLine(e)),e=[[5],[3,5],[5],[3,5],[5],[3,5]],Logger.log(getUniqueLine(e))}function getUniqueLine(e){var t={};return e.forEach(function(e){t[e+"::"+typeof e]=e}),Object.keys(t).map(function(e){return t[e]})}function test_getUniquePairs(){Logger.log(getUniquePairs([["one","two","fri","fri"],[1,2,3,3]]))}function getUniquePairs(e){return getUniqueLine(transpose(e))}var C_NUMBER_DELIMETER;C_USER_SETTINGS_SHEET="_Dv_Ini_",C_SORT_DV_DATA=!1,C_DELIMETERS=[">","=>","->",">>","|"," |","--\x3e",">>>"],C_USER_PROPERTY_DV="Dv_my_property_1984",C_SAMPLE_DATA_SHEET="Data Sample",C_SAMPLE_WORK_SHEET="Work Sample",C_USER_SETTINGS_KEYS=["Work Sheet","Data Sheet","Source File Id","Header Row","Columns"],C_USER_SETTINGS_HIDDEN=[0,0,1,1,1],C_USER_SETTINGS_NOTES=["Name of sheet to make dependent drop-downs","Name of sheet with data relating to the Work Sheet","[Optional] Id of file with data. Please omit if Data Sheet is in current file","[Optional] Header row of Work Sheet. Data Validations come after this row. Leave this field blank to make trigger automatically find matching headers from Data Sheet in Work Sheet.","[Optional] Column numbers for Data Validation, [ "+(C_NUMBER_DELIMETER=",")+" ] separated list. Data Validations come after this row. Leave this field blank to make trigger automatically find matching headers from Data Sheet in Work Sheet."],C_CUSTOM_ERROR_KEY="Oh, Boy!";function test_getDvSettings(){Logger.log(getDvSettings())}function getDvSettings(){var e=SpreadsheetApp.getActiveSpreadsheet(),t=this;t.values={},t.values.file=e;var a=getSettingsSheet(C_USER_SETTINGS_SHEET,C_USER_SETTINGS_KEYS,C_USER_SETTINGS_HIDDEN,C_USER_SETTINGS_NOTES),r=a.getDataRange().getValues();1==r.length&&(r=getSampleSheetsIni(C_SAMPLE_WORK_SHEET,C_SAMPLE_DATA_SHEET,a));for(var n=[],o=[],i=[],s=[],g=[],l=[],u=1,S=r.length;u<S;u++)l=r[u],n.push(l[0]),o.push(l[1]),i.push(l[2]),s.push(l[3]),g.push(l[4]);t.values.namesWork=n,t.values.namesData=o,t.values.idsSource=""!==i&&i,t.values.rowsHeader=""!==s&&s,t.values.columns=""!==g&&g;var h=[];for(u=0,S=g.length;u<S;u++)if(g){h=g[u].split(C_NUMBER_DELIMETER);for(var c=0;c<h.length;c++)h[c]=+h[c];g[u]=h}return t.values}function test_getSettingsSheet(){Logger.log(getSettingsSheet(C_USER_SETTINGS_SHEET,C_USER_SETTINGS_KEYS,C_USER_SETTINGS_HIDDEN,C_USER_SETTINGS_NOTES))}function getSampleSheetsIni(e,t,a){var r=getSampleDataSheet(t);getSampleWorkSheet(e),a.getRange(2,1).setValue(e),a.getRange(2,2).setValue(t),a.getRange(2,3).setValue(a.getParent().getId());var n=getConnection(e,r.getDataRange().getValues()[0]);return a.getRange(2,4).setValue(n.r),a.getRange(2,5).setValue(n.c.join(",")),a.getDataRange().getValues()}function getSettingsSheet(e,t,a,r){var n=SpreadsheetApp.getActive(),o=n.getSheetByName(e);if(null!=o)return o;n.insertSheet(e),o=n.getSheetByName(e);for(var i=0,s=a.length;i<s;i++)1==a[i]&&o.hideColumns(i+1);var g=s+1,l=o.getMaxColumns()-s-1;return o.deleteColumns(g,l),o.appendRow(t),o.setFrozenRows(1),o.getRange(1,1,1,s).setFontWeight("bold").setNotes([r]),l=o.getMaxRows()-30,o.deleteRows(31,l),o}function test_getSampleWorkSheet(){Logger.log(getSampleWorkSheet(C_SAMPLE_WORK_SHEET))}function getSampleDataSheet(e){var t=SpreadsheetApp.getActive(),a=t.getSheetByName(e);if(null!=a)return a;t.insertSheet(e),a=t.getSheetByName(e);var r=[["Planet","Mainland","Country","City"],["Earth","Europe","Britain","London"],["Earth","Europe","Britain","Manchester"],["Earth","Europe","Britain","Liverpool"],["Earth","Europe","France","Paris"],["Earth","Europe","France","Lion"],["Earth","Europe","Italy","Rome"],["Earth","Europe","Italy","Milan"],["Earth","Europe","Greece","Athenes"],["Earth","Asia","China","Pekin"],["Earth","Africa","Algeria","Algiers"],["Earth","America","USA","Dallas"],["Earth","America","USA","New York"],["Earth","America","USA","San Francisco"],["Earth","America","USA","Chicago"],["Tatooine","Yulab","Putesh","ASU"],["Tatooine","Yulab","Putesh","Niatirb"],["Tatooine","Yulab","Zalip","Duantan"],["Tatooine","Asia","Solo","Lion"],["Tatooine","Asia","Solo","To"]],n=r[0].length,o=r.length;a.getRange(1,1,o,n).setValues(r),a.setFrozenRows(1),a.getRange(1,1,1,n).setFontWeight("bold");var i=n+1,s=a.getMaxColumns()-n-1;a.deleteColumns(i,s),s=a.getMaxRows()-o-30;var g=o+31;return a.deleteRows(g,s),a}function getSampleWorkSheet(e){var t=SpreadsheetApp.getActive(),a=t.getSheetByName(e);if(null!=a)return a;t.insertSheet(e),a=t.getSheetByName(e);var r=[["Dependent Drop-Down Lists","","","",""],["Please try entering the following:","","","",""],["Planet → Mainland → Country → City","","","",""],["","","","",""],["Planet","Mainland","Country","Code","City"]],n=r[0].length,o=r.length;a.getRange(1,1,o,n).setValues(r),a.setFrozenRows(5),a.getRange(5,1,1,n).setFontWeight("bold"),a.getRange(1,1).setFontSize(14).setFontWeight("bold"),howMany=a.getMaxRows()-o-300;var i=o+301;return a.deleteRows(i,howMany),a}function getDvObject(e){var t=getPropertyAsArray(C_USER_PROPERTY_DV),a=Object.keys(t.w),r=e.nameSheet;if(-1===a.indexOf(r))return null;var n={},o=getListDvConnections(e,t.w[r]);if(null===o)return null;(n={}).connections=o;var i=getDvDataSets(o,t.d);return n.dataSets=i,n}function getListDvConnections(e,t){for(var a=Object.keys(t),r=[],n="",o=0,i=a.length;o<i;o++){var s=getDvConnection(t[n=a[o]],e);null!==s&&(s.name=n,r.push(s))}return 0===r.length?null:r}function getDvConnection(e,t){return t.getLastRow<=e.r?null:0===(a=arrayIntersection(e.c,t.columns).sort(function(e,t){return e-t})).length?null:(Logger.log(1e3),e.columnsChanged=a,e);var a}function getDvDataSets(e,t){for(var a={},r="",n=0,o=e.length;n<o;n++)a[r=e[n].name]=t[r];return a}function makeFirstDvRules(e){for(var t=e.w,a=Object.keys(t),r="",n={},o={},i={},s=[],g="",l=0,u=a.length;l<u;l++){o=t[r=a[l]];for(var S=0,h=(s=Object.keys(o)).length;S<h;S++)i=o[g=s[S]],n=e.d[g],makeDvFromList(getFirstDvRuleRange(r,i),getDvRuleList(n,""),"")}}function getFirstDvRuleRange(e,t){var a=SpreadsheetApp.getActive().getSheetByName(e),r=a.getMaxRows(),n=t.r+1,o=t.c[0],i=r-n+1;return a.getRange(n,o,i)}function test_getDvRuleList(){var e={n:"data",d:{Earth:{Europe:{Britain:["London","Manchester","Liverpool"],France:["Paris","Lion"],Italy:["Rome","Milan"],Greece:["Athenes"]},Asia:{China:["Pekin"]},Africa:{Algeria:["Algiers"]},America:{USA:["Dallas","New York","San Francisco","Chicago"]}},Tatooine:{Yulab:{Putesh:["ASU","Niatirb"],Zalip:["Duantan"]},Asia:{Solo:["Lion","To"]}}},h:["Planet","Mainland","Country","City"],l:4,s:">",f:0},t="Earth>Europe>France>Paris";Logger.log(getDvRuleList(e,t)),t="Earth>Europe>France",Logger.log(getDvRuleList(e,t)),t="Earth",Logger.log(getDvRuleList(e,t)),t="Earth>Europe",Logger.log(getDvRuleList(e,t)),t="",Logger.log(getDvRuleList(e,t)),t="Booooooo",Logger.log(getDvRuleList(e,t)),t="Earth>Boooooooooo",Logger.log(getDvRuleList(e,t)),t="Earth>Europe>France>Paris>Rue",Logger.log(getDvRuleList(e,t)),t=">>>>>>>>>>>>>>>>>>>>>>>>",Logger.log(getDvRuleList(e,t))}function getDvRuleList(e,t){if(""===t)return Object.keys(e.d);var a=t.split(e.s),r=a.length;if(r>e.l)return null;r===e.l&&r--;var n=e.d;if(void 0===n)return null;for(var o=0;o<r;o++)n=n[a[o]];return Array.isArray(n)?n:void 0===n?null:Object.keys(n)}function makeSmartDataValidation(e,t,a){for(var r=e.connections,n={},o=0,i=r.length;o<i;o++)makeSmartDVConnection(t,n=r[o],e.dataSets[n.name]);makeTasks(a)}function makeSmartDVConnection(e,t,a){for(var r=t.columnsChanged,n=r[r.length-1],o=e.rowNums,i=0,s=o.length;i<s;i++)o[i]>t.r&&makeSmartDVRow(getObjDvChange(n,o[i],t.c,r,e),a,e)}function getObjDvChange(e,t,a,r,n){var o={};o.numRow=t,o.numCol=e,o.addR1C1="R"+t+"C"+e,o.columns=a,o.columnsChanged=r;var i=n[o.addR1C1];return o.value=i.value,o.range=i.range,o}function makeSmartDVRow(e,t,a){var r;r=e.columns.indexOf(e.numCol)===t.l-1;var n=!1;""===e.value&&(n=!0);var o=a[e.addR1C1],i=o.range.getDataValidation();if(i)var s=i.getHelpText(),g=s+t.s+e.value;else n=!0;s||(g=o.value);var l=getDvRuleList(t,g);if(l||(n=!0),n)return dealWithBadValue(e,a,t),0;if(r)return 0;var u=e.columns,S=u.indexOf(e.numCol)+1;if(S===t.l)return 0;var h=u[S];addTask("makeDv",g,e.numRow,[h],l),1===l.length&&dealWithOnlyValue(l[0],g,t.s,e.numRow,h,u,t)}function dealWithOnlyValue(e,t,a,r,n,o,i){var s,g=o.indexOf(n);s=g===i.l-1;var l="setVal";if(addTask(l,e,r,[n]),!s){l="makeDv";var u=getDvRuleList(i,t=t+a+e);addTask(l,t,r,[n=o[g+1]],u),1===u.length&&dealWithOnlyValue(e=u[0],t,a,r,n,o,i)}}function dealWithBadValue(e,t,a){var r=e.columns.filter(function(t){return t>e.numCol});r&&r.length&&addTask("clearDv","",e.numRow,r);var n=e.columns.filter(function(t){return t>=e.numCol});n&&n.length&&addTask("clearContents","",e.numRow,n);var o=e.columns.indexOf(e.numCol),i=e.columnsChanged.indexOf(e.numCol);if(0===o){var s=getDvRuleList(a,"");return addTask("makeDv","",e.numRow,[e.numCol],s),0}if(0===i)return 0;makeSmartDVRow(getObjDvChange(e.columnsChanged[i-1],e.numRow,e.columns,e.columnsChanged,t),a,t)}function test_cellsProps(){var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(1,1);Logger.log(e.getDataSourceUrl()),Logger.log(e.getGridId());var t=e.getDataValidation();if(null!=t){var a=t.getCriteriaType(),r=t.getCriteriaValues();Logger.log("The data-validation rule is %s %s",a,r)}else Logger.log("The cell does not have a data-validation rule.")}function test_makeTestDataAndProps(){var e=[];e.push(["Name","Sum"]);for(var t=[],a=0;a<1e3;a++)(t=[]).push("Name "+a),t.push(parseInt(1e4*Math.random())),e.push(t);var r="test_data",n=setUserProperty(JSON.stringify(e),r);Logger.log(n);var o=new Date;"Set Properties -- ok!"==n&&getPropertyAsArray(r),Logger.log(new Date-o)}function test_dataValidationCapacity(){var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tooMuchData"),t=e.getDataRange().getValues();Logger.log(t);var a=e.getRange("B1"),r=SpreadsheetApp.newDataValidation().requireValueInList(t,!1).build();a.setDataValidation(r)}function test_dataValidationRangeFromOtherSheet(){var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tooMuchData").getDataRange(),t=SpreadsheetApp.openById("1_CKplszLjCnuCj8B5r-xFww5Ys-SjL-qD6BEl7cr9CA").getSheetByName("Каталог").getRange("L3"),a=SpreadsheetApp.newDataValidation().requireValueInRange(e,!1).build();t.setDataValidation(a)}function test_trigger(){var e=ScriptApp.getAuthorizationInfo(ScriptApp.AuthMode.FULL),t=e.getAuthorizationStatus(),a=e.getAuthorizationUrl();Logger.log(t),Logger.log(a);for(var r=[],n=DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);n.hasNext();)file=n.next(),r.push([file.getName(),file.getUrl(),file.getDateCreated(),file.getLastUpdated()]);Logger.log(r)}function test_getTournamentMatches(){var e=["Brazil","USA","Argentina","Ukraine"];Logger.log(getTournamentMatches(e)),e.push("Vietnam"),Logger.log(getTournament_(e))}function test_getTournament(){Logger.log(getTournament([["Brazil"],["USA"],["Argentina"],["Ukraine"]]))}function getTournament(e){var t=[];return e.forEach(function(e){e.forEach(function(e){t.push(e)})}),getTournament_(t)}function getTournament_(e){for(var t=0,a=e.length,r=[],n=[],o=0;o<a;o++)for(var i=++t;i<a;i++)(n=[]).push(e[o]),n.push(e[i]),r.push(n);return r}function setDv(){var e=new SetsDV(getDvSettings());setUserProperty(JSON.stringify(e),C_USER_PROPERTY_DV),makeFirstDvRules(e),Logger.log(e),setTriggerOnEdit("dvOnEdit")}function SetsDV(e,t){namesWork=e.namesWork,namesData=e.namesData,idsFileData=e.idsSource,rowsHeader=e.rowsHeader,columnsList=e.columns;var a=namesWork.length;idsFileData||(idsFileData=getFilledArray(!1,a)),rowsHeader||(rowsHeader=getFilledArray(!1,a)),columnsList||(columnsList=getFilledArray(!1,a));for(var r={},n={},o={},i={},s="",g="",l="",u="",S=0;S<a;S++)(s=namesWork[S])in r||(r[s]={}),o=r[s],(u=idsFileData[S]||"0")==SpreadsheetApp.getActive().getId()&&(u="0"),(g=u+(l=namesData[S])+"-"+columnsList[S].join(";"))in n||("0"==u?SpreadsheetApp.getActive():SpreadsheetApp.openById(u),n[g]=new SheetData(l,idsFileData[S],rowsHeader[S],columnsList[S])),i=n[g],g in o||(o[g]=getConnection(s,i.h,rowsHeader[S],columnsList[S]));this.w=r,this.d=n}function getFilledArray(e,t){for(var a=[],r=0;r<t;r++)a.push(e);return a}function test_getConnection(){var e={};e["0data"]=getConnection("Work Sample",["Planet","Mainland","Country","City"]),Logger.log(JSON.stringify(e))}function getConnection(e,t,a,r){var n={};if(a&&r)return n.r=a,n.c=r,n;for(var o=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e).getDataRange().getValues(),i=[],s=t.length,g=0,l=[],u=0,S=0,h=o.length;S<h;S++){i=o[S],l=[];for(var c=0;c<s&&0!=(g=i.indexOf(t[c])+1);c++)if(l.push(g),c==s-1)return u=S+1,n.r=u,n.c=l,n}return-1}function test_sheetData(){var e=new SheetData("Data Sample","1W2M_SardPuzP...0Z2fhL18");Logger.log(JSON.stringify(e))}function SheetData(e,t){var a=this;a.n=e,t=t||SpreadsheetApp.getActiveSpreadsheet().getId();var r=SpreadsheetApp.openById(t).getSheetByName(a.n);if(null===r)throw C_CUSTOM_ERROR_KEY+": No sheet called '"+a.n+"'. Please review your settings. ... You may find this in a script by key: '"+C_CUSTOM_ERROR_KEY+". Error #1'";var n=r.getDataRange(),o=getSmartSort(n.getDisplayValues());return C_SORT_DV_DATA&&n.setValues(o),a.d=getDvData(o),a.h=o[0],a.l=o[0].length,a.s=getDelimeter(o,C_DELIMETERS),t==SpreadsheetApp.getActiveSpreadsheet().getId()&&(t=0),a.f=t,0}function test_getDelimeter(){var e=C_DELIMETERS,t=[["One","Two","Fri"]];Logger.log(getDelimeter(t,C_DELIMETERS)),t[0].push(e[0]),Logger.log(getDelimeter(t,C_DELIMETERS)),t[0].push(e.join("")),Logger.log(getDelimeter(t,C_DELIMETERS)),t[0].push("(1)"),t[0].push("(2)"),Logger.log(getDelimeter(t,C_DELIMETERS))}function getDelimeter(e,t,a){var r=JSON.parse(JSON.stringify(t)),n=[],o="",i="";a>=0&&(r.push("("+a+")"),a++);for(var s=0,g=e.length;s<g;s++)for(var l=0,u=(n=e[s]).length;l<u;l++){o=n[l];for(var S=r.length-1;S>=0;S--)i=r[S],o.indexOf(i)>-1&&r.splice(S,1)}return r&&r.length?r[0]:(a||(a=1),getDelimeter(e,r,a))}function test_getDvData(){var e=getDvData(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Sample").getDataRange().getValues());Logger.log(JSON.stringify(e))}function getDvData(e){for(var t,a={},r={},n={},o="",i=e[0].length,s="",g=1,l=e.length;g<l;g++){if(o=e[g][0],s=e[g][i-1],i>2){n=r=a[o]||{};for(var u=1;u<i-2;u++)r[t=e[g][u]]=r[t]||{},r=r[t];r[t=e[g][i-2]]=r[t]||[],(r=r[t]).push(s)}else(n=a[o]||[]).push(s);a[o]=n}return a}function test_dvOnEdit(){testOnEditTwice_(SpreadsheetApp.getActive().getActiveCell())}function test_dvOnEdit_Script(){testOnEditTwice_(SpreadsheetApp.getActive().getSheetByName(C_SAMPLE_WORK_SHEET).getRange("A6"))}function testOnEditTwice_(e){dvOnEdit(getEditObject(e)),dvOnEdit(getEditObject(e.offset(0,0,3,5)))}function dvOnEdit(e){var t=new ObjectOnEdit(e),a=getDvObject(t);if(null===a)return-1;makeSmartDataValidation(a,getRangeMapR1C1(t.range,t.getValues()),t.sheet)}function onOpen(){ui.createMenu("Smart Data Validation").addItem("Set/Update","setDv").addToUi()}function test_dvOnEdit(){var e=SpreadsheetApp.getActive().getActiveCell();testOnEditTwice_(e)}function test_dvOnEdit_Script(){var e=SpreadsheetApp.getActive().getSheetByName(C_SAMPLE_WORK_SHEET).getRange("A6");testOnEditTwice_(e)}function testOnEditTwice_(e){dvOnEdit(getEditObject(e)),dvOnEdit(getEditObject(e.offset(0,0,3,5)))}function dvOnEdit(e){var t=new ObjectOnEdit(e),a=getDvObject(t);if(null===a)return-1;var r=getRangeMapR1C1(t.range,t.getValues());makeSmartDataValidation(a,r,t.sheet)}function onOpen(){SpreadsheetApp.getUi().createMenu("Smart Data Validation").addItem("Set/Update","setDv").addToUi()}