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

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.

thank you for your help

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]")
  }
    
}

CodePudding user response：

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:

CodePudding user response：

@Shironeki 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);

  }
}

Hope this helps.

References - OFFSET