Google Sheets Connected System (Macedon)

Overview

If you've ever viewed and edited a spreadsheet online, chances are good you've used Sheets, Google's online spreadsheet editor with real-time collaboration. Google Sheets is great for many use cases, but what if you want to connect your spreadsheets with Appian? Building out an integration from scratch could easily take weeks of development time to get a solution up and running.

Fortunately, there's a better option. With the Google Sheets Connected System plug-in for Appian, you can start interacting with Sheets from within your Appian applications in minutes. The plug-in is easy to install and includes a number of operations that cover a variety of use cases. Pull in revenue data from your financial spreadsheets? Check! Write a list of users' names an emails to an employee directory sheet? Check! Export your sales projections to a new spreadsheet that management can use to make decisions? Check! Whether you need to read data, write data, create new sheets, or do all three, this plugin allows you do so with ease.

In addition to the plugin, which can be easily deployed to any cloud or self-managed environment, this component also comes with a companion Appian application. The companion application has a connected system and integration objects already created, which can be configured with just a few fields. There are also a number of functions available that make it easier to do common operations (like converting column numbers to a spreadsheet header), as well as several examples that show how the plugin functionality can be used in a real-world application. As a result, you not only get the functionality of the plugin, but also get a head-start on the Appian development as well!

Key Features & Functionality

This plug-in allows you to create a new type of connected system, Google Sheets. The connected system uses Appian's out-of-the-box OAuth 2.0 handling for authentication and is easy to set up.

There are three integration operations available for this connected system:

Read Cell Values, which allows you to specify a spreadsheet with a range of cells and will return all the values in the range you selected. You can also specify the expected type for each column, and the plugin will handle the type conversions automatically.
Write Cell Values, which allows you to enter a spreadsheet and a set of values to write. Again, type conversion is handled by the plugin - if you pass in a date, for example, the plugin will do the hard work of translating it from an Appian date into a value that is compatible with Google Sheets.
Create New Sheet, which allows you to specify the name for the new sheet and the folder in which to create it. You can also include values to write to the new sheet, which enables export behavior (for example, if you wanted to send the contents of a database table to a sheet).

The companion app also includes a number of useful functions and examples:

Functions: Column Number to Letter, Column Letter to Number, Add Value to Column, CDT Array to JSON String, Character to Integer
Example - Interactive Grid: Shows a grid that can be synced to a spreadsheet, allowing you to view the data from the sheet. The grid is editable, so you can make changes in Appian and watch your spreadsheet as the values get updated in real time!
Example - Export CDTs to Sheets: Provides a simple interface showing how you would export a list of CDTs (such as those retrieved via a database query) into a spreadsheet, including all the available options, such as specifying a starting cell and including a header row.

Put together, the plug-in and companion app can shave weeks off the development time for your Google Sheets project.