Copy Lots To One

The was an answer to a recent question.

This script allows you to select many columns and it creates a comma separated string of these ranges in a1 notation.

Since this is a modeless dialog you can keep selecting ranges and pushing the Add button after each one and it will keep building the string.

You may not select more than one columnar range at a time but you may copy the string and save somewhere and then paste it into the text area and click the load button and it will save all of the ranges at one time.

After you have all of the ranges loaded then select the top cell of the column you wish to copy all of these values to and click the Copy button. It copies them all at one time. If you build the csv string yourself separate the strings only with commas. No spaces. No quotation marks.

The buttons have tool tips on them in case you forget what they do.

Code.gs:

function lotsToOneMenu()//this creates of Lots to One Tools Menu

{

SpreadsheetApp.getUi().createMenu('LotsToOne Tools')

.addItem('Copy Lots To One', 'copyLotsToOne')

.addToUi();

}

function copyLotsToOne()//This is the main function which starts the ball rolling.

{

clearCurrentRangeList();

var ui=HtmlService.createHtmlOutputFromFile('lots2one');

SpreadsheetApp.getUi().showModelessDialog(ui, 'Copy Lots of Columns To One');

}

function copyToColumn()//This copies all of the range values into one array and copies to final column

{

var ss=SpreadsheetApp.getActive();

var sh=ss.getActiveSheet();

var rg=sh.getActiveRange();

var rgRow=rg.getRow();

var rgCol=rg.getColumn();

var rgL=getCurrentRangeList();

var rgA=rgL.split(',');

var vA=[];

for(var i=0;i<rgA.length;i++)

{

var rgA1vA=sh.getRange(rgA[i]).getValues();

for(j=0;j<rgA1vA.length;j++)

{

vA.push([rgA1vA[j]]);

}

sh.getRange(rgRow,rgCol,vA.length,1).setValues(vA);

}

function addSelectedRange()//This adds one column to the current range at a time

{

var ss=SpreadsheetApp.getActive();

var sh=ss.getActiveSheet();

var rg=sh.getActiveRange();

var rgA1=rg.getA1Notation();

appendCurrentRangeList(rgA1);

return getCurrentRangeList();

}

function appendCurrentRangeList(rgA1)//This is used by above function to append to the string.

{

var current=getCurrentRangeList();

if(current)

{

current+=',' + rgA1;

}

else

{

current=rgA1;

}

putCurrentRangeList(current)

}

function getCurrentRangeList()

{

var props=PropertiesService.getScriptProperties();

var crl=props.getProperty('CurrentRangeList');

return crl;

}

function putCurrentRangeList(rgA1)

{

var props=PropertiesService.getScriptProperties();

props.setProperty('CurrentRangeList',rgA1)

}

function clearCurrentRangeList()

{

var props=PropertiesService.getScriptProperties();

props.setProperty('CurrentRangeList', '')

}

lots2one.html:

<!DOCTYPE html>

<html>

<head>

function addARange()

{

google.script.run

.withSuccessHandler(dispRange)

.addSelectedRange();

}

function dispRange(rl)

{

$('#txt1').val(rl);

}

function setRange()

{

var rs=$('#txt1').val();

google.script.run.putCurrentRangeList(rs);

}

function copyToColumn()

{

google.script.run.copyToColumn();

}

</script>

</head>

<body>

</div>

</body>

</html>

This is what the dialog looks like. It's a modeless dialog so you can select the input column ranges one at a time and click the add button and when complete select the cell at the top of the column that you want the data insert and press copy.

It you wish to save the string you make do so and if at a later date you want to reuse it you may simply paste it into the text area and click Load. It will load it and then you may copy it to another destination.

The image below shows the dialog in action on a spread sheet.

Google Sites

Report abuse