نرخ دلار در گوگل شیت و اکسل؛ فرمول‌های آماده

یک فایل قیمت دارید با صد ردیف دلاری و یک سلول زرد بالای صفحه که «نرخ دلار» است. هر صبح عدد را دستی عوض می‌کنید، و بعدازظهر که بازار تکان می‌خورد، کل فایل با نرخ صبح حساب شده. این کار را می‌شود یک بار درست کرد و دیگر سراغش نرفت.

در این راهنما نرخ را از وب سرویس نرخ ارز نِت اَرز مستقیم داخل گوگل شیت و اکسل می‌آوریم: یک تابع سفارشی برای شیت، یک کوئری برای اکسل، و یک راه ساده‌تر برای وقتی که نمی‌خواهید درگیر کلید و IP شوید. همهٔ کدها روی endpointهای مستندشده اجرا می‌شوند.

اول: شکل داده‌ای که می‌گیرید

هر درخواست به /rates یک آرایهٔ data و یک بخش meta برمی‌گرداند. همین را باید در صفحهٔ گسترده باز کنید:

{
  "data": [
    { "code": "USD", "name": "دلار آمریکا", "unit": 1, "per_usd": 1, "is_pegged": true,
      "buy": 101900, "sell": 102900, "mid": 102400, "change_24h_percent": 0.39, "quote_currency": "IRT" },
    { "code": "TRY", "name": "لیر ترکیه", "unit": 1, "per_usd": 48.27, "is_pegged": false,
      "buy": 2111, "sell": 2133, "mid": 2122, "change_24h_percent": -0.12, "quote_currency": "IRT" }
  ],
  "meta": {
    "quote_currency": "IRT", "usd_irt": 102400, "as_of": "2026-09-05T10:20:00+03:30",
    "delayed_minutes": 15, "is_delayed": true, "plan": "free",
    "refresh_interval_minutes": 5, "source": "netarz.ir"
  }
}

اعداد این نمونه فقط برای نشان دادن شکل پاسخ‌اند. سه فیلدی که در صفحهٔ گسترده بیشتر به کارتان می‌آیند: mid برای محاسبه، as_of برای این‌که بدانید عدد مال چه لحظه‌ای است، و unit که می‌گوید قیمت برای چند واحد است. توضیح همهٔ فیلدها در مستندات نرخ‌ها آمده.

یک تصمیم کوچک ولی مهم هم همین‌جا گرفته می‌شود: کدام عدد را در محاسبه بگذارید. اگر هزینهٔ یک خرید را برآورد می‌کنید، عدد محافظه‌کارانه‌تر sell است؛ اگر گزارش تحلیلی می‌نویسید، mid بی‌طرف‌تر است. هر کدام را انتخاب کردید در کل فایل همان را نگه دارید و در سربرگ ستون بنویسید کدام است؛ فایلی که نیمی از ستون‌هایش با خرید و نیمی با فروش حساب شده، جمع‌بندی‌اش قابل دفاع نیست.

یک نکتهٔ مهم پیش از شروع: کلید در هدر می‌رود

احراز هویت این سرویس با هدر انجام می‌شود، یا Authorization: Bearer … یا X-API-Key. هیچ کلیدی از طریق پارامتر آدرس پذیرفته نمی‌شود. این یک جزئیات فنی ساده نیست؛ تکلیف کل مقاله را روشن می‌کند:

• توابعی که هدر می‌فرستند — Apps Script در شیت و Power Query در اکسل — مستقیم کار می‌کنند.
• توابعی که فقط یک آدرس می‌گیرند — IMPORTDATA و WEBSERVICE — نمی‌توانند هدر بفرستند و باید به آدرسی وصل شوند که کلید نمی‌خواهد؛ یعنی یک آدرس روی سرور خودتان.

چون فراخوانی از سرور هدر Origin ندارد، IP همان سرور باید در پنل «API نرخ ارز» به فهرست IPهای مجاز اپ اضافه شود. این قاعده و پیام‌های خطایش در شروع سریع نوشته شده است.

گوگل شیت با Apps Script

بهترین حالت برای شیت. از منوی «افزونه‌ها ← Apps Script» این کد را بگذارید. کلید را در «Script Properties» ذخیره می‌کنیم تا داخل سلول‌ها نیفتد و با اشتراک‌گذاری فایل لو نرود:

const FX_API = "https://netarz.ir/api/fx/v1";

function fxKey_() {
  return PropertiesService.getScriptProperties().getProperty("NETARZ_FX_KEY");
}

function fxBoard_() {
  const cache = CacheService.getScriptCache();
  const hit = cache.get("fx_board");
  if (hit) return JSON.parse(hit);

  const res = UrlFetchApp.fetch(`${FX_API}/rates`, {
    headers: { Authorization: "Bearer " + fxKey_() },
    muteHttpExceptions: true,
  });

  if (res.getResponseCode() !== 200) {
    throw new Error("FX " + res.getResponseCode() + ": " + res.getContentText());
  }

  const body = JSON.parse(res.getContentText());
  cache.put("fx_board", JSON.stringify(body), 300);   // five minutes
  return body;
}

/**
 * نرخ یک ارز به تومان.
 * @param {string} code کد سه‌حرفی، مثل USD
 * @param {string} side mid یا buy یا sell
 * @customfunction
 */
function NETARZ_RATE(code, side) {
  const row = fxBoard_().data.find((r) => r.code === String(code).toUpperCase());
  if (!row) throw new Error("ارز ناشناخته: " + code);
  return row[side || "mid"];
}

/** زمان نرخی که آخرین بار گرفته شده. @customfunction */
function NETARZ_ASOF() {
  return new Date(fxBoard_().meta.as_of);
}

/** کل تابلو را از سلول جاری به پایین می‌نویسد. @customfunction */
function NETARZ_BOARD() {
  const rows = fxBoard_().data.map((r) => [r.code, r.name, r.unit, r.buy, r.sell, r.change_24h_percent]);
  return [["کد", "نام", "واحد", "خرید", "فروش", "تغییر ۲۴ ساعته"]].concat(rows);
}

حالا در هر سلولی =NETARZ_RATE("USD") یا =NETARZ_RATE("TRY";"sell") بنویسید و ستون دلاری‌تان را در همان ضرب کنید. =NETARZ_BOARD() هم کل تابلو را می‌ریزد. کش پنج‌دقیقه‌ای باعث می‌شود صد سلول، صد درخواست نفرستند.

دو نکتهٔ عملی: اول، اجرای Apps Script از سرورهای گوگل انجام می‌شود، پس آن IP باید در فهرست مجاز اپ باشد؛ با یک اجرای ساده می‌توانید IP خروجی را ببینید و همان را در پنل ثبت کنید:

function showEgressIp() {
  Logger.log(UrlFetchApp.fetch("https://api.ipify.org").getContentText());
}

دوم، توابع سفارشی در شیت خودکار و مکرر اجرا می‌شوند. اگر فایل بزرگی دارید، به‌جای صد بار NETARZ_RATE، یک بار نرخ را در یک سلول بگیرید و بقیهٔ فرمول‌ها به همان سلول ارجاع بدهند.

گوگل شیت با IMPORTDATA

IMPORTDATA هدر نمی‌فرستد، پس نمی‌تواند مستقیم به این سرویس وصل شود. اما اگر همان فایل کوچک PHP را که در نمایش نرخ ارز زنده در سایت نوشتیم روی سرور خودتان دارید، کافی است خروجی‌اش را CSV کنید:

<?php
// rates.csv.php — a header-free URL your own sheet can read
require __DIR__ . '/rate.php';           // the cached board from the other guide
$board = json_decode(file_get_contents(__DIR__ . '/fx-cache.json'), true);

header('Content-Type: text/csv; charset=utf-8');
echo "code,name,unit,buy,sell,change\n";
foreach ($board['data'] as $r) {
    echo "{$r['code']},{$r['name']},{$r['unit']},{$r['buy']},{$r['sell']},{$r['change_24h_percent']}\n";
}

و در شیت: =IMPORTDATA("https://example.com/rates.csv.php"). با همین یک خط، کلید هرگز از سرور شما بیرون نمی‌رود و فایل شیت را هم می‌توانید با خیال آسوده‌تری به‌اشتراک بگذارید. توجه کنید که گوگل خروجی IMPORTDATA را خودش هم کش می‌کند، پس تازه‌شدنش دقیقاً لحظه‌ای نیست.

اکسل با Power Query

در اکسل، مسیر «Data ← Get Data ← From Other Sources ← Blank Query» و بعد «Advanced Editor». این کوئری هدر می‌فرستد و جدول را می‌سازد:

let
    Key      = "fx-ntz-v1-...",
    Source   = Json.Document(
                 Web.Contents(
                   "https://netarz.ir/api/fx/v1/rates",
                   [ Query   = [ codes = "USD,EUR,AED,TRY" ],
                     Headers = [ #"Authorization" = "Bearer " & Key ] ]
                 )
               ),
    Rows     = Table.FromList(Source[data], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(
                 Rows, "Column1",
                 {"code","name","unit","buy","sell","mid","change_24h_percent"},
                 {"کد","نام","واحد","خرید","فروش","میانگین","تغییر"}
               ),
    Typed    = Table.TransformColumnTypes(
                 Expanded,
                 {{"خرید", Int64.Type}, {"فروش", Int64.Type}, {"میانگین", Int64.Type}, {"تغییر", type number}}
               )
in
    Typed

بعد از ساخت، روی کوئری راست‌کلیک و «Properties» را بزنید تا «Refresh every N minutes» را روشن کنید؛ پنج دقیقه عدد معقولی است. IP همان رایانه یا سروری که اکسل روی آن اجرا می‌شود باید در فهرست IPهای مجاز اپ باشد.

اگر اینترنت خانگی‌تان IP ثابت ندارد، به‌جای دنبال کردن IP، همان آدرس CSV روی سرور خودتان را در Power Query بخوانید: Csv.Document(Web.Contents("https://example.com/rates.csv.php")).

اکسل با WEBSERVICE و FILTERXML

تابع WEBSERVICE هم هدر نمی‌فرستد و خروجی JSON را هم نمی‌فهمد؛ فقط متن می‌گیرد و FILTERXML فقط XML را می‌خواند. پس برای این مسیر هم باید یک آدرس ساده روی سرور خودتان بسازید که مقدار خام را برگرداند:

=WEBSERVICE("https://example.com/rate.php?code=USD&side=mid") * B2

این ساده‌ترین حالت است و برای یک سلول «نرخ روز» کافی. محدودیتش را هم بدانید: WEBSERVICE در اکسل تحت وب کار نمی‌کند و در هر بار محاسبه دوباره صدا زده می‌شود، پس کش روی سرور ضروری است.

یک نمونهٔ واقعی: ستون دلاری به تومان

فرض کنید ستون B قیمت دلاری هر ردیف است و می‌خواهید ستون C معادل تومانی را با نرخ روز نشان بدهد. در گوگل شیت با همان توابع بالا:

D1:  =NETARZ_RATE("USD";"sell")
D2:  =NETARZ_ASOF()
C2:  =ROUND(B2 * $D$1; -3)

نکتهٔ کلیدی این است که نرخ فقط در یک سلول گرفته می‌شود و بقیهٔ ردیف‌ها به همان ارجاع می‌دهند. اگر در هر ردیف تابع را صدا بزنید، هم فایل کند می‌شود و هم ممکن است دو ردیف با دو نرخ متفاوت حساب شوند. گرد کردن را هم یک بار و در همان ستون انجام بدهید تا جمع کل با مجموع ردیف‌ها بخواند.

در اکسل، کوئری Power Query یک جدول می‌سازد؛ یک سلول را با XLOOKUP روی ستون کد به نرخ دلار وصل کنید و بقیهٔ فرمول‌ها به همان سلول ارجاع بدهند. الگو دقیقاً همان است: یک نرخ، در یک جا، برای کل فایل.

چهار روش، کنار هم

تأخیر، سهمیه و یک هشدار

در طرح رایگان نرخ‌ها پانزده دقیقه تأخیر دارند و سقف روزانه ۳۰۰۰ درخواست است. برای یک فایل قیمت این هیچ مشکلی نیست، اما اگر فایل شما مبنای فاکتور رسمی یا تسویهٔ لحظه‌ای است، عدد پانزده دقیقه پیش را مبنا نگذارید؛ یا طرح پرو را بگیرید که تأخیر ندارد، یا زمان as_of را کنار عدد در خود فایل بنویسید تا هر کسی فایل را باز می‌کند بداند نرخ مال چه لحظه‌ای است. مقایسهٔ دو طرح در صفحهٔ طرح‌ها آمده است.

یک هشدار دربارهٔ توابع سفارشی: شیت آن‌ها را بی‌اجازه و مکرر دوباره حساب می‌کند. بدون کش، یک فایل با دویست فرمول می‌تواند در یک ساعت سهمیهٔ روز را تمام کند. کش پنج‌دقیقه‌ای که بالا نوشتیم دقیقاً برای همین است. و اگر می‌خواهید بدانید عددی که در فایلتان می‌نشیند از کدام بازار می‌آید، چهار نرخ ارز در ایران و تفاوت نرخ تتر و دلار آزاد را بخوانید. اگر برای میزبانی همان فایل کوچک PHP سرور لازم دارید، سرور مجازی چیست پایه را توضیح می‌دهد و پلن‌های آماده در دستهٔ سرور مجازی فهرست شده‌اند.

کد کامل Apps Script و کوئری اکسل

کد کامل تابع =NETARZ_RATE("USD") در google-sheets/Code.gs و کوئری اکسل در excel-power-query/netarz-rates.pq آماده است. نکته‌ای که بیشتر وقت‌ها کار را همین‌جا متوقف می‌کند: Google Apps Script و اکسلِ روی لپ‌تاپ IP خروجی ثابت ندارند، پس فراخوانی سرور با قفل IP برایشان پایدار نمی‌ماند. راه‌حل، رلهٔ کوچک relay/fx-relay.php است که روی یک سرور با IP ثابت می‌نشیند، کلید را نگه می‌دارد و خروجی CSV هم می‌دهد تا با IMPORTDATA یا Power Query بخوانید.