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

    • Zoho IP blocked by SpamCop 136.143.188.16

      Hello, I am unble to send any single email during the whole time due to the Zoho IP 136.143.188.16 being blocjed by SpamCop.net Please help on this.

    • Is there a way to configure dark mode for Campaigns emails that go out to customers?

      I've found a lot of information on how to configure dark mode for my (The user) personal Zoho workspace and email, but is there any way to edit dark mode settings on emails that we send out to customers via campaigns?  We sent out a test email the other

    • Can I hide empty Contact fields from view?

      Some contacts have a lot of empty fields, others are mostly filled. Is there a way I can hide/show empty fields without changing the actual Layout? I would like to de-clutter my view, and also be able to add information later as I am able. I would be

    • Auto-publish job openings on my Zoho Recruit Careers Website

      I have developed a script using the Zoho Recruit API that successfully inserts new jobOpening records to my Zoho Recruit website, but my goal is to auto-publish to the Careers Website. The jobOpening field data shows two possible candidates to make this

    • Credit Card Terminal for Zoho Books

      Hello, Instead of punching the credit card number manually for customer payment, do you have a third-party hardware credit card reader that works with Braintree? Thank You

    • How do you print out the invoices comments

      I have some invoices where i need to print out the comments that show when reminders and etc were sent how do we print those out in Zoho Books.

    • Feature Request: Enable Custom PDF Layout Editor for All Modules (Including Package Slips)

      Hello Zoho Community and Product Team, I am writing to share a suggestion that would significantly enhance the customization capabilities within Zoho Books. We all appreciate the power of the Custom PDF Layouts (the "New" template engine) that allows

    • Need advice for product/item search functionality when adding invoices.

      My client uses "Catalog or Vendor" name and Product code to search for his items. But Zoho only allow to search by product name and SKU when adding items to Invoices/Estimates. Clients product codes are not unique as they may overlap from different catalogs/vendors.

    • How to dynamically pass IDs from one API response to a second API call in Zoho DataPrep?

      Hi Team, I am setting up a global consolidated reporting pipeline in Zoho Analytics (India) using Zoho DataPrep as the ETL engine. I am pulling data from multiple Zoho Books Data Centers (US, Singapore, India) thats why i used the URL Source (OAuth 2.0)

    • Feature Request: "Send Invitation" Toggle for Events

      I am writing to request a critical "Quality of Life" update for the Activities module. Currently, adding people to the Participants field in an Event triggers an automatic email invitation/acceptance tracking with no way to opt-out. For general events

    • Account Review Pending - Daily Limit 100

      Hi ZeptoMail Team, I'm writing to request a manual review of my account. I am currently stuck on the 100-email daily limit and cannot purchase credits. Status: My domain is already verified (DKIM/CNAME). Issue: The "Customer Validation" option is no longer

    • Automation Series: Move Tasks to a different Task List on Status Change

      In Zoho Projects, you can automatically move a task from one task list to another when its status is updated. This keeps your tasks organised and ensures better visibility while reducing manual work. For instance, during a product launch, the marketing

    • Introducing Bigin's all new integration with Microsoft Teams

      Picture this: You are on call with your team to discuss some important deals that need to be closed soon. While discussing each deal, your team has to switch back and forth between your Teams account and Bigin account to refer to details about the deal.

    • Scaling the customer journey: What's new in CommandCenter

      Dear Customers, The last time we connected about CommandCenter, it was about the tool's new controls and revamped UI. Personally, the overhaul of the UI from the last update, still excites me. Today, we're here with news about its scalability and added

    • Adjusting Physical Inventory

      Not getting very far with support on this one, they say they are going to fix it but nothings happened since November. Please give this a thumbs up if you would like to see this feature or comment if you have some insight. Use Case: Inventory set to be

    • How to book GST paid in zoho books

      hi, i am a new user to Zoho books and not able to book GST paid in books, kindly suggest how i can book it in books. thanks, siddharth

    • Dynamic Remaining Quantity in Lookup During Allocation

      Hi everyone, From what I understand in Zoho Creator, lookup fields only display the stored value from the source record and do not dynamically update while a form is being filled. Because of this, showing a real-time updated remaining quantity inside

    • Is it possible to use HTML formatting in SMS messages sent from Zoho?

      Hi everyone, I have a question regarding sending SMS from Zoho When we send emails from Zoho, we can include HTML code to format the message (like adding links, styling, etc.). Is it possible to do something similar when sending SMS messages through Zoho

    • Important update: Migrate to the new SalesIQ live chat widget before April 14, 2026

      The old SalesIQ live chat widget will be deprecated on April 14, 2026. This is a final reminder to migrate to the new SalesIQ live chat widget before this date. After April 14, 2026, the old widget will no longer be maintained, which can lead to slower

    • If your IM chats aren’t auto-assigning, here’s what to check

      Hey everyone! We’ve been seeing quite a few questions around Instant Messaging (IM) Chat Routing in Zoho Desk, especially around how it actually behaves in real time. So I thought I’d share a practical breakdown of what’s happening behind the scenes.

    • connect zoho creator with google drive

      Hello everyone, I need to connect to a folder drive. The idea, is that google drive loads a text document with some data, I must read that text document to be able to autofill a form that I have in zoho creator with that data. I also attach PDFs and place

    • Uploaded files are not included when using "Include user submitted data" in Email Notification

      In Send Email notification workflow in Zoho Creator, there is an option called "Include user submitted data" which allows the email to contain all the form submission details. However, when this option is enabled, files or images uploaded through File

    • Extend Zoho Canvas Customization to Zoho Creator Forms and Reports

      Currently, Zoho Canvas allows users to design and customize the UI of Zoho CRM modules with a much better visual experience. This helps organizations create cleaner layouts, improve usability, and design interfaces that match their workflows. However,

    • Kanban View for Projects.

      At our organization, we describe active projects with various statuses like "In Proofing" or "Printing" or "Mailing". In the Projects view, one can set these project statuses by selecting from the appropriate drop-down. While this works, it's difficult to view and comprehend the progress of all of your projects relative to each other in a table. Creating a Kanban view for projects where I can move them from one status to another allows me to see where each project is in the order of our workflow.

    • Button ''I'm Interested'' won't translate

      Since our main platform is in French, I would like to get this ''I'm Interested'' button translated to French ''Je suis intéressé''. We managed to translate the whole carreer site in French, except the button. How come ? We have a standard subscription,

    • Feature request - image resizing on sales orders

      I need to be able to show the items on the sales orders, currently the item image shows really small and no way to resize it, need the ability to make the image larger to showcase the product on the pdfs

    • Email Parser Not Extracting Fields Correctly with Certain Label Formats

      I’ve been testing the Email Parser functionality in Zoho CRM to automatically extract data from incoming emails and map it to CRM fields. During testing, I noticed that parsing sometimes fails when the email contains field labels formatted like this:

    • Zoho CRM Community Digest - February 2026 | Part 1

      Hello Everyone! February has been off to a productive start in the Zoho CRM Community, with several thoughtful product enhancements and helpful technical discussions making their way into the forums. To help you stay up to speed, we’ve rounded up the

    • Nouvelle fonctionnalité : donnez du style à vos pages avec le Branding

      Que vous prépariez un webinaire important. Vous avez investi du temps dans votre contenu, invité des intervenants de qualité, et les inscriptions commencent à affluer. Mais votre page d’inscription ? Elle ressemble à toutes les autres : générique, sans

    • How to sync Zoho CRM Quotes with Zoho Books/Finance Estimates or Quotes

      Hi everyone, We’re building quotes in the Zoho CRM Quotes module because of its strong CPQ features and better communication options (multiple contacts, email customization, etc.). However, these don’t sync directly with Zoho Books/Finance for invoicing.

    • Marketing Tip #24: Encourage customers to leave reviews

      Reviews are one of the strongest trust signals in ecommerce. When shoppers see real feedback from other customers, they feel more confident about buying, especially when they’re choosing a brand for the first time. Not all reviews are equal. Detailed

    • Ability to Attach Record-Specific Files Automatically in Workflow Email Templates

      Currently in Zoho CRM, email templates allow attachments to be added, but these attachments are static and remain the same for every recipient. There is no straightforward option to automatically attach a file that is stored within the specific CRM record

    • This version of app doesn't support this notecard type Error

      So this problem is happening for any notes created within the last week, as well as any note recently edited on Android. I can open them on my phone fine, but they don't open on the website version. They DO work on the desktop app version. It's just web

    • Issue with Picklist Dropdown Not Opening on Mobile

      Hello I am experiencing an issue with picklist values on mobile. While the arrow is visible, the dropdown to scroll through the available values often does not open. This issue occurs sporadically, it has worked occasionally, but it is very rare and quite

    • {Action Required} Re-authenticate your Google Accounts to Continue Data Sync

      Hello Users! To align with Google’s latest updates on how apps access files in Google Drive, we’ve enhanced our integration to comply with the updated security and privacy standards, ensuring safer and more reliable access to your data. With this update,

    • Allocating inventory to specific SO's

      Is there a way that allocate inventory to a specific sales order? For example, let's say we have 90 items in stock. Customer 1 orders 100 items. This allocates all 90 items to their order, and they have a back order for the remaining 10 items which could

    • Is this a SCAM email or is it really Zoho?

      L.S. I received the following message. Is this from Zoho? I have had a Zoho One account for many years and my website has been online for years. If it is a scam, I think you should know about it.

    • How to close an estimate ?

      Hello, I have created estimates, and converted them to invoices to get 50% payment. Now I have 2 cases where the estimate stills shows status partially invoiced, however: 1. for one of them, project stopped half way, so the remaining part will never be

    • Updating Analytical Fields Data

      Dear Zoho team, I'm having an issue with the recently added fields in both Analytical Desk and Analytical. How can I generate the data in Analytical when new fields are added? https://analytics.zoho.com/workspace/2436819000000007005/edit/24368190000

    • My Zoho mail stopped receiving or sending emails about 3 hours ago

      Its a pop 3 account. The emails get into the actual mailbox on the server and I can send emails directly from the server, but they are no longer in Zoho, in neither of my Zoho accounts. All green ticks under Mail Accounts under Settings

    • Next Page