Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
400 views
in Technique[技术] by (71.8m points)

google sheets - Can importrange move everything into a single column

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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:

enter image description here

The input and the output:

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...