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? https://stackoverflow.com/questions/41175326 — TheMaster, Sep 04 '20 at 15:51
@TheMaster I think he is just calling `copyAllInRange()` from his script editor. The rest of the code is not executed. — Marios, Sep 04 '20 at 15:56
@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, Sep 04 '20 at 15:59
@Marios I'm stumped. OP marked it as working. I still don't see how though. — TheMaster, Sep 04 '20 at 16:03
@TheMaster OP was executing only the clear function (copyallinrange) from the script editor because that was the ONLY function available to run. — Marios, Sep 04 '20 at 16:04
@Marios Yes. So why was it clearing K2? If you explain that, you have my respect. — TheMaster, Sep 04 '20 at 16:04
@TheMaster see my updated answer. This part: `var run = copyAllInRange()` executes by accident the function as it is a global variable. So essentially this function is executed twice! — Marios, Sep 04 '20 at 16:13
@Marios Yes I understood it executes twice: as said in [my previous comment](https://stackoverflow.com/questions/63744154/google-script-copy-paste-clear-removing-pasted-cell?noredirect=1#comment112721482_63744154). Thinking again I think I understand: K3 data to K2> K3 cleared> Now empty K3 is copied again to K2. (To OP, it looks like it clears K2 as well). That's why your answer works. — TheMaster, Sep 04 '20 at 16:16
@TheMaster I fixed my answer. Function encapsulation is a stupid idea. The issue is that he calls the function twice and as a result the second time there is no value in cell K3. — Marios, Sep 04 '20 at 16:19
@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. — Marios, Sep 04 '20 at 16:23
I mean, your original solution to wrap in an outside function worked. This new one does not. — Keith Petrillo, Sep 04 '20 at 19:45

Marios · Accepted Answer · 2020-09-04T16:24:14.103

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 Answers1

Issues:

Solution: