Pull data from any API into Google Sheets on a schedule

Short answer: Use a free Google Apps Script that calls a JSON API, turns the results into rows and columns, and refreshes the tab every hour. It runs in your own Google account; nothing is sent to us. Setup takes 6 steps: Make a Settings tab; Say where the list is; Paste the script; Add your key safely; Fetch the data; How the columns are made.

How to set it up

1. Make a Settings tab. Open a new Google Sheet and add a tab called Settings. B1 is the API address. B2 is the name of the header the API wants your key in, such as Authorization or X-API-Key; leave it blank if it needs none.
2. Say where the list is. In B3, write where the list of records sits in the response, such as data or data.items. Leave it blank if the response is the list. B4 is the tab to write to.
3. Paste the script. Open Extensions, then Apps Script, paste in the code from our email, and save. Reload the sheet and a API menu appears.
4. Add your key safely. In the Apps Script editor, open Project Settings and add a Script property called API_KEY with your key, written the way the API expects, such as Bearer abc123. It stays out of the sheet.
5. Fetch the data. Choose API, then Fetch now, or Fetch every hour. Each run replaces the tab with fresh data.
6. How the columns are made. Each record becomes a row. Nested fields become columns such as address.city, and lists are kept as text in one cell.

Want this set up for your business? Free written scope in 20 minutes

Get the script

Good to know

• If the API answers with an error, the run stops and shows the error, and your tab keeps its last good data.
• APIs that split results across pages need one request per page; this script reads the first page.

Questions

What does this script do?

It is a free Google Apps Script that calls a JSON API, turns the results into rows and columns, and refreshes the tab every hour.

How do I set it up?

In 6 steps: 1. Make a Settings tab; 2. Say where the list is; 3. Paste the script; 4. Add your key safely; 5. Fetch the data; 6. How the columns are made. The full steps are on this page.

Is it free, and does it send my data anywhere?

It is free: enter your email on this page and we email you the script (Code.gs). It runs in your own Google account; nothing is sent to us.

Can you install it for me?

Yes. We set it up in your Google account in a short Connect session, connect it to your sheet and check that it runs.

Want it wired into QuickBooks, your CRM or your payment system? Send us a short brief.