Google Sheets Error – ‘Exception: Service Spreadsheets Failed’ When Using insertCells

google sheetsgoogle-apps-script

I have a confusing problem. I'm using GAS to shift several cells down by one. Worked fine for a while, then I started getting this error message:

Exception: Service Spreadsheets failed while accessing document with id xxxxxxxxxx

Of course including some long ID associated with my spreadsheet.

But then the issue went away for reasons unknown, and now is back again. Here is my code:

function ShiftDown() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A5').insertCells(SpreadsheetApp.Dimension.ROWS);
  spreadsheet.getRange('C5').insertCells(SpreadsheetApp.Dimension.ROWS);
  spreadsheet.getRange('D5').insertCells(SpreadsheetApp.Dimension.ROWS);
  spreadsheet.getRange('G5').insertCells(SpreadsheetApp.Dimension.ROWS);
  spreadsheet.getRange('H5').insertCells(SpreadsheetApp.Dimension.ROWS);
  spreadsheet.getRange('O5').insertCells(SpreadsheetApp.Dimension.ROWS);
};

It was originally created by simply recording a macro. It's really quite simple, but I can't figure out what's going on. it's a vey small sheet, it does not have "too much data" in it at all.

What's truly odd, is that if I comment our the line that moves cell A5, the rest runs perfectly fine, no issues at all. The problem only exists with column A, no matter where in the chain of events I place it.

EDIT: To make things even stranger, I just discovered that I can change the cell from A5 to something else like A4 or A6, and it works perfectly fine. Only A5 triggers this error. It is not protected, I can't see anything "special" about it.

Best Answer

I faced the same issue, suddenly I started getting the error on the execution of the script.

Exception: Service Spreadsheets failed while accessing document with id

The issue was in the formulas that were linking to the range that was shifted.

So modifying formulas that use the problem ranges (if you run the script from the editor, it shows on which step it stacked) by including headers or one extra cell completely solved my issue with it.

In my example, I was adding new cells above row 5 with the script, and on other sheets I had my SUMIFS formulas that summed the data from row 5.

The solutions that I found:

  1. Include one extra cell in the formula, so that it would stay untouched when new cells are added there

    In my example, I changed SUMIFS formula to sum starting from row 4 (headers) instead. I always do like this, and in my previous sheet the script was working fine.

    During the development I haven't noticed how I changed these formulas to sum from row 4, and the script stopped working because of this problem that arises in Google Sheets.

  2. If the first method doesn't work for you, you can use INDIRECT function instead of the actual range.
    In this way, I could sumifs from row 5

Related Topic