Google Apps Script: Fetching Data From An External API

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() {
  apiFetch();
}

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("https://api.hubapi.com/crm/v3/properties/contact/country?archived=false", 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.