CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Cilenco
Cilenco

Reputation: 7117

Servererror in Google App Script code

In my Google Sheet I want to copy a range from all different sheets in the document to the first page as a summary. For this I tried to do it with this code:

function listAllTasks()
{
  var startRow = 2;
  var startColum = 3;

  var rowIndex = 2;

  var document = SpreadsheetApp.getActive();
  var sheets = document.getSheets();

  // Skip first Sheet
  for(var i=1; i < sheets.length ; i++)
  {
    var sourceRange = sheets[i].getDataRange();

    var rows = sourceRange.getNumRows() - 1;
    var columns = sourceRange.getNumColumns();

    sourceRange = sheets[i].getRange(startRow, startColum, rows, columns);

    sourceRange.copyFormatToRange(sheets[0], 1, columns, rowIndex, rows);
    sourceRange.copyValuesToRange(sheets[0], 1, columns, rowIndex, rows);

    rowIndex += rows;
  }
}

The first pass from the loop works great but on the second pass it comes to a server error on line:

sourceRange.copyFormatToRange(sheets[0], 1, columns, rowIndex, rows);

The error does not give any information. It only says:

Servererror. Please try again later...

Do you have any ideas why? I know that my range is correct and if I try this with only one sheet in the document it works great. I have duplicated the sheet and then renamed it. Can that be a problem?

Upvotes: 0

Views: 100

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

You need to change your problem line

sourceRange.copyFormatToRange(sheets[0], 1, columns, rowIndex, rows);

to

sourceRange.copyValuesToRange(sheets[0], 1, columns, rowIndex, rowIndex+rows)

Adding the rowIndex to row gives you the new end row of the target range. It must change to the new end row as new data is added to sheet[0].

The same change needs to be made to the copyFormaToRange

sourceRange.copyFormatToRange(sheets[0], 1, columns, rowIndex, rowIndex+rows)

Upvotes: 1

Related Questions