Lots To One
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>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script>
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>
<style>#btn1{float:right;margin:0 0 0 50px;}#txt1{width:100%;}#btn2{margin:0 0 0 20px;}</style>
</head>
<body>
<div id="div1">
<textarea id="txt1" rows="4" cols="35"></textarea>
<br /><input id="btn0" type="button" value="Add" title="Select a Columnar Range and Click Add" onClick="addARange();" />
<input id="btn2" type="button" value="Load" title="Loads s string of A1Notation columns separated by commas only" onClick="setRange();" />
<input id="btn1" type="button" value="Copy" title="Select Top of Destination Column and Click onCopy" onClick="copyToColumn();" />
</div>
</body>
</html>
Here's what the dialog looks like:
The input and the output:
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
