如何不将空白列转移到主表? [英] How to Not Transfer Blank Column to Master Sheet?

问题描述

在添加"if"之前，此代码可以正常工作."if"应防止将第2列中没有数据的行转移到母版纸.如何使没有数据的数据无法传输.

This code works perfectly until the 'if' is added. The 'if' should prevent the row that has no data in column 2 from being transferred to the master sheet. How to I make data that has not data prevent from transferring.

    function combineSheets() {
  var sApp = SpreadsheetApp.getActiveSpreadsheet();
  var s1= sApp.getSheetByName("January");
  var s2= sApp.getSheetByName("February");
  var s3= sApp.getSheetByName("March");
  var s4= sApp.getSheetByName("April");
    var s5= sApp.getSheetByName("May");
  var s6= sApp.getSheetByName("June");
  var s7= sApp.getSheetByName("July");
  var s8= sApp.getSheetByName("August");
    var s9= sApp.getSheetByName("September");
  var s10= sApp.getSheetByName("October");
  var s11= sApp.getSheetByName("November");
  var s12= sApp.getSheetByName("December");
  var s13= sApp.getSheetByName("Master");
 

  var s1values = s1.getRange(1,2,s1.getLastRow(),27).getValues();
  var s2values = s2.getRange(1,2,s2.getLastRow(),27).getValues();
  var s3values = s3.getRange(1,2,s3.getLastRow(),27).getValues();
    var s4values = s4.getRange(1,2,s4.getLastRow(),27).getValues();
  var s5values = s5.getRange(1,2,s5.getLastRow(),27).getValues();
  var s6values = s6.getRange(1,2,s6.getLastRow(),27).getValues();
    var s7values = s7.getRange(1,2,s7.getLastRow(),27).getValues();
  var s8values = s8.getRange(1,2,s8.getLastRow(),27).getValues();
  var s9values = s9.getRange(1,2,s9.getLastRow(),27).getValues();
    var s10values = s10.getRange(1,2,s10.getLastRow(),27).getValues();
  var s11values = s11.getRange(1,2,s11.getLastRow(),27).getValues();
  var s12values = s12.getRange(1,2,s12.getLastRow(),27).getValues();
  var sourceData  =  s1values.concat(s2values,s3values,s4values,s5values,s6values,s7values,s8values,s9values,s10values,s11values,s12values);

 
    for (var i in sourceData) {
  if (sourceData[i][1] !== null && sourceData[i][1] !== '') {
    targetData.push(sourceData[i]);
  }

  var s13values = [];
  s13values =  s1values.concat(s2values,s3values,s4values,s5values,s6values,s7values,s8values,s9values,s10values,s11values,s12values);
  s13.getRange(1,1,s13values.length,27).setValues(s13values);
}

推荐答案

说明:

我相信您的目标是将数据从每个月工作表传输到 Master 工作表，但 B 列为空的行除外.

Explanation:

I believe your goal is to transfer the data from each month sheet to the Master sheet except for the rows for which column B is empty.

• 遍历工作表并 forEach 工作表从第二列获取数据，直到 27 列，直到最后一行包含相应工作表内容的数据.

• Iterate over the sheets and forEach sheet get the data from the second column until column 27 and until the last row with content of the corresponding sheet.

过滤器列 B 为空的行.请记住，您的范围从 B 列开始，因此就JavaScript而言，values数组的 zero 元素对应于 B 列的值:

Filter out the rows for which column B is empty. Remember your range starts from column B therefore in terms of JavaScript, the zero element of the values array corresponds to the values of column B:

sh.getRange(1,2,sh.getLastRow(),27).getValues().filter(r=>r[0]!='');

传播语法用于确保您正确连接这些值.

Spread syntax is used to make sure you concatenating the values properly.

我还改进了代码，不仅提高了性能，而且使修改代码变得更加容易，例如更改了一些范围参数等.

I also improved your code both in terms of performance but also make it easier to modify it, for example change some range parameters etc.

function combineSheets() {
  const sApp = SpreadsheetApp.getActiveSpreadsheet();
  const months = ['January','February','March','April','May','June','July',
                 'August','September','October','November','December'];  
  const master = sApp.getSheetByName('Master');           
  const sourceData = [];
  months.forEach(m=>{
     let sh = sApp.getSheetByName(m);
     let vals = sh.getRange(1,2,sh.getLastRow(),27).getValues().filter(r=>r[0]!='');
       sourceData.push(...vals);
  });
  master.getRange(1,1,sourceData.length,sourceData[0].length).setValues(sourceData);
}

这篇关于如何不将空白列转移到主表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！