Why do I get a 500 internal server error from the Google Drive API when adding users to a Google Sheet in Python?

Question

I am writing python script using the Google Sheets apiAPI. It reads data and writes it to a new file, shares that file with a specified email and returns the id of the new file.

def read_sheet(self,spreadsheetId):
    try:
        result=self.service.spreadsheets().get(spreadsheetId=spreadsheetId,includeGridData=True,fields='namedRanges,properties,sheets').execute()
        return result
    except apiclient.errors.HttpError as e:
        traceback.print_exc()
        print(e)
        sys.exit(1)

def create_spreadsheet(self,data,email):
    try:
        newid=self.service.spreadsheets().create(body=data,fields='spreadsheetId').execute()
        newid=newid.get('spreadsheetId')
        self.give_permissions(email,newid)
        return newid
    except apiclient.errors.HttpError as e:
        traceback.print_exc()
        print(e)
        sys.exit(1)

This code works very well, but not with 100% accuracy. Sometimes I get a 500 Internal Server Error, but the file is created in my account. I found a similar Stack Overflow question (Getting 500 Error when using Google Drive API to update permissions), but it didn't help. I want to know the exact reason for this. Can anyone help?

EDIT1:

This is the exact error message

https://www.googleapis.com/drive/v3/files/349hsadfhSindfSIins-rasdfisadfOsa3OQmE/permissions?sendNotificationEmail=true&alt=json&transferOwnership=false returned "Internal Error. User message: "An internal error has occurred which prevented the sharing of these item(s): Template"">

Internal server error is flood protection. You are going to fast you should implement some kind of Exponential backoff https://en.wikipedia.org/wiki/Exponential_backoff — Linda Lawton - DaImTo, Aug 12 '16 at 08:36
Sorry I am not a python expert. Try Googling for it I found this http://stackoverflow.com/q/27438273/1841839 — Linda Lawton - DaImTo, Aug 12 '16 at 09:42

score 0 · Answer 1 · edited May 23 '17 at 11:53

As hinted to above in DaimTo's comment, the error is due to Google Drive still processing the create request while you're trying to add the permission to share the (new) file with. Remember, when you add a file to Drive, Google's servers are still working on the file-create as well as making it accessible globally. Once the flurry of activity settles down, then adding additional users to the document shouldn't be a problem.

You can see from this Drive API documentation page a description of the (500) error you received as well as the recommended course of action which is to implement exponential backoff, which is really just saying you should pause a bit before trying again & extending that delay each time you get the same error. He also pointed to another SO Q&A which you can look at. Another resource is this descriptive blog post. If you don't want to implement it yourself, you can try the retrying or backoff packages.

NOTE: You didn't show us all your code, but I changed the title of this question to more accurately reflect that you're using the Drive API for adding the permissions. While you've used the Sheets API to create the Sheet with, realize that you can just do this all with the Drive API (and not use the Sheets API at all unless you're doing spreadsheet-oriented operations. The Drive API is for all file-related operations like sharing, copying, import/export, etc.)

Bottom line is that you can create Sheets using either API, but if you're not doing anything else with the Sheets API, why bother making your app more complex? If you want to see how to create Sheets with both APIs, there's a short segment in my blog post that covers this... you'll see that they're nearly identical but using the Drive API does require one more thing, the MIMEtype.

If you want to learn more about both APIs, see this answer I gave to a related question that features additional learning resources I've created for both Drive and Sheets, most of which are Python-based.

score 0 · Answer 2 · answered Dec 27 '18 at 00:51

I guess I'm late but just in case: just add a few seconds delay between the create request and the give permissions one. For me it works making the thread sleep for 10 seconds. Try this:

def create_spreadsheet(self,data,email):
  try: 
    newid=self.service.spreadsheets().create(body=data,fields='spreadsheetId').execute()
    newid=newid.get('spreadsheetId')
    time.sleep(10)
    self.give_permissions(email,newid)
    return newid
  except apiclient.errors.HttpError as e:
    traceback.print_exc()
    print(e)
    sys.exit(1)

Why do I get a 500 internal server error from the Google Drive API when adding users to a Google Sheet in Python?

2 Answers2