Automate Export From Jenkins API Job List to Google Sheets Using Google Apps Script
One day… you were asked to create a list of data that comes from an API to a common platform appearance that will be easier for the managerial level to understand, such as Google Sheets. One of the real cases I experienced was exporting a list of Jenkins Job to Google Sheets for managerial view auditing purposes. Thanks to Google for providing easy integration with the various additional features that we can use. One of which we will discuss this time is Google Apps Script.
Easy fetching data using Google Apps Script
To access the Google Apps Script in Google Sheets just go to the upper menu section, click Tools, then Script Editor. This tool is easy to adapt if you’re familiar with Javascript syntax and has pretty good features that can provide some additional features in the use of various Google products. Here’s some feature quoted from Google Developer page
- Add custom menus, dialogs, and sidebars to Google Docs, Sheets, and Forms.
- Write custom functions and macros for Google Sheets.
- Publish web apps — either standalone or embedded in Google Sites.
- Interact with other Google services, including AdSense, Analytics, Calendar, Drive, Gmail, and Maps.
- Build add-ons to extend Google Docs, Sheets, Slides, and Forms, and publish them to the Add-on store.
- Convert an Android app into an Android add-on so that it can exchange data with a user’s Google Doc or Sheet on a mobile device.
- Streamline Google Chat workflows by building a chatbot.
One of the advantages that I quite feel great about is that we don’t need to create an interface for simple user input. For example, if you just want to user input Sheets name, you can easily add the following line when editing the .gs script:
and here’s the result :
Things to consider using Google Apps script (Case Study)
One of the things you might consider to learning this tool is the ease of retrieval of the data you need if the work you are doing is going to be repetitive. Maybe we will often use the copy & paste method to process data, if we can reduce the steps that are done manually, this can prevent human errors later. For to be more descriptive, let’s look at the example case below
using the Google Apps script we can directly enter data from the API into our Sheets without using a manual process. One example of a use case above is when you entering API data manually (for example using covertcsv.com), you copying the result back from the API using some converter tools and copy it back to Google Sheets. If you do it just once it might look normal, but if you do it a dozen times (because of updates from the data source/any kind of circumstances) it drives us to a less productive task.
By reducing the use of external tools and being carried out automatically, allow us to reduce the risk of human errors that may occur and also shorten the time we do to process / reprocessing data. In addition, creating a script can help us anticipate when the data source / API source that we use has some changes or additional structure because we can simply adjust the mapping of the data source in the script. The manual process may make this adjustment step longer and easier for human errors to occur
Let’s begin!
This tutorial can be applied to another API usage, just adjust the data structure you want to use in fetching steps.
Fetching data from Jenkins
First, we start by understanding the data structure of the Jenkins API, the structure, in this case, is only specific to the Job list section to retrieve information about jobs already run in the Jenkins with their last activity date.
The Jenkins API structure has many redundant attributes, we can take advantage of this structure to make it easier to retrieve data based on the context we need. I try to pull all the attributes available on the Jenkins API and put them at the end of this article, you can use it as a reference.
In this case, we will retrieve the Job ID (get from fullNameattribute), displayName, url, and last job run timestamp (get from thetimestampattribute in lastBuild object).
Fetching API data from Google Apps Script
The Jenkins API requires authentication to work, just adjust to the API authentication method you use if you’re not using the Jenkins API.
First, you need to generate a user token. Click Configure menu in your Profile menu list section. find API Token section, and click Add new Token button.
Type your token name for describing the token usage, and then click Generate.
Copy the shown token or simply just click the copy icon on the right side. Here’s a snippet to retrieve data from API in Google Apps Script
let auth_header = Utilities.base64Encode(user_name+ ‘:’ + user_token);
In this section, you can see Jenkins authentication uses a combination of username and user token encoded with the base64 scheme. Then sent it with the request header. Google Apps Script already provides the base64 encoder by calling the Utiliteslibrary. Next one, this part is the most important
let response = UrlFetchApp.fetch(url_endpoint,{ ‘method’: ‘GET’, ‘muteHttpExceptions’ : true, ‘headers’ : {‘Authorization’ : ‘Basic ‘+ auth_header} });
Google Apps Script has a library UrlFetchAppthat we can use for fetching data from API. the return type of this request is HTTPResponse object type, so we need to parse it to JSON to make it easier to process. And also, to determine which attribute we want to retrieve from Jenkins, we can specify the attribute in the endpoint URL like the snippet above.
Run Apps Script in Google Sheets
2 methods can be used to run the script that we have created. The first is to run directly on the Google Apps Script editor menu and Run it on Google Sheets.
To run in the editor menu, simply press the Run key or press CTRL + R on the keyboard. Make sure you keep the Sheets window open because various Sheets-related libraries won’t work if you close that window.
The second one is Creating a Menu to call the function. This method is creating a display menu that appears in the Google Sheets navigation, by making this menu it will be easier for us to access the script without the need to open the menu editor. The following is a function snippet to add a menu to Google Sheets navigation.
But a little note that the menu has been created will disappear when the user refreshes the window on the sheets, for that we need to re-run the script so that the menu reappears again. We don’t need to open the editor menu again to add this menu because Google provides an import macro function so we can execute this function without opening the editor. Just access the following menu in the Google Sheets
Then, you can select the function that contains adding menus to Sheets. and then the function name will appear below the Importbutton. Click the function name and the script will be run. Okay, now let’s add data to our Sheets
Adding data from API to Sheets
The process of adding data to Google Sheets starts with taking the sheet name, then we will add a header to the data and perform a loop to enter the data. The result will be like this
In this script, we create an array that is used to hold and formatting the data source and insert the data into Sheets.
For the header, we can use setFrozenRows so that the header doesn’t move when the data is scrolled down. because A1untilD1 is a header, then the range used to enter data starts from A2.
Result
Here’s an example result of the full script
Closing
- Google Apps Script helps us to fetch data from API easily and refill data again if there are new data added by just running the script.
- The reusable automated action prevents us from human error if we doing it manually.
- We can also improve the script usage experience by adding a UI menu so we don’t need to open the Google Apps Script editor just to run the script
- Google Apps Script Provide build in custom UI for every Google Document apps so we don’t need to build UI if we need simple input from users.
