Automate Customer Recurring Billing with Stripe and Google Sheets

Introduction

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

For a comprehensive visual guide, watch our video tutorial. If you prefer, you can also watch it directly on YouTube.

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

To follow along with this guide, you can access and copy our Google Sheet Template here.

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.

Subscription Data

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.

StripeApiClient Class

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.

And more…

	/**
	* Stripe API Client for handling various Stripe operations.
	*/
	class StripeApiClient {
	constructor(apiKey) {
	this.apiKey = apiKey;
	}

	/**
	* Private method to make requests to the Stripe API.
	* @param {string} endpoint - The Stripe API endpoint.
	* @param {string} method - The HTTP method (e.g., 'post', 'get').
	* @param {object} data - The data payload for the request.
	* @returns {object} - The parsed JSON response from the Stripe API.
	*/
	_makeRequest(endpoint, method, data) {
	let headers = {
	"Authorization": "Bearer " + this.apiKey,
	"Content-Type": "application/x-www-form-urlencoded"
	};

	let options = {
	"method": method,
	"headers": headers,
	"payload": data
	};

	let response = UrlFetchApp.fetch('https://api.stripe.com/v1/' + endpoint, options);
	return JSON.parse(response.getContentText());
	}

	/**
	* Create a new customer in Stripe.
	* @param {string} name - The customer's name.
	* @param {string} email - The customer's email address.
	* @param {string} address - The customer's address.
	* @param {string} country - The customer's country.
	* @param {string} city - The customer's city.
	* @param {string} postalCode - The customer's postal code.
	* @returns {object} - The created customer object.
	*/
	createCustomer(name, email, address, country, city, postalCode) {
	let customerData = {
	"name": name,
	"email": email,
	"address[line1]": address,
	"address[city]": city,
	"address[postal_code]": postalCode,
	"address[country]": country
	};

	return this._makeRequest('customers', 'post', customerData);
	}

	/**
	* Create a subscription for a customer.
	* @param {string} customerId - The ID of the customer.
	* @param {string} priceId - The ID of the price.
	* @param {number} duration - The duration of the subscription in months.
	* @param {number} trialDays - The trial period in days.
	* @returns {object} - The created subscription object.
	*/
	createSubscription(customerId, priceId, duration, trialDays) {
	let currentDate = new Date();
	currentDate.setMonth(currentDate.getMonth() + parseInt(duration));
	let cancelAtTimestamp = Math.floor(currentDate.getTime() / 1000);

	let subscriptionData = {
	"customer": customerId,
	"items[0][price]": priceId,
	"cancel_at": cancelAtTimestamp.toString(),

	};

	if(trialDays > 0) {
	let trialEndDate = new Date();
	trialEndDate.setDate(trialEndDate.getDate() + trialDays);
	let trialEndTimestamp = Math.floor(trialEndDate.getTime() / 1000);

	subscriptionData["trial_end"] = trialEndTimestamp.toString();
	}

	return this._makeRequest('subscriptions', 'post', subscriptionData);
	}

	/**
	* Create a checkout session for a customer.
	* @param {string} priceId - The ID of the price.
	* @param {string} customerId - The ID of the customer.
	* @param {string} [success_url="https://your-success-url.com"] - The URL to redirect to upon successful checkout.
	* @param {string} [cancel_url="https://your-cancel-url.com"] - The URL to redirect to upon checkout cancellation.
	* @returns {object} - The created checkout session object.
	*/
	createCheckoutSession(priceId, customerId, payment_method_type = "card", success_url = "https://your-success-url.com", cancel_url = "https://your-cancel-url.com") {
	let sessionData = {
	"customer": customerId,
	"payment_method_types[]": payment_method_type,
	"line_items[0][price]": priceId,
	"line_items[0][quantity]": "1",
	"mode": "subscription",
	"success_url": success_url,
	"cancel_url": cancel_url
	};

	return this._makeRequest('checkout/sessions', 'post', sessionData);
	}

	/**
	* Fetch a list of customers from Stripe.
	* @returns {object} - The list of customers.
	*/
	fetchCustomers() {
	return this._makeRequest('customers', 'get', {});
	}

	/**
	* Fetch a list of products from Stripe.
	* @returns {object} - The list of products.
	*/
	fetchProducts() {
	return this._makeRequest('products', 'get', {});
	}

	fetchProduct(productId) {
	return this._makeRequest('products/' + productId, 'get', {});
	}

	/**
	* Fetch a list of prices for a specific product from Stripe.
	* @param {string} product - The ID of the product.
	* @returns {object} - The list of prices for the specified product.
	*/
	fetchProductPrices(product) {
	return this._makeRequest('prices?product=' + product, 'get', {});
	}


	/**
	* Create a new price for a product in Stripe.
	* @param {number} unit_amount - The amount for the price.
	* @param {string} product - The ID of the product.
	* @param {string} currency - The currency for the price.
	* @returns {object} - The created price object.
	*/
	createProductPrice(unit_amount, product, currency) {
	let priceData = {
	"product": product,
	"unit_amount": unit_amount.toString(),
	"currency": currency,
	"recurring[interval]": "month"
	};

	return this._makeRequest('prices', 'post', priceData);
	}
	}

view raw StripeApiClient.js hosted with ❤ by GitHub

Main Apps Script Logic

The main logic is implemented in two main functions:

SubscribeCustomers

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.

SubscribeCustomer

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.


	// Constants for settings labels
	const SETTINGS_API_KEY_LABEL = 'Stripe API Key';
	const SETTINGS_SELECTED_PRODUCT_LABEL = 'Selected Product';
	const SETTINGS_DEFAULT_TRIAL_PERIOD_LABEL = 'Default Trial Period (Days)';

	const MAIN_SHEET_NAME = 'Subscription Data';
	const CURRENCY = 'EUR';


	// Initialize settings and Stripe client
	let settings = new Settings();
	let stripeApiKey = settings.getSetting(SETTINGS_API_KEY_LABEL);
	let stripeClient = new StripeApiClient(stripeApiKey);


	/**
	* Sets the API key in the script properties.
	*/
	function setApiKeyToScriptProperties() {
	let apiKey = settings.getSetting(SETTINGS_API_KEY_LABEL);
	if (!apiKey) {
	//notify user that the API key is not set
	SpreadsheetApp.getUi().alert('API Key not set');
	return;
	}
	settings.setSettingInScriptProperties(SETTINGS_API_KEY_LABEL, apiKey);
	}

	function onOpen() {
	settings.init();

	let ui = SpreadsheetApp.getUi();
	ui.createMenu('Stripe')
	.addItem("Create Subscriptions", 'subscribeCustomers')
	.addToUi();
	}

	/**
	* Fetches customer data from the active Google Sheet.
	* @returns {Array} List of customer objects.
	*/
	function fetchCustomersFromSheet() {
	let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
	let lastRow = sheet.getLastRow();

	// Assuming the data starts from the second row (considering the first row has headers)
	let dataRange = sheet.getRange(2, 1, lastRow - 1, 10);
	let customers = dataRange.getValues();

	let customerList = [];

	for (const element of customers) {
	let customer = {
	"name": element[0],
	"email": element[1],
	"address": element[2],
	"country": element[3],
	"city": element[4],
	"postalCode": element[5],
	"months": element[6],
	"trialPeriod": element[7],
	"amount": element[8],
	"subscriptionStatus": element[8]
	};

	customerList.push(customer);
	}

	return customerList;
	}

	/**
	* Fetches or creates a price for a product in Stripe.
	* @param {number} unit_amount - Amount for the product.
	* @param {string} product - Stripe product ID.
	* @param {string} currency - Currency code (e.g., 'EUR').
	* @returns {string\|null} Stripe price ID or null if not found.
	*/
	function getPrice(unit_amount, product, currency) {

	let prices = stripeClient.fetchProductPrices(product);

	for (const element of prices.data) {
	if (element.unit_amount == unit_amount && element.currency.toLowerCase() == currency.toLowerCase()) {
	// If a price with the specified unit_amount exists, return its ID
	return element.id;
	}
	}


	return null;
	}

	/**
	* Sets a specific field in the Google Sheet based on the provided email.
	* @param {string} email - Customer's email.
	* @param {number} column - Column number to set the value.
	* @param {string} value - Value to set in the specified column.
	*/
	function setField(email, column, value) {
	let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(MAIN_SHEET_NAME);
	let lastRow = sheet.getLastRow();

	// The data starts from the second row (considering the first row has headers)
	let emailRange = sheet.getRange(2, 2, lastRow - 1, 1);
	let emails = emailRange.getValues();

	for (let i = 0; i < emails.length; i++) {
	if (emails[i][0] === email) {
	sheet.getRange(i + 2, column).setValue(value);
	break;
	}
	}
	}

	/**
	* Main function to initiate the subscription process for customers.
	*/
	function subscribeCustomers() {
	try {
	// Start by checking if the required settings exist and notify the user if they don't
	let apiKey = settings.getSettingFromScriptProperties(SETTINGS_API_KEY_LABEL);
	let selectedProductId = settings.getSetting(SETTINGS_SELECTED_PRODUCT_LABEL);
	let defaultTrialPeriod = settings.getSetting(SETTINGS_DEFAULT_TRIAL_PERIOD_LABEL);

	if (!apiKey) {
	throw new Error('API Key not found in script properties.');
	}

	if (!selectedProductId) {
	throw new Error('Selected Product not found in settings.');
	}

	if (defaultTrialPeriod == null) {
	throw new Error('Default Trial Period not found in settings.');
	}

	// Get Customers from sheet
	let sheetCustomers = fetchCustomersFromSheet();

	for (const element of sheetCustomers) {
	let fc = element;
	if (fc.subscriptionStatus != 'Subscribed') {
	subscribeCustomer(fc, selectedProductId, defaultTrialPeriod);
	}
	}
	} catch (error) {
	SpreadsheetApp.getUi().alert(`Error: ${error.message}`);
	}
	}


	/**
	* Handles the subscription process for a single customer.
	* @param {Object} fc - Customer object.
	* @param {string} productId - Stripe product ID.
	* @param {number} defaultTrialPeriod - Default trial period in days.
	*/
	function subscribeCustomer(fc, productId, defaultTrialPeriod) {
	try {
	// Create Customer
	let newStripeCustomer = stripeClient.createCustomer(fc.name, fc.email, fc.address, fc.country, fc.city, fc.postalCode);
	if (!newStripeCustomer) {
	throw new Error('Failed to create a new Stripe customer.');
	}

	let amount = fc.amount * 100;
	let product = stripeClient.fetchProduct(productId);

	if (product == null) {
	throw new Error('Product not found in Stripe.');
	}

	// Check if the price exists, if not create it
	let priceId = getPrice(amount, product.id, CURRENCY);
	if (priceId == null) {
	throw new Error('Price not found for the selected product in Stripe.');
	}

	let trialPeriod = (fc.trialPeriod != null && fc.trialPeriod != "") ? parseInt(fc.trialPeriod) : defaultTrialPeriod;
	let subs = stripeClient.createSubscription(newStripeCustomer.id, priceId, fc.months, trialPeriod);

	if (!subs) {
	throw new Error('Failed to create a subscription in Stripe.');
	}

	let session = stripeClient.createCheckoutSession(priceId, newStripeCustomer.id, "card", "https://apptivasoftware.com");
	if (!session) {
	throw new Error('Failed to create a checkout session in Stripe.');
	}

	sendCheckoutLink(newStripeCustomer, session);
	setField(fc.email, 10, 'CREATED');
	setField(fc.email, 11, subs.id);
	} catch (error) {
	SpreadsheetApp.getUi().alert(`Error for customer ${fc.email}: ${error.message}`);
	}
	}


	/**
	* Sends a checkout link to the customer via email.
	* @param {Object} customer - Stripe customer object.
	* @param {Object} session - Stripe checkout session object.
	*/
	function sendCheckoutLink(customer, session) {
	let checkoutUrl = session.url;

	// Send an email to the customer with the checkout URL
	let subject = "Complete Your Subscription Setup";
	let body = `
	Dear ${customer.name},<br><br>
	Please complete your subscription setup by <a href="${checkoutUrl}">clicking here</a>.<br><br>
	Regards,<br>
	Your Company Name
	`;

	MailApp.sendEmail({
	to: customer.email,
	subject: subject,
	body: "",
	htmlBody: body
	});
	}

view raw SheetsToStripe.js hosted with ❤ by GitHub

Conclusion

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.

Automate Customer Recurring Billing with Stripe and Google Sheets

Introduction

Watch the Video Tutorial

Overview of the Customer Recurring Billing Solution

Creating a Stripe Subscription Product

Access the Google Sheet Template

Detailed Walkthrough of the Google Sheet Solution

Settings

Subscription Data

Customer Data to automate Customer Recurring Billing

Triggering the Script

Access the Complete Code on GitHub

Google Apps Script Implementation Details

Settings Class

StripeApiClient Class

Main Apps Script Logic

SubscribeCustomers

SubscribeCustomer

Conclusion

Talk to us