Pygsheet keeps sharing new worksheet IDs with same name

Question

I'm trying to use Python to maintain a single report tied to a "title" and share then with specific people. In my code, I try to open my existing sheet report. If it fails in opening I create a new one.

My problem is, this code keeps getting a new ID everytime I do a 'x = client.open(title)', I get a new 'x.id' every time. I debugged and made sure that it was opening and not creating, but still keeps getting a new ID.

This makes it so that multiple versions of this sheet exist since I do a 'x.share' and they all have the same name and appear to have the same history. But looks different with every share. Every share appears to have their own versino because of this unique ID. Edits on previous shares does not apply to the latest share. What can I do here?

#########################
## Open/Create GSheet ###
#########################
client = pygsheets.authorize(service_account_file=credsfile)
try:
    gsheet = client.open(title)
except:
    gsheet = client.create(title)
    gsheet = client.open(title)
    gsheet.share('[email protected]', role='writer')

To test this, run code above and share it to yourself. Edit the first sheet and run the code again. Open that share. It won't have the previous edits you did, but history will show them.

Answer 1

I believe your goal and your current situation as follows.

• You want to check the existing Spreadsheet using the Spreadsheet title.
• When the Spreadsheet is existing, you want to open the existing Spreadsheet.
• When the Spreadsheet is not existing, you want to create new Spreadsheet and open it.
• You want to achieve this using pygsheets for python.
• You have already been able to use pygsheets.

Modification points:

• From client = pygsheets.authorize(service_account_file=credsfile), I confirmed that you are using pygsheets with the service account. In this case, when I consider this and My problem is, this code keeps getting a new ID everytime I do a 'x = client.open(title)', I get a new 'x.id' every time. I debugged and made sure that it was opening and not creating, but still keeps getting a new ID.. Spreadsheet of the value of title of gsheet = client.open(title) might be not existing in the Drive of the service account. If my understanding is correct, this might be the reason of your issue.
  • The Google Drive of the service account is different from the Google Drive of your Google account. When the Spreadsheet of title of gsheet = client.open(title) is existing in your Google Drive and it tries to search the Spreadsheet using the service account, the service account cannot find the Spreadsheet. In your script, by this, new Spreadsheet is created. In this case, when the Spreadsheet is shared with the email of service account, the service account can find it. I'm worry that this situation might be the reason of your issue.
• And, I thought that in order to check whether the Spreadsheet is existing, Drive API Wrapper of pygsheets can be used. In this case, try - except is not required to be used.
  • It seems that open medhod also uses the files.list method of Drive API. Ref

When above points are reflected to your script, it becomes as follows.

Modified script:

title = '###' # Spreadsheet title.
yourEmail = '###' # Your email address.
userEmail = '###' # User's email address.

client = pygsheets.authorize(service_account_file=credsfile)
try:
    gsheet = client.open(title)
except:
    gsheet = client.create(title)
    gsheet.share(yourEmail, role='writer')
    gsheet.share(userEmail, role='writer')

or

title = '###' # Spreadsheet title.
yourEmail = '###' # Your email address.
userEmail = '###' # User's email address.

client = pygsheets.authorize(service_account_file=credsfile)
res = client.drive.list(q="name='" + title + "'")
if res == []:
    gsheet = client.create(title)
    gsheet.share(yourEmail, role='writer')
    gsheet.share(userEmail, role='writer')
else:
    gsheet = client.open(title)

• By sharing the created Spreadsheet by the service account with your Google account, you can see the created Spreadsheet at "Shared with me".

Note:

• When you want to open the Spreadsheet existing in your Google Drive, at first, please share the Spreadsheet with the email of the service account. By this, in your script and above modified script, the Spreadsheet is opened and new Spreadsheet is not created.

Reference:

• list(**kwargs) of Drive API Wrapper