Google app script adding a function to new columns

Question

First time time writting, long time reading. I have a google sheet that is always being updated with three columns at the end. I want to write a script that will add the three colums at the end and then add a function to each new columns and pull the fuction/formula down. This is what I have so far. Any help would be greatly appreciated. Thank you.

 function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var column = sh.insertColumnsAfter(1, 3);
  var cell = ss.getRange("");
  cell.setFormula();
}

Answer 1

Here is a simple example of how to add formulas and copy them down.

Code.gs

function addColumns() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getActiveSheet();
    sheet.insertColumnsAfter(1,3)
    let formulas = [["=A1","=A1+A2","=A1+A2+A3"]];
    let source = sheet.getRange(1,2,1,3)
    source.setFormulas(formulas);
    let destination = sheet.getRange(2,2,10,3); // B2:D11
    source.copyTo(destination);
  }
  catch(err) {
    console.log(err);
  }
}

Reference

• Sheet.getRange()
• Range.setFormulas()
• Range.copyTo()