CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsarray-formulas
thanasix
thanasix

Reputation: 37

arrayformula causes "coordinates of the target range are outside the dimensions of the sheet array formula" error

I copy the data of Sheet1 to Sheet2 with the code below.

When I inserted an arrayformula(A2:A&B2:B) in Sheet2, the sript stopped working and i got the error:

coordinates of the target range are outside the dimensions of the sheet array formula

When I deleted the arrayformula, the execution of the script completed successfully.

What should i change to the code (or to the arrayformula) to get them to cooperate?

function dailyLog() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('Sheet1');
  var logSheet = ss.getSheetByName('Sheet2');
  var lastRow = logSheet.getLastRow();
  var range = sourceSheet.getDataRange();
  range.copyTo(logSheet.getRange(lastRow + 2, 1), {contentsOnly: true}); // paste under previous paste
}

Upvotes: 0

Views: 72

Answers (1)

Marios
Marios

Reputation: 27350

Issue:

The input range and the output range do not match:

Input range: range = sourceSheet.getDataRange()

Output range: logSheet.getRange(lastRow + 2, 1)

The output range is a single cell, while the input range is the full data range (therefore multiple cells).

Solution:

If the input range is dynamic it is a better idea to use getValues/setValues instead:

function dailyLog() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('Sheet1');
  var logSheet = ss.getSheetByName('Sheet2');
  var lastRow = logSheet.getLastRow();
  var values = sourceSheet.getDataRange().getValues();
  logSheet.getRange(lastRow+1,1,values.length,values[0].length).setValues(values);
}

and now the size of the data (values) is dynamically set with the help of the length function.

Reference:

Upvotes: 2

Related Questions