Sync JobNimbus jobs, invoices and payments to Google Sheets
Every night, copy the last day of JobNimbus jobs, invoices and payments into one spreadsheet your bookkeeper and owner can pivot.
JobNimbus
Google SheetsOperationsFinanceData SyncEvery night at 11pm, pull the last 24 hours of activity out of JobNimbus and append it to three tabs of a single Google Sheet, so my bookkeeper and my owner can pivot the numbers themselves instead of me copying them out by hand each week. Use a cron trigger set to 11pm daily. JobNimbus does not offer a poll trigger, so a scheduled run plus list operations is the correct shape here.
Start by capturing a single run date for the whole execution and reuse it on every row, so all rows written by one run share the same stamp. Then make three JobNimbus calls: List Jobs, List Invoices and List Payments. Narrow each one to recently touched records with the JobNimbus filter DSL, URL-encoded as the filter query parameter: {"must":[{"range":{"date_updated":{"gte":"now-1d"}}}]}. This keeps each nightly run small rather than pulling the whole account every time.
Page each list call properly. These endpoints are offset based: size sets the page size (default 1000, max 1000) and from is the zero-based offset. Advance by increasing from by size, and stop when a page returns fewer rows than size. Do not use the count field in the response envelope to decide when to stop. Responses come back shaped { count, results } where count is the total number of matching records in the account, not the size of the page you just received. Be aware that some JobNimbus list endpoints name the array files or activity instead of results, so read the array defensively rather than assuming the key.
JobNimbus timestamps are Unix epoch seconds. Convert every one of them to a plain readable date before it reaches the sheet, so nobody opens the spreadsheet to a column of ten digit numbers. Records are keyed by jnid, which is also surfaced as id; carry that value onto every row.
Append the jobs to a Jobs tab using the Google Sheets Append Values operation, with these columns: job name, customer, status, record type, sales rep, and created date. Append the invoices to an Invoices tab with: invoice number, customer, amount, due date, and the job the invoice is linked to. Append the payments to a Payments tab with: amount, date, method, and the invoice the payment applies to. All three tabs live in the same spreadsheet.
Every row on every tab must also carry two extra columns: the JobNimbus record id for that record, and the run date captured at the start of the execution. Those two columns are what let rows be deduplicated downstream, so never omit them. Append rows rather than overwriting, so the sheet accumulates history across runs.
If one of the three list calls returns no records for the window, skip the append for that tab and carry on with the others rather than failing the whole run. A quiet night for payments should not stop jobs and invoices from landing.
Additional information
What does this prompt do?
- Runs on its own every night at 11pm and picks up every job, invoice and payment that was created or changed in JobNimbus in the past 24 hours.
- Files each record on its own tab of a single Google Sheet: one tab for jobs, one for invoices, one for payments.
- Writes plain readable dates instead of raw system timestamps, so the sheet is ready to sort, filter and pivot the moment you open it.
- Stamps every row with its JobNimbus record number and the date it was pulled, so repeated rows are easy to spot and clean up.
What do I need to use this?
- A JobNimbus account with permission to view jobs, invoices and payments.
- A Google account with access to Google Sheets.
- One Google Sheet created ahead of time with three tabs named Jobs, Invoices and Payments, each with a header row.
How can I customize it?
- Change the timing. Run it earlier in the evening, or a few times a day if your crews update jobs late.
- Change the columns on any tab, for example adding the job address, the invoice status or the estimator.
- Widen or narrow the window, for example pulling the last seven days instead of the last one.
FAQs
Will this overwrite what is already in my spreadsheet?
What happens if the same job changes two nights in a row?
Do I need to set up the spreadsheet first?
Will this still work if we run thousands of jobs?
Can my bookkeeper use this without a JobNimbus login?
Related templates
Every weekday, find every unpaid JobNimbus invoice, email each customer one reminder that gets firmer as it ages, and post a receivables summary to Slack.
New roof inspection requests from your website form land in JobNimbus within a minute, with no duplicate records and nothing retyped by your team.
Every weekday at 7am, your active courses are checked for missing work, silent logins, and slipping grades, with a ranked list sent to your advising channel.
Every Monday at 8am, see exactly which assignments have work waiting, sorted worst first, posted to Slack and logged to a spreadsheet.
Every weekday morning your advising team gets a ranked list of students slipping behind in each course, plus a tracking log that shows whether outreach worked.
Every evening we find the jobs you just finished, email each customer a personal review request, and log it on the job so nothing slips.
Stop copying JobNimbus numbers into a spreadsheet by hand.
Let this run every night so your bookkeeper and your owner open the same up to date sheet every morning.