CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Keith Petrillo
Keith Petrillo

Reputation: 161

Google Script Copy/Paste/Clear Removing Pasted Cell

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:

  1. Copy contents of K3
  2. Paste contents in K2
  3. Clear K3

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

Upvotes: 1

Views: 464

Answers (1)

Marios
Marios

Reputation: 27350

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();
}

Upvotes: 1

Related Questions