跳到正文
DEV Community· Hayrullah Kar·· 6 小時前AI 評分54

Google Sheets 佇列防止 RPA 機械人故障後重複輸入訂單

RPA Robots Retype Orders After a Crash. A Google Sheets Queue That Stops It

AI 導讀

作者以 Google Sheets 佇列和 Apps Script 管理 RPA 訂單交接,將逾時未回報的訂單轉入 CHECK_ERP 人工核查,避免直接重派。作者在 Node 模擬環境中通過 58 項測試,並確認測試捕捉到 47 次刻意引入的程式錯誤。測試未涵蓋真實 ERP、RPA 工具或已部署的 web app,文中的租約時長和 0.9 信心門檻亦只是示例,並非實測值。

正文

I ran an order-entry robot against a fake ERP with three invented purchase orders. The robot saved PO 1042, then stopped before it could report back. Ten minutes later the queue handed PO 1042 out again, the robot typed it a second time, and the queue sheet showed every row as DONE. The ERP had four orders for three purchase orders.

Nothing in that run looked broken. That is the problem with the usual way of feeding a software robot from a spreadsheet, and it is the part of RPA that the build estimate never covers.

When a robot is the right tool at all

Our RPA vs API automation guide gives the decision rule in full. The short version: if the system has an API, use it. If it can import a file, or send a report by email, use that. A robot that clicks through screens is the fallback for a system with no API, no import and stable screens, such as an old desktop ERP that someone retypes orders into every week.

The pattern that works is a split pipeline. Apps Script does everything that has an API: it watches the mailbox, reads the PDFs and writes order lines to a sheet. The robot only does the last step that has no other door, typing an approved row into the ERP and writing the ERP order number back. This post is about the handoff between the two, because that handoff is where orders get typed twice.

Why the usual retry makes duplicates

The robot works one row at a time: claim it, type it, save it, report back. Any of those steps can be the last one before the robot stops. The machine reboots for an update, the remote session drops, the ERP shows a dialog the script has never seen.

The common answer is a timeout. If a claimed row has not been reported within ten minutes, put it back in the queue. UiPath users describe the same mechanism from the other side: when a job faults, the queue item is marked Abandoned and retried according to the queue's retry settings.

That timeout cannot tell two cases apart:

  • The robot stopped before it pressed Save. Retrying is correct.
  • The robot stopped after it pressed Save. Retrying types the order again.

From the queue's side both look the same: a claim with no report. A robot that types orders is not an API call you can repeat safely. The ERP takes the second order without complaint.

Here is what the fake ERP showed for both designs, with the same invented orders and the same stop after saving PO 1042:

Same run (invented orders, fake ERP) Expired claim goes back in the queue Expired claim goes to CHECK_ERP
ERP orders for PO 1042 2 1
ERP orders in total, for 3 purchase orders 4 3
Status of the PO 1042 row afterwards DONE CHECK_ERP
Emails to a person 0 1

The queue sheet

One sheet, Queue, with a header row and ten columns: Key, Customer, Lines, Confidence, Status, Token, ClaimedAt, Tries, ErpOrder, Note. The key is the supplier plus the PO number. It does two jobs: it stops the same PDF from being staged twice, and the robot types it into a reference field in the ERP, so a person can later search for it.

The first block is the API side. Whatever reads the PDFs (I covered parsing emails into Google Sheets before) calls stageOrder once per order:

const QUEUE = 'Queue';
const C = { KEY: 0, CUSTOMER: 1, LINES: 2, CONF: 3, STATUS: 4,
  TOKEN: 5, CLAIMED: 6, TRIES: 7, ERP: 8, NOTE: 9 };
const MIN_CONFIDENCE = 0.9;

function queueRows_() {
  const sh = SpreadsheetApp.getActive().getSheetByName(QUEUE);
  const n = sh.getLastRow() - 1;
  const rows = n > 0 ? sh.getRange(2, 1, n, 10).getValues() : [];
  return { sh, rows };
}

// The API side calls this once per order it extracted from a PDF.
function stageOrder(order) {
  const key = order.supplier + ':' + String(order.po).trim();
  const lock = LockService.getScriptLock();
  lock.waitLock(20000);
  try {
    const q = queueRows_();
    if (q.rows.some(r => r[C.KEY] === key)) return 'DUPLICATE';
    const sure = order.confidence >= MIN_CONFIDENCE &&
      order.lines.every(l => l.sku && l.qty > 0);
    const status = sure ? 'APPROVED' : 'REVIEW';
    q.sh.appendRow([key, order.customer,
      JSON.stringify(order.lines), order.confidence, status,
      '', '', 0, '', sure ? '' : 'check before the robot types it']);
    return status;
  } finally {
    lock.releaseLock();
  }
}

Two choices in there are deliberate. A PO that is already in the sheet returns DUPLICATE instead of a second row, so a forwarded email or a re-run of the parser does nothing. And any row the extractor is not sure about lands in REVIEW with a note, never in front of the robot. The 0.9 threshold is a placeholder: set it from how often your extractor is wrong, not from this post.

The robot's side: claim, done, fail

The robot talks to a web app (doPost) with three actions. Apps Script's doPost(e) cannot read HTTP headers, so the shared secret travels as a k parameter and is compared with a script property. Every call takes the script lock, so two robot instances cannot claim the same row.


const LEASE_MS = 10 * 60 * 1000;   // longer than the slowest order
const MAX_TRIES = 3;

function doPost(e) {
  const p = e.parameter;
  const props = PropertiesService.getScriptProperties();
  const secret = props.getProperty('ROBOT_KEY');
  if (!secret || p.k !== secret) return json_({ error: 'forbidden' });
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(20000)) return json_({ error: 'busy' });
  try {
    props.setProperty('ROBOT_SEEN', String(Date.now()));
    if (p.action === 'claim') return json_(claim_());
    if (p.action === 'done') return json_(finish_(p, 'DONE'));
    if (p.action === 'fail') return json_(finish_(p, 'FAIL'));
    return json_({ error: 'unknown action' });
  } finally {
    lock.releaseLock();
  }
}

// A claim that outlived its lease is never handed out again:
// the robot may have saved the order in the ERP before it died.
function expireLeases_(rows, now) {
  rows.forEach(r => {
    if (r[C.STATUS] !== 'CLAIMED') return;
    if (now - Number(r[C.CLAIMED]) <= LEASE_MS) return;
    r[C.STATUS] = 'CHECK_ERP';
    r[C.NOTE] = 'lease expired: search the ERP for ' + r[C.KEY];
  });
}

function claim_() {
  const q = queueRows_();
  expireLeases_(q.rows, Date.now());
  let job = { idle: true };
  const r = q.rows.find(x => x[C.STATUS] === 'APPROVED');
  if (r) {
    r[C.STATUS] = 'CLAIMED';
    r[C.TOKEN] = Utilities.getUuid();
    r[C.CLAIMED] = Date.now();
    r[C.TRIES] = Number(r[C.TRIES]) + 1;
    job = { key: r[C.KEY], customer: r[C.CUSTOMER],
      lines: JSON.parse(r[C.LINES]), token: r[C.TOKEN] };
  }
  if (q.rows.length) {
    q.sh.getRange(2, 1, q.rows.length, 10).setValues(q.rows);
  }
  return job;
}

function finish_(p, outcome) {
  const q = queueRows_();
  const i = q.rows.findIndex(x => x[C.KEY] === p.key);
  if (i < 0) return { error: 'no such key' };
  const r = q.rows[i];
  const open = r[C.STATUS] === 'CLAIMED' ||
    r[C.STATUS] === 'CHECK_ERP';
  if (!open || r[C.TOKEN] !== p.token) {
    return { error: 'not your claim' };
  }
  if (outcome === 'DONE') {
    if (!p.erp) return { error: 'ERP order number missing' };
    r[C.STATUS] = 'DONE';
    r[C.ERP] = p.erp;
    r[C.NOTE] = '';
  } else if (p.saved !== 'no') {
    // The robot cannot prove nothing was saved: a person looks.
    r[C.STATUS] = 'CHECK_ERP';
    r[C.NOTE] = p.reason || 'robot unsure whether it saved';
  } else {
    const more = Number(r[C.TRIES]) < MAX_TRIES;
    r[C.STATUS] = more ? 'APPROVED' : 'FAILED';
    r[C.NOTE] = p.reason || 'no reason given';
  }
  q.sh.getRange(i + 2, 1, 1, 10).setValues([r]);
  return { ok: true, status: r[C.STATUS] };
}

function json_(o) {
  return ContentService.createTextOutput(JSON.stringify(o))
    .setMimeType(ContentService.MimeType.JSON);
}

What the robot gets from claim is the row and a token. It must send the same token back with done or fail. A report with any other token is refused, so a robot that was presumed dead and comes back an hour later cannot overwrite a row that a person has already dealt with.

The important line is in expireLeases_. A claim older than ten minutes becomes CHECK_ERP, not APPROVED. It is never handed to the robot again. A person searches the ERP for the key: if the order is there, they write the ERP number into the row and set it to DONE; if not, they set it back to APPROVED. The robot can still finish a CHECK_ERP row itself if it comes back with its original token, because then it knows the ERP number.

fail follows the same rule. The robot has to say saved=no to get a retry, which it can only say when it failed before pressing Save (the login page changed, a field was missing). Anything else, including "the confirmation screen did not appear", goes to CHECK_ERP. After three failed tries a row becomes FAILED.

Ten minutes is the lease I used for the test. Make it longer than your slowest real order, measured, not guessed. A lease that is too short turns a slow ERP into a stream of CHECK_ERP rows; a lease that is too long only delays the alert.

The watchdog

If the robot dies completely, nobody calls claim, so nobody expires the lease. The third block runs on a time-driven trigger every 15 minutes. It expires leases on its own, and it emails the script owner when the robot has been silent for over an hour while work is waiting, or when rows need a person. If you have not set up a time-driven trigger before, the Apps Script triggers guide has the steps.

const SILENT_MS = 60 * 60 * 1000;

// Time-driven trigger, every 15 minutes.
function watchQueue() {
  const props = PropertiesService.getScriptProperties();
  const lock = LockService.getScriptLock();
  lock.waitLock(20000);
  let rows;
  try {
    const q = queueRows_();
    rows = q.rows;
    expireLeases_(rows, Date.now());
    if (rows.length) {
      q.sh.getRange(2, 1, rows.length, 10).setValues(rows);
    }
  } finally {
    lock.releaseLock();
  }
  const count = s => rows.filter(r => r[C.STATUS] === s).length;
  const seen = Number(props.getProperty('ROBOT_SEEN') || 0);
  const lines = [];
  if (count('APPROVED') && Date.now() - seen > SILENT_MS) {
    lines.push('The robot has been silent for over an hour.');
  }
  ['CHECK_ERP', 'FAILED', 'REVIEW'].forEach(s => {
    if (count(s)) lines.push(count(s) + ' row(s) in ' + s);
  });
  const text = lines.join('\n');
  if (text === (props.getProperty('QUEUE_ALERT') || '')) return text;
  if (text) {
    MailApp.sendEmail(Session.getEffectiveUser().getEmail(),
      'Order robot: a person is needed', text);
    props.setProperty('QUEUE_ALERT', text);
  } else {
    props.deleteProperty('QUEUE_ALERT');
  }
  return text;
}

The email text is stored, and the same text never goes out twice. Without that, a robot that is down over a weekend sends 96 emails a day, people filter them, and the one alert that matters is lost. The silence line is deliberately a fixed sentence, with no count of waiting orders in it, so new orders arriving while the robot is down do not produce new emails.

Pitfalls

  • The robot has to type the key into the ERP. Without it, a person checking a CHECK_ERP row has to guess which of today's orders for that customer is the robot's. Use a field the ERP shows in search, such as a customer reference or PO field.
  • "Saved" means the ERP confirmed it. If your robot reports done as soon as it clicks Save, a validation error that appears a second later is invisible. Report done only after reading the ERP order number off the confirmation screen.
  • A person has to own CHECK_ERP. The design turns a silent duplicate into a visible row. If nobody looks at the email, the row simply waits.
  • The secret is in the robot's configuration. Keep it in the RPA tool's credential store, not in the flow file, and rotate it if the flow is shared.
  • Lock timeouts. Each call waits up to 20 seconds for the lock. With one robot that is plenty; with several robots and a slow sheet, busy answers are normal and the robot should simply try again.

How this breaks over time

  • The ERP gets slower. Month-end, a new module, a fuller database: the slowest order creeps past the lease and CHECK_ERP rows appear for orders that were fine. Look at ClaimedAt against the time the ERP number came back once a month.
  • The screens change. The robot starts failing with saved=no, every row burns its three tries and lands in FAILED. That is the watchdog's job to report, and the robot's job to be fixed. The queue does not hide it.
  • The sheet grows. Every call reads the whole queue. Move DONE rows older than a month to an archive sheet before the reads get slow.
  • The vendor adds an import or an API. Ask once a year. When it appears, replace the robot side and keep the staging side as it is.

What I tested, and what I did not

The three blocks ran in Node against mocked Sheets, LockService, PropertiesService, MailApp and ContentService, plus a fake ERP and a scripted robot that can stop at any step. 58 tests pass in three time zones. I then broke the code on purpose 47 times (moved the lease boundary, dropped the token check, let an expired claim go back to the queue, removed the email flag, and so on); the tests caught all 47. The duplicate in the opening paragraph is the first of those breakages: one changed line in expireLeases_.

I have not run this against a real ERP, a real RPA tool or a deployed web app. I have not measured how Apps Script's script lock behaves under several robots calling at once, how long a real doPost call takes, or what your RPA tool does when the web app answers slowly. The 0.9 confidence threshold and the ten-minute lease are examples, not measurements.

The takeaway

When a robot stops without reporting, the queue does not know whether the order was saved, so it should stop guessing: send that row to a person, keyed so they can find it in a minute, and keep the robot on rows nobody doubts. The full decision rule for when a robot is the right tool, and when it is not, is in our RPA vs API automation guide on the MageSheet blog.


Drafted with AI help; the code was run and tested, and every number comes from those tests.

來源:DEV Community · dev.to