Google Drive to SQLite
20th February 2022
I released a new tool this week: google-drive-to-sqlite. It’s a CLI utility for fetching metadata about files in your Google Drive and writing them to a local SQLite database.
It’s pretty fun!
Here’s how to create a SQLite database of every file you’ve started in your Google Drive, including both files created in Google Docs/Sheets and files you’ve uploaded to your drive:
% pip install google-drive-to-sqlite
% google-drive-to-sqlite auth
Visit the following URL to authenticate with Google Drive
https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&...
Then return here and paste in the resulting code:
Paste code here:
# Authentication is now complete, so run:
% google-drive-to-sqlite files starred.db --starred
% ls -lah starred.db
-rw-r--r--@ 1 simon staff 40K Feb 20 14:14 starred.db
The OAuth client ID it is using hasn’t been verified by Google yet, which I think means that only the first 100 people to use it will be able to authenticate. If you need to you can work around that by creating your own client ID, as described in the README.
Having created that starred.db
file you can explore the resulting database using Datasette or Datasette Desktop:
datasette starred.db
# or if you have the Datasette Desktop macOS app installed:
open starred.db
Here’s Datasette running against one of my larger metadata collections:
Why build this?
I recently got involved with a participatory journalism project, where a team of reporters have used FOIA requests to gather a huge corpus of thousands of files. The files are in a complex folder hierarchy a Google Drive. I wanted to start getting a feel for what’s in there.
Pulling the metadata—file names, sizes, file types, file owners, creation dates—into a SQLite database felt like a great way to start understanding the size and scope of what had been collected so far.
Outside of that project, there’s something very exciting to me about being able to use Google Drive to collate all kinds of different data and then tie it into the larger Datasette and Dogsheep ecosystems. I think there’s a lot of potential here for all kinds of interesting projects.
How it works
The tool is written in Python using Click (based on my click-app template) and sqlite-utils. It works by calling the Google Drive API.
The auth
command needs to get hold of an OAuth access token scoped to make read-only calls to the user’s Google Drive contents.
This took a bit of figuring out. I wrote up what I learned in this TIL: Google OAuth for a CLI application
Notably, the end result of that flow is a JSON response containing both an access_token
and a refresh_token
.
The access token can be used to make authenticated API calls, but it expires after an hour and that expiration cannot be extended.
The refresh token lasts forever, and can be used at any time to obtain a fresh access token.
So the auth
command writes the refresh token to a file called auth.json
, then future calls to other commands use that token to retrieve a fresh access token on every run.
The most useful command is google-drive-to-sqlite files, which retrieves file metadata based on various criteria, then either writes that to a SQLite database or dumps it out as JSON or newline-delimited JSON. It does this by paginating through results from the Google Drive files list API.
The files --folder ID
option is a special case. It retrieves every nested file and subfolder starting at the specified folder. The Google Drive API doesn’t support this operation directly, so the tool instead has to recursively call directory listings on every folder until it has pulled back all of the data. See my TIL Recursively fetching metadata for all files in a Google Drive folder for more details.
This operation took over an hour for the largest folder I tested it against! So long that the access token it was using expired and I had to implement code to refresh the token in the middle of the operation.
Some other neat tricks
The download command downloads the specified file to disk:
google-drive-to-sqlite download \
0B32uDVNZfiEKLUtIT1gzYWN2NDI4SzVQYTFWWWxCWUtvVGNB
It detects the file type and uses that as the extension—in the above example, it saves the file as 0B32uDVNZfiEKLUtIT1gzYWN2NDI4SzVQYTFWWWxCWUtvVGNB.pdf
.
The export command only works against the file IDs for docs, sheets and presentations create using Google Apps. It can export to a variety of different formats:
google-drive-to-sqlite export html \
10BOHGDUYa7lBjUSo26YFCHTpgEmtXabdVFaopCTh1vU
This writes to 10BOHGDUYa7lBjUSo26YFCHTpgEmtXabdVFaopCTh1vU-export.html
.
The get command takes a URL to a Google Drive API endpoint and fetches it using a valid access token. This is a great tool for debugging and API exploration—my github-to-sqlite
tool has this too.
google-drive-to-sqlite get 'https://www.googleapis.com/drive/v3/about?fields=*'
It also knows how to paginate! Adding --paginate files
will cause it to fetch all of the subsequent pages of the API and return just the items from the "files"
key combined into a single JSON array, for example:
google-drive-to-sqlite get \
https://www.googleapis.com/drive/v3/files \
--paginate files
Exploring other APIs with the same tools
While I was building this, I realized that with just a little extra work the auth and get commands could be used to explore other Google APIs too.
If you are a developer, you can create your own OAuth credentials and enable access to other APIs using the Google Cloud console. You can then take the resulting client ID and secret, pick a scope and run the following:
google-drive-to-sqlite auth -a calendar-auth.json \
--scope 'https://www.googleapis.com/auth/calendar.readonly' \
--google-client-id '184325416553-nu5ci563v36rmj9opdl7mah786anbkrq.apps.googleusercontent.com' \
--google-client-secret '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'
calendar-auth.json
will now be a JSON file that looks something like this:
{
"google-drive-to-sqlite": {
"refresh_token": "1//...",
"google_client_id": "184325416553-nu5ci563v36rmj9opdl7mah786anbkrq.apps.googleusercontent.com",
"google_client_secret": "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",
"scope": "https://www.googleapis.com/auth/calendar.readonly"
}
}
You can now fetch your Google Calendar items by adding your email address to the following:
google-drive-to-sqlite get \
https://www.googleapis.com/calendar/v3/calendars/...@gmail.com/events \
--auth calendar-auth.json
This will output JSON to the console. For newline-delimited JSON, add --nl
.
Since we can paginate with --paginate items
, this means we can pipe the results to sqlite-utils insert and create a SQLite database of our calendar items!
google-drive-to-sqlite get \
https://www.googleapis.com/calendar/v3/calendars/...@gmail.com/events \
--auth calendar-auth.json \
--paginate items --nl \
| sqlite-utils insert calendar.db events \
- --pk id --nl --alter --replace
Maybe google-drive-to-sqlite
wasn’t the right name for this after all!
What’s next?
Google severely tightened their policies on apps that can access Google Drive a few years ago. I’m currently waiting to see if my app will make it through their verification process, see issue #15.
If it doesn’t the tool will still be usable, but users will have to jump through some extra hoops to set up their own client ID. I don’t see this as a huge concern.
I’ve started thinking about ways to import additional data from the Google Drive APIs. I’m particularly interested in the idea of creating a full-text search index in SQLite based on plain text exports of documents created in Google Docs, see issue #28.
For other short-term future plans, take a look at the project’s open issues.
More recent articles
- Storing times for human events - 27th November 2024
- Ask questions of SQLite databases and CSV/JSON files in your terminal - 25th November 2024
- Weeknotes: asynchronous LLMs, synchronous embeddings, and I kind of started a podcast - 22nd November 2024