2

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.

6
  • Unfortunately this one isn't so reproducible in general for me to do that. The problem already vanished once for reasons unknown, then came back for also reasons unknown. I can delete the script entirely, and re-record the macro, and the problem will also disappear. Until it comes back randomly. I have no idea what's causing it or why it pops back up. – JVC Sep 28 '20 at 22:15
  • 1
    Have you read this similar question on Stack Overflow “Service Spreadsheets failed” error when calling insertCells method. There are several "try this" suggestions for troubleshooting? Would you also give us some information about the spreadsheet (such as whether created from scratch, duplicated or converted from Excel), cell contents, number of rows and columns, number of sheets, whether this happens on other sheets in the spreadsheet, etc – Tedinoz Sep 29 '20 at 1:16
  • Yep I sure did find that, in fact it's the only reference I could find of this error specific to using insertCells. This sheet was in fact originally imported from Excel. I thought that was the reason for it, but then the problem went away and I figured I was wrong. And even now as stated in my edit, it works fine just as long as I don't try to move A5. There are 6 sheets, not a massive amount of data anywhere. In fact I just trimmed every sheet down to under 100 rows just to be safe. No change. Have no tried on another sheet, I will do so now. – JVC Sep 29 '20 at 1:22
  • OK @Tedinoz, to my surprise, it works fine on a different sheet. – JVC Sep 29 '20 at 1:24
  • 1
    I think that this question should be migrated to Stack Overflow. For one side because it was already asked and answered there and the answer with the higher score answer points to a couple of open issues on the official issue tracker. Note: It might be marked as duplicate of the referred by Tedinoz question ( Service Spreadsheets failed” error when calling insertCells method ). – Rubén Sep 29 '20 at 13:04
4

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

3

This was a REALLY confusing issue to solve, as the errors reported by the debugger did not point to the true culprit.

In another cell elsewhere on the sheet, I had this formula:

=SUMPRODUCT(LEN($A$5:$A)>0)

But since my inserted cells started with A5, this was causing the error to be thrown when I would attempt the insert, if it tried to insert ahead of A5. By removing this formula, the insert code works perfectly every time, immediately.

So if you get this error and it is not related to having a sheet that is too large etc., check to see if you have another formula somewhere that is referencing the cell being inserted on.

2
  • Well done. I suggest that you post this answer on the Stack Overflow question as well. Though any given user might not have the exact formula that you are using, the essence of the issue (formulas/commands that refer to whole columns) might be transferable. – Tedinoz Sep 30 '20 at 3:32
  • Excellent point! – JVC Sep 30 '20 at 3:35
1

This error is likely caused by calculations not completing before the script tries to shift the ranges still actively being calculated. This is why the issue may arise intermittently.

For example, if you have a number of formulas that are dependent on cells that are being shifted by the code, they will be triggered to recalculate when the cells they reference are shifted. So if you shift all the cells below 'A5' down by inserting a cell at 'A5' and formulas referencing those cells will be triggered to recalculate. If those formulas require enough time to complete, they may interfere with the script when it tries to shift other calculation dependent ranges. Then you will get the kind of error described here.

The problem may appear to come and go because of changes in network congestion or other strains on computation speeds.

Other solutions may appear to fix this issue, like expanding the range referenced so the inserted cells are inside the range instead of outside it, but they are merely incidental and the issue may arise again when there is enough strain on the calculation times of the dependent formulas in your sheet.

Instead, try forcing the calculations to complete before shifting the cell ranges referenced by your formulas by using SpreadsheetApp.flush(); This applies all pending changes to the spreadsheet before moving on to the next line of code. In the case referenced here. You may need this line of code between each insertCells() command, depending on what cell ranges your sheet formulas are referencing.

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

In my case, the spreadsheet was in .xlsx format. I just saved it as a Google spreadsheet and the exception was gone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.