Can importrange move everything into a single column

Question

My team leaders submit a form with their team members' names and emails (up to 8 emails per team), which populates a master list. I'd like to import those emails into a single column of a new sheet (everything from cells CO2:CO20; CR2:CR20; CU2:CU20 etc.etc. into one column)

I can see how multiple importrange strings seperated by ";" would work, but given the number of times I would need to stack the command, it seems inefficient. Is there a better, faster way?

why would I split the text into columns? they are already in columns. Many of them... — Andrew Mcneely

Cooper Cooper · Accepted Answer · 2017-08-27T21:12:49

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:

Can importrange move everything into a single column

1 Answers

Lots To One