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: Get your File ID

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

To get this file's ID, open Google Drive, right-click the file name, and click 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'll now fetch that JSON file into Sheets. Substitute own file ID and API key into the request URL.

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • ApplicationCustom
    • MethodGET
    • Request URLhttps://www.googleapis.com/drive/v3/files/your_file_id?alt=media&key=your_api_key
  3. Create a new tab and click Set current to use that tab as your data destination.
  4. 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

Leave a Comment

Jump To