Last Updated OnJanuary 3, 2024
byAna
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
- Part 1: Set up your Google Cloud Project
- Part 2: Get your Google Drive API Key
- Part 3: Get your File ID
- 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.
![Import Google Drive API Data to Google Sheets [2024] | API Connector (1)](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw== "Import Google Drive API Data to Google Sheets [2024] | API Connector (1)")
- 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: Get your File ID
For this demo I first uploaded a small, public JSON file to Google Drive:
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.
- Open up Google Sheets and clickExtensions > API Connector > Open > Create request.
- In the request form enter the following:
- Application:
Custom - Method:
GET - Request URL:
https://www.googleapis.com/drive/v3/files/your_file_id?alt=media&key=your_api_key
- Create a new tab and clickSet currentto 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
FAQs
Writing the Script to Import Data
- We define the API URL in the url variable.
- Using UrlFetchApp. ...
- We parse the JSON response into a JavaScript object using JSON. ...
- We get the active sheet using SpreadsheetApp. ...
- Finally, we write the data to the sheet using sheet.
Data Connector is powerful, yet easy-to-use and lets you pull data from any API into your Google Sheets™ worksheet. Just enter your API key or connect via OAuth2. Data Connector is the API connector of choice!
How to get API connector in Google Sheets? ›
After installing Apipheny, open a Google Sheet. In the menu click Extensions > Apipheny > Import API to open the API Connector. If you haven't already, sign up for a developer account on your API of choice, read the API documentation, and get your API URL, endpoint, parameters, key, and headers.
How to fetch API data in Google Sheets? ›
Enable the Google Sheets API:
- Go to the Google Cloud Console (console.cloud.google.com).
- Create a new project, or select an existing one.
- In the library section, enable the Google Sheets API for your project.
- Create credentials for your project. ...
- Save the API key that's generated.
How to extract the API data
- Step 1: retrieve the API data URL.
- Step 2: create a new, blank Excel document.
- Step 3: paste the API data URL in the Excel document.
- Step 4: Enter the credentials.
- Step 5: load the API data in the Excel.
Export from any REST API to Google Sheets
- Open the Method drop-down and select the operation you want to perform. ...
- (Optional) Enter one or more parameters. ...
- (Optional) In the Output options, configure how your data will be inserted into your spreadsheet.
- Schedule a refresh for your export.
APIs are generally easier to maintain because they follow standard rules. Connectors require more specialized care and need to be updated to work with specific systems.
How much does it cost to use Google Sheets API? ›
Google Sheets' API is free to use, but usage limits apply to each user. If your app experiences heavy traffic from many users, you'll see an error message (429: Too many requests). In other words, if you exceed the number of requests, your account won't be charged for the overage.
How much is API connector Google Sheets? ›
Unlock the power of data with Apipheny, a free API connector that makes it easy to import data from or push data to any REST JSON or CSV API directly from your Google Sheets™. If playback doesn't begin shortly, try restarting your device. Your browser can't play this video.
Do you have to pay for Google Sheets API? ›
All use of the Google Sheets API is available at no additional cost.
Call REST API from Google Sheets. Sync REST API data sources and automate data refreshes within Google Sheets, streamline workflows, and most importantly - save time. Apipheny is a powerful no-code REST API connector that makes it easy to make API calls from Google Sheets.
How do I enable API in Google Sheets? ›
To enable an API for your project:
- Go to the API Console.
- From the projects list, select a project or create a new one.
- If the APIs & services page isn't already open, open the console left side menu and select APIs & services, and then select Library.
- Click the API you want to enable. ...
- Click ENABLE.
Google Sheets Query uses the Google API Query Language to retrieve and manipulate data from the Google Sheets API. For example, you want to retrieve all rows from a sheet where the value in column A is greater than 10.
How do I import JSON into Google Sheets? ›
Using Third-Party Add-ons
Third-party add-ons like ImportJSON offer a no-code solution to import JSON data into Google Sheets. Install the ImportJSON add-on from the Google Workspace Marketplace. After installation, use the =IMPORTJSON("URL") function directly in your Google Sheet, where "URL" is your JSON source.
How do I auto pull data in Google Sheets? ›
Smart Fill will detect patterns and make suggestions to help automate data entry. You can use Smart Fill for tasks such as extracting the first name from a given list of full names or finding values in a range or table.
How do I import JSON data into Google Sheets? ›
Using Third-Party Add-ons
Third-party add-ons like ImportJSON offer a no-code solution to import JSON data into Google Sheets. Install the ImportJSON add-on from the Google Workspace Marketplace. After installation, use the =IMPORTJSON("URL") function directly in your Google Sheet, where "URL" is your JSON source.
How do I import data from the Web into Google Sheets? ›
Here's how you can use the IMPORTDATA function: Open a Google Sheets document or create a new one. Select the cell where you want the imported data to appear. In that cell, enter the following formula: =IMPORTDATA(url), replacing "url" with the actual URL of the data source you want to import.
How do I automatically import data into Google Sheets? ›
Import Live Data to Google Sheets: Top Methods and Tools
- Step 1 – Install Coefficient.
- Step 2 – Import Live Data.
- Filter, sort, and limit rows to import only the data that you want into Google Sheets.
- Step 3 – Schedule Automatic Data Refreshes.
Call REST API from Google Sheets. Sync REST API data sources and automate data refreshes within Google Sheets, streamline workflows, and most importantly - save time. Apipheny is a powerful no-code REST API connector that makes it easy to make API calls from Google Sheets.
![Import Google Drive API Data to Google Sheets [2024] | API Connector (1)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-cloud-vision-img1.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (1)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (2)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-cloud-vision-img2.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (2)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (3)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-drive-create-project.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (3)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (4)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-drive-enable-apis.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (4)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (5)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-drive-api-library.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (5)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (6)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-drive-api-library-enable.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (6)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (7)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-drive-credentials.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (7)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (8)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-drive-api-key.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (8)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (9)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-drive-api-key-created.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (9)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (10)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-drive-demo-file.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (10)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (11)](https://i0.wp.com/mixedanalytics.com/blog/wp-content/uploads/google-drive-results.jpg?x90737 "Import Google Drive API Data to Google Sheets [2024] | API Connector (11)")
![Import Google Drive API Data to Google Sheets [2024] | API Connector (2024)](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mP8/h8AAvMB+NzmkbcAAAAASUVORK5CYII=)