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

• Sort Legend & stacked bar chart by value

  I'd love to see an option added to sort the legend of graphs by the value that is being represented. This way the items with the largest value in the graph are displayed top down in the legend. For example, let's say I have a large sales team and I create

• Scanned Doc - selecting Item overwrites Rate

  I have a Vendor Invoice which was uploaded to Documents. I select Add To > New Bill. The OCR is actually quite good, but it is reading an Item Description instead of an Item Number. I remove the description and select the correct Item Number... and it

• Timesheet invalid data error

  Getting the "Invalid Date" error when trying to add a time sheet to an appointment in a work order. I initially though the work order was corrupt or something so I deleted the work order and recreated it. I added the first time sheet to the AP and saved

• Convert invoice from zoho to xml with all details

  How to convert an Invoice to XML format with all details

• Any update on adding New Customer Payment Providers who support in store terminal devices?

  Currently there is only one Customer payment provider listed for terminal devices in USA- Everyware. They charge a monthly fee of almost $149 minimum. Will you add other providers - like Zoho Payments or Stripe or Worldpay that would allow integrated

• Dealing With One-Time Customers on Zoho Books

  Hello there! I am trying to figure out a way to handle One-Time customers without having to create multiple accounts for every single one on Zoho Books. I understand that I can create a placeholder account called "Walk-In Customer", for example, but I

• Passing the image/file uploaded in form to openai api

  I'm trying to use the OpenAI's new vision feature where we can send image through Api. What I want is the user to upload an image in the form and send this image to OpenAI. But I can't access this image properly in deluge script. There are also some constraints

• "Temporary" Field Value?

  I have a custom action in Form A report Detail View that passes the Rec ID and updates a Temp Record ID lookup field in the Form B record via openURL (and opens the Form B report in popup) . The updated Temp Record ID field value in Form B is then used

• File Upload field automatically replaces spaces with underscores – support experience

  Hi everyone, I want to share my recent experience regarding the File Upload field behavior in Zoho Creator and my interaction with the Zoho support team. When a user uploads a file, the system automatically renames the document by replacing spaces in

• We Asked, Zoho Delivered: The New Early Access Program is Here

  For years, the Zoho Creator community has requested a more transparent and participatory approach to beta testing and feature previews. Today, I'm thrilled to highlight that Zoho has delivered exactly what we asked for with the launch of the Early Access

• Queries on Project-Based Inventory Consumption and Proforma Invoice in Zoho ERP

  We would appreciate your clarification on how Zoho ERP plans to handle the following: Project-based inventory consumption without itemized sales orders Accurate project cost tracking along with inventory reduction Proforma Invoice usage We look forward

• Applying Excess Payments & Conflict Invoices Due to Cancelled Items

  I’m running into several issues that appear to stem from deeper-than-expected overlap between Zoho Finance (Books/Inventory) and Zoho POS. The level of coupling between these systems seems greater than what was originally communicated, and it’s leading

• Zoho Sprint Backlog View, filter by item status

  Hello, In Zoho Sprints, it would be great to be able filter out specific items in the Backlog based on their status. We would like to track items that were Removed from our backlog without seeing them constantly in the Backlog view, as this view should

• WATERFALL CHART IN ZOHO ANALYTICS

  Hi Team, I would like to know whether Zoho Analytics currently supports a Waterfall Chart as a built-in visualization type. If yes, could you please share the steps to create one? If not, is there any workaround or recommended method to build a Waterfall

• Export contacts from Bigin's mobile app to your Android device

  Hello everyone! We're happy to announce that you can now export customer contacts from the Bigin mobile app to your device. Scenario A small-scale business owner has two pipelines in Bigin—one for procuring raw materials and the other for selling his

• ASAP iOS SDK – Xcode Compatibility Update

  Hello everyone, We have been delivering the ASAP iOS SDK developed using Xcode 16.1 to provide Help Center support within mobile applications. Thus, ASAP iOS SDK versions upto 4.5.8 are compatible with development environments running in Xcode 16.1 and

• Sub form auto field population based on parent form

  I have a parent form called "Sites" with a text field called "Site". I have a subform called "Design Comments" (actual form name "Review Comments") with a lookup field name "Sites1" that looks up from the Sites form. I want the Sites1 lookup field to

• Cloud Setup Solution

  I am here looking for Cloud Setup and Data Migration solution for organization. I also searched this on many different website and found many solutions. We are bit confused to which one to choose. One of my friend suggest me this platform, and i am hoping

• A note-taking app right inside your project management space

  How do you feel when you check off the last task from your to-do list after a long day at work? Euphoric, blissful, satisfied? Now, imagine completing the same checklist from your PM platform, without juggling tools. Sounds simple and handy? That’s exactly

• Email Forwarding: Maintain business continuity

  As an administrator, ensuring uninterrupted email communication is critical when a role-based employee is unavailable due to extended leave or a temporary role change. Emails from customers, partners, and internal teams must continue to be received and

• Tip #61- Collaborate live with Invite Technician- 'Insider Insights'

  Hello Zoho Assist Community! From the series of Technician Console, we will be exploring Session. Let's jump right into a brief overview. We’ve all been there: you’re deep into a complex remote session, and you realize you need a second pair of eyes.

• How to integrate Zoho CRM, Zoho Forms and a WIX Web Site

  Attached video demonstrates how to use Zoho Forms included in Zoho One, to design a Contact Us form to be embedded into a WIX web site and integrated into Zoho CRM.

• Bug Causing Major Sync & SO Access Failures in Zoho POS

  We are experiencing critical and recurring issues in Zoho POS that all trace back to role-permission handling defects in the latest POS app version. These issues directly affect syncing, login ability, and Sales Order access for role-restricted users

• Add Custom Reports To Dashboard or Home Tab

  Hi there, I think it would be great to be able to add our custom reports to the Home Tab or Dashboards. Thanks! Chad Announcement: The feature is being rolled out across DC's and Edition in phases. To know more refer to the announcement post here.

• How to block whole domain?

  I am getting at least 50-75sometimes over 100 spams emails a day. I see a lot of the spam is coming from .eu domains. I would like to block /reject all email coming for the .eu domain. I do not have any need for email from .EU domains. Why won't the BlackList

• Hiding Pre-defined Views

  You can enhance Zoho with custom views - but you cannot hide the pre-defined views. Most users focus on 4 or 5 views. Right now for EVERY user EVERY time they want to move to one of their 4 or 5 views - they have to scroll down past a long list of pre-defined

• why can't agent see accounts & contacts

  My new user, with 'Agent' privileges, cannot see records that don't belong to them. How can I give them access? Why isn't this the default configuration in Zoho?

• Sender Email Configuration Error.

  Hello Team, Hope you are all doing well. We are in the process of creating the Zoho FSM environment in the UAE. When we try to add the sender email address “techsupportuae@stryker.com”, we receive the error message: “Error occurred while sending mail

• 【Zoho CRM】フィルター機能のアップデート：ルックアップ項目を使ったデータフィルタリング

  ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 今回は「Zoho CRM アップデート情報」の中からフィルター機能のアップデートをご紹介します。 ルックアップ項目を使ったデータフィルタリングがより詳細に行えるようになりました。 この機能は詳細フィルターとカスタムビューで利用でき、必要な情報を正確に取得できます。 これにより、タブ間を移動することなく、より深く正確な方法でデータを絞り込むことが可能になります。 ◉できること 詳細フィルターとカスタムビューで、ルックアップ先タブの項目が選択可能

• This will be long, Please bear with me - Next Gen Layout - Search

  In general, I think that Zoho are going in the right direction with the Next Gen UI. The latest update brings some nice improvements and all-in-all from a user's perspective I think the improvements are generally very good. However, there are some areas

• Zoho Books | Product updates | July 2025

  Hello users, We’ve rolled out new features and enhancements in Zoho Books. From plan-based trials to the option to mark PDF templates as inactive, explore the updates designed to enhance your bookkeeping experience. Introducing Plan Based Trials in Zoho

• Zoho Books | Product updates | August 2025

  Hello users, We’ve rolled out new features and enhancements in Zoho Books. From the right sidebar where you can manage all your widgets, to integrating Zoho Payments feeds in Zoho Books, explore the updates designed to enhance your bookkeeping experience.

• Weekly Tips: Protect Confidential Information with PGP in Zoho Mail

  We deal with confidential information almost every day, whether it is being sent out or received. Though emails sent using Zoho Mail are encrypted both during transit and at rest, attempts to access and steal your sensitive data are always a threat that

• Quotes Approval

  Hey all, Could you please help in the following: When creating quotes, how to configure it in a way, that its approval would work according to the quoted items description, not according to quote information. In my case, the quote should be sent to approval

• Important Update: Facebook Pages API Deprecation

  Dear Zoho Analytics users, As of January 26, 2026, Facebook has officially deprecated Facebook Pages API version 18. This update involves the removal of several metrics that were previously supported by the API. As a result, these changes will affect

• Adding a Deal to and Existing Contact

  I want to easily add a Deal to an existing Contact. If I click on New Deal on the Contact page this currently this is what happens: All of the mandatory field (and other field) information exists within the Contact. Is there a simple way for it to automatically

• Custom SMTP is now available in Zoho Sign

  Hi there! Want to send Zoho Sign emails from your organization's or personal email server? Look no further! Zoho Sign has introduced custom Simple Mail Transfer Protocol (SMTP) for Enterprise users across all data centers. By enabling custom SMTP, you

• Deleting a memorized email address

  How can I delete a memorized email address? Even though the address has been deleted from Contacts, Zoho mail still auto suggests the address when typing it into the TO field. Thanks!

• Email was sent out without our permission

  Hi there, One customer just reached out to us about this email that we were not aware was being sent to our customers. Can you please check on your end?

• Flexible Partial-Use Coupons (Stored Value Credits)

  Subject: Feature Request: Ability for users to apply partial coupon balances per transaction Problem Statement Currently, our coupons are "one-and-done." If a user has a $50 coupon but only spends $30, they either lose the remaining $20 or are forced

• Next Page