Automatically Populate a Spreadsheet via Akamai APIs

by David Sztykman

Spreadsheets are a great way to organize and visualize data, but uploading data is usually a tiresome manual process. In this blog post I’ll show you how you can automatically populate a Google sheet using Akamai’s APIs. This can be a great way to view and sort data, run pivot tables, etc. For example, you could use Akamai APIs to:

  • Create a simple spreadsheet for Edge Redirector where column A is the initial path, column B is the redirect path, and column C is the status code.
  • View Property Manager information in a spreadsheet.
  • View your billing report in spreadsheet.
  • View alerts, and generate charts around the number of alerts per months.
  • List all users and groups of an account.
  • Manage Fast DNS zone.
  • And whatever else you can think of!

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services. By using Google Script Editor, I built out a sample spreadsheet and script which will generate the Akamai open API auth token, fetch the response, and write it in a new sheet.

The spreadsheet is generic and works with any Akamai Open API call.

How to Use It

  1. Make a copy of this spreadsheet to your Google Drive.
  2. Update the spreadsheet value to use the credentials and whatever you need to generate the call.
  3. Once you’ve modified the value you can launch the call using the menu.
  4. Once the call is done it’ll be displayed on the alternate sheet:

Going Deeper In the Code

If you want to see the code you can see it using the script Editor. From the Google sheets Tools menu choose Script Editor.

This will open a new page with all the code used for this. The sample is pretty straightforward, and you can modify it to fetch specific elements in the response.

Automate the API Call

For those familiar with crontab you can actually schedule the API call to be done every N minutes even if the spreadsheet is not active or open.

  1. In the Script Editor tab click the timer icon.
  2. Then define what to trigger and how often. Here I trigger the function submitRequest every minute.

What Next?

We use this kind of thing to generate a report for customers about their property hostname, the certificate used on their origin, etc. We’re sharing this around to customers to see what use cases they come up with. If you have an interesting one, share it in the comments with a public link to the spreadsheet you’re using.

Categories: DevOps How To, No Tags, Technology

Suggested Article