如何复制范围并将仅包含值的行粘贴到另一张工作表中? [英] How do you copy a range and paste only rows with values into another sheet?

问题描述

让我以此开头作为开头，是说我意识到简单的解决方案是过滤列/取消选中空白，复制并将值粘贴到新列中.问题在于，对于那些不是精通技术"的老师来说，没有什么是简单的.

Let me preface this by saying I realize the simple solution is to filter the column/uncheck blanks, copy, and paste values into the new column. The problem is that with teachers who aren't "tech savvy", nothing is simple.

话虽如此，我要将数据从同一Google电子表格中的多个工作表中提取到主"工作表中.我有一个自定义菜单项，称为可见性"，用于隐藏/显示空行.隐藏行之后，教师单击复制数据"按钮将A1:A复制到另一个名为复制"的工作表中.这就是我遇到的问题-将数据复制到复制"工作表时，空行也是如此.我只想要复制值的行.

With that said, I'm pulling data from multiple sheets in the same google spreadsheet into the "Master" sheet. I have a custom menu item called "Visibility" to hide/show the empty rows. After the rows are hidden, the teacher clicks the "Copy Data" button to copy A1:A to another sheet called "Copy". This is where I run into the problem - when the data is copied to the "Copy" sheet the empty rows are too. I just want the rows with values copied over.

这是我用来将值从母版"复制到复制"的脚本:

Here's the script I'm using to copy the values from Master to Copy:

/**
 * A function named onEdit will be called whenever
 * a change is made to the spreadsheet.
 * 
 * @param  {object} e The edit event (not used in this case)
 */
function copyRange(e){
  var copyFromRange = 'Master!A1:A'; // no row for second cell reference
  var copyToRangeStart = 'Copy!A1';
  copyValuesOnly(copyFromRange, copyToRangeStart);
}

/**
 * This function will copy the values from a given range to
 * a second range, which starts from the given cell reference
 * 
 * @param  {string} copyFromRange    Range reference eg: 
 * @param  {string} copyToRangeStart Cell reference eg:
 */
function copyValuesOnly(copyFromRange, copyToRangeStart) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange(copyFromRange);
  source.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
}

此外，我编辑了发现的脚本以获取要使用的脚本，因此忽略了代码中的描述.感谢您提供的任何帮助！

Also, I edited a script I found to get what I'm working with so disregard the descriptions in my code. Thank you for any help you can provide!

推荐答案

这应该可以做到.注意:您的链接无法访问.

This should do it. Note: your link is not accessible.

function copyRangeNoEmpties(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Master');
  var copyToSheet=ss.getSheetByName('Copy');
  var copyFromRange=sh.getRange('A1:A');
  var vA=copyFromRange.getValues();
  var oA=[];
  for(var i=0;i<vA.length;i++)
  {
    if(vA[i][0])
    {
      oA.push([vA[i][0]]);
    }
  }
  var copyToRange=copyToSheet.getRange(1,1,oA.length,1);
  copyToRange.setValues(oA);
}

这篇关于如何复制范围并将仅包含值的行粘贴到另一张工作表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！