# 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.

```plaintext
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](https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#advanced-parameters_2) provides even more advanced options for configuring the `UrlFetchApp`service 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](https://help.openai.com/en/collections/3675931-openai-api). If I manage to think of an interesting use case, I'd definitely write a follow-up blog post.