All jobs

Which Supplier Prices Went Up This Month? A Field-Service App's Check of Each HVAC Contractor's OneDrive Price Lists

Field-service software makes one agent per HVAC contractor that compares the newest supplier price lists in OneDrive with the last and flags parts whose cost rose.

Run this on RushOpens the console with this prompt ready to send. Connect OneDrive there first.

What the agent does

This is for a company that sells quoting, scheduling and invoicing software to heating, cooling and plumbing contractors. Each contractor's distributors send a new price list as an Excel file every month or so, and the office manager saves it to a OneDrive folder called Supplier price lists. The software's price book, the parts and costs technicians quote from, goes stale between those files, so a tech quotes a condenser fan motor at last quarter's cost. The company saves one agent as a template with "connections": ["onedrive"] and instructions that it reads price lists and never writes to OneDrive, and makes one agent from it per contractor with POST /o/{org}/agents {"template": "<template id>", "name": "prices-<contractor id>"}.

The office manager connects OneDrive once from the software's settings. The backend calls POST /o/{org}/agents/{agent}/connections {"type": "app", "name": "onedrive"} and opens the authorize_url in a popup, where they sign in on Microsoft's page. Rush's OneDrive app asks for Files.ReadWrite.All, its one permission. The company registers no Microsoft app and stores no token.

On a run the agent lists the folder with Microsoft Graph, GET /me/drive/root:/Supplier price lists:/children, following @odata.nextLink, and picks the two newest files for each supplier by the naming pattern in the prompt and each file's lastModifiedDateTime. For a work or school account it reads each sheet through the workbook API, GET /me/drive/items/{id}/workbook/worksheets/{name}/usedRange(valuesOnly=true). Microsoft's docs say that call does not support personal Microsoft accounts, so for those it downloads the file with GET /me/drive/items/{id}/content and reads it in its own computer.

It matches rows between the old and new list by the supplier's part number, and against the contractor's price book, which the backend passes in the prompt as each part number and the cost the software holds. It reports parts whose cost rose by more than the threshold the contractor set, parts dropped from the new list, new parts, a unit of measure that changed (each against a box of ten), and part numbers that changed format so they no longer match. It returns JSON for the software to show as proposed cost updates. It changes nothing in OneDrive: Files.ReadWrite.All would let it, and the template and the prompt forbid it.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "<agent id>",
  "input": "List GET /me/drive/root:/Supplier price lists:/children (follow @odata.nextLink). Suppliers and file name patterns: Northline Supply 'Northline *.xlsx', Coastal HVAC Distributors 'Coastal*.xlsx'. For each supplier take the two newest files by lastModifiedDateTime. Read the first worksheet with GET /me/drive/items/{id}/workbook/worksheets/{name}/usedRange(valuesOnly=true); if the workbook API refuses the account, download /content and read the file locally. Match rows by part number (column headed 'Part #' or 'Item'). Our price book (part_number, our_cost): <price book JSON from our database>. Report: cost_increase over 4% versus the previous list or versus our_cost, dropped_part, new_part, unit_changed, part_number_format_changed. Return only JSON: {supplier_files: [{supplier, newest, previous}], changes: [{supplier, part_number, description, kind, old_cost, new_cost, our_cost, unit_old, unit_new}]}, largest increases first. Do not create, edit, move or delete anything in OneDrive."
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

The answer is JSON in the session's result and in the run.completed webhook. The software shows it as a list of proposed price-book changes, largest increases first, each with the old and new cost and the file it came from, and the office manager accepts or skips each one. Nothing in the price book changes until they do.

What the office manager checks: unit-of-measure changes, which make a price look like it jumped tenfold; part numbers the agent could not match because the supplier changed its format; and any change from a file whose name did not fit the expected pattern. The agent reports costs only. What the contractor charges a customer, and whether to honour a quote already sent at the old cost, is theirs to decide. The session's accounts names the Microsoft account the run read, and the transcript shows every Graph call.

Run it on every new supplier price list

The company's scheduler sends one POST /o/{org}/sessions per connected contractor on the first working day of each month, with that contractor's agent id as agent, its suppliers and price book in input, and a request_id that is a UUID v5 of the contractor id and the month, so a retried job starts no second run. Runs past the key's concurrent limit are created queued and start as earlier ones finish.

Suppliers do not all publish on the same day, so the software also offers a Check now button: when the office manager saves a new list, they press it and the backend starts the same request for that contractor at once.

The use case this job follows, with its automation and trigger: Run an agent per customer task.

Why run it on Rush?

Every distributor lays out its spreadsheet its own way, with different column names, a header block of terms and conditions, and part numbers that gain a prefix one month. An import mapping per supplier per contractor breaks every time a file changes. The agent reads each file as it is and says when it cannot match a row, and each contractor's Microsoft account is connected to their own agent only, so a run for one contractor never opens another's files.

The company keeps one template. Adding a check, such as flagging a discontinued part that a scheduled job still uses, is one PATCH to the template that reaches every contractor's next run. GET /o/{org}/sessions?agent=<agent id> lists one contractor's runs and their total_cost_usd, what each run's model calls cost, so the company can add up the check's model cost per account; compute comes from the same credits but is not on the session.

Frequently asked questions

Does the contractor need a Rush account?

No. The office manager signs in to Microsoft once from the software's settings. The agent, its runs and the bill belong to the person who created the software company's API key.

What if a supplier sends a PDF instead of a spreadsheet?

The agent can download and read a PDF the same way, but a table in a PDF is easier to misread than cells in a workbook. The prompt can ask it to mark every row from a PDF so the office manager checks those against the file.

Will it update our price book automatically?

No. It proposes changes and the office manager accepts each one in the software. A cost that changed because of a unit change, not a price rise, is exactly the kind of row a person should see first.

Our price lists live in a SharePoint library, not a personal OneDrive. Does that work?

Microsoft Graph addresses a SharePoint document library through /sites/{site-id}/drive, and the permission covers files the signed-in person can reach there. The template's instructions name that path instead of /me/drive for contractors who keep files on a site.

More OneDrive jobs

More jobs like this: Run an agent per customer task

Keep reading