Only set values if the following range is not null

Hi! Folks, can someone help me with this problem? I only want to transpose the Sheet1 Column1 values to Sheet2 Column5 to Column9 and Column2 values to Sheet2 Column10 to Column 12. The criteria is if the 'D10:G13' range is not null only then the values will transpose to Sheet2 form Sheet1. Ex: The range 'D10:G12' from Sheet1 has some data on it. So, only the values from Sheet1 Column1 and Column2 will store to Sheet2 against that particular range. But in my code the values from Sheet1 Column1 and Column2 is storing to Sheet2 though the range 'D13:G13' is null but still is storing data against this empty range. Which I do not wanted. What I want is if the range 'D13:G13' is filled with some data only then the Column1 and Column2 data will store against it.

Note: The given range 'D10:G13' is preset and cannot be changed.

function transpose() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh1 = ss.getSheetByName('Sheet1');
var sh2 = ss.getSheetByName('Sheet2');
var lr = sh2.getLastRow() 1;
var gv1 = sh1.getRange('D10:G13').getValues();
var gv2 = sh1.getRange('A2:A6').getValues();
var gv3 = sh1.getRange('B3:B5').getValues();

var result1 = [...gv1[0], ...gv2.flat(), ...gv3.flat()];
var result2 = [...gv1[1], ...gv2.flat(), ...gv3.flat()];
var result3 = [...gv1[2], ...gv2.flat(), ...gv3.flat()];
var result4 = [...gv1[3], ...gv2.flat(), ...gv3.flat()];


var results = [result1, result2, result3, result4];

sh2.getRange(lr,1,results.length, results[0].length).setValues(results);

}

CodePudding user response：

From what I've understand, you want to match the number of rows in Sheet1 D10:G13 to the transposed data of Sheet1 Column1 and Column2.

In your example, you want to remove the 5th row of Sheet2 since D13:G13 is empty.

Try this:

function transpose() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('Sheet1');
  var sh2 = ss.getSheetByName('Sheet2');
  var lr = sh2.getLastRow() 1;
  var gv1 = sh1.getRange('D10:G13').getValues();
  var gv2 = sh1.getRange('A2:A6').getValues();
  var gv3 = sh1.getRange('B3:B5').getValues();
  var results = [];
  for(var i = 0; i < gv1.length; i  ){
    if(JSON.stringify(gv1[i]) != `["","","",""]`){
      results.push([...gv1[i], ...gv2.flat(), ...gv3.flat()])
    }
  }
  sh2.getRange(lr,1,results.length, results[0].length).setValues(results);
}

Output:

Note: Let me know if my understanding of your issue is correct. Also, it would be easier for us to provide an answer if you can provide screenshot of the expected output.