Reputation:
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
https://docs.google.com/spreadsheets/d/1HiOrz1KAuV1nN9a2IlwQgv3aWrGNKFaKHq2a8JZw3gI/edit#gid=0
Upvotes: 0
Views: 698
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
Reputation: 27410
Your goal is to copy the formulas to the newly inserted row upon inserting a new row.
INSERT_ROW
and
make sure you activate some code when you insert a new row.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();
}
Upvotes: 1