How To Get Currency Data In Google Sheets

In this tutorial, you will learn how to get currency data in Google Sheets using three different methods.

We all got to know and love (more or less) spreadsheets throughout our professional lives, especially if you are working with money-related data. And when working with this data, you will sooner or later stumble upon currency conversions.

If you are looking to get currency data in google sheets, you have come to the right place! There is more than one method to get currency data in google sheets and all of them have different pros and cons. Don’t worry, we will walk you through them and show you how it’s done. Let’s get started!

TLDR;

First of all, here is a quick summary of the methods we are going to show you, so you can skip ahead to the one that is most helpful for your use case.

Using the GOOGLE FINANCE Function

The fastest and easiest way to get your currency data in GSheets is by using the GOOGLE FINANCE function. It allows you to fetch almost real-time currency data provided by Google.

All you need is the right formula:

=GOOGLEFINANCE(“CURRENCY:<base currency><target currency>”)

For the base and target currency you need to use the three-letter code for the currencies, you want to convert.

For example, let’s convert US Dollar into Euros. We will therefore write:

=GOOGLEFINANCE("USDEUR")

Now, let’s look at an example, step by step.

Example: Let‘s say we have three currencies that we want to convert. Our base currency is US Dollar (column A). We want to convert them into Euro, Canadian Dollar, and British Pound Sterling (column B).

Step 1

We type the formula into the cell where we want the results to show. Therefore, we click on C2 and type =GOOGLEFINANCE("USDEUR") and press enter.

And now we have the current conversion rate for USD to EUR.

Step 2

We can now do this for the other two currencies as well. However, if you have more than three conversions to do, typing in the formula manually would take way too much time. To save a little time, we can include references.

To do so, we type the formula as follows:

=GOOGLEFINANCE("CURRENCY:"&A2&B2)

…and press enter.

Tipp: To copy the formula to the rest of the cells click and hold the little blue square in the right low corner of the cell and drag it down to the last currency in your list.

Step 3

What we now have is the conversion rates between two currencies. If you, however, want to convert money from one currency to another, for example, if you are working with prices, we will simply multiply the function above with the price we want to convert.

Let’s assume we have the following prices in US Dollars and want to convert them to Euro.

For this, we first select the cell where we want our first column to appear (B2), type the following formula, and then press enter: =GOOGLEFINANCE("CURRENCY:USDEUR")*A2 We then again use the little blue square to drag the formula down the column to get the rest of the prices.

For this, we first select the cell where we want our first column to appear (B2), type the following formula, and then press enter: =GOOGLEFINANCE("CURRENCY:USDEUR")*A2 We then again use the little blue square to drag the formula down the column to get the rest of the prices.

Using an API

Two ways to do it. Number two shows data more clearly, but you will need a script. Don’t worry, we have you covered, you will not need any programming skills, we have prepared the code you need. But first, let’s look at API method 1, where you don’t need any code.

API Method 1

This method allows you to quickly get your currency data into Google Sheets. It requires you to install an Add-on, which means no coding skills are required.

Let’s take a closer look at what to do:

Step 1: Get the API Key

For this method to work, you will need an API Key. For this example, we used CurrencyAPI.com. If you want to try out this currency conversion API, there is a free version which makes it ideal for this tutorial.

Create an account and open your dashboard. Here you can see your API Key, your remaining credits, and more general information.

What we will need for this method is the following endpoint:

Step 2: Install the Connector

Now that we have an API key, we can get started on the Google Sheets document. For this we go to Extensions > Add-ons > Get add-ons

Click into the search field, search for “API Connector”, and install it. It is free, all you need is an internet connection.

Step 3: Open the connector

After you have installed the add-on, go to Extensions again. Now you will see the API Connector at the bottom of the dropdown. Click open. A window on the right will appear. It will show us our saved requests, which for now only consist of a dem request. We will add a new request by clicking “Add new request”.

Step 4: Configuring the API Request

Enter your API Key as shown above into the “API URL” field. Select the current sheet as destination and name your request. We named it CurrencyConversion. Now click Save and then Run.

Step 5: Making it easier to read

What you will get now should look something like this. Since this is not the easiest way to work with the conversions, especially if you are working with more than one pair, like we did, it can help to switch the rows and columns.

For this, we will open another sheet and select cell A1. We will enter the following formula:

=transpose(<sheet-name>!1:2)

is hereby the name of the sheet you have your currency data in. In our example, this would simply be “sheet1”. So, our formula goes as follows:

=transpose(Sheet1!1:2)

By hitting the enter key we get the data from before transposed into a new layout which makes it easier to work with.

API Method 2

While method 1 may not be very easy on the eyes, method 2 can be adjusted to your liking.

Step 1: Get your API key

Just like in method 1, we will need an API key for this method as well. See here API method 1 > Step 1 for more information

Step 2: Create a Table

Now, what we want to do first is create a table with the data we want to fetch. For this, we will create three columns. The first column will simply tell us the amount of money we want to convert. The second column includes our currency symbols. This is the most important column since the script will only get the data, we ask for in this column. The third column will show us the conversion rate for the respective currency.

In our example this looks as follows:

• Our base currency is USD (column A). For the start, we only want to know the exchange rate for one US Dollar, so we enter a 1 into the cells.
• We want to convert USD into EUR, GBP, BTC, XRP, STD, BYR, CUC, and ALL. Therefore, in our second column (B) we enter the international symbols for the currencies we want to convert.
• The third column (C) we name Conversion Rate, this is the column that the apps script will fill for us automatically.

Step 3: Open Apps Script

To open Apps Script we will now go to Extensions > Apps Script

A new tab will open that will look like this.

Step 4: Enter the Code

You can now enter the code we have prepared for you. You can either copy and paste the code below or you can open this Google Sheet where we already have created a table and the code is integrated.

If you want to use our preprepared sheet, open the doc, click on File > Make a copy to create your own document.

The only thing you now need to do is enter your API key into the code where it says “YOUR-APIKEY”.

function coin_price() {
    const myGoogleSheetName =
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Coins')
    const currencyApiCall = {
        method: 'GET',
        uri: 'https://api.currencyapi.com/v3/latest',
        headers: { 'apikey': 'YOUR-APIKEY' },
        json: true,
        gzip: true,
    }

    let myCoinSymbols = []
    const getValues = SpreadsheetApp.getActiveSheet().getDataRange().getValues()
    for (let i = 0; i < getValues.length; i++) {
        // 1 = column B in the spreadsheet
        const coinSymbol = getValues[i][1]
        if (i > 0 && coinSymbol) {
            myCoinSymbols.push(coinSymbol)
        }
    }

    const result = UrlFetchApp.fetch(currencyApiCall.uri, currencyApiCall)
    const txt = result.getContentText()
    const jsonData = JSON.parse(txt)

    for (let i = 0; i < myCoinSymbols.length; i++) {
        const ticker = myCoinSymbols[i]
        
        const row = i + 2

        // Puts a column of current market price's in dollars into the sheet at B3.
        const currencyData = jsonData.data[ticker]
        console.log(ticker, currencyData?.value)
        myGoogleSheetName.getRange(row, 3).setValue(currencyData?.value ?? 'N/A')
    }
}

Step 5: Save and Run

To fetch the data from CurrencyAPI.com, simply click on the Save icon! first and then click on Run. A window will open the first time you want to run this. Click on Review permissions, choose your account, and then allow the script to run.

And that’s it! Our sheet is now filled with the data we need in a clear and structured way.