Weekly Aged Payable Report

Drafting in Gmail: www.make.com/en/integration/18868-aged-payable-weekly?templatePublicId=18868

What This Automation Does
This scenario runs on a weekly schedule and automatically pulls all outstanding approved bills from Xero that are due up to and including the current week. It groups them by supplier, calculates the total amount owing per supplier, and generates a formatted PDF report which is attached to a draft email ready for review and sending. No manual report generation needed.

Apps Used

• Xero - You will need access to the clients xero in your savvy account

• Gmail - The email account you will send the email FROM

• Google Drive - Where the Google Sheet is held 

• Google Sheets 

How It Works

1. Xero (First Module) pulls all outstanding approved payable invoices 

2. Iterator (Second Module) splits the invoices into individual bundles for processing

3. Filter removes any suppliers with DD in their name as these are direct debits and do not need to be reported on

4. Tools modules calculate the status, total amount due, earliest due date and latest due date per supplier

5. Array Aggregator groups all invoices by supplier contact so each supplier appears as one row

6. Google Sheets writes one row per supplier to the client's aged payables sheet & adds the total 

7. Google Drive exports the sheet as a PDF

8. Gmail creates a draft email with the PDF attached ready for review and sending

9. Google Sheets clears the data rows ready for the following week's run

Scheduling This automation: 

It is set to run every Friday at 4pm. This can be changed to a day and time that suits you but remember the data will be current from that time of pulling the info. 

Notes:

Direct Debits (DD)

Any supplier that is on a direct debit should have DD added to their contact name in Xero. For example Cardlink would be updated to Cardlink - DD. This is important because the automation has a filter built in that automatically excludes any supplier with DD in their name from the report. This means direct debit suppliers will never appear on the aged payables report as they do not need to be manually reviewed or paid. If you want a supplier to appear on the report do not add DD to their contact name.

If a client has direct debit suppliers that are not already labelled with DD in Xero this will need to be updated before the automation is run for the first time. Check with the client or bookkeeper to confirm which suppliers are on direct debit and update their contact names in Xero accordingly.

For any bills that have been paid by receipt, make sure these are left as a draft in Xero and not approved. The automation only pulls through approved bills, so any draft bills will not appear on the report.

Setting Up the Aged Payables Google Sheet - Complete Before Building the Automation

Before you begin setting up the Make automation you must first create and configure the client's Google Sheet. The automation will not work without this step being completed first as it needs an existing sheet to write data to and export from.

Step 1 - Download the Template Download the Aged Payables Google Sheet template from the intranet using this link below. Save it to your computer.
https://docs.google.com/spreadsheets/d/1-uHonSFCmSI5LkujLT_2whpddb1pad9cHWnrmln8X_E/edit?usp=sharing

Step 2 - Create a New Google Sheet

1. Go to Google Drive and navigate to your drive, where you will save this spreadsheet. This spreadsheet must not be touched or moved so best to create its own folder for it.

2. Click New > Google Sheets to create a blank sheet

3. Rename the file to: [Client Name] Aged Payables Sheet — for example Te Marua Aged Payables Sheet

Step 3 -  Import the Template

1. In the blank Google Sheet go to File > Import

2. Click Upload and select the template file you downloaded in Step 1

3. Under Import location select Replace spreadsheet

4. Under Separator type select Detect automatically

5. Click Import data

6. The sheet will now be populated with the correct layout, branding, headers, conditional formatting and currency formatting from the template

Step 4 - Update the Client Name

1. Find row 2 which contains the company name

2. Click on cell A2 and update it to the client's correct company name

3. Make sure the formatting stays the same after editing - it should remain bold and the same font size

Step 5 - Turn Off Gridlines if not already

1. Go to View in the top menu

2. Untick Show gridlines

3. This ensures the PDF export looks clean and professional

You are now ready to begin setting up the automation in Make. Refer to the guided template instructions.