"Service Spreadsheets failed" error when calling insertCells method

Question

This may be a simple issue, but I am having some trouble with a section of code.

Here is the code:

function CreateSheet() {
    
    function toTitleCase(str) {
    return str.replace(/\w\S*/g, function (txt) {
        return txt.charAt(0)
            .toUpperCase() + txt.substr(1)
            .toLowerCase();
    });
    }
    var ss = SpreadsheetApp.getActive();
    var templatesheet = ss.getSheetByName('Template');
    //var fieldName = Browser.inputBox('Field Name', 'Insert Field Name', Browser.Buttons.OK_CANCEL);
    var ui = SpreadsheetApp.getUi();
    var fieldResult = 
        ui.prompt(
        'FIELD NAME',
        'Please type in the Field Name',
            ui.ButtonSet.OK
        )
    
    var fieldName = toTitleCase(fieldResult.getResponseText());
    var acreResult = ui.prompt('ACRES',
    'Please type in the # of acres, if the # is not known just leave it blank',
    ui.ButtonSet.OK
    )
    var acres = acreResult.getResponseText();
    var url = '';
    ss.insertSheet(fieldName,3,{template: templatesheet});
    ss.getRange('B3:D3').activate();
    ss.getCurrentCell().setValue(fieldName);
    ss.getRange('E3').activate();
    ss.getCurrentCell().setValue(acres);
    url += '#gid=';
    url += ss.getSheetId();
    ss.setActiveSheet(ss.getSheetByName('Summary'));
    SpreadsheetApp.setActiveSpreadsheet(ss);
    ss.getRange('A5:J5').activate();
    ss.getRange('A5:J5').insertCells(SpreadsheetApp.Dimension.ROWS);

The last line is where it is throwing the exception message:

Service Spreadsheets failed while accessing document with id..

If I go into the sheet itself and manually insert range 'A5:J5' then run the code it will work. What am I missing here?

Answer 1

Service Spreadsheets failed while accessing document with id

is usually an error message one obtains when hitting the maximum size limit for a spreadsheet

• This limit is 5 000 000 cells, so it should not be the issue for your spreadsheet
• However, several users experienced the same problem as you and have reported it on Google's Public Issue Tracker here or here
• It is likely to be a bug and it currently being investigated by Google
• Some users reported that they experience the problem only with V8 runtime
• Try to disable it (Run > Disable new Apps Script runtime powered by V8), hopefully it will solve the issue temporarily
• Otherwise, try to find a workaround replacing the line leading to the error
• When I reproduce your script it does not error for me, this is why I can only give you some suggestions
• If insertCells gives you trouble, try insertRowAfter() or similar instead
• If the problem comes from ss.getRange('A5:J5'), try ss.getActiveSheet().getRange('A5:J5') or ss.getSheetByName('Summary').getRange('A5:J5')
• Also, try removing the line SpreadsheetApp.setActiveSpreadsheet(ss); - you already defined ss as the active spreadsheet above, there is no need to set it to active again

Answer 2

I had this same problem related to insertCells. It drove me nuts for a couple of days but I finally just solved it! In case this helps someone else, here is a link to my answer as well as a copy/paste of it to be thorough.

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.