Navigating through the maze of managing both offline and online sales can be a daunting task for businesses. The complexities of handling customer data, setting up recurring billing, and ensuring timely payment collection often pose significant challenges. What if there was a way to streamline this entire process, ensuring you can focus more on growing your business and less on administrative tasks? In this comprehensive guide, discover a seamless method to automate customer recurring billing with Stripe and Google Sheets, providing you with a simplified, automated, and efficient billing system.
Whether you’re a seasoned business owner or a budding entrepreneur, this step-by-step walkthrough will equip you with the tools to transition smoothly from manual billing to a streamlined online recurring payment system, enhancing both your business operations and customer experience.
Watch the Video Tutorial
Overview of the Customer Recurring Billing Solution
Utilizing Google Sheets as a centralized database, we can input vital customer details effortlessly. An integrated Apps Script automates the creation of Stripe customer profile subscription setups and dispatches payment links via email. You can see the automated 4-step process in the diagram below.
Creating a Stripe Subscription Product
Before diving into the automation with Google Sheets and Apps Script, setting up a subscription product in Stripe is essential.
Prefer working on a product in Stripe’s “Test mode” first.
In order to create a subscription product, follow these steps:
- Log into Your Stripe Dashboard
- Navigate to the ‘Products’ Section
- Create a New Product by clicking the ‘+ Add product’ button.
- Input Product Details. Only the name is required.
- Set up pricing to “Recurring” setting the Billing Period to “Monthly”.
- Save the Product
Access the Google Sheet Template
Detailed Walkthrough of the Google Sheet Solution
Once we are done creating the Product, let’s move on to our Google Sheet, which consists of two tabs: Settings and Subscription Data.
In the Settings tab, we have to fill in:
- Our Stripe API Key and press the “Set API Key” to store the key in the Script Properties. Please remove the Stripe API Key from the Settings tab after that.
- The default trial period duration of our subscription, if any, in days
- The ID of the Subscription Product we would like to use. This ID can be found in the URL of the product details page in our Stripe dashboard.
Customer Data to automate Customer Recurring Billing
The Subscription Data tab will be used for entering the customer information:
- Full Name
- Postal Code
- Subscription Duration (Months)
- Trial Period (Days) [Optional Field]: This can be set if we want to override the default value set in the Settings tab.
- Monthly Amount: This is the monthly amount the user will be charged. Please note that this amount should already be defined in the pricing section of the product.
- Subscription ID (auto-filled later): The script will automatically add the subscription ID here once it is created
- Subscription Status (auto-filled later): This will be set to “CREATED” after the script has processed the respective row.
Triggering the Script
The script is triggered by the “Create Subscription” menu item on the top right corner of the Google Sheet.
When the script runs, it selects all rows with an empty subscription status.
For every such row, the script:
- Creates a new customer and subscription in Stripe. The picture below shows a sample subscription with a 4-day trial period and a 4-month duration.
- Sends an email with the checkout link to the new subscriber with a link to the checkout page:
Once the subscriber has paid for the subscription, it appears as active in Stripe.
Access the Complete Code on GitHub
For a hands-on look at the entire codebase and to experiment with it yourself, access our GitHub Repository here. It contains all the necessary scripts and detailed documentation to help you set up and customize the solution to your needs.
Google Apps Script Implementation Details
I always like to split the Google Apps Script code into separate modules according to the specific responsibility of each module.
Splitting code into modules offers several advantages. It promotes a clear separation of concerns, allowing each module to focus on specific tasks, enhancing readability and maintainability.
Modules can be reused across different parts of an application or even in separate projects, providing consistency and reducing redundancy.
Therefore, we have the following two modules in this case:
This class manages settings stored in a Google Sheet named ‘Settings’ or in the Script Properties. If you want to see in detail how the Settings class is implemented, feel free to read the respective blog post here.
The StripeApiClient class serves as a client to interact with the Stripe API.
It provides methods to:
- Create a new customer in Stripe.
- Create a subscription for a customer.
- Create a checkout session for a customer.
- Fetch a list of customers, products, and prices from Stripe.
Main Apps Script Logic
The main logic is implemented in two main functions:
The subscribeCustomers function stands out as the main driver. This function begins by validating essential settings like the API key and selected product. It then fetches customer data from the Google Sheet and iterates over each customer to initiate their subscription process using the subscribeCustomer function.
The subscribeCustomer function handles individual customer subscriptions by creating a customer in Stripe, determining product pricing, and establishing a subscription.
If successful, a checkout session is created in Stripe, and a checkout link is sent to the customer’s email, allowing them to finalize their subscription.
These functions essentially automate enrolling customers into a subscription service via Stripe, all managed within a Google Sheet.
In the digital age, organizations that want to offer ongoing value to their customers must be able to manage subscriptions effectively.
Businesses may use a strong yet user-friendly solution that bridges the gap between offline operations and online billing by connecting Google Sheets with Stripe using Apps Script.
This strategy combines the comfort of conventional encounters with the ease of contemporary technologies, streamlining the subscription process while providing clients with a smooth transition. This solution offers a scalable and practical approach to managing and building your subscription-based business, whether you’re a local gym owner, an online educator, or a burgeoning SaaS startup.
Explore the template, dive in, and see how το automate customer recurring billing.