Import Google Drive API Data to Google Sheets [2024]

Last Updated OnJanuary 3, 2024

byAna

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: 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

Import Google Drive API Data to Google Sheets [2024] | API Connector (2024)

FAQs

How do I import data from API into Google Sheets? ›

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.

Read On ›

What is the best API connector for Google Sheets? ›

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!

Discover More Details ›

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.

See Details ›

How do I get data from API to spreadsheet? ›

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.

Oct 28, 2021

Find Out More ›

How do I export API data to Google Sheets? ›

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.

Tell Me More ›

What is the difference between API and connector? ›

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.

Show Me More ›

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.

Explore More ›

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.

Show Me More ›

Can we call API from Google Sheets? ›

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.

Read The Full Story ›

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.

See Details ›

Can you query API in Google Sheets? ›

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.

Get More Info Here ›

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? ›

View Details ›

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.

Jun 16, 2023

Learn More ›

Can you do API calls in Google Sheets? ›

Discover More Details ›

Import Google Drive API Data to Google Sheets [2024] | API Connector (2024)

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 API Data into Sheets

Part 5: API Documentation

FAQs

How do I import data from API into Google Sheets? ›

Can we call API from Google Sheets? ›