javascript - How to Not Transfer Blank Column to Master Sheet?

Question

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);
}

question from:https://stackoverflow.com/questions/65876025/how-to-not-transfer-blank-column-to-master-sheet

Answer 1

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.

• 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.

• 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.

Solution:

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);
}