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.

• Topic Participants

• Sasidaran K

  

• Luke

• Sasidaran Kumar

  

• 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

• World Password Day: 5 interesting facts about passwords

  It's World Password Day: that time of the year when we talk about password hygiene and the importance of safe password management. World Password Day is observed on the first Thursday of every May, and this year, we'd like to talk about some of the most

• Free Webinar: Go passwordless in 2022 with Zoho Vault

  Passwords have long been the preferred authentication method, largely due to their universal appeal. While they're easy for people to use and implement, they're also convenient for hackers to exploit. Reports from 2021 state that weak and stolen passwords

• Myki has announced EOL for its services | Learn why Zoho Vault password manager is the best alternative

  Hello Myki users,   Myki has announced end-of-life for its Teams, MSP, and GUARD services, after being acquired by JumpCloud. In their recent announcement, Myki stated that they will be removing their apps and extensions from the respective stores, turning

• Join our exclusive meetup with Zoho's Real Estate community

  Hey there, The Zoho Vault team is conducting a meetup for all real-estate users from Zoho. During this session, we will be discussing the need for secure password management and how Vault can help you and your clients safely protect passwords and other

• Free webinar: A quick walkthrough of Zoho Vault and major updates in 2023

  Managing passwords is crucial for all businesses. You can securely store, share, and manage passwords effectively from anywhere with Zoho Vault. We have introduced several new features in 2023 to offer the best online experience for our users. Join our

• Free webinar: Why a password manager is a “must-have” for everyone in 2024

  In the past decade, we've witnessed numerous cybersecurity breaches globally, with a significant portion resulting from the "it won't happen to me" mindset. Shockingly, in 2023, 86% of breaches involved weak and stolen passwords. Password hygiene is crucial

• Zoho Vault - Webinars 2023 - Video Recordings and Slide Decks

  Hello, We wanted to offer a consolidated list of Zoho Vault webinar resources from 2023. Therefore, we're putting together a list that includes links to our webinar recordings and slide decks for easy access. Webinar Video recording Slide deck Getting

• Free webinar: ‌Focal point: Building a financial ecosystem with Zoho Vault and Zoho Workplace

  Hi everyone! Cyber threats against the financial sector are escalating. In the last two decades, nearly one-fifth of reported incidents targeted financial institutions, causing $12 billion in direct losses. Cybercriminals are becoming more sophisticated,

• New features in Zoho Vault

  We’re thrilled to introduce a wave of powerful updates in Zoho Vault, designed to enhance security, streamline workflows, and improve your overall experience. Let’s dive into what’s new! Folder creation restrictions Limit who can create folders in your

• Join our World Password and Passkey Day expert Q&A 2025

  Hey everyone! World Password and Passkey Day is almost here, and there's no better time to talk about something we all rely on daily—secure authentication. Did you know that a staggering 60% of hacking-related breaches are tied to weak or stolen passwords?

• Dashlane discontinued its free plan: Here's why Zoho Vault's free plan is worth the switch

  Hey everyone, Dashlane password manager has officially announced that its free plan will be discontinued starting September 16, 2025. This change means that current free users will need to either upgrade to a paid subscription or export their data and

• Introducing SecureForms in Zoho Vault

  Hey everyone, Let’s face it—asking someone to send over a password or other sensitive data is rarely straightforward. You wait. You nudge. You follow up once, twice—maybe more. And when the information finally arrives, it shows up in the worst possible

• Clickjacking: Zoho Vault's Response

  Issue: Password manager browser extensions are found to be vulnerable to clickjacking security vulnerabilities that could allow attackers to steal account credentials, TFA codes, and card details under certain conditions. Reported by: Marek Toth, Independent

• Free webinar: Security that works: Building resilience for the AI-powered workforce

  Hello there, Did you know that more than 51% of organizations worldwide have experienced one or more security breaches, each costing over $1 million in losses or incident response? In today’s threat landscape, simply playing defense is no longer enough.

• Free webinar—Redefining workforce security with Zoho Vault: Passwords, passkeys, and multi-factor authentication

  Hi everyone! Did you know that in Q2 alone, 94 million data records were leaked globally? Behind every breach is a combination of poor password habits, phishing attacks, privilege misuse, and simple human error. The fallout—including reputational damage,

• Audio/video quality issues with Zoho Meeting – Any roadmap for improvement?

  Hi Zoho Team, We’ve been using Zoho Meeting for both internal and external meetings, and unfortunately, the experience has been consistently poor. The video and audio quality are so unreliable that it often renders meetings ineffective—especially with

• In arattai received message can't be deleted

  The issue has been noticed in following: arattai app (Android) arattai app (Window) arattai web While the message posted by me may be deleted, the ones received from others can't be. The item <Delete> change to <Report> when the message is a received

• Fill Colors

  Hello, Just curious, is it possible to manage the Used and standard colors in the fill drop down menu ? I would like to change/edit them is possible to colors I frequently use. Thanks for you reply, Bruce

• Is there a way to generate a virtual meeting for a group service in Zoho Bookings?

  Are virtual meetings not supported for group services/meetings? I have integrated Zoom with one-on-one services, but I need a way to create an online group meeting. Thanks

• Attachment reminder?

  My team and I often need to attach files to our messages, e.g. an explanatory screenshot or a shipping label. More often that I want to admit I mention the attachment but forget to actually attach it. Some email clients have a check-for-missing-attachments

• Items Below Reorder Point Report?

  Is there a way to run a report of Items that are below the Reorder Point? I don't see this as a specific report, nor can I figure out how to customize any of the other stock reports to give me this information. Please tell me I'm missing something s

• Markdown for Desk?

  Hi, my company wants to use markdown for formatting text in Desk (in all modules there, especially Tickets and Helpcenter). Zoho already offers use of markdown in several products (see https://help.zoho.com/portal/en/kb/backstage/microsite-guide/formatting-with-markdown/articles/formatting-with-markdo)

• Multiple upload field CRM

  I desperately need the functionality to add more than one upload field to web to contacts form. How can I do this?

• Client scripts for Zoho Books ?

  Good day everyone, I am looking for a way to be able to interact with the Quotes and Invoices as they are being created. Think of it like Zoho client script in Zoho CRM. But for the life of me I dont see a way to do this. The issue with having function

• Fillable template with dynamic tables?

  Is there a way to build a fillable template so that users can add rows to a table? To describe what I'm trying to accomplish the table has 3 sections; a header row, some number of rows with custom information, and a summary row with totals. I can't figure

• Invoicing multiple expense items as a single line item

        My client would like me to invoice them for multiple expense items on a single line item on an invoice.  Can this be done?  I know I can import billable expenses to an invoice, but I don't know how to show them as a single line item or have them

• Zoho Books | Product updates | November 2025

  Hello users, We’ve rolled out new features and enhancements in Zoho Books. From translating email notification templates to the new transaction locking restrictions, explore the updates designed to enhance your bookkeeping experience. Making Tax Digital

• Tables improvement ideas / features

  Heya, I've been using Zoho Tables for a few months now and wanted to post some features that I think will be greatly beneficial for the tool: 1. Ability to delete a record in automation or move a record in automation. - Usecase: I move a record from one

• Announcing offline payment modes for event tickets

  Hello everyone,   Collecting ticket payments from prospective attendees can be a tricky part of event planning. While Zoho Backstage has always allowed you to collect payments online as people buy tickets from your event microsite, we have now enabled

• What is going on with GMail extension.

  Suddenly, it is popping up and saying "Please wait while we work our magic... 10%" and it sits there. Yesterday I waited for an entire hour call and it never got further. Today, it's not resolved. Is there a fix I can do? I've tried all the typical

• Gmail is ramping up its email sender policy from November 2025

  Hello marketers, As you may be aware, Gmail introduced a guideline for bulk senders starting February 2024. If not, here's a quick refresher straight from Google: After this policy was announced first in 2023 and soft-implemented in February 2024, we

• Collapsible Sections & Section Navigation Needed

  The flexibility of Zoho CRM has expanded greatly in the last few years, to the point that a leads module is now permissible to contain up to 350 fields. We don't use that many, but we are using 168 fields which are broken apart into 18 different sections.

• Gmail is ramping up its email sender policy as of November 2025

  Hello marketers, As you may be aware, Gmail introduced a guideline for bulk senders starting February 2024. If not, here's a quick refresher straight from Google: After this policy was announced first in 2023 and soft-implemented in February 2024, we

• Good news! Calendar in Zoho CRM gets a face lift

  Dear Customers, We are delighted to unveil the revamped calendar UI in Zoho CRM. With a complete visual overhaul aligned with CRM for Everyone, the calendar now offers a more intuitive and flexible scheduling experience. What’s new? Distinguish activities

• Deluge Error Code 1002 - "Resource does not exist."

  I am using the following script in a Custom Button on a Sales Return. Basically, the function takes the information in the sales return (plus the arguments that are entered by the user when the button is pushed) and creates a return shipping label via

• Zoho Analytics Export API

  Hi Team, I’m working on some integration tasks and wanted to confirm if it’s possible to retrieve a Zoho Analytics table as JSON data using a Deluge script. I’ve already stored my custom data from multiple sources and combined it into a single source.

• Item images

  Can we get an "On hover" expanded image for items please ?

• Ability to Create and Manage YouTube Video Chapters in Zoho Social

  Hi Zoho Social Team, How are you? We would like to request an enhancement to Zoho Social that would allow users to create and manage YouTube video chapters directly within the platform. Currently, Zoho Social does not support manually adding chapters

• What happens to my current site SEO if i opt for zoho creator?

  I have an existing website and I need to use Zoho creators for the rapid creation of my webpage creation. Currently, my IT team is creating the web pages, but I am concerned about the SEO of my current website if I shift to zoho will i loose it all?

• Automatic Portal invite

  We have numerous customers we move through a blueprint in deals, when they get to a certain point we need to give them portal access, how can this be done through deluge or a workflow?

• Next Page