Search API Connector Documentation
Import Google Drive Data to Google Sheets
In this guide, we’ll walk through how to get data from the Google Drive API directly from Google Sheets with just an API key, using the API Connector add-on for Sheets.
This API is pretty useful because it lets you turn your Google Drive contents into an API. You can use Google Drive as a space to host JSON or other content, and then pull it into your spreadsheet, or get metadata about the files on your drive.
Please note that this demo will show only how to get data from publicly shared Google Drive files (getting private files requires OAuth; if there’s interest I’ll add information on that).
- Before You Begin
- Part 1: Set up your Google Cloud Project
- Part 2: Get your Google Drive API Key
- Part 3: Create your API Request URL
- Part 4: Pull Google Drive Data into Sheets
- Part 5: API Documentation
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Set Up Your Google Cloud Project
- While logged into your Google account, navigate to https://console.developers.google.com/ and click Select a project.
- A modal will appear, prompting you to choose an existing project or create a new one. You can do either, but here we’ll create a new one.
- You’ll now see a screen asking you to name your project. Click Create.
Part 2: Get Your Google Drive API Key
- From the dashboard, click Enable APIs and Services.
- Use the search box or scroll down the page to find ‘Google Drive API’. Click to select it.
- On the details page, click Enable.
- Back in the dashboard, choose Credentials from the menu.
- Click +Create Credentials > API Key
- You’ll now be presented with your API key. You can also restrict your key via this page, to prevent unauthorized use.
Part 3: Create Your Google Drive API Request URL
For this demo I first uploaded a small, public JSON file to Google Drive:
We’ll now show how to get that file into Sheets.
- API root: https://www.googleapis.com
- Endpoint: /drive/v3/files/your_file_id
- Query string: ?alt=media&key=AIza…..
Putting it together, we get the full API Request URL:
Just substitute in your own API key and file ID. You can get the file ID by opening Google Drive, right-clicking your file name, and clicking Get Link. The File ID will be the long string within the URL, shown in bold text here: https://drive.google.com/file/d/1s8tcak22l06tpDg-5yew_1S09_oD3UjG/view?usp=sharing
Part 4: Pull Google Drive API Data into Sheets
We can now enter our values into API Connector.
- Open up Google Sheets and click Extensions > API Connector > Open.
- In the Create tab, enter the Request URL we just created
- We don’t need any OAuth2 authentication so leave that as None. We don’t need any Headers either so just skip that section.
- Create a new tab and click Set current to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see your file in Google Sheets.
Part 5: API Documentation
Official API documentation: https://developers.google.com/drive/api/v3/reference