How to Update a Google Sheet With The Latest Currency Exchange Rates

Scenario: Fetches the latest exchange rate between currency pairs and updates them on a Google Sheet.

Level of difficulty: Easy

Implementation Time: Approximately 5 mins

Prerequisites: The following is required to set up this scenario:

1. A Google account for Google Sheets

Forex trader? Off-shore investor? An online shopaholic that constantly checks exchange rates to get the best bang for your buck? Whatever the case, we will show you exactly how to update a Google Sheet with the latest exchange rates for a variety of currency pairs. Ready? Let’s go.

Step 1: Add the Gmail module

First, you need to create a Google Sheet and label it. We have labelled ours ‘Currencies’. Next, add the column headers as C1, C2, Exchange Rate, Updated. Now add the currency pairs that you are interested in keeping track of:

• Now let’s go ahead and create the scenario. Navigate to the top right of your Integromat Dashboard and click on the Create a new scenario button. You can select the apps on the next page or simply skip as you will be able to choose your modules directly when building your scenario:

• You will be taken to the scenario builder which contains an empty module with a question mark and a little clock icon. First, you need to add the Google Sheets module, so click on the question mark, search for Google Sheets and choose the Search Rows module:

• The first step is to connect your Google account to Integromat. If you have an existing account connected, skip to the next step. Click on the Add button on the top right under Connection:

• Here you can edit the name of the connection or simply click continue:

• Sign in to your Google account and click the Allow button at the bottom right to grant Integromat access to your account:

• Now select your Spreadsheet and the Sheet:

• This module requires a mandatory filter. To set up the filter, select C1(A) from the dropdown menu and Exists as the basic operator. Since there will always be a value in C1(A), the bundle will always pass through the filter:

• Set the Maximum number of returned rows to 10 or more depending on how many currency pairs you have on the sheet. This will ensure that all rows are returned when the scenario is executed:

• If you click on the Advanced setting option, you will be able to determine how the values and dates should be rendered in the output:

Step 2: Retrieve the latest currency exchange rates and update the Google Sheet

The Google Sheets>Search Rows module is now configured. The next step in the scenario is to fetch the exchange rate between currencies and add them to the Google sheet. Now let’s add the currency exchange module.

• To add the currency exchange module, click the plus sign on the right side of the Google Sheets module:

• Search for currency exchange module and select the Convert an amount between currencies action module:

• Now you need to configure the currency exchange module. In the amount field, simply enter 1:

• Next, you need to map the elements containing the Input and Output currencies. Toggle the map switch on, map the C1(A) element in the Input currency field and map the C2(B) in the Output field:

• The currency exchange module is now fully configured. Finally, add the Google Sheets>Update a Row module:

• As you did in the first Google sheets module, select the Spreadsheet and Sheet. In the Row number field, map the Row number element from the Google Sheets>Search Rows module:

• Scroll down to the Values field, map the C1(A) and C2(B) from the Google Sheets module and the Exchange rate element from the Currency module in the corresponding fields:

• The date will be displayed in the Updated field. Select the Time and Date tab and map the Now element:

• If you would like the date to be displayed in a specific format, select the formatDate function, map the Now element and enter the format that you prefer. If you would like to check out the various tokens for date/time formatting, click here:

Step 3: It’s time to test

Now that all the hard work is done, the next step is to test the scenario. First, save the scenario by clicking on the Save button. To execute the scenario, simply click the Run once button:

• After the execution, you will see the number of operations in the bubbles above the modules:

• Simply click on the bubble to view the operations of the module. If you click on the bubble of the Currency module, you will notice that it has consumed 7 operations. Each operation contains an Input with data from the preceding Google Sheets module and an Output containing the amount in output currency and exchange rate from the currency module. The data is sent through to the subsequent module as an input:

• Let’s go back to the Google Sheet. You can format the Exchange Rate column to display only 2 decimals spaces by selecting the Number option under the Format tab:

• Here are the currency pairs with the latest Exchange Rates:

Another very useful use case is to update this information on various platforms like Facebook, Telegram, Twitter, etc. by adding another module to the scenario and configuring it accordingly.

• Since the scenario has executed successfully, the final thing to do is to schedule the scenario to execute at certain intervals. This scenario is scheduled to run every 15 minutes:

• Now turn on the scenario by toggling the switch at the bottom left:

Boom! There you have it. Now you can get the latest exchange rates sent directly to your Google sheet!

Happy Automating! 🙌