In this article:

How to Use an API in Google Sheets [2024 Update]

How to Use an API to Import Data in Google Sheets

1. Get the API URL and API Key

The data in many eCommerce and marketing platforms such as Salesforce and MySQL databases can be accessed via their own API. Others may require additional information such as database name, username, password, and such. 

As the data in these platforms can only be accessed by authorized users, accessing the data through the API of these platforms requires API keys. 

2. Insert Script to Access API in Google Sheets

The data that can be accessed via a platform’s API is usually in JSON format. We can process it by using the ImportJSON code below:

api to google sheets

Access ImportJSON here.

After copying the code, go back to Google Sheets. Click Extensions then select Apps Script. 

how to use api in google sheets

A new tab will load for Google Apps Script with a code area.

Apps script blank code area

Clear the code area then paste the code in it. 

Code added to apps script 

Save the script by clicking Save project.

Save project on google apps script

3. Use the Formula =ImportJSONBasicAuth("database_URL","api_key","password")

Go back to the tab where Google Sheets is loaded. To use the script to import data to Google Sheets via API, use the following formula: 

=ImportJSONBasicAuth("database_URL","api_key","password")

Where:

database_URL has the following format: https://{hostname}/admin/api/{version}/{resource}.json

Api_key is the API key to access the data

And Password is the password to access the data. 

Add it to the cell where you want to insert the data.

Pretty straightforward? Well it depends. Not all platforms work the same way! But we got you covered. Here is an alternative: Lido.app. Yep, this website you are browsing right now has its own spreadsheet. It has a built-in import data function that can help you integrate data from different platforms in just a few clicks. Scroll right to the next section to see how Lido.app works!

How to Import Data from API Using Lido.app

It will be much better if a spreadsheet has built-in import functions from different eCommerce and marketing platforms. Fortunately you don’t have to look far! We present to you Lido.app. How easy is it to use? Here are the steps:

1. Click Connect Data then Select Source

Click the Connect Data button on the upper-left corner of Lido sheet.

Lido, connect data to API

A box labeled Add Data will appear, listing the platforms you can integrate with Lido. Select the platform you want to integrate.

Add data options

2. Input Information to Connect Platform to Lido via API

The information needed to connect the platform to Lido depends on the platforms themselves, so make sure you prepare them in advance. They usually include one or more of the following: 

  • API URL
  • API Key
  • Username
  • Password

After selecting the platform, you will be asked to input such information. Add them, then click Connect with Platform.

Connect lido to platform via API

A new tab will load, where you authorize Lido to access the data in the platform you selected. Approve the request.

Allow lido access to data in your platform via API

You will be returned to the Lido tab, showing the connection success.

Lido successfully connected to platform via API

3. Select the Data to Import

You then select the data to import next. Lido automatically detects the data in the platform you connected. First, select the object and the table to import. Next, select the specific columns to import. 

Select data to add

Once done, click Add Data. The data is now imported to Lido.app!

Data added to Lido.app

…Interested in Trying Something New?

Having seen how to import data to Google Sheets via API, do you still want to use it as your main spreadsheet platform? Or do you want to try something new, something that allows you to easily import data from different platforms in just a few clicks?

Well, try Lido now!

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started