How to automatically refresh JSON data in Google Sheets every hour, day, week, or month using the Apipheny add-on

If you need to connect an external data source to Google Sheets and schedule it to refresh on a recurring basis, we’ve got you covered.

Using the Apipheny Google Sheets API add-on, you can not only call an API in your Google Sheet, but you can save your API request and schedule it to refresh automatically.

In this tutorial, I’ll show you how to:

  1. Connect a JSON or CSV API to Google Sheets without any code, using the Apipheny add-on
  2. Save the API request in your Google Sheet
  3. Schedule the API request to refresh automatically every hour, day, week, or month

Step 1.) Connect an API to Google Sheets using the Apipheny add-on

To connect an API to Google Sheets without coding, first install the Apipheny add-on.

You can install Apipheny by clicking on the following link, which will take you to Google’s G-Suite Marketplace. Click the “Install” button on the page: https://gsuite.google.com/marketplace/app/apipheny/966163326746

Install Apipheny
Install Apipheny in the G Suite Marketplace

Note: Apipheny costs $19-29/month. There’s a free 30 day trial included. No credit card required.

After installing Apipheny, you can open the add-on in your Google Sheet. Just open a new or existing Google Sheet and click “Add-ons” > “Apipheny“.

💡 Tip: You can create a brand new Google Sheet by typing “sheets.new” in your browser (or by clicking that link)

Once you have your Google Sheet open, locate and click on the “Add-ons” button in the menu at the top of the sheet. If you’ve installed Apipheny, you’ll see it as an option in the dropdown menu. Click “Apipheny” > “Import API” to begin the process of connecting your API to Google Sheets.

Open Apipheny add-on
Open the Apipheny add-on in your Google Sheet

Now that you’ve got Apipheny open, you can connect your first API. Just enter your API URL in the dedicated field. Add an endpoint and any parameters to the end of your URL. Add any required headers required by your API request. Then click the “Run” button at the bottom of the add-on.

In this example, we’re using the Cat Facts API, which doesn’t require any authorization or headers:

https://cat-fact.herokuapp.com/facts
Importing/connecting an API to Google Sheets using Apipheny
Importing/connecting an API to Google Sheets using Apipheny

Step 2.) Save your API Request

Now that you’ve successfully made an API request using Apipheny, you’re ready to Save and Schedule your API request.

To save your API request in your Google Sheet, just click the “Save” button at the bottom of the add-on, then enter a name for your Saved API request, and then click Save again.

Note: It’s important to make sure your API request works and doesn’t return any errors, before saving and scheduling your API request.

Save your API Request in Google Sheets
Save your API Request in Google Sheets

After you save your API request, you will be redirected to the “Manage” tab, where you will see your saved request.

You can click on the saved request to edit the request, edit the name of the Sheet that the request is tied to, enable append/overwrite mode, enable timestamp mode, or delete the saved request.

If you’re ready to schedule your API request to refresh automatically, proceed to the next step.

Step 3.) Schedule your API request to refresh automatically

Now that you’ve saved your API request in the Apipheny add-on, it’s time to schedule it so that it will update automatically every hour, day, week, or month.

To create a scheduled request, follow these steps:

  • Go to the “Schedule” tab
  • Click “Create Scheduled Request
  • Enter the “Scheduled Request Name”, choose a “Saved Request”, choose your “Frequency”, and then when you’re done click “Save”
Creating a scheduled request in Google Sheets
Creating a scheduled request in Google Sheets
Entering my scheduled request details
Entering my scheduled request details

That’s it!

If your API request is set up successfully, and you’ve successfully created a new Scheduled Request, then your API data will automatically update in your Google Sheet for the frequency you’ve chosen (hourly, daily, weekly, or monthly).

The reason that our add-on does not have faster refreshing than hourly is because Google limits add-ons to update only once an hour at most, so we are not able to implement automatic refreshing faster than every hour.

If you want to refresh faster than every hour, cosider using a workaround using the custom =Apipheny() formula. Every saved API request has it’s own =Apipheny() formula, which you will see when you edit the saved request.

So if you take that formula and you paste it in a cell of your Google Sheet, and then you create a way for that cell to automatically refresh every few minutes, then you can achieve faster refreshing. Check this post on stackoverflow for more information.

Google Sheets API Demo

Related Articles:

Related Posts