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:

1. Log into Your Stripe Dashboard
2. Navigate to the ‘Products’ Section
3. Create a New Product by clicking the ‘+ Add product’ button.
4. Input Product Details. Only the name is required.
5. Set up pricing to “Recurring” setting the Billing Period to “Monthly”.
6. 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.

Settings

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
• Email
• Address
• Country
• City
• 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:

Settings Class

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.