copyTo fails after several copies

Question

I have a Google Spreadsheet where we track the lifecycle of servers in our network, there are over 25K, this number is dynamic as servers are added, decommissioned, etc. There are several project managers who manage different sets of servers. I’ve set the sheet up so each PM has their own sheet and they have the permissions to edit that sheet. All Sheets have the same format; headers, columns, data validation, etc. I want a Master Sheet which will combine each of the individual sheets and keep the formatting, data validation, etc if the individual sheets. From this master sheet I build my dashboard.

I have tried using the .setValues method but that does not copy the formatting, and is quite slow. So, I tried using .copyTo and it seemed to work fine until I got to the fourth sheet (this varies depending on the data) and then I get an error message, “Exception: The coordinates of the target range are outside the dimensions of the sheet.” and the script bombs out.

I assume that once I reach the default size of the spreadsheet, copyTo fails. If i look at the target sheet there is data from the sheets copied before the error.

I found this comment in the copyTo documentation, "If the destination is larger or smaller than the source range then the source is repeated or truncated accordingly."

So, does anyone have any suggestions on how I can get past this roadblock?

I’ve included my script below.

function copySheets(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  let targetSheet= ss.getSheetByName('test') || ss.insertSheet('test');
  targetSheet.getRange('A1:AG').clearContent(); // If there is od data clear it out
  let last = 0;
  var allSheets =   
['Sheet1','Sheet1','Sheet2','Sheet3',   'Sheet4','Sheet5','Sheet6','Sheet7'];
  for (var j = 0; j<allSheets.length ; j++){    // loop through each sheet
    let sheet = ss.getSheetByName(allSheets[j])
    let sourceRange = "A5:AG"; // first 5 rows are headers
    if(j == 0){
    // set the range to include headers for 1st 
        sourceRange = "A1:AG"; 
      last = 1;
      };
    let targetRange = "A"+last+":AG"; //set starting range for copyTo
    sheet.getRange(sourceRange).copyTo(targetSheet.getRange(targetRange), SpreadsheetApp.CopyPasteType.PASTE_NORMAL,false);
    last = last + sheet.getLastRow(); // last row will be the starting row for next copy
 }

Answer 1

Thanks for the responses. What I did to resolve this was.

1. Caculate the number of rows which will be needed for the combined target sheet by getting the last row for each of the individual sheets and adding them together then subtracting the number of rows for each sheet's header.
2. get the last row of the existing target sheet.
3. Then if the nmber of rows needed in the target sheet is greater than the rows in the existing target sheet, insert blank that numbe of rows at the end of the target sheet prior to strating the copy.

This seems to work.

One interesting note. If I clear existing sheet, and don't add the rows as above, so the sheet is basically empty, the script alwasy fais after the 3549th copy. This must be soe knod of default size of the spreadsheet.

Answer 2

This function calculates the number of rows to add to the target so that there will be no empty rows at the bottom.

function copySheets1() {
  const numhdrs = 5;
  const ss = SpreadsheetApp.getActive();
  const tsh = ss.getSheetByName('Sheet0') || ss.insertSheet('Sheet0');
  tsh.insertRows(1,numhdrs);//just to make sure headers will fit extra rows wont matter because the data will fill them up later.
  let trg = tsh.getRange(1, 1);
  trg.clearContent();
  const shts = ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4'].map(name => ss.getSheetByName(name));
  const hrg = shts[0].getRange(1, 1, numhdrs, shts[0].getLastColumn());
  hrg.copyTo(trg);
  shts.forEach((sh, i) => {
    let tlr = tsh.getLastRow();
    let tmr = tsh.getMaxRows();
    let slr = sh.getLastRow();
    let ter = tmr - tlr;//target empty rows
    let sdr = slr - numhdrs;//data rows
    let tar = sdr - ter;//rows to add in target
    if (tar <= 0) {
      let trg = tsh.getRange(tsh.getLastRow() + 1, 1);
      let frg = sh.getRange(numhdrs + 1, 1, sh.getLastRow() - numhdrs, sh.getLastColumn());
      frg.copyTo(trg);
    } else {
      tsh.insertRowsAfter(tmr, tar);//add tar to tmr
      let trg = tsh.getRange(tsh.getLastRow() + 1, 1);
      let frg = sh.getRange(numhdrs + 1, 1, sh.getLastRow() - numhdrs, sh.getLastColumn());
      frg.copyTo(trg);
    }
  });
  SpreadsheetApp.flush();
}

I never tested it without a Sheet0 which is my target sheet. I assumed that you had a handle on that.