google-sheetsgoogle-apps-script

Check if range is empty and copy data from Range


how can i past only in Blank cell not all of Cell

this script is writing all cell in C to same Value as B but i just need to Write only Blank Cell with the value of B.

can some one find the error in this funktion????

function fill() {
  var tss = SpreadsheetApp.getActiveSpreadsheet();
  var values = tss.getRange("data!C:C").getValues();

  for(i=0; i<values.length; i++) {
    var value = values[i][0];

    if (value == "") {

      var rangeA = tss.getSheetByName("data").getRange(i+1,2);
      var rangeB = tss.getSheetByName("data").getRange(i+1,3);
      var copypaste = tss.getSheetByName("data");

      if(!rangeA.isBlank()) {
        copypaste.getRange("B2:B").copyTo(copypaste.getRange("C2:C"),{contentsOnly:true})
      }
    }
  }
}

Solution

  • Paste only on blank cells

    If I understood your problem correctly, the code below should achieve the desired outcome.

    function fill() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("<your-sheet-id>");
      var range = sheet.getRange("C1:C" + sheet.getLastRow()); //Get populated cells in column C
      var valuesC = range.getValues();
      var valuesB = sheet.getRange("B1:B" + sheet.getLastRow()).getValues();
    
      for (var i = 0; i < valuesC.length; i++) {
        if (valuesC[i][0] === "") {
          valuesC[i][0] = valuesB[i][0]; //Assign value of column B to column C only if corresponding cell in column C is empty
        }
      }
    
      range.setValues(valuesC); //Updates values on column C with the modified values
    }
    

    This script copies values from column B to column C only where column C is blank. It directly updates the values in column C instead of using copyTo.