API based Reporting: Add-on for Google Sheets. Part I.

photo created by rawpixel.com — www.freepik.com

Hmm… we need [THIS] report!

‘’Hmm… we need this report!’’ — The truth is, this is a request we hear far too often from our users. And it sure would take an enormous amount of time to implement the millions of unique custom reports that are needed for each hotel — that’s where Public API comes to the rescue. This solution allows every hotel to create any kind of custom report using their favorite tools and apps; like Google Sheets, for example. In this series of articles we would like to tell you how to create an add-on for Google Sheets. It’s easy, but of course there are some interesting details on the way. We will start with a basic implementation that contains all parts required to create a simple report, and then we will add more features to create a ready-to-publish add-on for Google Workspace Marketplace. Of course, all code is open-source and can be found in our GitHub repository — apaleo add-on for Google Sheets.

💡 We need a Plan!

• Prepare API endpoint to get the data. We will use apaleo API for our report.
• Implement authentication and authorization — OAuth 2.0
• Create Apps Script. Fetch data. Create report.

[ Part II will cover the items below, but for now, let’s focus on the first three steps]

• Create UI using Vue.js and Vue Material to make the report customizable and easy to use.
• Create Open Source project like a PRO.
• Publish the add-on in the Google Workspace Marketplace.

Let’s get it started

Okay, we have our plan and can now start to implement it. In this article we will talk about the first report that was implemented in the apaleo Add-on — Open Receivables and Liabilities Report. The API endpoint that returns JSON data for this report looks like this:

POST https://api.apaleo.com/reports/v0-nsfw/reports/gross-transactions

What is important to know about this endpoint is that the users must be authorized for a scope reports.read. So we need to note it down and request it using our auth service before making calls to this endpoint.

Authentication and Authorization

The apaleo Platform allows us to log in with a click of a button, or even without, depending on the the authorization type:

• OAuth 2.0 Client Credentials grant flow, or
• OpenID Connect and Authorization Code grant flow

With the Client Credentials grant flow add-on, after verifying its client ID and the Secret, will have access only to the one certain user account. Also, it’s a zero-click authentication scenario for the users of the report — they simply open a shared Google Sheets file that contains the add-on’s code, and they can generate the report right away. But it is only recommended for internal usage in a highly trusted environment. Read more about this flow in our developer’s guide about Client Credentials grant flow.

If you think about publishing the add-on in the Google Workspace Marketplace and allow users from multiple accounts to have access to their data, then the Authorization Code grant flow has to be considered. It means that before generating the report, users will have to click the “Sign In” button, log into their account, and grant the add-on access to their data through a consent screen. We also have a great technical guide that describes how this scenario works, check out this example of the apaleo platform — OAuth 2.0 — Authorization Code grant flow.

For both scenarios, we need to obtain the credential in the form of Client ID and Client Secret. In this part, we will go with the simplest scenario: the Client Credentials grant flow.

Apps Script and a simple report

There are different types of add-ons. In this article we will create a simple container-bound Apps Script. Bound scripts are effectively unpublished Editor add-ons that function only for the file they are bound to. To create a script bound to a Google Sheets file:

1. Create a new Google Sheets file,
2. Open the Script Editor by clicking on “Tools” > “Script editor” in the top menu.

To implement authentication and authorization we will use the existing OAuth library provided by Google Workspace team — OAuth2 library for Apps Script. To add it to your script, do the following in the Apps Script code editor:

1. Click on the menu item “Resources” > “Libraries…”
2. In the “Find a Library” text box, enter the script ID of this library: 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF and click the "Select" button.
3. Choose a version in the dropdown box (usually best to pick the latest version).
4. Click the “Save” button.

To be able to make any requests to the third-party APIs from our script, we also have to explicitly add the following authorization scope to the manifest file of the current project: https://www.googleapis.com/auth/script.external_request. You can read about authorization scopes for Apps Script project and how to set them explicitly here - Authorization Scopes.

After the library is connected, we can create our auth service that will be responsible for handling all authorization aspects and requesting the required apaleo API scope — reports.read :

Next, we need to create an API client that would allow us to call any API endpoint and provide the access token from our auth service for every API request:

Now that we have this done we are ready to make calls to apaleo API to get our data for the report:

Function getResponseBody is responsible for parsing the response body and handling error messages:

Basically, that’s all we need to load the data. And then it’s up to us how to transform and prepare it for rendering. Let’s just say that there is some function processData that does this job for us. So after putting all these things together, our final function for creating the report might look like this:

More information about how to work with spreadsheets from Apps Script can be found in this article — Extending Google Sheets.

Now we are ready to test our report. Select createReport function in the top panel of the Script Editor and then press Run. But make sure that the document to which the script is attached is also open — otherwise you'll get an error that “the function cannot be executed in the current context.”

To be continued

In the next part, we will dive into technical details and talk about how to create a UI for the report, set up GitHub repository, use CLI for Apps Script projects, and publish the add-on in the Google Workspace Marketplace. Stay tuned for Part II, we look forward to sharing more with you!

👋 Ciao!