Tip #8 : Implement metered billing with Google Sheet

Tip #8 : Implement metered billing with Google Sheet

Hello everyone,

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.

Business Scenario:
Metered Billing  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 Custom Functions in Zoho Subscriptions and Google Sheets. 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).




Field
Description
Last Recorded Reading
The meter reading at the end of the previous month.
Closing Reading
The meter reading at the end of the current month.
Status
The field is used to specify whether the invoice for a customer has been updated in Zoho Subscriptions or not.
ID
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:
  1. it fetches the meter readings from Google Sheets using Google script;
  2. processes the data and adds an additional charge to the invoice, if needed (based on the consumption);
  3. updates the Status in Google Sheets as Updated; and
  4. 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:
  1. The first script will be used to fetch the last recorded reading and the closing reading of a customer.
  2. The second script for changing the Status field of the customer once the invoice is updated.
To create a script in Google App Scripts:
  1. Go to and click the New Script option on the top-left corner.
  2. Copy the scripts provided above and modify it to suit your needs.
  3. Once you are done, click the Publish option on the top and select the Deploy as web app option.
  4. Provide the project name.
  5. Choose Anyone, even anonymous in the Who has access to the app dropdown.
  6. Click the Deploy button.
  7. Save the Web app URL which will be displayed on the screen for future reference.
Once the Google app scripts are successfully deployed:
  1. Create a new Custom Function in Zoho Subscriptions.
  2. Choose the Module as Invoice and Predefined event as Invoice created.
  3. Copy the Custom function from this GitHub link and modify it as per your requirements.
  4. 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.

Cheers,
Sasidaran K,
The Zoho Subscriptions Team.

    • Sticky Posts

    • 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 #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 #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

      • Recent Topics

      • Ability to Link Reported Issues from Zoho Desk to Specific Tasks or Subtasks in Zoho Projects

        Hi Zoho Desk Team, Hope you're doing well. When reporting a bug from Zoho Desk to Zoho Projects, we’ve noticed that it’s currently not possible to select an existing task or subtask to associate the issue with. However, when working directly inside Zoho

      • How do i move multiple tickets to a different department?

        Hello, i have several tickets that have been assigned to the wrong department.  I am talking about hundreds of automatically generated ones that come from a separate system. How can i select them all at once to move them to another department in one go? I can select them in "unsassigned open tickets view" but i can't find a "move to another department" option. I also can't seem to assign multiple tickets to the same agent in that same view. Could somebody advice?

      • Capture Bank Charges for Invoice Payments

        We've added the Bank Charges field in the "Add Payments" page both in Zoho Invoice & Zoho Books. So all you need to do is to enter the bank charges when you record a payment for the invoices. This bank charge will be included to the amount paid for that invoice. Steps to add the bank charges while you record the payment:       1. Click the "Invoices" sub-tab under the "Money-In" tab.       2. Click the 'add payment' link for the invoice that you wish to record the payment for.       3. On the add

      • How to apply customized Zoho Crm Home Page to all users?

        I have tried to study manuals and play with Zoho CRM but haven't found a way how to apply customized Zoho CRM Home Page as a (default) home page for other CRM users.. How that can be done, if possible? - kipi Moderation Update: Currently, each user has

      • Plug Sample #14: Automate Invoice Queries with SalesIQ Chatbot

        Hi everyone! We're back with a powerful plug to make your Zobot smarter and your support faster. This time, we're solving a common friction point for finance teams by giving customers quick access to their invoices. We are going to be automating invoice

      • Enrich your contact and company details automatically using the Data Enrichment topping

        Greetings, I hope you're all doing well. We're happy to announce the latest topping we've added to Bigin: The Data Enrichment topping, powered by WebAmigo. This topping helps you automatically enhance your contact and company records in Bigin. By leveraging

      • Zoho Billing Partial Proration...

        Hi, In Zoho billing, we offer upgrades, downgrades, suspends, and resumes. We use online payment for most subscriptions, but have only a few that are offline, but we treat them the same either way.. We prorate only parts of these changes.. Right now zoho

      • Reconciling a month with no transactions

        I'm treasurer for a small non profit cemetery association and I'm trying to reconcile a bank statement for a month that did not have any transactions. Do I skip the month entirely and go a month with transactions?

      • Getting Subform Fields to Display Top to Bottom

        I have a form where the fields are all in one column. I want to insert a subform where the fields are stacked in one column as well. I have built both the form and subform but the subform displays the fields from left to right instead of a stacked column. This will cause a problem displaying the subform correctly on mobile apps. How can I do this please?' Here is my form with subform now. As you can see the subform "Follow Up Activity" is displaying the fields left to right. I need them to go top

      • [Feature Deprecated] Gamescope in Zoho Projects

        This feature is no longer available. All work and no play makes Jack a dull boy. Well, yes. That's true! Tiring work, buzzing coffee makers, busy turnstiles , occupied meeting rooms, back-to-back status updates and what not. Your everyday routine need not have to be this hectic. It can be more rewarding. It can be more fun. Zoho Projects believes in this fact and hence has launched Gamescope - A module that turns every project into an exciting game and boosts the spirit of your team by leaps and

      • Creating a task, i can not work out how to get ID for What_Id

        hi From Module A function I map Module B record reference membershipid (ours). I need Module B Zoho ID to create the related to on task. All examples i've seen start with the Zoho ID. void automation.LTM_Assign_Dispute_Task(String membershipid) { try

      • Can't connect to POP and SMTP over VPN

        I use Thunderbird to access Zoho Mail via POP and SMTP. I have configured an app-specific password for Thunderbird to use. Everything is properly configured and works correctly — until I enable my VPN. I'm using a paid commercial VPN service (Mullvad).

      • my zoho mail is hacked

        my email is sending my username and password to people i dont know

      • Suministrar accesos

        Asunto: Enable UPLOAD_RULE for API Attachments Mensaje: Hola, Necesito habilitar la capacidad de subir attachments vía API en Zoho Mail. Actualmente recibo el error: "UPLOAD_RULE_NOT_CONFIGURED" al usar el endpoint: POST /api/accounts/{accountId}/messages/attachments

      • Introducing real-time document commenting and collaboration in Zoho Sign

        Hi, there! We are delighted to introduce Document commenting, a feature that helps you communicate with your recipients more efficiently for a streamlined document signing process. Some key benefits include: Collaborate with your recipients easily without

      • Ticket Loop from Auto Responce

        We’re seeing an issue where a client’s help desk triggers a ticket loop. When they email us, our system creates a ticket and sends the acknowledgement. Their system then creates a ticket from that acknowledgement and sends one back to us, which creates

      • How to make rule to filter out some emails from receiving the notification "Receiving a new ticket"?

        Good Morning, We are currently using the "Receiving a new ticket" notification rule to provide that auto reply template to a customer that emails us for support. However, there are some emails we don't want to receive this auto-reply. Where can I set

      • Zoho Signatures Missing

        In the past after collecting signatures from two different PDFs I would merge them by calling an api and the signatures would appear in the combined PDF. Recently the signatures have disappeared whenever I combine the PDFs together. Why did this randomly

      • Filter button in the Zoho Sheet Android App doesn't toggle on

        I am a new Zoho Sheets user and experiencing a specific issue with the filter functionality in the Android mobile application. Detailed Issue Description: The filter icon appears correctly in the toolbar. Upon tapping the filter icon/button, the toggle

      • Automate pushing Zoho CRM backups into Zoho WorkDrive

        Through our Zoho One subscription we have both Zoho CRM and Zoho WorkDrive. We have regular backups setup in Zoho CRM. Once the backup is created, we are notified. Since we want to keep these backups for more than 7 days, we manually download them. They

      • How to charge Convenience fee OR payment gateway charges to the end client who is paying the invoice?

        Hello, I am creating this topic after having discussions with various sets of users and have understood that with people moving more and more to digital payments, it is important for the client to enable the "Convenience fee" kind of scenario. I have

      • Exciting Updates to the Kiosk Studio Feature in Zoho CRM!

        Hello Everyone, We are here again with a series of new enhancements to Kiosk Studio, designed to elevate your experience and bring even greater efficiency to your business processes. These updates build upon our ongoing commitment to making Kiosk a powerful

      • New activity options for workflows

        Greetings, We are excited to announce the addition of two new dynamic actions to our workflow functionality: Create Event and Schedule Call. These actions have been thoughtfully designed to enhance your workflow processes and bring more efficiency to

      • Remove the “One Migration Per User” Limitation in Zoho WorkDrive

        Hi Zoho WorkDrive Team, Hope you are doing well. We would like to raise a critical feature request regarding the Google Drive → Zoho WorkDrive migration process. Current Limitation: Zoho WorkDrive currently enforces a hard limitation: A Zoho WorkDrive

      • Kaizen #228 - Process Large-Scale Migrated Data Using Catalyst Solutions

        Howdy, tech wizards! This week’s Kaizen explores how Catalyst Solutions in the Zoho CRM Developer Hub help import large volumes of data into Zoho CRM while improving data quality and simplifying the migration process. Why Catalyst Solutions? Catalyst

      • Zoho Expense Import Reports Won't Work Because Default Accounts Already Exist

        Im trying to import reports from another Zoho expense account to mine and im getting errors that won't allow the import to happen The account name that you've entered 'Ground Transportation' already exists. Enter another name for the account and try again.z

      • Inactive License for free account.

        I recently upgraded my Cliq subscription not my team (on the free version), are unable to login to their accounts. The error message received is Inactive License Looks like you have not been covered under the current free plan of users. Please contact

      • Syncing zoho books into zoho crm

        I was wondering how I can use zoho books in crm as I have been using them separately and would like to sync the two. Is this possible and if so, how? Thanks

      • Announcing new features in Trident for Mac (1.34.0)

        Hello everyone! We’re excited to introduce the latest updates to Trident, which are designed to take workplace communication to the next level. Let’s take a quick look at what’s new. Connect with customers using Zoho Voice integration. You can now easily

      • How Zoho Contracts Makes Negotiations Clear, Secure, and Transparent

        Negotiation is one of the most critical—and often most chaotic—stages of the contract lifecycle. Multiple stakeholders review the same document, propose changes, debate terms, and exchange feedback. Without the right tools, this collaborative process

      • Error: Invalid Element tax_override_preference

        In both Books and Inventory, we're getting the following error whenever we try to enter any Bill: I think this is a bug. Even cloning an old bill (that obviously was entered just fine), this error occurs.

      • Assign Income to Project Without Invoice

        Hello, Fairly new user here so apologies if there is a really obvious solution here that I am just missing... I have hundreds of small deposits into a bank account that I want to assign to a project but do not want to have to create an invoice every time

      • How to Print the Data Model Zoho CRM

        I have created the data model in Zoho CRM and I want the ability to Print this. How do we do this please? I want the diagram exported to a PDF. There doesnt appear to be an option to do this. Thanks Andrew

      • our customers have difficult to understand the Statements

        our costumers have big problems to understand Zohos Statements. we need a text box after the payment number to explain what the payments are for. Is it possible to develop a version of the Statement with that kind of box and if so whu can do it

      • How to track a list of ALL the items that ONE customer bought?

        Hello, I am interested in getting a list of all the items that only ONE of my customers bought, and the invoices are what show up instead. It's very tedious to go through every single invoice when we sell a lot of items to this customer. Surely there

      • Exchange Rate Updates

        Hi, It would be great that when you work with multiple currencies, the exchange rate updates automagically every day (as seen on Zoho Books) or at least that when you create/update an opportunity the exchange rate could be manually updated, or maybe both!

      • Locked Out of Super Admin Account

        I'm locked out of my Super Admin account and the external e-mail is no longer associated with it. There seems to be a problem during set up, the system ought to ask to assign a new password instead of using Google accounts. Please help me get back access.

      • Emails are going to notification folder and not in inbox

        emails are going to notification folder and not into inbox

      • 550 5.4.6 Unusual sending activity detected. Please try after sometime

        Hi, I am receiving this error message when trying to send my emails. The only reason I can think why this is happening is my previous two emails were bounced back to me due to a non working mailbox error. I have followed the online links for unblocking

      • Projects Home Customization

        Hello! We've been in Zoho One since July of last year, and my team has started providing feedback on what they'd like to do. Their latest wish is that they could have more control over the Projects Home content. For example, they want a card that shows

      • Next Page