Home > Back-end >  How can I overwrite only the values in a range after applying a formula to it?
How can I overwrite only the values in a range after applying a formula to it?

Time:09-19

I have formulas going down some columns, but I want to overwrite the same cells with only the values. I've tried a couple things but haven't been able to get it to work, it still leaves the formula in the cells. This is an example of what I have for one of the columns.

  function Info() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("J2").setFormula("=SHIP(G2)");

  var lr1 = ss.getLastRow();
  var fillDownRange1 = ss.getRange(2, 10, lr1-1, 1);
  ss.getRange("J2").copyTo(fillDownRange1);
  ss.getRange("J:J").activate();
  ss.getActiveRangeList().setFontWeight("bold");
  ss.getRange("J2:J"), SpreadsheetApp.CopyPasteType, {contentsOnly:true};

CodePudding user response:

Try this:

function Info() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  sh.getRange("J2").setFormula("=SHIP(G2)");
  let rg1 = sh.getRange(2, 10, sh.getLastRow()  - 1, 1);
  let vs = rg1.getDisplayValues();
  rg1.setValues(vs);
}
  • Related