Automate currency conversion in Databox using Google Sheets with daily conversion rate values with Databox calculated metrics

To create a Google Script that provides conversion rates from Currency_A to Currency_B daily and appends the data to a Google Sheet, you'll need two main components:

A Google Sheet: This is where your data will be stored.
A Google Apps Script: This is the code that will perform the conversion and update the sheet.

Step 1: Create Your Google Sheet

Go to Google Sheets (sheet.new) and create a new spreadsheet.
Name your sheet something like "Currency_A to Currency_B Exchange Rates".
In the first row, add headers for your columns.
For example:
- Cell A1: Date
- Cell B1: USD to EUR Rate

Step 2: Open Google Apps Script Editor

From your Google Sheet, click on Extensions > Apps Script. This will open a new tab with the script editor.

Step 3: Write the Google Apps Script Code

In the script editor, you'll see a default Code.gs file. Replace any existing code with the following:

function recordUsdToEurExchangeRate() {
  // IMPORTANT: Replace 'YOUR_SPREADSHEET_ID' with the actual ID of your Google Sheet
  // You can find the ID in the URL of your spreadsheet.
  const spreadsheetId = 'YOUR_SPREADSHEET_ID';
  const sheetName = 'Sheet1'; // Make sure this matches your sheet's name

  const ss = SpreadsheetApp.openById(spreadsheetId);
  const sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    Logger.log('Error: Sheet not found with name: ' + sheetName + '. Please check the sheetName variable.');
    return;
  }

  // Get today's date
  const today = new Date();
  const dateFormatted = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy-MM-dd');

  // --- ExchangeRate-API.com Configuration ---
  // You MUST replace 'YOUR_API_KEY' with your actual API key from ExchangeRate-API.com
  const apiKey = 'YOUR_API_KEY';
  const apiUrl = `https://v6.exchangerate-api.com/v6/${apiKey}/latest/USD`;

  let response;
  let data;
  let usdToEurRate = 'N/A'; // Default value if fetch fails or rate isn't found

  Logger.log('Attempting to fetch exchange rate from: ' + apiUrl);

  try {
    response = UrlFetchApp.fetch(apiUrl, {muteHttpExceptions: true}); // muteHttpExceptions helps in error logging
    const responseCode = response.getResponseCode();
    const responseText = response.getContentText();

    if (responseCode !== 200) {
      Logger.log('API request failed with status code: ' + responseCode);
      Logger.log('API response body: ' + responseText);
      // Attempt to parse JSON even if not 200, in case the API sends error details in JSON
      try {
        data = JSON.parse(responseText);
        Logger.log('Parsed error response: ' + JSON.stringify(data));
      } catch (e) {
        Logger.log('Could not parse error response as JSON: ' + e.toString());
      }
    } else {
      data = JSON.parse(responseText);

      // Log the full API response for debugging
      Logger.log('Full API response data: ' + JSON.stringify(data));

      // Check if 'conversion_rates' and 'EUR' exist in the response
      if (data && data.conversion_rates && data.conversion_rates.EUR) {
        usdToEurRate = data.conversion_rates.EUR;
        Logger.log('Successfully retrieved EUR rate: ' + usdToEurRate);
      } else {
        Logger.log('Could not find EUR rate under "conversion_rates" in API response.');
        Logger.log('API response keys: ' + Object.keys(data));
        if (data.conversion_rates) {
            Logger.log('Conversion rates keys: ' + Object.keys(data.conversion_rates));
        }
      }
    }

  } catch (e) {
    Logger.log('An error occurred during API fetch or parsing: ' + e.toString());
  }

  // Append new row with date and exchange rate
  sheet.appendRow([dateFormatted, usdToEurRate]);

  Logger.log('Finished recording USD to EUR rate for ' + dateFormatted + ': ' + usdToEurRate);

Step 4: Configure the Script (Important!)

Get Your Spreadsheet ID: Go back to your Google Sheet. The spreadsheet ID is the long string of letters and numbers in the URL between /d/ and /edit. Copy this ID.
- Example URL: https://docs.google.com/spreadsheets/d/1ABCDEFG12345_HIJKLMN/edit#gid=0
- Spreadsheet ID: 1ABCDEFG12345_HIJKLMN
Paste the Spreadsheet ID: In your Apps Script code, replace 'YOUR_SPREADSHEET_ID' with the actual ID you copied.
Choose an Exchange Rate API:
- Crucial: The example code uses a placeholder API URL. You need to choose a reliable exchange rate API. Popular options include:
  - ExchangeRate-API.com: Offers a free plan with a good number of requests. You'll get an API key. The URL would look something like https://v6.exchangerate-api.com/v6/YOUR_API_KEY/latest/USD (replace YOUR_API_KEY).
  - Fixer.io: Also offers a free plan. The URL would be similar: http://data.fixer.io/api/latest?access_key=YOUR_API_KEY&base=USD&symbols=EUR.
- Sign up for an API key if required by your chosen service.
- Modify the apiUrl variable in the script to use the correct endpoint and include your API key if necessary.

Step 5: Test the Script

In the Apps Script editor, select the recordUsdToEurExchangeRate function from the dropdown menu (if it's not already selected).
Click the "Run" (▶) button.
Authorization: The first time you run it, Google will ask for authorization because the script needs to access your spreadsheet and make external API calls. Follow the prompts to authorize it.
Check your Google Sheet. You should see a new row added with today's date and the exchange rate.

Step 6: Set Up a Daily Trigger

In the Apps Script editor, on the left sidebar, click the "Triggers" (alarm clock icon).
Click "Add Trigger" button in the bottom right.
Configure the trigger as follows:
- Choose which function to run: recordUsdToEurExchangeRate
- Choose deployment to run: Head (or your latest deployment)
- Select event source: Time-driven
- Select type of time-based trigger: Day timer
- Select time of day: Choose a time when you want the script to run (e.g., between 1 AM - 2 AM).
Click "Save".

How it Works:

recordUsdToEurExchangeRate(): This is the main function that does all the work.
SpreadsheetApp.openById(spreadsheetId): Opens your specific Google Sheet.
ss.getSheetByName(sheetName): Gets the specific sheet (tab) within your spreadsheet.
new Date() and Utilities.formatDate(): Gets the current date and formats it nicely.
UrlFetchApp.fetch(apiUrl): This is the key part that makes a request to the external exchange rate API to get the latest rates.
JSON.parse(response.getContentText()): Parses the JSON response from the API.
data.rates.EUR: Accesses the EUR rate from the parsed data (this path might vary slightly depending on your chosen API).
sheet.appendRow([dateFormatted, usdToEurRate]): Adds a new row to your sheet with the date and the fetched exchange rate.
Triggers: The time-driven trigger automatically executes the recordUsdToEurExchangeRate function once every day at your specified time.

This setup will automatically update your Google Sheet daily with the USD to EUR exchange rate, allowing you to use that data in Databox, e.g., within a Data calculation.

See example Google Sheets doc