Search API Connector Documentation

Print

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).

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Set Up Your Google Cloud Project

  1. While logged into your Google account, navigate to https://console.developers.google.com/ and click Select a project.
    google-cloud-vision-img1
  2. 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.
    google-cloud-vision-img2
  3. You’ll now see a screen asking you to name your project. Click Create.
    google-drive-create-project

Part 2: Get Your Google Drive API Key

  1. From the dashboard, click Enable APIs and Services.
    google-drive-enable-apis
  2. Use the search box or scroll down the page to find ‘Google Drive API’. Click to select it.
    google-drive-api-library
  3. On the details page, click Enable.
    google-drive-api-library-enable
  4. Back in the dashboard, choose Credentials from the menu.
    google-drive-credentials
  5. Click +Create Credentials > API Key
    google-drive-api-key
  6. You’ll now be presented with your API key. You can also restrict your key via this page, to prevent unauthorized use.
    google-drive-api-key-created

Part 3: Create Your Google Drive API Request URL

For this demo I first uploaded a small, public JSON file to Google Drive:
google-drive-demo-file

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:

https://www.googleapis.com/drive/v3/files/1s8tcak22l06tpDg-5yew_1S09_oD3UjG?alt=media&key=AIza.....

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.

  1. Open up Google Sheets and click Extensions > API Connector > Open.
  2. In the Create tab, enter the Request URL we just created
    google-drive-url
  3. We don’t need any OAuth2 authentication so leave that as None. We don’t need any Headers either so just skip that section.
  4. Create a new tab and click Set current to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see your file in Google Sheets.
    google-drive-results

Part 5: API Documentation

Official API documentation: https://developers.google.com/drive/api/v3/reference

Previous Import Google Cloud Vision API Data to Google Sheets
Next Import Google PageSpeed Insights Data to Google Sheets

Leave a Comment

Table of Contents