NetSuite SuiteQL in SuiteScript: runSuiteQL and runSuiteQLPaged Generator

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 */
define(['N/query', 'N/runtime', 'N/log'], (query, runtime, log) => {
  const SQL = "SELECT t.id, t.tranid, BUILTIN.DF(t.entity) AS customer, t.foreigntotal FROM transaction t WHERE t.type = ? AND t.trandate >= TO_DATE(?, 'YYYY-MM-DD') ORDER BY t.id";
  const PARAMS = ['CustInvc', '2026-01-01'];

  const execute = (context) => {
    const resultSet = query.runSuiteQL({
      query: SQL,
      params: PARAMS,
      customScriptId: 'netray_open_invoices_2026'
    });
    const rows = resultSet.asMappedResults();
    log.audit({ title: 'runSuiteQL rows', details: rows.length });
    rows.forEach((row) => {
      // keys are the column aliases, returned in lowercase
      log.debug({ title: 'row ' + row.id, details: JSON.stringify(row) });
    });
    log.audit({ title: 'remaining usage', details: runtime.getCurrentScript().getRemainingUsage() });
  };

  return { execute };
});

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 */
define(['N/query', 'N/runtime', 'N/log'], (query, runtime, log) => {
  const SQL = "SELECT t.id, t.tranid, BUILTIN.DF(t.entity) AS customer, t.foreigntotal FROM transaction t WHERE t.type = ? AND t.trandate >= TO_DATE(?, 'YYYY-MM-DD') ORDER BY t.id";
  const PARAMS = ['CustInvc', '2026-01-01'];
  const MIN_UNITS = 200;

  const execute = (context) => {
    const script = runtime.getCurrentScript();
    const paged = query.runSuiteQLPaged({
      query: SQL,
      params: PARAMS,
      pageSize: 1000,
      customScriptId: 'netray_open_invoices_2026'
    });
    log.audit({ title: 'total rows / pages', details: paged.count + ' / ' + paged.pageRanges.length });

    let processed = 0;
    for (const range of paged.pageRanges) {
      if (script.getRemainingUsage() < MIN_UNITS) {
        log.audit({ title: 'stopping early', details: 'page index ' + range.index + ', rows done ' + processed });
        break;
      }
      const page = paged.fetch({ index: range.index });
      page.data.asMappedResults().forEach((row) => {
        // process row here, for example row.tranid, row.customer, row.foreigntotal
        processed += 1;
      });
    }
    log.audit({ title: 'rows processed', details: processed });
  };

  return { execute };
});

/**
 * @NApiVersion 2.1
 * @NScriptType ClientScript
 */
define(['N/query'], (query) => {
  const pageInit = (context) => {
    query.runSuiteQL.promise({
      query: "SELECT t.id, t.tranid, BUILTIN.DF(t.entity) AS customer, t.foreigntotal FROM transaction t WHERE t.type = ? AND t.trandate >= TO_DATE(?, 'YYYY-MM-DD') ORDER BY t.id",
      params: ['CustInvc', '2026-01-01']
    }).then((resultSet) => {
      const rows = resultSet.asMappedResults();
      console.log('SuiteQL rows', rows.length, rows[0]);
    }).catch((e) => {
      console.error('SuiteQL failed', e.name, e.message);
    });
  };

  return { pageInit };
});