CODEWITHSUNDEEP
javascripthtmlgoogle-apps-script
MATHAVAN A L KRISHNAN
MATHAVAN A L KRISHNAN

Reputation: 55

How to set the range until last row of a column in sheet using Google App Script

I am creating a formula where it will be effective until the last row of a particular column (for example last row of column B) in AppScript. I have attached the formula here. Can anyone help me with it

sheet.getRange("A2:A11").setFormula('=IF(ISBLANK(B2:B),"",(B2:B &"."&substitute(substitute(lower(C2:C),"jalan","jln")," ","")))');

Upvotes: 1

Views: 2501

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15308

Try this

var lastR = getLastDataRow(sheet,"A")
sheet.getRange("A2:A" + lastR).setFormula('=IF(ISBLANK(B2:B),"",(B2:B &"."&substitute(substitute(lower(C2:C),"jalan","jln")," ","")))');

and add this function

function getLastDataRow(sheet,col) {
// col in letter
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(col + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

Upvotes: 1

Related Questions