更改将行检查空白的moverows脚本更改为连续保留空白的特定单元格 [英] Change moverows script that checks blanks i a row to specific cells left blank in a row

问题描述

我从Cooper那里获得了脚本的帮助，但我需要进行一些修改，如果该行中的单元格留空，则该脚本基本上会与自定义对话框一起移动. 我想完成的是仅检查一行中的特定单元格. 例如第1行和第4行是必填项，第2行和第3行不是必填项. 这是到目前为止我得到的代码.

Got help with my script from Cooper yet i need some modification, the script basically moverows with a custom dialog if a cell in the row is left blank. What i like to accomplish is that only the specific cells in a row will be checked. e.g. row 1 and 4 are mandatory and row 2 and 3 are not. This is the code what I've got so far.

    function Moverows57654() { 

 var ss=SpreadsheetApp.getActive();
 var sourceSheet=ss.getSheetByName("sheet1");
 var targetSheet=ss.getSheetByName("sheet2");
 var sourceRange=sourceSheet.getRange("A2:T2");
 var values=sourceRange.getValues();
 var range0=sourceSheet.getRange("A2:B2");
 var range1=sourceSheet.getRange("D2:E2"); 
 var firstFreeRow=goToFirstRowAfterLastRowWithData(targetSheet, "A:AD");
 var noBlanks=true;
 var bA=[];
 var ro=sourceRange.getRow();
 var co=sourceRange.getColumn();
 for(var ri=0;ri<values.length;ri++) {
 for(var ci=0;ci<values[ri].length;ci++) {
  if(!values[ri][ci]) {
    noBlanks=false;
    bA.push(sourceSheet.getRange(ri+ro,ci+co).getA1Notation());
  }
   }
    }
 if(noBlanks) {

  targetSheet.getRange(firstFreeRow,1,values.length,values[0].length)
 .setValues(values);      targetSheet.getRange(firstFreeRow, 1, values.length, values[0].length)

    .setValues(values);
range0.clearContent();
range1.clearContent();

//createPdf()//function gets started
}else{
SpreadsheetApp.getUi().alert('Sorry there are blanks in the following cells: ' + bA.join(', '));
return;
 }

推荐答案

尝试一下:

function Moverows57654() { 
  var ss=SpreadsheetApp.getActive();
  var sourceSheet=ss.getSheetByName("sheet1");
  var targetSheet=ss.getSheetByName("sheet2");
  var sourceRange=sourceSheet.getRange("A2:T2");
  var values=sourceRange.getValues();
  var range0=sourceSheet.getRange("A2:B2");
  var range1=sourceSheet.getRange("D2:E2");
  //var firstFreeRow=targetSheet.getLastRow() + 1; 
  var firstFreeRow=goToFirstRowAfterLastRowWithData(targetSheet, "A:AD");
  var noBlanks=true;
  var bA=[];
  var skA=[1,2];//column -1
  var ro=sourceRange.getRow();
  var co=sourceRange.getColumn();
  for(var ri=0;ri<values.length;ri++) {
    for(var ci=0;ci<values[ri].length;ci++) {
      if(!values[ri][ci] && skA.indexOf(ci)==-1) {
        noBlanks=false;
        bA.push(sourceSheet.getRange(ri+ro,ci+co).getA1Notation());
      }
    }
  }
  if(noBlanks) {

    targetSheet.getRange(firstFreeRow,1,values.length,values[0].length)
    .setValues(values);      targetSheet.getRange(firstFreeRow, 1, values.length, values[0].length)

    .setValues(values);
    range0.clearContent();
    range1.clearContent();

    //createPdf()//function gets started
  }else{
    SpreadsheetApp.getUi().alert('Sorry there are blanks in the following cells: ' + bA.join(', '));
    return;
  }
 }

这篇关于更改将行检查空白的moverows脚本更改为连续保留空白的特定单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！