Google Sheets API v4 Spreadsheets.Values.Append returns ERROR 500 and 503

Question

Google Sheets API v4 This API has been giving error 500 and error 503 for 24+ hours.

The code was working previously for many months and nothing has changed.

I know 500 and 503 are supposed to be internal errors, but from searches it seems that they are returned in obscure cases that might not be well documented that users can work around.

The issues is not related to rate-limiting. My gut feel was related to an auth token expiring (as nothing in the code had changed), but I tried refreshing the auth token and still get the issue.

I don't see any issues on Google's status/uptime pages.

The response from the sheets.spreadsheets.values.append API is usually:

{
  "code": 503,
  "errors": [
    {
      "message": "The service is currently unavailable.",
      "domain": "global",
      "reason": "backendError"
    }
  ]
}

But sometimes also:

{
  "code": 500,
  "errors": [
    {
      "message": "Internal error encountered.",
      "domain": "global",
      "reason": "backendError"
    }
  ]
}

Example request payload, which includes an actual sheet ID that repros this if there's a Googler available to try it repro on their end:

{
  "spreadsheetId": "1_P5IR4OLbYd27L9m184R37L_PP2drCk6PSJndIlEhms",
  "range": "Incoming!A4",
  "valueInputOption": "USER_ENTERED",
  "insertDataOption": "INSERT_ROWS",
  "resource": {
    "values": [
      [
        "=HYPERLINK(\"https://url/\", \"Blah\")",
        "6/13 22:18",
        "=IF(AND(INDIRECT(\"R[0]C[3]\",false)<>\"\",INDIRECT(\"R[0]C[9]\",false)=\"\"),((INDIRECT(\"R[0]C[-1]\",false)+C$3/24)-NOW())*24,)",
        "dv1",
        "testdoc",
        "170613_006_0400PM.MP3",
        "00:40:00.000",
        "",
        "",
        "",
        "",
        "",
        "=IF(INDIRECT(\"R[0]C[-1]\",false)<>\"\",IFERROR(INDIRECT(\"R[0]C[-6]\",false)/INDIRECT(\"R[0]C[-1]\",false),\"---\"),)",
        "",
        "",
        "",
        "",
        "",
        "=IF(INDIRECT(\"R[0]C[1]\",false)=\"\",(INDIRECT(\"R[0]C[-17]\",false)+S$3/24-NOW())*24,)",
        ""
      ]
    ]
  },
  "auth": {
    "transporter": {},
    "clientId_": "anonymizied.apps.googleusercontent.com",
    "clientSecret_": "anonymizied",
    "redirectUri_": "urn:ietf:wg:oauth:2.0:oob",
    "opts": {},
    "credentials": {
      "access_token": "anonymizied",
      "refresh_token": "anonymizied",
      "token_type": "Bearer",
      "expiry_date": 1502144766732
    }
  }
}

Thanks for the details, John. I've found the exception in our backends. Can you try to paste the "shape" of the data in the "Incoming" sheet here? e.g, what rows/columns have data, are some of them blank, etc.. ? Thanks! — Sam Berlin
I just made a repro on a public sheet: docs.google.com/spreadsheets/d/… I called it at about 1:11 and 1:14 PM PST, both got error 503. The only difference from the original request data above is that the sheet ID is different, obviously. Thanks very much for taking a look. — John McD
Glad to hear it Sam! Thanks for taking a look. Please note the workaround below-- the Google bug is related to hidden rows on the sheet being appended to. — John McD
We've identified the problem and have a fix in the works. I'll post back here once it's released. — Sam Berlin

John McD John McD · Accepted Answer · 2017-08-08T20:38:50

I've carefully worked out this issue and a workaround. It's definitely a bug on Google's side, which seems to have been pushed to production around Aug 5th (+/- 1.5 days).

In my case, simply un-hiding a hidden row resolves the error. Hiding the row again reproduces the issue.

So if you hit this error, try un-hiding any hidden rows.

I have filed this issue with Google at https://issuetracker.google.com/64468867, but it seems they only triage public issues there every month or two.

Google Sheets API v4 Spreadsheets.Values.Append returns ERROR 500 and 503

2 Answers