Google Apps Script: Fetching Data From An External API

Published on
2 min read

While manually importing data into a Google Sheet to complete the boring chore of data restructuring, I wondered if there was any way that the initial import might be automated. After all, it would be much more efficient to link directly to an external platform to populate a spreadsheet.

Google App Scripts provides a UrlFetchApp service giving us the ability to make HTTP POST and GET requests against an API endpoint. The following code demonstrates a simple API request to a HubSpot endpoint that will return values from a Country field by performing a GET request with an authorization header.

function run() {

function apiFetch() {
  // API Endpoint options, including header options.
  var apiOptions = {
     "async": true,
     "crossDomain": true,
     "method" : "GET",
     "headers" : {
       "Authorization" : "Bearer xxx-xxx-xxxxxxx-xxxxxx-xxxxx",
       "cache-control": "no-cache"

  // Fetch contents from API endpoint.
  const apiResponse = UrlFetchApp.fetch("", apiOptions);
  // Parse response as as JSON object.
  const data = JSON.parse(apiResponse.getContentText());

  // Populate "Sheet1" with data from API.
  if (data !== null && data.options.length > 0) {
      // Select the sheet.
      const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
      const sheet = activeSheet.getSheetByName("Sheet1");

      for (let i = 0; i < data.options.length; i++) {
        const row = data.options[i];

        // Add value cells in Google sheet.
        sheet.getRange(i+1, 1).setValue(row.label);

When this script is run, a request is made to the API endpoint to return a JSON response containing a list of countries that will populate the active spreadsheet.

The Google App Script official documentation provides even more advanced options for configuring the UrlFetchAppservice to ensure you are not limited in how you make your API requests.

In such little code, we have managed to populate a Google Sheet from an external platform. I can see this being useful in a wide variety of use cases to make a Google Sheet more intelligent and reduce manual data entry.

In the future, I'd be very interested in trying out some AI-related integrations using the ChatGPT API. If I manage to think of an interesting use case, I'd definitely write a follow-up blog post.

Before you go...

If you've found this post helpful, you can buy me a coffee. It's certainly not necessary but much appreciated!

Buy Me A Coffee

Leave A Comment

If you have any questions or suggestions, feel free to leave a comment. I do get inundated with messages regarding my posts via LinkedIn and leaving a comment below is a better place to have an open discussion. Your comment will not only help others, but also myself.