Using the Stripe API and a Google Apps Script to populate a Google Sheet invoice template

Published April 29, 2019 (updated: April 5, 2021) in code.

image via @kmuza on Unsplash under Creative Commons

As someone who has been working in Customer Support for Streak for three years, I frequently handle billing questions from users. One of the most common requests, especially from our international customers, is for an invoice with more information than we are currently able to offer either directly from the Streak UI or from Stripe. This article is about a tool I built to solve that problem.

Problem

At Streak, we use Stripe as our billing backend to handle processing payments, manage recurring subscriptions, and generate receipts. A problem I’ve frequently run into is some users wanting to add in additional billing info onto their invoices, such as a billing address and/or a VAT number. But there’s unfortunately not a way currently to do this within Stripe and have it show in their invoices.

This has led to us on the Support team having to generate invoices every so often by hand, manually entering the data into a Google Sheet invoice template. We then export the Sheet invoice to a PDF and email it to the customer. It’s not super difficult filling out these invoices manually by hand as a one-off request, but with enough users requesting custom invoices, it can become time consuming fairly quickly. Another problem is if the customer is requesting multiple invoices at once: Doing multiple invoices by hand would obviously take an even larger amount of time (you’d be amazed at the number of users who want 12+ months worth of invoices at once!).

I started thinking about the problem and wondered if there was any potential way to automate the entire process (or at least part of it). Originally I was thinking of possibly using a third invoicing party tool to extract the data from Stripe, then export the data from the third party tool. But I ran into a few problems: First, I didn’t want every single Streak payment to become an invoice in this new format, only one-off invoices upon request. Then most third-party invoicing tools want to handle the entire payment process, not just the act of generating the invoices. I soon started realizing I was going to have to build my own tool from scratch. I knew this would be a massive undertaking, but soon I was diving in.

Doing Research On What To Build And How To Build It

Not knowing anything about how APIs work other than at a very high, macro level, I knew enough conceptually that if I could somehow tap into the Stripe API and pull the data from the invoice that would be the best solution. I considered making my own landing page in HTML, CSS, & JS that would contain the layout of the invoice, but I then decided that wasn’t necessary and would be too much work.

Since I already had an invoice template in Google Sheets that I’ve been using to create invoices by hand for users, I decided it made the most sense to use the same template and pull the data from the Stripe API into Google Sheets, automating as much of the process as possible.

After some copious amount of Googling, I discovered that Google Sheets has a tool called Google Apps Script which makes it easy to extend the functionality of Sheets by allowing the use of code behind the scenes of the Sheet. And luckily for me, Apps Script is primarily based off of the language I’ve been primarily been spending my time learning, JavaScript.

I did some digging about connecting to the Stripe API via JavaScript and saw it’s possible. I started reading through the Stripe API documentation and found they specifically have a section under “Invoices” for retrieving data from an invoice. Exactly what I needed!

My First Time Retrieving Data From An API

Since I had never used an API before, I figured now was as good a time as any to dive in and get my feet wet. I copied the Stripe test API URL with the unique invoice code from the test Stripe invoice. Then I fired up Terminal on my Macbook and ran the command to pull data from the API (private data hidden below by x’s):

curl https://api.stripe.com/v1/invoices/xxxxxxxxxxxxxxxxxxxxx \
-u sk_test_xxxxxxxxxxxxxxxxxxxx:

Success! The terminal responded with data from the test Stripe invoice. I was ecstatic, knowing that I had crossed a major milestone in my learning and development.

Building Test Projects In Google Apps Script

Now that I knew how to connect to an API to retrieve data, the next thing I needed to do was to learn how to build a tool in Google Apps Script. I discovered this awesome article by Ben Collins called “Beginner guide to APIs with Google Sheets & Google Apps Script“. Ben’s article not only explained how to use Google Apps Script to pull data from an API into Google Sheets but shows you how to build a few test projects along the way. Very cool!

Ben’s article was super helpful and I built the first two projects following his instructions, called Numbers API and iTunes API. After following along with the article and building those two test projects, I decided I was ready to try my luck at building my own project.

Into The Deep End – Building My Own Project With No Hand Holding

There’s a giant leap that takes place during the jump from the hand-holding of coding along with detailed instructions as part of an online course to building a brand new project from scratch. I’m not going to lie, it was a bit intimidating at times knowing everything was on me and that I was building something new from scratch.

I slowly built up more and more confidence as I wrote each line of code little by little and nothing catastrophic broke down ( ::knock on wood::). The StackOverflow community was extremely helpful in answering my questions and helping me out when I got stuck. I asked the following three questions working on my project:

My experience working in customer support for Streak has helped a tremendous amount with asking questions to StackOverflow. Anywhere from 3-5 times per week, I file customer bug reports and/or product feedback to our engineering team. Doing this for the past three years I’ve learned a tremendous amount on how to communicate properly with engineering teams, giving them all of the information they need up front, giving as many details as needed, while also writing very concisely. This helped me tremendously in knowing how to frame my questions to the StackOverflow community, how much detail to give, and how to ask for precisely the information I needed to answer my question.

Success!

With my questions answered from StackOverflow, I was able to run my tests with the test Stripe invoices and confirm my code was working successfully! I was able to pull the data from the API I needed. The next step was looking at the results of the data. One interesting thing I learned is the API returned timestamps in Epoch/Unix time. Luckily I found this website to explain that it’s possible to convert this into human MM/DD/YYYY time.

I then did some Googling and found a formula to convert a date in Epoch time in Google Sheets to MM/DD/YYYY. I then used some different formulas in Sheets to convert some of the API data into the format I wanted in the invoice.

Once I confirmed everything worked like I wanted it to, I was able to get a live API key in Stripe from our dev team and start doing some testing with live customer invoices. Everything works great!

One of the interesting quirks is that invoices generated part of a new billing cycle output slightly different code than invoices in the middle of an existing billing cycle. So I have to slight variations of the same code and two slightly different Google Sheet invoice templates.

You can view the GitHub repo here!

Part 2 of this project can be read about here, where I add in the ability to save the invoice as a PDF and email it via one click from the Sheet dashboard. The same script at the same time also saves a copy of the PDF invoice to a specific folder in Google Drive.