Connect your APIs to Google Sheets and Automate them to run every week quickly and completely free

If you are taking in data from 3rd parties, you might’ve gotten tired of constantly going to their platform and downloading data. You might’ve even discovered that this party has an API. However, after writing code to query the API, you might’ve realized that you don’t know how automate this code to run on a schedule without it being on your computer. You look online and see services that could do it for you, but charge you for doing so. This isn’t so bad, but at the same time, what if you could do it for less? If you have some coding experience and are looking for a free way to query apis INSIDE your google sheets, and schedule them to run every now and then, read on!

Paid / Semi-free Options

If you’re looking for a paid option with minimal hassle try out Zapier instead. OR, if you want a free version, and don’t mind limitations in terms of how many records (10,000) you can import check out this add on in Google Sheets. I highly recommend this option if you don’t want to code. With many APIs, there are different ways of getting the data. This means the more APIs, the more code you have to write. This can be a hassle, and sometimes it’s better to have a dedicated third party that will keep up to date with known APIs / API formats. Therefore all you have to do is learn the parameters of the API, and fill them in using their interface:

Generating your Google App Script

Otherwise, if you’re okay with coding lets get straight into it. In order to run api requests INSIDE our Google Sheet you will need Google App Script. To access the script editor go to a Google Sheet then Tools > Script Editor.

Now depending on your API, you will have different parameters to fill in. Usually they will provide you with a url to send your requests to. You can copy that into this website here to generate your App Script code: https://www.labnol.org/apps/urlfetch.html

Filling in the Parameters

In my case, my API url is https://someapi.com (I just made this up don’t try to query this api). In this API documentation (if you want to understand how to read API documentation see this) they have two parameters. The page number and the page size. The api here is paginated, meaning we have to do multiple requests in order to get all the data, as each page only contains up to 100 records. If you don’t have an API in mind try this fake api to go through this tutorial.

Now depending on how many pages you have, you can loop through them to run! Paste this into the App script editor and test it out! You should see the response code and the api request returned. The editor also has a debugger for you to use to see each and every variable during runtime.

Formatting

The thing about APIs is that there may be nested data. In order to address this we can declare the following functions inside our request function:

Using those methods we then can write the data to the current google sheet you are on using the sheet setValues() function. 1,1 indicates we start at row one and column one. We also indicate which data points we want using the header variable. To get specific data points, check out your API documentation. APIs will return responses in a json format. In the case of nested json results, take the parent key and then the child key and combine them using the period. This is because we flattened our data. Ie. if your json results are like: column_name: { subcolumn: “hello”, subcolumn2: “hello3”} access subcolumn2 by typing column_name.subcolumn2. Because api requests often do not return data if it doesn’t exist, we have to make sure that the data stays consistent in every column. Therefore before writing, we map all data to the correct keys. Hence why after flattening we do two map functions to address this.

Scheduling

If you want to schedule your script click the Alarm icon and click the Add Trigger button. This will run your scripts whenever you want!

Viola! The code here will change completely depending on what API you use. Therefore, you would have to write a new piece of code for every API. If this isn’t worth it to you, I again recommend trying out the Google add on I mentioned if you don’t plan on importing too much data at once.

Liked this article? Subscribe to me if you want get notifications when I post more related content: https://rich-gaogle.medium.com/subscribe

Not subscribed to Medium and want explore without limitations? Join now (Note this is a referral link and part of your membership will go to me if you join through this link): https://rich-gaogle.medium.com/membership

Want to learn how to manipulate the data in python? See this tutorial here: https://medium.com/swlh/set-up-google-sheets-api-in-minutes-bc92ca0a10c3