Google Script Copy/Paste/Clear Removing Pasted Cell

Question

I am writing an action script in Google Sheets to copy a cell, paste it in another, then, ideally, clear the first cell. I have the copy and paste function working, but when it runs the clear on the copied field, it's also clearing the cell that it was pasted in.

Here is my code:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");

function copyAllInRange(){
  var copyRange = sheet.getRange("K3");
  var pasteRange = sheet.getRange("K2");
  
  copyRange.copyTo(pasteRange);
  copyRange.clear();
};

var run = copyAllInRange()

What I'm going for is:

Copy contents of K3
Paste contents in K2
Clear K3

What's happening is when clearing K3, it's also clearing K2.

Does this answer your question? stackoverflow.com/questions/41175326 — TheMaster
@TheMaster I think he is just calling copyAllInRange() from his script editor. The rest of the code is not executed. — soMario
@Marios Test and see. All globals will be executed( function will be executed twice). Even OP says it is clearing as intended. Encapsulation is good and definitely should be done, but That won't fix OP's issue because it is not the cause,IMO. Edit: I'm stumped. OP marked it as working :| — TheMaster
@TheMaster I am done editing my answer. Now it is clear I think. OP accepted the "wrong" explanation and the bad answer. I hope he reads again my answer to know why this happened. — soMario

soMario soMario · Accepted Answer · 2020-09-04T15:50:58

Issues:

In your code var run = copyAllInRange() is a global variable. When that is declared it simply executes the copyAllInRange() function for the first time.
Then, from the script editor, you also manually execute copyAllInRange(), therefore you execute it twice. The second time you execute this function, K3 has already been cleared by the first execution, namely you paste the empty cell K3 to K2 and as a result both are being cleared.

Solution:

Try this:

  function copyAllInRange(){
  
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Sheet1");
    var copyRange = sheet.getRange("K3");
    var pasteRange = sheet.getRange("K2");
  
    copyRange.copyTo(pasteRange);
    copyRange.clear();
}

Google Script Copy/Paste/Clear Removing Pasted Cell

1 Answers

Issues:

Solution: