CODEWITHSUNDEEP
excelgoogle-apps-scriptgoogle-sheets
WallyG
WallyG

Reputation: 197

Google Script - How to set formula to adjacent cell

I am trying to set a formula in Google Spreadsheet F2:F if E2:E has a date. If E2:E is blank, then no formula is set to F2:F.

My Code so far. Any Help or suggestions would be much appreciated.

function formula(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var date = Utilities.formatDate(new Date(), "America/New_York", "MMMM dd, yyyy");
  var colE = sheet.getRange("E2:E");
  var colF = sheet.getRange("F2:F");

  if (colE != date){
    colF.setFormula("=round(abs(E2:E-NOW()),-1)");
  } else {
    colF.setValue("");
 }

}

Upvotes: 0

Views: 1164

Answers (1)

Serge insas
Serge insas

Reputation: 46802

Here is how to do it... 

function formula(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var colE = sheet.getRange("E2:E").getValues();
  var colF = sheet.getRange("F2:F").getFormulas();
  for(var n in colE){
    if (typeof(colE[n][0])=='object'){
      colF[n][0] = "=round(abs(E"+n+":E-NOW()),-1)";
    } else {
      colF[n][0] = "";
    }
  }
  sheet.getRange('F2:F').setFormulas(colF);
}

Upvotes: 1

Related Questions