CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
googlesheet test
googlesheet test

Reputation: 247

Google Apps Script CopyPasteType.PASTE_FORMULA also including values

Google Sheets sample file: https://docs.google.com/spreadsheets/d/18xd8sEpm95-sGoawg9gjKfoIqmM0blmuMYooeAPhXPY/edit?usp=sharing

Here is my current code:

function onEdit(e) {
  var sh=e.range.getSheet();
  var completionColumn=1;
  var topRow=2;
  var editSheet='Sheet1';
  if(sh.getName()!=editSheet)return;//return if not correct sheet
  if(e.range.columnStart==completionColumn && e.range.rowStart==topRow) {
    sh.insertRowBefore(topRow);
    //sh.getRange('D3').copyTo(sh.getRange('D2'), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
    sh.getRange('3:3').copyTo(sh.getRange('2:2'), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  }
}

The script will add a new row at row 2 whenever the current row 2's column A is edited. The next function of the script I want to add is to copy all the formulas in the current row 2 to the new row 2.

When I tried copying just specific cells it will work out because I am only copying cells with formulas like in my current code example:

sh.getRange('D3').copyTo(sh.getRange('D2'), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);

But when I try using it for the entire row it also copies the values even though I used PASTE_FORMULA instead of PASTE_NORMAL or other CopyPasteTypes:

sh.getRange('3:3').copyTo(sh.getRange('2:2'), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);

How do I prevent the non-formula values in the row from copying over to the new added row?

Upvotes: 1

Views: 1757

Answers (1)

Nikko J.
Nikko J.

Reputation: 5533

To ensure that we only fetch the cells with formula, you can use getRange(row, column, numRows, numColumns)

Change your:

sh.getRange('3:3').copyTo(sh.getRange('2:2'), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);

To:

sh.getRange(3, 4, 1, 2).copyTo(sh.getRange(2, 4, 1, 2), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);

Example:

During edit:

enter image description here

After edit:

enter image description here

Reference:

Upvotes: 1

Related Questions