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

    • Introducing the all-new email parser!

      Greetings, We are pleased to introduce to you, a brand-new, upgraded version of the Zoho CRM Email Parser, which is packed with fresh features and has been completely redesigned to meet latest customers needs and their business requirements. On that note,

    • Zoho Projects - Refine Access to Collaboration Menu

      Hi Projects Team, I noticed that Calendar, Chat and Meeting menu options in the Collaboration section are visible to client users, even when they don't have access to the features. This could be confusing and frustrating, because if it's there you expect

    • Kaizen #217 - Actions APIs : Tasks

      Welcome to another week of Kaizen! In last week's post we discussed Email Notifications APIs which act as the link between your Workflow automations and you. We have discussed how Zylker Cloud Services uses Email Notifications API in their custom dashboard.

    • Extracting Images from a Zoho Creator Report into a Pages

      If you’ve uploaded images in a form and can see them in your report, you might also want to display those same images inside a Page (custom HTML page). This is useful for dashboards, profile pages, or any place where images should be visible dynamically

    • Zoho is blocking emails I subscribe to from one sender

      About 4 months ago I stopped receiving newsletters that I subscribe to from @thedispatch.com. They tell me that zoho's server is blocking them. I've added them to my contacts list, but they're not even reaching my inbox. I don't know how to troubleshoot

    • Inactive Items - Make Less Prominent by Default

      Currently, when one marks an Item as "Inactive", it really doesn't do much of anything to hide it or get it out of the way. Search and reporting within Finance should, by default, hide inactive Items from standard reports, searches, etc. If one specifically

    • Items should display under specific warehouse

      I have configured the multi warehouse but it show all the items under all warehouse which is not correct according to our business logic, so i want that items should only display under that specific warehouse not under all the warehouses not even with zero quantity. Some items should be common but not all so is there any option for that purpose so i can specific the items to its warehouse. Regards

    • How to calculate GST based on "Ship To Address"

      We into the interior designing work, providing "Works Contract Services" to our clients across India. We are registered under GST in Maharashtra state. For works contract services as per the GST rule, we need to decide place of supply based on "Ship to

    • I NEED TO NUMBER TO TEXT NO HERE

      =NUMBERTEXT NEEED

    • Auto-fill New Row with Previous Row Values

      rowsize = input.Order_Items.count(); for each row1 in input.Order_Items { rowsize = rowsize - 1; if(rowsize == 1) { row.Door_Model = row1.Door_Model; row.Door_Color = row1.Door_Color; row.Materials = row1.Materials;

    • When I click on PDF/PRINT it makes the invoice half size

      When I click PDF / Print for my invoice in Zoho Books, the generated PDF appears at half size — everything is scaled down, including the logo, text, and layout. The content does not fill the page as it should. Could someone advise what causes Zoho Books

    • Python - code studio

      Hi, I see the code studio is "coming soon". We have some files that will require some more complex transformation, is this feature far off? It appears to have been released in Zoho Analytics already

    • API question - adding a thread to an existing ticket

      Hi Is there an API function for the customer to add to an existing ticket thread? example, customer puts in new support ticket. support replies and ask for more details. customer replies with more details -what api function is used for this (will add record append to same ticket number?) Thanks

    • Why is Zoho Meeting quality so poor?

      I've just moved from Office 365 to Zoho Workplace and have been generally really positive about the new platform -- nicely integrated, nice GUI, good and easy-to-understand control and customisation, and at a reasonable price. However, what is going on

    • 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

    • Calendar week view: Today + 6

      Is there anyway to have the calendar change dynamically based on the date? Due to the amount of events, we only display a week at a time, but towards the end of the week, we can no longer see ahead to next week (without changing it manually every time).

    • How to restrict user/portal user change canvas view

      Hi , I would like to restrict user / portal user change their canvas view because I hide some sensitive field for them. I dont want my user switch the canvas view that do not belong to them But seems Zoho do not provide this setting?

    • Introducing parent-child ticketing in Zoho Desk [Early access]

      Hello Zoho Desk users! We have introduced the parent-child ticketing system to help customer service teams ensure efficient resolution of issues involving multiple, related tickets. You can now combine repetitive and interconnected tickets into parent-child

    • How to add to Subforms via Zapier with Zoho Writer?

      I have the following layout for a Zoho Writer Document. As you can see there is a repeating subform taking in "Items". I am trying to make a Zapier integration with it, and I can see there is 1 field saying: "Items", but it does not specify how I'm supposed

    • Contrôles administratifs granulaires : un atout pour la sécurité des e-mails

      La moindre erreur dans votre système de messagerie peut coûter très cher à votre entreprise, tant en argent qu’en conformité et en image de marque. Pour beaucoup d’organisations, ce risque est bien réel. Les e-mails véhiculent quotidiennement des informations

    • Collections Management: #6 Realign Customers who gets back In-Term

      Arun stared at the subscription list on his dashboard. Another account had just been moved to Cancelled status after completing the whole dunning process. Nothing unusual, just that payment failures happen, retries fail, and cancellation is the expected

    • Zoho Mail IP Blacklist

      I need problems with send mails: Error: junk mail rejected - sender4-op-o10.zoho.com 136.143.188.10, is in RBL. Spamcop. Please remove FQDN for blacklist. Regards.

    • I can receive but not send emails

      Hello, I've been not able to send emails for almost a year now. I been using alternate email to do this. I want to know how to fix this so I can use my zoho account normally again.

    • The challenge of 24/7 connectivity: Being present and meeting customer expectations

      Before television entered our homes, radio was our window to the world. We had to tune carefully to catch voices from distant places. When television arrived, the world began to grow smaller. We can watch rocket launches, see the goal that wins our favorite

    • How to download all attachments from inbox, send, other folders in one go

      Hi All, Appreciate if anyone could help me with steps for below requirement. How to download all attachments from inbox, send, other folders in one go. Even mapping to new folder will help me. Thanks in advance.

    • Cannot connect mail accounts to Thunderbird

      Hi Support - I'm attempting to add my mail accounts to Thunderbird but I'm getting an unable to login to server error. I tried to use the password associated with my account I received the unable to login error. So I went into Zoho Accounts and generate

    • Alias Email Id already exists

      Hi, I just verified my domain sesque (dot) com and now I am trying to create the admin account using admin (at) sesque (dot) com, but I am getting an error saying "Alias Email Id already exists". I used to have another Zoho account with this email address,

    • Unable to connect to smtp server, connection timed out

      Hi Team, I am facing below issue, while sending out emails from thunderbird client. It used to work, facing this issue from morning. Error: Sending of the message failed. The message could not be sent because the connection to Outgoing server (SMTP) smtp.zoho.com

    • javax.mail.authenticationfailedexception 535 authentication failed

      Hi, I am facing 535 authentication failed error when trying to send email from zoho desktop as well as in webmail. Can you suggest to fix this issue,. Regards, Rekha

    • DKIM Verification Failed (Namecheap)

      Hi! I have already set up the TXT records in Namecheap but I keep getting the "Verification Failed" pop up. Was wondering if I'm the only one who has this problem and can anyone help me with this? Thanks!

    • Emails stuck in Queue

      Hi there, Since yesterday I have a few out going emails stuck in a queue. It say it will auto retry sending however nothing is happening. It seems to be affecting roughly 50% of my outgoing emails. Please help Thanks

    • Soft Bounce from transational emails from BREVO (Sendinblue)

      I manage the website of a client who uses your EMAIL service for the domain floranativadobrasil.com. And I use the BREVO email service, previously called SendinBlue, to send transactional emails about events specific to the website. All emails sent to

    • Ability to Edit YouTube Video Title, Description & Thumbnail After Publishing

      Hi Zoho Social Team, How are you? We would like to request an enhancement to Zoho Social that enables users to edit YouTube video details after the video has already been published. Your team confirmed that while Zoho Social currently allows editing the

    • Getting an error Address not found Your message wasn't delivered

      Hey, I'm trying to configure zoho mail for my website https://businessentity.org/ The email is meredith.karter@businessentity.org I'm able to successfully send the mails but when someone sends an email to above mail, this error shoots up: Address not

    • Support Uploading YouTube Videos Longer Than 60 Minutes

      Hi Zoho Social Team, How are you? We would like to request support for uploading YouTube videos longer than 60 minutes directly through Zoho Social. Your support team informed us that Zoho Social currently cannot upload videos over 60 minutes due to “API

    • Need Faster Help? Try Live Chat Support

      Hello there, We understand that sometimes, whether you’re facing an issue, exploring a feature, or need quick clarification, sending an email and waiting for a response just doesn’t cut it. You need answers, and you need them now. That’s exactly why we

    • Can't deactivate Spell Check

      Hi Community, right now I'm using the Zoho Mail Desktop-Software. So far, so good.. many possibilities. Overall very nice. What is extremely annoying right now, is that we are not able to deactivate the Spell Check feature. And we are barely able to focus

    • Zoho Toolkit Email Signature Generator

      I'm having real issues with the email signature generator with no matter where I host the photo, Zoho doesn't seem to show the photo on the link provided?

    • Company Policy Upload - Request All EE to review and sign

      How can I upload policies into Zoho People and have the employees review them and sign off saying they agree, etc.? Also, if I make a revision to a policy, I would like that changed or updated policy to be distributed or have the employees notified that

    • Zoho Sign Global Settings vs. Template and Document

      Hello, We are running into an issue on a current use case. We already use Zoho Sign. Now that KBA is available, we want to begin using it in our tax delivery process, by allowing clients to sign electronically, but also download a copy of their return

    • Next Page