I need an API link between Xero and Google Sheets - to enable further reconciliation

General information for the business: We're an accounting firm working primarily with Xero accounting software
Description of requirements/functionality: We use Xero for many of our clients - and it's brilliant, it just doesn't have the facility for us to reconcile balance sheet accounts. An example of this is there might be 500 transactions going through a nominal account, but money should be going in and out, making the eventual balance nil. When the balance isn't nil, there might be a few transactions that need an opposite entry going through.

In Quickbooks, there's the option to "mark" a transaction, meaning that if you have £100 going in and then out again, they can both be ticked to say that they're not a problem. Therefore, all the ins and outs equal each other and can be marked to say so - the items that aren't marked will be the remaining items to be looked at. Xero doesn't have this functionality.

The issue for us with Xero is that we need to do this exercise only once, to find the items that need to be looked at. We currently have to do this every time we have an issue, since we can't refer back to the last list of items, as it will likely have changed.

What we'd like is to get an automatic export of every transaction that goes through Xero, to Google Sheets (critically, for these items to be updated if a transaction gets amended). So initially, a raw list of all entries into Xero is where we'd like to start. We assume (perhaps incorrectly) that each transaction has some sort of identifier so hopefully this can be done. This is phase one - and the whole job if that's where we agree to end.

The second phase is to arrange for us to be able to somehow mark these transactions as "reconciled" - not necessarily in Xero as there's no apparent functionality for that - but in the Google Sheet export - we intend for this to be our reconciliation record side of things. An idea for this could be to have a tab in the Google Sheet for each nominal code but the solution we have, needs to be expandable to other Xero systems so I think this could be tricky.

There are a number of ideas running through my mind about how to achieve what we need, but the basics are:
1) Have a way of exporting every transaction from Xero into another platform (see 2)
2) Have a platform, whether on Google Sheets or even something bespoke, to be able to mark transactions as reconciled
3) Make whatever solution we come up with, expandable to other Xero businesses, so for as many of our clients as need to use it.
4) This needs to be cloud based - we use Macs, so need to be able to access this solution from any operating system.

If you have any questions about this job, please ask them on the public page, not privately - I don't know what I might have missed so need to develop the plan publicly where possible.

We don't have a budget in mind as we don't know what this sort of thing involves so please ignore the bit we had to put in about budget/job skill level!
Extra notes: