For privacy reasons YouTube needs your permission to be loaded. For more details, please see our Privacy Policy.

Introduction

This guide takes you through integrating the ExchangeRate-API, a robust and free currency conversion API, directly into your spreadsheets. Whether you’re managing financial portfolios, tracking international sales, or simply planning a trip, our tutorial ensures you have access to real-time currency conversion rates right at your fingertips. By the end of this walkthrough, you’ll be equipped with the knowledge to set up your Google Sheet with base and target currencies and execute a custom script to fetch and apply up-to-the-minute exchange rates to your data.

Automating Currency Exchange with Google Apps Script

Google Apps Script offers a powerful platform for extending the capabilities of Google Sheets, allowing users to automate tasks, integrate external data, and much more. This tutorial focuses on leveraging Google Apps Script to integrate real-time currency conversion into your spreadsheets, simplifying the process of working with international currencies.

Setting up

Step-by-Step Guide to Currency Conversion Integration

Getting Started with ExchangeRate-API

Obtain Your API Key: The first step involves signing up for ExchangeRate-API to receive your unique API key. This key will allow your script to securely access currency exchange rates.
Setting Up Your Spreadsheet: Prepare your Google Sheet by designating columns for the base currency, the amount to convert, and the target currency. This organization will serve as the foundation for your currency conversion script.

Understanding the structure

Writing the Google Apps Script

Script Setup: Begin by accessing the Google Apps Script environment through your Google Sheet. Create a new script file to house your currency conversion code.
Defining the Conversion Function: Implement the main function, convertCurrency, which iterates through each row of your specified data range, fetching and applying conversion rates where applicable.
Fetching Conversion Rates: Utilize the getConversionRate function to construct and send a request to ExchangeRate-API using your API key. This function retrieves the conversion rate between your specified base and target currencies.
Applying Conversion Rates: For each row of data, calculate the converted amount using the retrieved rate, then update your spreadsheet to reflect these converted values in real-time.
Error Handling: Ensure your script gracefully handles any errors encountered during the API request, such as invalid currencies or API outages, to maintain the integrity of your data.

Tips and best practices

Practical Tips for Successful Integration

API Key Security: Keep your API key confidential to prevent unauthorized access to your ExchangeRate-API account.
Spreadsheet Organization: Clearly label your columns and maintain consistent data formatting to ensure the script runs smoothly.
Regular Updates: Consider setting up a time-driven trigger in Google Apps Script to automatically refresh exchange rates at regular intervals.

Conclusion

Integrating real-time currency conversion into Google Sheets via Google Apps Script and ExchangeRate-API transforms your spreadsheet into a powerful financial tool. This automation not only enhances the accuracy of international currency data but also streamlines workflows, enabling you to focus on analysis and decision-making rather than manual data entry. Follow this guide to elevate your Google Sheets projects with dynamic currency exchange capabilities.

Appscript Code

Sample Code

Copy to Clipboard

// Main function to convert currency
function convertCurrency() {
  // Access the active sheet in the Google Spreadsheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Define the range of data to process (from row 2 to the last row, covering columns B to D)
  var dataRange = sheet.getRange(2, 2, sheet.getLastRow() - 1, 3);
  // Fetch all the data from the defined range
  var data = dataRange.getValues();
  // Your API key for ExchangeRate-API
  var apiKey = 'API KEY HERE';

// Iterate over each row in the data
  data.forEach(function(row, index) {
    // Extract base currency, amount, and target currency from each row
    var baseCurrency = row[0]; // Base currency (column B)
    var amount = row[1]; // Amount to convert (column C)
    var targetCurrency = row[2]; // Target currency (column D)
    // Check if all necessary data is present
    if (baseCurrency && amount && targetCurrency) {
      // Fetch the conversion rate for the current row's currencies
      var conversionRate = getConversionRate(apiKey, baseCurrency, targetCurrency);
      // If a conversion rate is successfully retrieved
      if (conversionRate) {
        // Calculate the converted amount
        var convertedAmount = amount * conversionRate;
        // Write the converted amount to column E, formatted to 2 decimal places
        sheet.getRange(index + 2, 5).setValue(convertedAmount.toFixed(2));
      } else {
        // If conversion rate is not available, write an error message in column E
        sheet.getRange(index + 2, 5).setValue("Error in conversion");
      }
    }
  });
}

// Function to fetch the conversion rate from ExchangeRate-API
function getConversionRate(apiKey, baseCurrency, targetCurrency) {
  // Construct the API request URL
  var url = `https://v6.exchangerate-api.com/v6/${apiKey}/pair/${baseCurrency}/${targetCurrency}`;
  try {
    // Make a request to the API
    var response = UrlFetchApp.fetch(url, { 'muteHttpExceptions': true });
    // Parse the JSON response
    var json = JSON.parse(response.getContentText());
    // Check if the conversion rate is available in the response
    if (json && json.conversion_rate) {
      // Return the conversion rate
      return json.conversion_rate;
    } else {
      // Log error information if conversion rate is not available
      Logger.log('Error fetching conversion rate: ' + json.error);
      return null;
    }
  } catch (e) {
    // Log exceptions that occur during the API call
    Logger.log('Exception when calling API: ' + e.toString());
    return null;
  }
}

This blog is my canvas, where I paint with words, sharing tutorials, tips, and tidbits that have enriched my life.

I welcome you. Dive into my articles, engage with my stories, and let’s embark on this journey of discovery together.

Happy reading, and thank you for visiting ArisAzhar.com!

Aris Azhar
Knowledge Seeker

Over 8,000
Benefited from my Channel

Check out my tutorials

I hope you can join in as well!

How to Automate Currency Conversion in Google Sheets

Introduction

Setting up

Understanding the structure

Tips and best practices

Appscript Code

Introduction

Automating Currency Exchange with Google Apps Script

Setting up