CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Justin Graham
Justin Graham

Reputation: 31

"Service Spreadsheets failed" error when calling insertCells method

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?

Upvotes: 3

Views: 6162

Answers (6)

JLMosher
JLMosher

Reputation: 161

Sometimes this error is caused by a process or calculation that was triggered by an earlier part of the script that has not completed by the time you try to insert those cells.

For example, if you have a formula that is summing a column in your summary sheet, that formula may still be calculating at the time you are trying to shift the rows that the sum formula is referencing. You can force the order of operations to complete those calculations before shifting the rows by adding SpreadsheetApp.flush(); before you insert new cells.

    SpreadsheetApp.setActiveSpreadsheet(ss);
    SpreadsheetApp.flush();
    ss.getRange('A5:J5').activate();
    ss.getRange('A5:J5').insertCells(SpreadsheetApp.Dimension.ROWS);

Upvotes: 1

user2350051
user2350051

Reputation: 1

I ran into the same error message, with a sheet of 400 rows and 9 columns only. Inspired by the variouos proposals for solutions, I tried the following, and this did the trick:

  1. duplicated the "orig sheet" to "copy of sheet"
  2. deleted rows 4 to [last row] in "orig sheet" (so only 3 rows left)
  3. copied rows 4 to [last row] from "copy of sheeet" back to "orig sheet"

and the script worked fine again :-)

(advantage: I did not have to change any formulas or scripts)

Upvotes: 0

Adam Maloney
Adam Maloney

Reputation: 537

I found that a chart using the range caused this exception, remove the chart and it worked.

Upvotes: 0

JVC
JVC

Reputation: 783

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.

Upvotes: 1

Marcin Frejlich
Marcin Frejlich

Reputation: 1

I had same issue.

First, you always need to make sure the sheet you are working on is an actual 'Google SpreadSheet' and not an uploaded one (for example, .xlsx or .xls).

Then use the Save as function to save the document as a Spreadsheed; this solution worked for me.

Upvotes: 0

ziganotschka
ziganotschka

Reputation: 26796

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

Upvotes: 5

Related Questions