2

I am trying to create a menu driven script that will insert a new row at the end of a sheet and copy down all formulas - with it auto increasing according to row index.

This is what I have so far but it copies down the EXACT formulas into the new row/cells but doesn't increase the row reference# like it would if I simply copied and pasted to new row.

EG, formula in B133 is =iferror(VLOOKUP(A37,DATA,5,0),"") and when i run the script it copies it down to next new row/cell as =iferror(VLOOKUP(A37,DATA,5,0),"") - when it should be =iferror(VLOOKUP(A38,DATA,5,0),"")


What I have written so far:

var ss = SpreadsheetApp.getActive();

function onOpen() {      
  var menu = [{name:"Add New Last Row", functionName:"addRow"}];
  ss.addMenu("AM FUNCTIONS", menu); 
}

function addRow() {    
  var sh = ss.getActiveSheet(), lRow = sh.getLastRow(); 
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
  sh.insertRowsAfter(lRow,1);
  var formulas = range.getFormulas();
  var newRange = sh.getRange(lRow+1,1,1,lCol);
  newRange.setFormulas(formulas);
}
1

Instead of getFormulas and setFormulas, use copyTo method:

range.copyTo(newRange);

This will have the same effect as if you selected the last row and copied it below. In particular, it will also copy down any values that were entered directly into cells. If you prefer such values to not be copied, use getFormulas/setFormulas combination on newRange:

range.copyTo(newRange);
newRange.setFormulas(newRange.getFormulas());

The effect of the last line: no change of formulas, but the cells that didn't contain a formula will become empty.


R1C1 approach

You can also try replacing the last three rows of addRow with

var formulas = range.getFormulasR1C1();
var newRange = sh.getRange(lRow+1,1,1,lCol);
newRange.setFormulasR1C1(formulas);

This will make sure the references in formulas are remapped as if you copy-pasted them. However, this has a major limitation: setFormulasR1C1 doesn't work correctly with empty string as its argument, which will result in #ERROR! messages in the cells that should be left blank.

The reason R1C1 works differently is that getFormulasR1C1 retrieves formulas in a relative form: for example, a reference from A3 to D2 becomes R[-1]C[3], meaning "one row up, three columns to the right". When the formula is set to, for example, E4 with setFormulasR1C1, the cell reference will appear as H3.

  • I have changed the above but I get #ERROR! in all cells of the new row - either formula parse error or invalid content on cells that have validation – Karl Pohlman Jul 10 '16 at 10:05
  • Good point. I updated the answer. – user79865 Jul 10 '16 at 10:39
  • Just wondering if there is a way this script can be run by users who are using a protected sheet. I currently have all columns that contain formulas protected so they do not delete them but want the above script to be able to be run to add new line. They are getting protection/permission message. Is this possible? – Karl Pohlman Jul 20 '16 at 23:57
  • A script has the same permissions as the user running it. So if the user isn't allowed to edit those cells, the script won't be either. – user79865 Jul 21 '16 at 0:56
0

I am currently solve same problem. This post extends previous reply from user79865:

You should use getFormulasR1C1() and setFormulasR1C1 function instead of getFormulas, because they are use relative links to another cells.

Function copyFormatAndFormulas

I write this simple function to copy formats & formulas between ranges:

function copyFormatAndFormulas(rangeSource, rangeDestination) {
  rangeSource.copyTo(rangeDestination, {formatOnly: true});

  var formulas = rangeSource.getFormulasR1C1();
  for(var x in formulas) {
    for(var y in formulas[x]) {
      if(formulas[x][y] == "") continue;
      rangeDestination.getCell(parseInt(x) + 1, parseInt(y) + 1).setFormulaR1C1(formulas[x][y]);
    }
  }
}

Example:

I modified your function addRow() from your ask:

function addRow() {    
  var sh = ss.getActiveSheet(),
  lRow = sh.getLastRow(); 
  var lCol = sh.getLastColumn(),
  range = sh.getRange(lRow,1,1,lCol);
  sh.insertRowsAfter(lRow,1);
  var newRange = sh.getRange(lRow+1,1,1,lCol);
  copyFormatAndFormulas(range, newRange);
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.