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:
To easily connect an API to Google Sheets without writing any code, first install and open the Apipheny Google Sheets add-on.
Apipheny is a no-code API integrator for Google Sheets that you can use to make unlimited API requests, connect to unlimited APIs (including the QuickBooks API), save API requests, schedule API requests, and more. Click here if you want to learn more about Apipheny.
1. Install Apipheny by clicking the Install button on this page.
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 address bar.
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.
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:
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.
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.
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:
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).
Please note that the exact times of requests are randomly assigned by Google within an hour from the selected time. More info: https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers
Google says: “The time may be slightly randomized — for example, if you create a recurring 9 a.m. trigger, Apps Script chooses a time between 9 a.m. and 10 a.m., then keeps that timing consistent from day to day so that 24 hours elapse before the trigger fires again.“
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.