Getting Error "The coordinates of the range are outside of the dimensions of the sheet" wh-CodePudding

I am trying to run a script that clears a range on sheet 1, then copies a range from sheet 2, then pastes that range from sheet 2 into where the cleared range is in sheet 1. This will be a time-driven script that runs daily. Below is the script.

function Skai1() {
var spreadsheet = SpreadsheetApp.getActive();
var range = spreadsheet.getRange('A2509:CU26611');
range.clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('All Performance Data 2022 (Skai)'), 
true);
var range = spreadsheet.getRange('A2:CU5612');
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('All Performance Data 2021 (Skai)'), 
true);
spreadsheet.getRange('\'All Performance Data 2022 
(Skai)\'!A2:CU5612').copyTo(spreadsheet.getrange('\'All Performance Data 2021 
(Skai)\'!A2509:CU26611'),SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

CodePudding user response：

Try

function Skai1() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  var range = sh.getRange('A2509:CU26611'   sh.getLastRow());
  ... 
}

CodePudding user response：

I believe your goal is as follows.

In your current script, an error like "The coordinates of the range are outside of the dimensions of the sheet" occurs at the line of range.clear({contentsOnly: true, skipFilteredRows: true});.
You want to remove this error.

Modification points:

About the error at the line of range.clear({contentsOnly: true, skipFilteredRows: true});, in this case, it is considered that your active sheet might not have the range of A2509:CU26611.
- For example, it supposes that there is a sheet that has 5 rows and 5 columns. Under this condition, sheet.getRange('A1:Z1000').clear({ contentsOnly: true, skipFilteredRows: true }) can be worked. Because, in this case, the upper left cell of "A1" includes the existing sheet. On the other hand, when sheet.getRange('A6:Z1000').clear({ contentsOnly: true, skipFilteredRows: true }) is run, an error like The coordinates of the range are out of the size of the sheet. occurs. Because the sheet has no range of A6:Z1000.
- I thought that this might be the reason for your issue.
- As an additional information, for example, for this sample sheet, when sheet.getRange('A6:Z1000').clear({ contentsOnly: true, skipFilteredRows: true }) is used, an error occors. But, when sheet.getRange('A6:Z1000').clear() and sheet.getRange('A6:Z1000').clearContent() are used, no error occurs. And, the cells of range are created by expanding the sheet. I thought that this might be able to be used.
About spreadsheet.getRange('\'All Performance Data 2022 (Skai)\'!A2:CU5612').copyTo(spreadsheet.getrange('\'All Performance Data 2021 (Skai)\'!A2509:CU26611'),SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);, getrange of spreadsheet.getrange is required to be getRange. But, in your script, I thought that setActiveSheet might not be required to be used.

From your showing script, I thought that you wanted to copy the values from A2:CU5612 of All Performance Data 2022 (Skai) sheet to A2509 of All Performance Data 2021 (Skai).

If my understanding is correct and when these points are reflected in your script, it becomes as follows.

Modified script:

function Skai1() {
  var srcSheetName = 'All Performance Data 2022 (Skai)'; // Please set the source sheet name.
  var dstSheetName = 'All Performance Data 2021 (Skai)'; // Please set the destination sheet name.

  var spreadsheet = SpreadsheetApp.getActive();
  var srcSheet = spreadsheet.getSheetByName(srcSheetName);
  var dstSheet = spreadsheet.getSheetByName(dstSheetName);
  var clearRange = dstSheet.getRange('A2509:CU26611');
  try {
    clearRange.clear({ contentsOnly: true, skipFilteredRows: true });
  } catch (e) {
    clearRange.clearContent(); // or clearRange.clear();
  }
  srcSheet.getRange('A2:CU5612').copyTo(dstSheet.getRange('A2509'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}

When this script is run, the content of the range of "A2509:CU26611" in the destination sheet is cleared. In this case, when this range "A2509:CU26611" is outside of the sheet, the range is expanded by clearContent(). When the destination sheet has the range of "A2509:CU26611", clearRange.clear({ contentsOnly: true, skipFilteredRows: true }) is used. And, the values are copied from "A2:CU5612" of the source sheet to "A2509" of the destination sheet.
In this case, even when clearContent() is changed to clear(), no error occurs.

Note:

Please check your source and destination sheet names again.

Reference:

clearContent()