1

I want to get particular spreadsheet from it's sheet id with get method of Google sheets api. i am using below method to get a speadsheet.

sheets_file = service.spreadsheets().get(spreadsheetId=sheets_id).execute()

Here sheet_id is the id of sheet which i want to get. However, this is even returning the sheet if it's moved to bin. I don't want that. i only want to get sheet with specified sheet_id if it's not deleted (or if it's not moved to bin). Can anyone please tell me how to do that.

Landi logan
  • 73
  • 1
  • 7
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand `I want to get particular spreadsheet from it's sheet id` and `Here sheet_id is the id of sheet which i want to get. However, this is even returning the sheet if it's moved to bin. I don't want that. i only want to get sheet with specified sheet_id if it's not deleted (or if it's not moved to bin).`. Can I ask you about the detail of your goal? – Tanaike Feb 18 '22 at 06:17
  • actually i want to get sheet from google sheets by using google sheets api. I have sheet id. This get method is returning me sheet info (like name, id etc) which is fine. But suppose i remove the sheet from my google sheets account, then also it is returning me sheet from bin folder. I only want to get sheet info if it's not been deleted (and not moved to bin folder). – Landi logan Feb 18 '22 at 06:27
  • @Tanaike suppose i have sheet id. Then i can get information about that spreadsheet by using get method provided by google sheets api. Now suppose i have deleted that sheet (sheet whose ID i have) from my drive. Now that sheet is moved to bin. If now (after deletion) i am using this get function, it is still returning me info about same sheet. this method is accessing sheets from bin folder also. i not want sheet info if its moved to bin. so i am asking how do i have to modify above given get method so that if the sheet with mentioned sheet id is in bin folder, then it should return empty. – Landi logan Feb 18 '22 at 06:57

1 Answers1

2

What you are trying to do is out of scope for the Google sheets api, the work around would be to use the google drive api.

If you look at the documentation for Spreadshets.get

Returns the spreadsheet at the given ID. The caller must specify the spreadsheet ID.

Google sheets is just going to get you the sheet. If you send a valid sheet id its going to return it to you. Google sheets assumes that you know what you are asking for. It is not going to check if this file has been trashed or not, or what directory it resides in. As long as the file id exists and you have access to it then its going to return it to you.

workaround

If you want to check the current location of a file and check if its been trashed then you should go though the Google drive api

The files.get method will take your sheet id or file id. This method will return a file recourse this contains a property called trashed. So you will be able to see if the file has been trashed or not.

trashed boolean Whether the file has been trashed, either explicitly or from a trashed parent folder. Only the owner may trash a file. The trashed item is excluded from all files.list responses returned for any user who does not own the file. However, all users with access to the file can see the trashed item metadata in an API response. All users with access can copy, download, export, and share the file.

So the solution to your problem is to use a file.get from the google drive api to check first if the file has been trashed if it has not then you can load it with the google sheets api.

Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449
  • 1
    Thank you for your answer. Yes you suggestion solves my problem. Thanks again `response = drive_service.files().get(fileId=sheets_id, fields='*').execute()` – Landi logan Feb 18 '22 at 08:53
  • Try just: response = drive_service.files().get(fileId=sheets_id, fields='trashed').execute() by adding fields trashed it will only populate that single property. Will save you a little traffic if you need. – Linda Lawton - DaImTo Feb 18 '22 at 09:02
  • 1
    Actually i want other information of sheet also. I have to check that if sheet with particular name and id already exist. If yes then write in that sheet otherwise create new one and then write in that sheet. I have done that now after you have provided suggestion by * parameter. it will be good if you tell me how to specify multiple values for `fields` parameter. – Landi logan Feb 18 '22 at 09:44
  • Open a new question if you want to go into detail on fields. but you can just separate them by , for example 'id, name' It can be a bit tricky user the try me in the api to test it. [example](https://developers.google.com/drive/api/v3/reference/files/get?apix=true&apix_params=%7B%22fileId%22%3A%221x8blGF22Lpwup8VtxNY%22%2C%22fields%22%3A%22id%2C%20name%22%7D) – Linda Lawton - DaImTo Feb 18 '22 at 09:50
  • Please tell me one last thing. is it possible to get user email ID from which he has gave permission to access GDrive and Google sheets?. Why i want this because suppose user is using my software to transfer data to his google sheet account to a spreadsheet. Now he want to use another gmail account to create new sheet in that transfer data to that account's google sheet. It will be good (and user friendly also) if i show to user that you are currently using this email id, Is there any way to do this? – Landi logan Feb 18 '22 at 10:50
  • 1
    If you mean email address try ```drive_service.about().get(fields='user(emailAddress)')``` If you mean their google id i think you need to go though the people api to get the users Google primary key. – Linda Lawton - DaImTo Feb 18 '22 at 11:14
  • 1
    Thank you so much. If i require any other help, i will ask it as a separate new question. – Landi logan Feb 18 '22 at 11:24