Tip #8 : Implement metered billing with Google Sheet
We've been sharing a tip every week to address specific business scenarios which can be implemented for your subscription business. Last week, we shared a tip about
integrating online form builders with Zoho Subscriptions
. This week, we will be looking at
how you can implement metered billing in Zoho Subscriptions with Google Sheet
is the concept of charging your customers based on their usage of a product of service. With Zoho Subscriptions, you can charge your customers for a base plan initially and then charge an additional fee based on their usage using metered billing.
Generally, when a business wants to automate the metered billing process, they should have a back-end server that handles the metered billing readings. Once the reading is recorded, the back-end server has to process the difference between the last recorded reading and the current closing reading. If the customer has exceeded the base limit, the back-end server makes an API request to Zoho Subscriptions to update the invoice and charge an additional fee.
Let's consider the following scenario to understand this better.
Zylker Electro provides electricity services to residential areas. They have a meter installed in all the houses of the residential area to record the power consumption. Every month, a Zylker personnel visits the houses in the residential area to note down the meter reading. Then, the difference between last month's meter reading and the current month is calculated. If the customer has consumed more than 500 units of power, they will charge the customer an additional 12 cents per unit.
To automate the above process, Zylker has to build a back-end server that will:
- store the meter readings;
- calculate the power consumed for every month; and
- charge the customers accordingly.
Instead of building a back-end server, you can automate the process using the
in Zoho Subscriptions and
. With the help of Google Sheets, you can note down all the metered readings every month. Later, you can use
Google App Scripts
to fetch the readings and update the pending invoice using the Custom Functions.
How can you do it?
You can use Google Sheets to note down the readings every month (similar to the image shown below).
Last Recorded Reading
The meter reading at the end of the previous month.
The meter reading at the end of the current month.
The field is used to specify whether the invoice for a customer has been updated in Zoho Subscriptions or not.
Unique Ids that are used to map the customers between Google Sheets and Zoho Subscriptions.
Google allows us to access and edit the data in spreadsheet with the help of Google Scripts. Coming back to the scenario, every month a Zylker personnel will record the readings in the Google Sheet. With Zoho Subscriptions, you can configure a custom function to run for the invoices in the
Pending status. You must configure the Custom Function in such a way that:
- it fetches the meter readings from Google Sheets using Google script;
- processes the data and adds an additional charge to the invoice, if needed (based on the consumption);
- updates the Status in Google Sheets as Updated; and
- updates the Last Recorded Reading with current month's closing reading.
Once the custom function executes successfully, the invoice will be updated with the additional charge. The value in the Status field of the Google sheet will also be updated.
Getting started with Google Apps Scripts:
Google Apps Scripts provides you with feature-rich APIs that allow you to build you own web applications. For our scenario, we will be using the Spreadsheet APIs to fetch and edit the spreadsheet document. We will create two scripts:
- The first script will be used to fetch the last recorded reading and the closing reading of a customer.
- The second script for changing the Status field of the customer once the invoice is updated.
To create a script in Google App Scripts:
- Go to and click the New Script option on the top-left corner.
- Copy the scripts provided above and modify it to suit your needs.
- Once you are done, click the Publish option on the top and select the Deploy as web app option.
- Provide the project name.
- Choose Anyone, even anonymous in the Who has access to the app dropdown.
- Click the Deploy button.
- Save the Web app URL which will be displayed on the screen for future reference.
Once the Google app scripts are successfully deployed:
- Create a new Custom Function in Zoho Subscriptions.
- Choose the Module as Invoice and Predefined event as Invoice created.
- Copy the Custom function from this GitHub link and modify it as per your requirements.
- Save the Custom Function and you are good to go.
Found this useful? Try it out for your subscription business and let us know. If you have any queries, feel free to comment below. We are here to answer them.
The Zoho Subscriptions Team.
Tip #2 - Cancellation of Offline Subscriptions based on the Expected Payment Date
Hello everyone, As you all know, we are coming up with a custom function workflow every week to address specific business scenarios. In continuation of this previous blog, this week we will be seeing about Cancellation of Offline Subscriptions based on the Expected Payment Date. Business Scenario: In general, for any business, the customers make payments via online as well as offline. In case of subscription where the automatic collection of charge is enabled (aka online subscription), the customer's
Tip #4 : Refer and Earn Workflow system for your Subscription Business
Hello everyone, We've been coming up with a tip every week to address specific business scenarios which can be implemented for your business. In continuation to the previous post, this week we will be seeing about how you can implement the Refer and Earn Workflow system to acquire more customers. Why are Referrals important? Customer Acquisition is crucial for any business to sustain itself in a constantly evolving market. It involves persuading new consumers to purchase your products. Customers
Tip #3 : Send SMS Reminders for your Subscription Business
Hello everyone, As you all know, we are coming up with a tip every week to address specific business scenarios. In continuation to the previous blog, this week we will be seeing how you can use SMS reminders for your Subscription Business. Using SMS messages for your Subscription Business: While managing your subscription business, more often than not, you need to keep your customers informed about upcoming renewals, payment reminders and cancellations. By keeping them informed in advance, you can
Tips And Tricks - Announcement
Hello everyone, Every business is unique and each of them follows a specific workflow. While managing your customers' subscriptions with Zoho, you might have some needs unique to your business. Custom Functions helps you address such needs. This is a start to a series of posts, where we will be coming up with a custom function workflow every week that addresses specific business scenarios. Watch this space for further updates! Also, if you have any specific business scenario which needs to be addressed,
Tip #6 : Accessing external URLs from Zoho Subscriptions
Hello everyone, We have been sharing a tip every week to address specific business scenarios which you can implement for your subscription business. Last week, we shared a tip about implementing the Approval Workflow system. This week, let's look at how you can access external URLs with the help of Custom Link. Business Scenario: Every business functions differently and has its unique requirements. At times, businesses might want to access other websites or applications by appending specific information
Zoho SalesIQ Resources
Zoho TeamInbox Resources
Zoho DataPrep Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Desk Resources
Zoho Projects Resources
Zoho Sprints Resources
Zoho Creator Resources
Zoho WorkDrive Resources
Zoho Campaigns Resources
Zoho CRM Resources
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.