将非空范围复制到新工作表中，然后向下拖动公式 [英] Copy non-empty range into new sheet, and drag down formulas

问题描述

1. 我想从合并表"工作表中复制A/B列中的所有非空单元格，然后将这些单元格粘贴到"Web应用程序的输入"工作表中的H:I列中(始终从顶部，而不是将它们附加为新行，因为这些值将不断更新.

1. I want to copy all the non-empty cells in columns A/B from sheet "Consolidation Sheet" and then paste these cells into the sheet "Inputs for Web App" into columns H:I (always starting from the top, rather than appending them as new rows, since these values will be updated constantly).

然后我基本上想将其他列(A到G)中的所有公式一直向下拖动到粘贴的最后一行.

Then I basically want to drag down all of the formulas in the other columns (A through G) all the way down to the last row pasted.

将第I列的格式设置为日期.

Format column I as a date.

我不确定是否需要循环来执行此操作，还是仅需要一个数组.我已经尝试过两种方法，但总是会出错.

I'm not sure if I need a loop to do this or just an array. I've tried it both ways but keep getting errors.

选项1:在这里我得到这个错误:

option 1: HERE I GET THIS ERROR:

TypeError:(class)@ 2e3b19c不是函数，而是对象.

TypeError: (class)@2e3b19c is not a function, it is object.

function copynotempty(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CONSOLIDATION SHEET");
  var col = 0 ; // choose the column you want to check: 0 = col A, 1= col B ...
  var lastrow = ss.getLastRow();
  var range = ss.getRange(5,1,lastrow-4,2);
  var values=range.getValues();// data is a 2D array, index0 = col A
  var formulas=range.getFormulas();// data is a 2D array, index0 = col A
  var target= new Array();// this is a new array to collect data
   //for(n=0;n<range.getHeight();++n){
     if (values()!=''|| formulas()!=''){ ;  (
           var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inputs for Web App"); //target sheet of the spreadsheet
           range.copyTo(sh2.getRange(4,8,range.height,range[0].length),{contentsOnly:true});
              }
            }

选项2:在这里，我不断收到此错误:

option 2: here I keep getting this error:

"TypeError:无法将未定义的属性" 0.0设置为"(VALUE OFCELL)"

"TypeError: Cannot set property "0.0" of undefined to "(VALUE OF CELL)"

function copynotempty(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CONSOLIDATION SHEET");
  var col = 0 ; // choose the column you want to check: 0 = col A, 1= col B ...
  var lastrow = ss.getLastRow();
  var range = ss.getRange(5,1,lastrow-4,2);
  var values=range.getValues();// data is a 2D array, index0 = col A
  var formulas=range.getFormulas();// data is a 2D array, index0 = col A
  var target= new Array();// this is a new array to collect data
for(n=0;n<range.getHeight();++n){
     if (values[n][col]!=''|| formulas[n][col]!=''){ ;
        for (cc=0;cc<range.getWidth();++cc){
                if (values[n][cc]!=''){target[n][cc]=values[n][cc]} // if the cell has a value, copy it  
                }
              }
            }
            if(target.length>0){// if there is something to copy
              var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inputs for Web App"); //second sheet of your spreadsheet
          sh2.getRange(4,8,target.height,target[0].length).setValues();// paste the selected values in the 2cond sheet
          }
        }

推荐答案

在第一个函数中，您的问题是您尝试调用 values()和 formulas()都是二维的字符串数组.

In the first function your issue is that you try to call values() and formulas() which are both 2d arrays of strings.

移动数据(这是当前您要在函数中唯一尝试做的事情)，可以这样完成:

Moving the data over (which is currently the only thing you are trying to do in the functions) can be done like this:

function copynotempty(){
  var ss = SpreadsheetApp
      .getActiveSpreadsheet()
      .getSheetByName("CONSOLIDATION SHEET");

  var lastrow = ss.getLastRow();
  var range = ss.getRange(5,1,lastrow-4,2);

  var nonEmpty = range
      .getValues()
      .filter(function(row) {           // filter matrix for rows
        return row.some(function(col) { // that have at least one column
          return col !== "";});});      // that is not empty

  if (nonEmpty) {
    var sh2=SpreadsheetApp
        .getActiveSpreadsheet()
        .getSheetByName("Inputs for Web App");

    sh2.getRange(4,8, nonEmpty.length, nonEmpty[0].length)
       .setValues(nonEmpty);
  }
}

1. 这可以用数组公式解决吗?
2. 最好通过将列格式设置为您选择的dtae格式还是您想要一个字符串来完成此操作?

这篇关于将非空范围复制到新工作表中，然后向下拖动公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！