I'm trying to copy a range and then paste values to the cells 2 away from it.

What am I doing wrong?

function NewQ2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('J2').activate();
  spreadsheet.getCurrentCell().setValue('0');
  spreadsheet.getRange('J3').activate();
  spreadsheet.getCurrentCell().setValue('0');
  spreadsheet.getRange('F14:G21').activate();
  spreadsheet.getRange('D14:E21').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('C25:C39').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('C25').activate();
};

I'm not a js user - I use R normally. I've only created this by recording it, so I'm somewhat lost.

I've tried searching for answers, but they all seem to be from people who haven't selected a range. I've tried doing that, but it still hasn't worked.

I'm trying to copy a range and then paste values to the cells 2 away from it.

What am I doing wrong?

function NewQ2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('J2').activate();
  spreadsheet.getCurrentCell().setValue('0');
  spreadsheet.getRange('J3').activate();
  spreadsheet.getCurrentCell().setValue('0');
  spreadsheet.getRange('F14:G21').activate();
  spreadsheet.getRange('D14:E21').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('C25:C39').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('C25').activate();
};

I'm not a js user - I use R normally. I've only created this by recording it, so I'm somewhat lost.

I've tried searching for answers, but they all seem to be from people who haven't selected a range. I've tried doing that, but it still hasn't worked.

• javascript
• google-sheets
• google-apps-script

2 Answers 2

Copying Data from a range to another range.

Your code looks correctly already, it seems that you have experienced a bug with range adapters in Google Sheet Apps Script. It will be helpful if you are going to contribute to this issue at issuetracker.google.

Since you are new with Google Sheets Apps Script I would like to suggest an alternative path on Copying Values.

Generally you can skip the activate() part of your code and straight up get values and paste it.

Sample Code.

function copyPasteValues(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var valuesToPaste = sheet.getRange("D14:E21").getValues();
sheet.getRange("F14:G21").setValues(valuesToPaste);
}

This is a baseline example of how to getValues() and setValues() to another range you might want to look more about this in case you will have another project using Apps Script.

References:

Ranges Apps Script

This does what you are trying to do. I would not normally do it this way.

function NewQ2() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  sh.getRange('J2').activate();
  sh.getCurrentCell().setValue('0');
  sh.getRange('J3').activate();
  sh.getCurrentCell().setValue('0');
  sh.getRange('D14:E21').copyTo(sh.getRange('F14:G21'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  sh.getRange('C25:C39').activate();
  sh.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  sh.getRange('C25').activate();
};