What is an API?

The term API stands for Application Programming Interface. It allows applications to talk to one another.

What is an example of an API?

The GOOGLEFINANCE() function in Google Sheets enables you to query and use financial information from various stock exchanges in spreadsheet formulas. So, when you enter the formula =GOOGLEFINANCE("NASDAQ:TSLA"), you're querying the price of Tesla stock on the Nasdaq stock exchange in the United States.

Screenshot of a Google Sheets spreadsheet.

The GOOGLEFINANCE function uses several APIs behind the scenes to provide financial information

Whenever you use the =GOOGLEFINANCE() function in Google Sheets, a message will appear in the bottom of the sheet informing you that quotes are not sourced from all markets and that there may be some delay. At the end of that message, you'll see a link called "Disclaimer" which links to: https://www.google.com/googlefinance/disclaimer/.

If you open that page, you'll learn that Google is using several companies to obtain the data that you're able to access using the function. For example, Google says that:

  • End of day prices provided by Morningstar.

  • Corporate actions and company metadata provided by Refinitiv.

  • Intra-day data may be provided by ICE Data Services.

These companies enable Google to access the information they have through APIs. For example, Morningstar offers end of day data via the API that is described here: https://developer-beta.morningstar.com/apis/market-data/end-of-day-ohlcv.

If your company wants to access this same information from Mornigstar, you'll have to contact them to find out how you can get access to their API. You may have to pay them a fee and agree to their terms and conditions that govern your use of their API. Once they approve your request to use their API, you'll be able to query the information that Morningstar provides.

APIs in Apps Script

Apps Script supports several APIs. Here are some of them:

  • SpreadsheetApp: This API lets you read data from and write data to a Google Sheets spreadsheet. You can also use it to create custom user interfaces in Google Sheets.

  • DocumentApp: This API lets you create, edit and manage documents in Google Docs.

  • SlidesApp: This API lets you create, edit and manage presentations in Google Slides.

  • CalendarApp: This API lets you manage calendars in Google Calendar. For example, you can create events or modify existing events in your calendar.

  • MailApp: This API lets you send emails. For example, you can export your Google Sheets spreadsheet as a PDF and send it to your colleagues via email.

  • UrlFetchApp: This API lets you download (i.e. fetch) web pages or other information that is accessible via a URL.

What are some benefits of APIs?

APIs make it easy to accomplish complex tasks

Suppose you want to send an email to your colleague from Google Sheets, all you need to do is write a few lines of code. In reality, there is a LOT that happens behind the scenes to send email from one person to another. However, the developers that designed and built the MailApp API have made it super simple for you to send emails without understanding the inner workings of how email works. In other words, they've hidden the complexity. APIs make it easy to accomplish complex tasks. The API developer deals with the complexity so you don't have to.

function sendEmail() {
  var recipient = "youremail@example.com";
  var subject = "Check out Apps Script";
  var body = "I just discovered Apps Script and it's so cool!";
  MailApp.sendEmail(recipient, subject, body);
}
APIs can help ensure security

When you try running the above function, Google will ask you to authorize sending email to the recipient on your behalf. The MailApp API is designed for a specific purpose, which is sending emails, and it checks to see if you've given it permission to act on your behalf. If not, it prompts you to do so before it sends the email. Similarly, if you use the CalendarApp API, Google will ask you to approve access to your calendar before running the script. API developers can build in safeguards to ensure the appropriate security policies are enforced before any action is taken or access to data is granted.

Screenshot of an authorization dialog in Apps Script.
APIs can limit usage

Sticking with the MailApp example, Google only allows you to send a certain number of emails per day using this API. This is likely to prevent bad actors from using their API to send spam emails. The MailApp API keeps track of how many emails you've sent on a given day and it will prevent you from sending emails once you hit the allowed cap. Some businesses offer APIs that have a tiered usage model. In this model, a free tier might limit usage but the paid tiers will often allow a lot more usage.

APIs can help companies focus on functionality that is core to their business

Suppose you're working as a project manager at a factory and you want to automate the inventory report you have to send to your boss via email every Monday morning. Your goal is to automate the process of creating and emailing this report to save you a lot of time. Creating a system from scratch to create spreadsheets and send them to your boss via email could take months even if you're an experienced developer. However, since Apps Script offers powerful functionality via several APIs, you can use these APIs to achieve your core objective. It may only take you a few hours or days to write the code to automate your weekly task.

APIs can unlock new business opportunities

APIs can unlock innovation both for the creator of the API and for businesses and individuals that use the API. For example, a school teacher who is deciding whether to use Microsoft Excel or Google Sheets might decide to use Google Sheets simply because they can easily automate sending weekly email reports from the Google Sheets spreadsheet they use for grading assignments. This teacher can use the time they save every week to pursue a part-time job or do something fun. In this scenario, both the creator of the API and its users benefit from its existence.

Conclusion

An API stands for Application Programming Interface. It allows applications to talk to one another. APIs let you build powerful applications without having to write code from scratch. You can simply use APIs to achieve complex tasks via simple interfaces that these APIs offer. Apps Script has several APIs that you can use to automate processes and tasks so you can save time and money.

Thanks for reading!

Sign up to be notified when I publish new content

By signing up you agree to the Privacy Policy & Terms.