Googlesheet脚本仅在范围不为空或错误时才复制范围 [英] Googlesheet script to copy range only if it is not empty or error

问题描述

我正在尝试从其他工作表(或同一工作表)中复制某个范围，但是仅在范围不为空或出现错误消息(如#N/A，#REF等)时才复制它./p>

例如:

• 第1页:包含原始数据表.
• 第2页:包含从脚本复制的数据，但此处的数据仅在原始数据不为空或错误的情况下才被覆盖.

  function test(){SpreadsheetApp.getActive().getSheetByName('sheet2').getRange('A2').setValue('= QUERY(sheet1！A2:E355，"SELECT A，B，C，D，E LIMIT 200"，1)')} 

注意:我在这里使用QUERY进行复制，但是我确定它不是复制的最佳选择，因为它不会保留另一张表中的值，而只是用于演示.

解决方案

如果我对您的理解正确，那么如果满足以下条件，则希望将范围复制到另一张纸上:

• 源范围不包含任何有错误的单元格.
• 源范围中的所有空行(如果有的话)都位于范围的末尾(在空行之后没有行，其值不存在).

如果正确，则可以执行以下操作(检查嵌入式注释):

 函数copyRange(){var ss = SpreadsheetApp.getActive();var sourceSheet = ss.getSheetByName("source_sheet");//源工作表名称var targetSheet = ss.getSheetByName("target_sheet");//目标工作表名称var A1notation ="C6:E23";//源范围的A1表示法(如有必要，请更改)var sourceRange = sourceSheet.getRange(A1notation);var sourceValues = sourceRange.getValues();var errors = ["#REF！"，#N/A"，#DIV/0！"，...];//错误数组(不完整)for(var i = 0; i< sourceValues.length; i ++){//遍历源代码范围中的所有值for(var j = 0; j< sourceValues [i] .length; j ++){如果(errors.includes(sourceValues [i] [j]))返回；//如果在单元格中发现错误，则结束执行}}//检查中间是否有空行:var emptyRowInBetween = sourceValues.map(rowValues => {返回rowValues.every(cellValue => {return cellValue ===";});}).join().indexOf("true，false")！== -1;var targetRange = targetSheet.getRange("A1");//复制到的范围(如有必要，请更改)如果(！emptyRowInBetween)sourceRange.copyTo(targetRange);//将范围复制到指定的目标} 

更新:

如果您只想复制值而不是公式等，则可以执行以下操作:

  sourceRange.copyTo(targetRange，SpreadsheetApp.CopyPasteType.PASTE_VALUES); 

注意:

• 您必须启用V8 才能正常工作

参考:

• Range.copyTo(destination)

I'm trying to copy certain range from other sheet (or the same one) but only copy it if the range is not empty or have an error message like (#N/A, #REF, etc.).

For example:

• Sheet 1: Contains the original data sheet.
• Sheet 2: Contains the copied data from the script, but the data here only overwritten if the original ones are not empty or errored.

function test() {  
  SpreadsheetApp.getActive().getSheetByName('sheet2').getRange('A2').setValue('=QUERY(sheet1!A2:E355, "SELECT A, B, C, D, E LIMIT 200",1)')
}

Note: I used QUERY here to copy, but I'm sure it's not the best for copying, as it doesn't retain the value in the other sheet, but just to demonestrate.

解决方案

If I understood you correctly, you want to copy your range to another sheet if the following conditions are met:

• The source range does not contain any cell with an error.
• All empty rows in the source range (if any) are located at the end of the range (there is no row with values after an empty row).

If that's correct, you could do the following (check inline comments):

function copyRange() {
  var ss = SpreadsheetApp.getActive();
  var sourceSheet = ss.getSheetByName("source_sheet"); // Source sheet name
  var targetSheet = ss.getSheetByName("target_sheet"); // Target sheet name
  var A1notation = "C6:E23"; // A1 notation of the source range (please change if necessary)
  var sourceRange = sourceSheet.getRange(A1notation);
  var sourceValues = sourceRange.getValues();
  var errors = ["#REF!", "#N/A", "#DIV/0!", ...]; // Array of errors (incomplete)
  for (var i = 0; i < sourceValues.length; i++) { // Iterate through all values in source range
    for (var j = 0; j < sourceValues[i].length; j++) {
      if (errors.includes(sourceValues[i][j])) return; // End execution if error found in a cell
    }
  }
  // Check if there is an inbetween empty row:
  var emptyRowInBetween = sourceValues.map(rowValues => { 
    return rowValues.every(cellValue => {
      return cellValue === "";
    });
  }).join().indexOf("true,false") !== -1;
  var targetRange = targetSheet.getRange("A1"); // Range to copy to (please change if necessary)
  if (!emptyRowInBetween) sourceRange.copyTo(targetRange); // Copy range to specified destination
}

Update:

If you only want to copy the values, and not the formulas, etc., you can do this:

sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES);

Note:

• You have to enable V8 for this to work.

Reference:

• Range.copyTo(destination)

这篇关于Googlesheet脚本仅在范围不为空或错误时才复制范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！