CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Shironeki
Shironeki

Reputation: 37

If cell is empty, make reference to another cell of other column in the same row

I would like to do this in G column : If Cell 'G8' is Blank Then (=K8) for example. My difficulty is to make reference of same row in K of empty cell in G.

enter image description here

I've tryed to adapt this script but I get a shift at some point and I don't know why.

function updatewithformula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet 1');
  var range = sheet.getDataRange()
  var source = sheet.getRange('G1:G13').getDisplayValues()
  var index = []
  for (var i = 1; i<source.length; i++){
    if (source[i][0] == ""){
      index.push(i+1) 
        }
  }
  index.push(range.getLastRow()+1)
  Logger.log(index)
  for(var i = 0;i<index.length-1;i++){
       var rangetomodify = sheet.getRange(index[i],7,1,1)
       var l = index[i+1]-index[i]-1
      rangetomodify.setFormulaR1C1("=R["+l+"]C[4]")
  }
    
}

Upvotes: 2

Views: 103

Answers (2)

Shaik Naveed
Shaik Naveed

Reputation: 448

There are better, easy and short methods to do it, I have used offset here.

The below works for me

function updatewithformula(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet 1');
  var range = sheet.getDataRange()
  var source = sheet.getRange('G1:G13').getValues();
  Logger.log(source);

  
  var index = [];
  for (var i in source){
    if(source[i][0] == ""){
      var j = +i;
      index.push(("G"+(j+1)));
    }
  }
 

  for (var i in index){
    var TEMP = sheet.getRange(index[i]);
    var TEMP1 = TEMP.offset(0,4).getA1Notation();
    const formula = `=${TEMP1}`;
    TEMP.setFormula(formula);

  }
}

References - OFFSET

Upvotes: 2

Tanaike
Tanaike

Reputation: 201503

As another approach, how about the following modification?

Modification points:

  • About I've tryed to adapt this script but I get a shift at some point and I don't know why., in your script, "=R[" + l + "]C[4]" is used with var l = index[i + 1] - index[i] - 1. In this case, when l is not 0, the other row is used. I thought that this might be the reason for your issue. In your script, I think that it is not required to use var l = index[i + 1] - index[i] - 1. When your script is simply modified, it becomes as follows.

    function updatewithformula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet 1');
  var range = sheet.getDataRange();
  var source = sheet.getRange('G1:G13').getDisplayValues();
  var index = [];
  for (var i = 1; i < source.length; i++) {
    if (source[i][0] == "") {
      index.push(i + 1);
    }
  }
  index.push(range.getLastRow() + 1);
  for (var i = 0; i < index.length - 1; i++) {
    var rangetomodify = sheet.getRange(index[i], 7, 1, 1);
    rangetomodify.setFormulaR1C1("=R[0]C[4]");
  }
}

  • But, in this case, getRange and setFormulaR1C1 are used in a loop. In this case, the process cost becomes high.

When your script is modified by reducing the process cost, how about the following modification?

Modified script:

function updatewithformula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet 1');
  var source = sheet.getRange('G2:G13').getDisplayValues();
  var ranges = source.reduce((ar, [g], i) => {
    if (!g) ar.push(`G${i + 2}`);
    return ar;
  }, []);
  sheet.getRangeList(ranges).setFormulaR1C1("=R[0]C[4]");
}
  • By RangeList, the process cost can be reduced a little.

References:

Upvotes: 3

Related Questions