CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user14807564
user14807564

Reputation:

When row is added it should keep all formulas

I have been trying to add a row which keep the formulas.

But when i add any row it always empty.

I could not found a way by searching on google.

I have attached below example sheet. Any help would be greatly appreciate

enter image description here

https://docs.google.com/spreadsheets/d/1HiOrz1KAuV1nN9a2IlwQgv3aWrGNKFaKHq2a8JZw3gI/edit#gid=0

Upvotes: 0

Views: 698

Answers (2)

NightEye
NightEye

Reputation: 11214

This should let you copy your last row, including data and formula, into its next row.

function addRow() {
  var sh = SpreadsheetApp.getActiveSheet();
  var lastRow = sh.getLastRow(); 
  var lastCol = sh.getLastColumn();
  var range = sh.getRange(lastRow, 1, 1, lastCol);
  sh.insertRowsAfter(lastRow, 1);
  range.copyTo(sh.getRange(lastRow + 1, 1, 1, lastCol), {contentsOnly:false});
}

The important parameter you need in copyTo here is {contentsOnly:false}. Feel free to modify the coordinates for you to paste the row to anywhere.

For more details, read the usage of copyTo

If you want only the formula in the newly added row, you need to get the formula from the source row and then copy it to your destination row.

var source = sheet.getRange("A1:C1");
var dest = sheet.getRange("A2:C2");
var arr = source.getFormulas();
dest.setFormulas(arr);

This doesn't adjust the formula to the new row, it will copy the exact cell formula so be careful.

Upvotes: 1

Marios
Marios

Reputation: 27410

Explanation:

Your goal is to copy the formulas to the newly inserted row upon inserting a new row.

  • You can use an onChange which has a property INSERT_ROW and make sure you activate some code when you insert a new row.
  • The following script will copy the content and formulas from the above row of the one which was inserted to the newly inserted row upon inserting a new row.

Solution:

Add both code snippets below to the script editor:

function myFunction(e) {
 const sh = SpreadsheetApp.getActiveSheet();
 if(e.changeType === 'INSERT_ROW') {
     const row = sh.getActiveRange().getRow(); 
     var fromRng = sh.getRange(row-1,1,1,sh.getLastColumn());
     var toRng = sh.getRange(row,1,1,sh.getLastColumn());
     fromRng.copyTo(toRng);
  }
}

and execute this function createTrigger only and once to create the onChange trigger:

function createTrigger(){
  var sheet = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("myFunction")
    .forSpreadsheet(sheet)
    .onChange()
    .create();
}

Illustration:

enter image description here

Limitation:

  • The disadvantage of this approach is that is copies both values and formulas, but it does not seem to matter in your case since you have only formulas as of now. If you want to put only formulas you need to use setFormulaR1C1 but since you have cells with no formulas, you need to make sure that you copy formulas, otherwise you will get errors.

Upvotes: 1

Related Questions