how to create this payments table?

Topic Labels: Automations
301 3
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

I want to create a tracking table for customer payment.
Every month I check in the bank account who paid and mark in Excel.
How can I create a table of all customers and mark for each customer whether he has paid or not?
Is there an automation that can every month duplicate all my customers from the customers table to the payments table?


3 Replies 3
6 - Interface Innovator
6 - Interface Innovator

For tracking customer payments, we can create an automated solution in Google Sheets using Google Apps Script. This will streamline your process, allowing you to check monthly payments without manual updates. Here's the approach:

  1. Create two sheets: "Customers" for customer details and "Payments" for monthly payment status.
  2. Use a script to duplicate all customer data from the "Customers" sheet to the "Payments" sheet at the start of each month.
  3. Add a column in the "Payments" sheet to mark whether each customer has paid or not. This column can be updated manually based on your bank account checks.

To automate this duplication process, we’ll write a Google Apps Script that triggers at the start of each month. This script will copy the data from the "Customers" sheet and append it to the "Payments" sheet, ensuring you have a fresh list each month.

Yeap, use the "At scheduled time" trigger to run it once a month.  Then add a "Find Record" step to get all the active customers and use that result in a repeating group to create them:
Screenshot 2024-06-14 at 2.25.23 PM.png

Link to base

Let's Schedule the call and discuss this in more detail. I look forward to talking to you and your team should then join us