Google Drive API search with "mimeType contains" query doesn't work correctly

Question

I am working with Google Drive v3 and I am trying to get all files which are related to following categories:

files which are not Google Documents (mime type doesn't start from "application/vnd.google-apps."), for example "text/plain", "image/bmp" etc.
Google Spreadsheets (mime type is "application/vnd.google-apps.spreadsheet")

I want get all these files with single request.

Files from first category can be get with following sub-query: not mimeType contains 'application/vnd.google-apps.'

Sub-query for second category: mimeType = 'application/vnd.google-apps.spreadsheet'

As result I unite these parts with or operator and then with query for parent folder id: '<parentId>' in parents and (mimeType = 'application/vnd.google-apps.spreadsheet' or not mimeType contains 'application/vnd.google-apps.')

But it doesn't work. It returns only Google Spreadsheet files. I tried to execute queries for both file categories separately and they work fine:

'<parentId>' in parents and (mimeType = 'application/vnd.google-apps.spreadsheet') returns all Google Spreadsheets

and

'<parentId>' in parents and (not mimeType contains 'application/vnd.google-apps.') returns all not-Google Drive formats. `

As experiment I checked another cases (a bit simplified):

mimeType = 'application/vnd.google-apps.spreadsheet' or mimeType = 'text/plain' - returns spreadsheets+texts, correct
mimeType = 'application/vnd.google-apps.spreadsheet' or mimeType contains 'text/plain' - returns spreadsheets only, incorrect
mimeType = 'application/vnd.google-apps.spreadsheet' or not mimeType = 'text/plain' - returns spreadsheets only, incorrect (I have images in the folder also)
mimeType contains 'text/plain' - returns texts, correct

Part of my code for files requesting (with parentId replaced, of course):

Drive.Files.List filesRequest = service
      .files()
      .list()
      .setFields("files(id, name)")
      .setQ("'<parentId>' in parents " +
              "and (mimeType = 'application/vnd.google-apps.spreadsheet' " +
                "or not mimeType contains 'application/vnd.google-apps.')");
    FileList result = filesRequest.execute();
    List<File> files = result.getFiles();

Used dependencies versions: google-api-services-drive from v3-rev162-1.23.0 to v3-rev173-1.25.0, JDK 1.8.0-91.

Please help.

Tanaike Tanaike · Accepted Answer · 2019-09-21T01:39:06

You want to retrieve all files except for Google related files except for Google Spreadsheet.
- files which are not Google Documents (mime type doesn't start from "application/vnd.google-apps."), for example "text/plain", "image/bmp" etc.
- Google Spreadsheets (mime type is "application/vnd.google-apps.spreadsheet")
You want to achieve this by a single request with one search query.

If my understanding is correct, how about this query? Please think of this as just one of several answers.

From your situation and your tested sample queries, how about the query for ignoring all mimeTypes of Google Docs except for application/vnd.google-apps.spreadsheet? All mimeTypes of Google related files can be seen at here.

Sample search query:

'<parentId>' in parents and (mimeType!='application/vnd.google-apps.audio' and mimeType!='application/vnd.google-apps.document' and mimeType!='application/vnd.google-apps.drawing' and mimeType!='application/vnd.google-apps.file' and mimeType!='application/vnd.google-apps.folder' and mimeType!='application/vnd.google-apps.form' and mimeType!='application/vnd.google-apps.fusiontable' and mimeType!='application/vnd.google-apps.map' and mimeType!='application/vnd.google-apps.photo' and mimeType!='application/vnd.google-apps.presentation' and mimeType!='application/vnd.google-apps.script' and mimeType!='application/vnd.google-apps.site' and mimeType!='application/vnd.google-apps.unknown' and mimeType!='application/vnd.google-apps.video' and mimeType!='application/vnd.google-apps.drive-sdk')

or

'<parentId>' in parents and not (mimeType='application/vnd.google-apps.audio' or mimeType='application/vnd.google-apps.document' or mimeType='application/vnd.google-apps.drawing' or mimeType='application/vnd.google-apps.file' or mimeType='application/vnd.google-apps.folder' or mimeType='application/vnd.google-apps.form' or mimeType='application/vnd.google-apps.fusiontable' or mimeType='application/vnd.google-apps.map' or mimeType='application/vnd.google-apps.photo' or mimeType='application/vnd.google-apps.presentation' or mimeType='application/vnd.google-apps.script' or mimeType='application/vnd.google-apps.site' or mimeType='application/vnd.google-apps.unknown' or mimeType='application/vnd.google-apps.video' or mimeType='application/vnd.google-apps.drive-sdk')

or

'<parentId>' in parents and not (mimeType='application/vnd.google-apps.audio' or mimeType contains 'application/vnd.google-apps.d' or mimeType contains 'application/vnd.google-apps.f' or mimeType contains 'application/vnd.google-apps.m' or mimeType contains 'application/vnd.google-apps.p' or mimeType='application/vnd.google-apps.script' or mimeType='application/vnd.google-apps.site' or mimeType='application/vnd.google-apps.unknown' or mimeType='application/vnd.google-apps.video')

Note:

If there are files more than 1000 by searching with the query, in order to retrieve all files, pageToken is required to be used. At this time, the multiple requests are required to be done. Please be careful this.

References:

If this was not the direction you want, I apologize.

Google Drive API search with "mimeType contains" query doesn't work correctly

1 Answers

Sample search query:

Note:

References: