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

    • Bulk Associate Tasks/Task Lists to Milestones (Similar to Issues)

      Hello Zoho Projects Team, We hope you are doing well. We would like to request an enhancement regarding Milestone management for Tasks in Zoho Projects. Current Behavior: In the Issues module, it is possible to select multiple issues and easily associate

    • Allow agents to enable or disable notifications from certain departments

      Agents who are part of multiple departments receive notifications frequently from across the departments. They may not be actively involved in performing every action in all departments. For example, an agent could be a part of both the sales and support

    • WorkDrive API returning empty response even after placing file in Team Folder

      Hi everyone, I am trying to fetch a file from Zoho WorkDrive using a Deluge standalone function in Zoho People. The API call executes successfully using a configured connection, but the response is coming back empty. I have verified the following: The

    • Address Typeahead Extension for Zoho Books

      I installed the Address Typeahead extension for zoho books but when I went to configure it, it said that that version was deprecated. Is there a newer version somewhere?

    • Transaction Rules & Customer Payments

      So I have a situation as follows. We have many clients who are all invoiced on the 1st of each month on a recurring invoice for 1 of 10 plans. This means that almost all payment dates are the same (some people pay late) and that a lot of the amounts are

    • Can I Integrate ADP Payroll with Zoho Books?

      Hi, I am hoping that I can integrate ADP Payroll with Zoho Books so that I do not need to manually input the payroll journal entries. Is this possible? If so, how do I do that?

    • Zoho Books | Product updates | February 2026

      Hello users, We’ve rolled out new features and enhancements in Zoho Books. From Advanced Reporting Tags to the ability to mark projects as completed, explore the latest updates designed to improve your bookkeeping experience. Introducing Advanced Reporting

    • Darshan Hiranandani : How many participants can join a Zoho Meeting at once?

      Hi everyone, I'm Darshan Hiranandani, trying to find out the maximum number of participants that can join a Zoho Meeting at once. Has anyone here used Zoho Meeting for larger groups and can share their experience or knowledge about the participant limit?

    • Ask The Expert: Deep Dive into Zoho CRM, Desk, SalesIQ, and Campaigns!

      Are you using Zoho to power your sales, support, and marketing, and have questions about configuration, automation, or best practices? We have great news: the “Ask The Expert” session is coming to the Zoho Benelux Community! This session is specifically

    • How to create a new Batch and update Stock via Inventory?

      Hi everyone, We are building an automation where a user enters batch details (Batch Number, Mfg Date, Expiry, and Quantity) into a Custom Module. I need this to trigger an API call to Zoho Inventory to: Create the new batch for the item. Increase the

    • Zoho books Inventory valuation report VS Stock GL value

      At one client, we track inventory using composite (bundled) items, and in the system settings we have enabled the option to prevent stock from going below zero. After the year-end physical count and quantity adjustments, we identified a mismatch: Inventory

    • Zoho Zoom extension for CRM

      When meetings are booked through Zoho using the Zoom extension, the Zoom link in the invite is a host start link that contains an expiring ZAK token. The invite should instead use a standard attendee join link or a non-expiring host link - why is this

    • Stereo audio

      L.S. Is stereo audio supported in Zoho Meetings? I would like to conduct music lessons with Meetings, but the audio needs to be good (if not great!) and stereo, and not too much compressed and such.

    • Is Zoho Sites still actively being developed?

      Hello, Is Zoho Sites still actively being developed as part of the Zoho ecosystem? I noticed that the What's New page (https://www.zoho.com/sites/whats-new.html) does not show any updates since Q1 2025. We were considering migrating our website from Squarespace

    • Zia should track how customer relationships evolve over time

      Here's a feature idea that I've been thinking about The Problem Zia is great at analyzing individual interactions email sentiment, call transcription, best time to contact. But here's what it can't do: tell you how a relationship has evolved over time.

    • Zoho Surveys

      Dear Zoho Support Team, I hope this message finds you well. I am writing to inquire about the availability and documentation for the Zoho Survey API. Background: I am currently working on a project that requires programmatic access to survey data and

    • Zoho FSM API Delete Record

      Hi FSM Team, It would be great if you could delete a record via API. Thank you,

    • Duplicate Leads Concerns with Round Robin and Lead Approval Process

      It is great to have the Duplicate Lead Approval Process, there are a few issues with the process that I would greatly appreciate taken consideration in enhancing. It appears that A Lead comes in Lead owner assigned by the Round Robin Check for Duplicate,

    • Cliq iOS can't see shared screen

      Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? Regards

    • Incoming email replies not automatically associating with Deals/Opportunities - Is this possible in Zoho CRM?

      Hello Zoho Community, I'm running a travel agency (B2B and B2C) and we've been struggling with what seems like a basic functionality that we cannot get to work properly. Our use case: We send emails to suppliers (hotels, transportation companies) and

    • Writing SQL Queries - After Comma Auto Suggesting Column

      When writing SQL Queries, does anyone else get super annoyed that after you type a comma and try to return to a new line it is automatically suggest a new column, so hitting return just inputs this suggested column instead of going to a new line? Anyone

    • New Enhancements to Zoho CRM and Zoho Creator Integration

      Hello Everyone, We’ve rolled out enhancements to the Zoho Creator and Zoho CRM integration to align with recent updates made to the Zoho Creator platform. With enhancements to both the UI and functionality, This update also tightens access control by

    • Desk API that can search Merged Tickets

      Is there any method that can still search a ticket that was already merged? zoho.desk.searchRecords - doesn't explicitly mention

    • Issue with "Send Email" from Quotes not loading Email Template data

      Hi everyone, I'm currently experiencing an issue when using the "Send Email" option from a Quote record in Zoho CRM. What’s happening: When I go to the Quotes module and select a record, then click Send Email, the attached file (Quote) correctly pulls

    • Suggestions for Improved Table Management and Dashboard Filter Controls in Zoho Analytics

      Dear Zoho Analytics Community, I hope you are doing well. I would like to share a few suggestions based on issues I am currently experiencing while working with visualizations and dashboards. Firstly, when I create a new visualization using the Sales-Order

    • Introducing Rollup summary in Zoho CRM

      ------------------------------------------Moderated on 5th July'23---------------------------------------------- Rollup summary is now available for all organizations in all the DCs. Hello All, We hope you're well! We're here with an exciting update that

    • Zoho Projects and CRM Integration in Analytics

      Hi Team, In Zoho CRM, I’ve integrated CRM with Zoho Projects and associated a project within the CRM. The integration is visible under the Deals module. However, I’m unable to find this data in Zoho Analytics. Does anyone know where this information is

    • Cursor lands on disabled fields when tabbing through forms

      As of yesterday, form behavior has changed w/ regard to disabled fields. Previously when tabbing through a form the cursor would skip over disable fields (as would typically be desired). But now the cursor is landing on all disabled fields, in all of

    • Permission query

      we have two permission settings i.e 'user permission ' & 'portal user permission' and both have the same setting that we enable for our users, Now those who have access to user permission can see a button on the pivot table, "regenerate" but those who

    • Zoho Forms sheet prefil

      I've been using zoho sheet prefill for a week and it was working perfectly, as soon as i deleted the particular sheet and replaced it with another one no matter what I do it cant find the record and i get this message. Could you please assist me to resolve

    • "Create Deal" button should default to the currently selected layout/pipeline

      When viewing Deals in Kanban or List view with a specific layout and pipeline selected, clicking "Create Deal" should default to that same layout — not the profile's default layout. Currently it always opens the default layout regardless of context, requiring

    • Zoho mail admin panel not opening

    • Canvas: is it possible to have a fixed header?

      Hello. Does Canvas provide the option to have a fixed header, similar to the standard view? It would also be interesting for other parts of the interface, like the header or sidebar of the tab section. Thanks!

    • Removing Related Modules Lookup Fields Assignment / Relationship

      Issue: When creating a related list, I accidently selected module itself creating a circle reference. See attached. Situation: I wish to relating a custom module called "Phone Calls" to Leads and Contacts. Outcome: 1) I either want to remove the this

    • Zoho LandingPage pricing update: Essentials plan is now fully free!

      Hello Everyone, We have updated the Zoho LandingPage pricing structure. The Essentials plan, previously priced at $9 per month for 1,000 visitors, is now fully free, and the visitor limit has been increased to 5,000. With this change, users can start

    • ZOHO.CRM.UI.Record.open not working properly

      I have a Zoho CRM Widget and in it I have a block where it will open the blocks Meeting like below block.addEventListener("click", () => { ZOHO.CRM.UI.Record.open({ Entity: "Events", RecordID: meeting.id }).catch(err => { console.error("Open record failed:",

    • Zoho Survey – Page Skip Logic Not Working

      Hi everyone, I'm experiencing an issue with the page skip logic in Zoho Survey. Last week, it was working fine, and I haven’t changed anything in the settings. However, today the skip logic is not working at all. I also tried testing it with different

    • Automatically create an Delivery Challan basis a Sales Order.

      Hi, I wanted to know if there is a possibility to automatically populate a Delivery Challan basis a Sales Order. Is it possible to do this with custom functions? If yes, how? 

    • Zoho Form URL displays incorrect name

      Hi, I have a form I created called "Design Request form". It displays this way everywhere I look. However, in the URL, it shows up as "DesignJobRequestFormFINAL011325PROOFV1B" and I'm not sure why. I can't find where to fix this. Does anyone have any

    • Introducing Radio Buttons and Numeric Range Sliders in Zoho CRM

      Release update: Currently out for CN, JP, AU and CA DCs (Free and standard editions). For other DCs, this will be released by mid-March. Hello everyone, We are pleased to share with you that Zoho CRM's Layout Editor now includes two new field formats—

    • Next Page