Sync invoices to QuickBooks or Xero

Northwind raises invoices in a spreadsheet because that is where the project data already lives — client, retainer amount, dates. But the accountant needs those invoices in Xero, and retyping each one is slow and error-prone.

This script closes that gap. It reads the invoices sheet, finds the rows that have not been pushed yet, creates each one in Xero through the accounting API, and writes the returned Xero invoice ID back into the row. That ID is both the record of success and the guard that stops the same invoice being created twice.

What you’ll need

• A Google Sheet with one row per invoice and a header in row 1, with these columns: client, amount, date, dueDate, and xeroId (left blank — the script fills it).
• The spreadsheet’s ID for the INVOICES_SHEET_ID config value.
• A Xero account with the Accounting API enabled and an OAuth 2.0 app set up.
• A valid Xero access token saved as XERO_TOKEN in Script Properties — see Store API keys and secrets securely.
• The account code your Xero chart of accounts uses for retainer revenue — the example uses 200.

The script

// The spreadsheet that holds your invoices.
const INVOICES_SHEET_ID = '1abcInvoicesId';

// Xero account code for retainer revenue — match this to your chart
// of accounts.
const REVENUE_ACCOUNT_CODE = '200';

// New invoices are created as drafts so the accountant can review
// before approving. Use 'AUTHORISED' to push them straight through.
const INVOICE_STATUS = 'DRAFT';

/**
 * Reads the invoices sheet, creates every not-yet-synced row in Xero,
 * and writes the returned Xero ID back so it is never pushed twice.
 */
function pushToXero() {
  const sheet = SpreadsheetApp.openById(INVOICES_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet, splitting the header off the data rows.
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;

  if (!rows.length) {
    Logger.log('No invoices to sync — nothing to do.');
    return;
  }

  // 2. Map column names to indexes so column order does not matter.
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  // 3. The OAuth token is read once and reused for every request.
  const token = PropertiesService.getScriptProperties()
    .getProperty('XERO_TOKEN');
  if (!token) {
    Logger.log('No XERO_TOKEN in Script Properties — aborting.');
    return;
  }

  let synced = 0;

  // 4. Walk every row. Skip any invoice that already carries a xeroId.
  rows.forEach((row, i) => {
    if (row[col.xeroId]) return;

    // 5. Create the invoice in Xero, then store the returned ID on the
    //    matching row in `values` (i + 1 accounts for the header row).
    const xeroId = createXeroInvoice(token, row, col);
    values[i + 1][col.xeroId] = xeroId;
    synced++;
  });

  // 6. Write the whole grid back in one call.
  sheet.getDataRange().setValues(values);
  Logger.log('Synced ' + synced + ' invoice(s) to Xero.');
}

/**
 * Creates a single ACCREC (accounts-receivable) invoice in Xero and
 * returns its InvoiceID. PUT creates a new invoice each call.
 */
function createXeroInvoice(token, row, col) {
  const res = UrlFetchApp.fetch('https://api.xero.com/api.xro/2.0/Invoices', {
    method: 'put',
    headers: { Authorization: 'Bearer ' + token, Accept: 'application/json' },
    contentType: 'application/json',
    payload: JSON.stringify({
      Invoices: [{
        Type: 'ACCREC',
        Contact: { Name: row[col.client] },
        LineItems: [{
          Description: 'Retainer',
          Quantity: 1,
          UnitAmount: row[col.amount],
          AccountCode: REVENUE_ACCOUNT_CODE,
        }],
        Date: row[col.date],
        DueDate: row[col.dueDate],
        Status: INVOICE_STATUS,
      }],
    }),
    muteHttpExceptions: true,
  });

  // A non-2xx response means the invoice was not created — surface the
  // body so the cause (bad token, unknown contact) is visible.
  const code = res.getResponseCode();
  if (code < 200 || code >= 300) {
    throw new Error('Xero returned ' + code + ': ' + res.getContentText());
  }

  return JSON.parse(res.getContentText()).Invoices[0].InvoiceID;
}

How it works

1. pushToXero opens the invoices spreadsheet and reads the entire grid, splitting row 1 off as the header.
2. If there are no data rows, it logs a message and stops.
3. It builds a col lookup from header text to column index, so the script does not break if the columns are reordered.
4. It reads the XERO_TOKEN once and reuses it for every request. If the token is missing it aborts early rather than firing failing calls.
5. It walks every row and skips any invoice that already has a value in xeroId — that is how rows already synced are left alone.
6. For each remaining row it calls createXeroInvoice, which PUTs an ACCREC invoice to the Xero API and returns the new InvoiceID. A non-2xx response throws with the response body so the failure is easy to diagnose.
7. The returned ID is written back into the row, and after the loop the whole grid is saved in a single setValues call.

Example run

The invoices sheet before a run:

client	amount	date	dueDate	xeroId
Acme Co	2400	2025-11-01	2025-11-15	220a8c-…-91f
Bluefin Ltd	1800	2025-11-01	2025-11-15
Cedar Studio	3200	2025-11-01	2025-11-15

The first row already has a xeroId, so it is skipped. The other two are created in Xero as draft invoices and their IDs are written back:

client	amount	date	dueDate	xeroId
Acme Co	2400	2025-11-01	2025-11-15	220a8c-…-91f
Bluefin Ltd	1800	2025-11-01	2025-11-15	7f3e10-…-4ad
Cedar Studio	3200	2025-11-01	2025-11-15	9b21d4-…-c08

The log reads Synced 2 invoice(s) to Xero. In Xero, two draft invoices appear under accounts receivable, ready for the accountant to review and approve.

Run it

Run it on demand after a billing batch, or on a timer:

1. In the Apps Script editor, select pushToXero and click Run, approving the authorisation prompt the first time.
2. To automate it, open Triggers (the clock icon), click Add Trigger, choose pushToXero, and set a Time-driven schedule — daily is plenty for most billing cycles.

The xeroId guard makes the script safe to re-run: already-synced rows are always skipped.

Watch out for

• Xero access tokens expire after 30 minutes. A production setup needs the OAuth 2.0 refresh-token flow to mint a fresh XERO_TOKEN before each run — the Apps Script OAuth2 library handles this. The script as written assumes the token in Script Properties is currently valid.
• Contact: { Name: ... } matches an existing Xero contact by name, or creates a new one. A typo in the client column will create a duplicate contact, so keep client names consistent with what is already in Xero.
• The AccountCode must exist in your chart of accounts. If REVENUE_ACCOUNT_CODE is wrong, Xero rejects the whole invoice — check the thrown error body.
• Invoices are created as DRAFT so nothing is finalised without review. Switch INVOICE_STATUS to AUTHORISED only if you trust the sheet data completely.
• Xero rate-limits to 60 calls per minute. One call per invoice is fine for a normal batch, but if you sync hundreds at once, add a short Utilities.sleep between rows.
• For QuickBooks, the structure is the same — read the sheet, skip synced rows, POST to the QuickBooks invoice endpoint, store the returned Id — only the endpoint URL and JSON field names differ.