Picture this: You’re a project manager, overseeing a sprawling software project with a big dev team, firm deadlines, and a tight budget. You need to create a weekly report for the folks who sign the checks, and they want the report to answer two key questions: 1. Are we going to meet our goals? 2. Are we under- or over-budget? The data you need to answer those questions exists in different systems, but unfortunately, those systems can’t aggregate or analyze the data. So what do you do?
If your first instinct is to use a spreadsheet, don’t worry – you’re not alone. In fact, this example is based on a real-life scenario. In that situation, the project manager had to spend time every week copying data over from one system, pasting it into a spreadsheet, and running the data through a series of formulas to produce the analysis needed for the report. It’s a process that’s tedious, time-consuming, and error-prone, plus almost every company has spreadsheet horror stories to share.
Improving the Experience With Digital Transformation
So is there a better way? Of course! At Macedon, we are continually looking for ways to streamline business processes, and one of the ways we do that is by utilizing Appian. One of the common themes of the projects we work on is trying to move clients away from the spreadsheet and into an Appian-based solution that focuses on automation and insights. Teams that can avoid interacting with spreadsheets typically operate much more efficiently, so that’s always a great solution, when feasible.
But there are some situations where a spreadsheet is still necessary. Even in these cases, though, Appian can always come to the rescue. One of Appian’s strengths lies with its ability to create harmony from disparate data sources. This feature means that Appian can take on the hard work of pulling data from source systems and inputting them into an Excel or CSV document.
What if you don’t use Excel?
I can hear what some of you are thinking: “Okay, that sounds great for Excel users, but my company uses Google Sheets. What about us?” For those unfamiliar, Sheets is Google’s spreadsheet offering, part of their family of office products. Sheets contains many of the same features as Excel, but it also offers real-time collaboration and simultaneous editing, making it an attractive option for modern companies that do much of their work using web-based applications.
However, despite the growing popularity of Sheets, it hasn’t enjoyed quite the same engagement level within the Appian community. If you were to run a search on Appian’s App Market at the time of this writing, you would find six results with “Excel” in the title. If you were to have run a similar search for “Sheets” just a few weeks ago, the number of results would have been…zero. That’s right; there were not any listings whatsoever to enable integration with Google Sheets.
That’s why we decided to create one! If you were to run that same “Sheets” search today, you’d find the Google Sheets Connected System Plug-In. During the summer, we developed a plug-in that could provide a robust set of integration capabilities that could enable companies that use Google Sheets to simplify their business processes.
Connecting Appian with Google Sheets
So what does the plug-in do? There are a few key features to highlight.
First, as you might expect, it allows you to connect an Appian application with a Google Sheet easily. After a quick authorization process, you can start using Appian integration objects that interact with the sheet. The integrations are based on templates, so most of the hard work is already done for you.
In terms of what types of interactions are available, at the most basic level, there is the ability to read from and write to a spreadsheet. Both are easy to use; for reading, you provide a spreadsheet ID (found in the URL) and a cell range, and the data in those cells is returned when you call the integration. For writing, you provide the spreadsheet ID and a dictionary of cell addresses and values, and when you call the integration, the values will get written to the sheet.
There’s also a third type of interaction, which allows you to create a new sheet. There’s an optional parameter to populate the new sheet with data, which enables useful functionality such as exporting a data set to a new sheet.
One of the cool features of all of these interactions is type conversion. To illustrate how this works, let’s consider date values. Google Sheets actually stores a date as a numeric value, but if you specify that you want to receive a date, the plug-in handles the conversion of the numeric value so that you receive a Date type rather than a Number (Decimal). That also works in reverse as well – when you pass in a value to get written, the plugin will determine the type based on what you passed in and automatically convert the value to the appropriate Google Sheets format.
The last thing to highlight is the companion application. While the plug-in provides all of the functionality described above, we wanted to accelerate development even further. As a result, we’ve created an Appian application that comes bundled with the plug-in. This companion application contains several examples of how to use the integrations modeled on real-world use cases, minimizing the amount of time an Appian developer has to spend familiarizing themselves with the plug-in’s capabilities. There are also a number of useful expression rules to handle some common functions (e.g., converting a spreadsheet column header to a numerical index).
Putting it all together
So let’s go back to the example of our poor project manager. In our model, they were spending an inordinate amount of time logging in to different systems, extracting out data, copying it into a spreadsheet, and running analysis on the data to answer the two big questions.
Using Appian, we can implement a few integrations to pull the data, and then utilize the Google Sheets plug-in to write the data to the appropriate sheet, where it can be easily shared and collaborated on. This can be completely automated, so the data is ready for analysis before the project manager even gets into the office on Monday morning. They can now spend more time doing work that utilizes all their skills and training and less time on tedious data entry. Hooray!
If the Google Sheets Connected System plug-in has piqued your interest, it can be found on the Appian App Market. If you have any questions at all, we’d be happy to help! Just reach out to info@macedontechnologies.com to start the conversation.
Want more insights from TechNest? Stay in the loop and subscribe to our magazine.
