Ramp URL sync from NetSuite

ramp_urls.js

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 * @NModuleScope SameAccount
 */

/**
 * Scheduled Script: Ramp URL Sync
 *
 * Purpose: Syncs Ramp bill URLs to NetSuite vendor bill records.
 * Searches for bills without custbodyramp_receipt_url set, queries the Ramp API
 * to find matching bills, and updates the NetSuite records with the Ramp URL.
 *
 * Parameters:
 *   custscript1 (checkbox) - When true (default), only logs actions without making changes
 *   custscript2 (integer) - Limit the number of candidate bills to process (optional)
 *   custscript3 (date) - "On Or After" date filter for candidate bills (required)
 */

define([
  "N/search",
  "N/record",
  "N/https",
  "N/runtime",
  "N/log",
  "N/encode",
  "N/format",
], function (search, record, https, runtime, log, encode, format) {
  const RAMP_TOKEN_URL = "https://api.ramp.com/developer/v1/token";
  const RAMP_BILLS_URL = "https://api.ramp.com/developer/v1/bills";
  const RAMP_CLIENT_ID_SECRET_ID = "custsecret_ramp_client_id";
  const RAMP_CLIENT_SECRET_SECRET_ID = "custsecret_ramp_client_secret";
  const RAMP_URL_FIELD = "custbodyramp_receipt_url";
  const RAMP_BILL_URL_BASE = "https://app.ramp.com/bill-pay/bills/";

  /**
   * Searches NetSuite for vendor bills that are candidates for Ramp URL sync.
   * @param {Date} onOrAfterDate - Only include bills with trandate on or after this date
   * @returns {Array} Array of {id, tranid} objects
   */
  function searchCandidateBills(onOrAfterDate) {
    const candidates = [];

    const dateString = format.format({
      value: onOrAfterDate,
      type: format.Type.DATE,
    });

    const billSearch = search.create({
      type: search.Type.VENDOR_BILL,
      filters: [
        ["mainline", "is", "T"],
        "AND",
        ["trandate", "onorafter", dateString],
        "AND",
        [RAMP_URL_FIELD, "isempty", ""],
      ],
      columns: [
        search.createColumn({ name: "internalid" }),
        search.createColumn({ name: "tranid" }),
      ],
    });

    billSearch.run().each(function (result) {
      candidates.push({
        id: result.getValue({ name: "internalid" }),
        tranid: result.getValue({ name: "tranid" }),
      });
      return true;
    });

    return candidates;
  }

  /**
   * Obtains an access token from the Ramp API using client credentials flow.
   * @returns {string} The access token
   */
  function getRampAccessToken() {
    // Create BASE-64 encoded client_id:client_secret pair
    const secStringKeyInBase64 = https.createSecureString({
      input:
        "{" +
        RAMP_CLIENT_ID_SECRET_ID +
        "}:{" +
        RAMP_CLIENT_SECRET_SECRET_ID +
        "}",
    });

    secStringKeyInBase64.convertEncoding({
      toEncoding: encode.Encoding.BASE_64,
      fromEncoding: encode.Encoding.UTF_8,
    });

    // Construct the Authorization header
    const authHeader = https.createSecureString({
      input: "Basic ",
    });

    authHeader.appendSecureString({
      secureString: secStringKeyInBase64,
      keepEncoding: true,
    });

    const response = https.post({
      url: RAMP_TOKEN_URL,
      body: "grant_type=client_credentials&scope=transactions:read bills:read",
      headers: {
        "Content-Type": "application/x-www-form-urlencoded",
        Authorization: authHeader,
      },
    });

    if (response.code !== 200) {
      throw new Error(
        "Failed to get Ramp access token. HTTP " +
          response.code +
          ": " +
          response.body,
      );
    }

    const tokenData = JSON.parse(response.body);
    const accessToken = tokenData.access_token;

    return accessToken;
  }

  /**
   * Searches Ramp API for bills by external ID.
   * @param {string} accessToken - The Bearer token for authentication
   * @param {string} invoiceNumber - The external ID to search for
   * @returns {Array} Array of matching Ramp bill objects
   */
  function searchRampBillsByinvoiceNumber(accessToken, invoiceNumber) {
    const url =
      RAMP_BILLS_URL + "?invoice_number=" + encodeURIComponent(invoiceNumber);

    const response = https.get({
      url: url,
      headers: {
        Authorization: "Bearer " + accessToken,
        "Content-Type": "application/json",
      },
    });

    if (response.code !== 200) {
      throw new Error(
        "Failed to search Ramp bills. HTTP " +
          response.code +
          ": " +
          response.body,
      );
    }

    const data = JSON.parse(response.body);
    return data.data || [];
  }

  /**
   * Finds a matching Ramp bill for a NetSuite candidate by matching remote_id.
   * @param {string} accessToken - The Bearer token for authentication
   * @param {Object} candidate - The NetSuite candidate bill
   * @returns {Object|null} Matching Ramp bill or null if no match
   */
  function findMatchingRampBill(accessToken, candidate) {
    const rampBills = searchRampBillsByinvoiceNumber(
      accessToken,
      candidate.tranid,
    );

    if (rampBills.length === 0) {
      return null;
    }

    for (let i = 0; i < rampBills.length; i++) {
      const rampBill = rampBills[i];

      if (rampBill.remote_id === candidate.id) {
        return rampBill;
      }
    }

    return null;
  }

  /**
   * Updates a NetSuite vendor bill with the Ramp URL.
   * @param {string} billId - The internal ID of the vendor bill
   * @param {string} rampUrl - The Ramp URL to set
   */
  function updateBillWithRampUrl(billId, rampUrl) {
    record.submitFields({
      type: record.Type.VENDOR_BILL,
      id: billId,
      values: {
        [RAMP_URL_FIELD]: rampUrl,
      },
    });
  }

  /**
   * Main scheduled script entry point.
   * @param {Object} context - Script context
   */
  function execute(context) {
    try {
      const scriptObj = runtime.getCurrentScript();
      const isDryRun =
        scriptObj.getParameter({ name: "custscript1" }) !== false;
      const limit = scriptObj.getParameter({ name: "custscript2" });
      const onOrAfterDate = scriptObj.getParameter({ name: "custscript3" });

      log.audit(
        "Script Start",
        "Dry run mode: " + isDryRun + ", On or after: " + onOrAfterDate + (limit ? ", Limit: " + limit : ""),
      );

      // 1. Search NetSuite for candidate bills
      const candidates = searchCandidateBills(onOrAfterDate);
      log.debug("Candidates Found", candidates.length + " bills to process");

      if (candidates.length === 0) {
        log.audit("Script Complete", "No candidate bills found");
        return;
      }

      // 2. Get Ramp access token
      const accessToken = getRampAccessToken();

      // 3. Iterate over candidates and search Ramp for each
      let updatedCount = 0;
      let noMatchCount = 0;
      const processCount = limit
        ? Math.min(limit, candidates.length)
        : candidates.length;

      for (let i = 0; i < processCount; i++) {
        const candidate = candidates[i];

        // Update progress percentage
        const percentComplete = Math.round(((i + 1) / processCount) * 100);
        scriptObj.percentComplete = percentComplete;

        const matchingRampBill = findMatchingRampBill(accessToken, candidate);

        if (!matchingRampBill) {
          log.error(
            "No Match",
            "No matching Ramp bill found for bill " +
              candidate.id +
              " (tranid: " +
              candidate.tranid +
              ")",
          );
          noMatchCount++;
        } else {
          const rampUrl = RAMP_BILL_URL_BASE + matchingRampBill.id;

          if (isDryRun) {
            log.audit(
              "Dry Run",
              "Would set bill " +
                candidate.id +
                " (tranid: " +
                candidate.tranid +
                ") URL to: " +
                rampUrl,
            );
          } else {
            updateBillWithRampUrl(candidate.id, rampUrl);
            log.audit(
              "Updated",
              "Set bill " +
                candidate.id +
                " (tranid: " +
                candidate.tranid +
                ") URL to: " +
                rampUrl,
            );
          }
          updatedCount++;
        }
      }

      log.audit(
        "Script Complete",
        "Processed: " +
          processCount +
          ", Updated: " +
          updatedCount +
          ", No Match: " +
          noMatchCount +
          (limit ? ", Limit: " + limit : "") +
          (isDryRun ? " (DRY RUN)" : ""),
      );
    } catch (error) {
      log.error(
        "Script Error",
        "Error: " + error.message + ", Stack: " + error.stack,
      );
    }
  }

  return {
    execute: execute,
  };
});