Engineering Diaries: How We Created a Google Sheets Integration for our Product

Everything starts with a spreadsheet. Well, almost everything. Spreadsheets are the backbone of all business operations, whether budgeting, people management, expense management, organizing lists, etc. Spreadsheets often become the first choice for fledgling businesses, mainly owing to their versatility and flexibility. Little goes a long way with spreadsheets. For a startup, smart utilization of available funds is critical, and Google Sheets often comes in handy to plan projects, analyze risks, report metrics, generate quotes and predict financial outcomes. Start-ups even use spreadsheets to keep track of client lists, investor lists, and more. So what’s the problem? Things can get messy once you work on it collaboratively or establish strict flows around maintaining a spreadsheet database. Data can get corrupted, and not knowing the latest version, too many changes by too many people who have access to the database and no admin control.

This is where the power of low-code can be melded with the power of spreadsheets. Turning an excel sheet into a web application is a great way to contain errors due to poor data management, allow for granular control, admin and user access, and these applications scale along with your business. Today, it’s pretty easy to make an application from Google Sheets in record time.

There are many low code and no-code tools out there that can help you do this, including Appsmith. In this blog, we will talk a bit about the Google Sheets integration on Appsmith, how we built the integration and all the things you can do with it.

Behind the Scenes

One of our colleagues, Nidhi Nair, worked on making this integration a possibility. Nidhi joined Appsmith a little less than a year ago, and she enjoys the creative liberty to explore her ideas at Appsmith. “It was possible to use Google Sheets on Appsmith even without our integration. Users could do this using the REST API plugin. However, it’s not the most convenient, and I found it to be unwieldy and something that every user couldn’t configure intuitively or easily,” says Nidhi.

The Google Sheets integration was created to simplify the interaction for end users. “We identified a set of actions that users would want to use Google Sheets for and optimized the way they interact with data in their sheets,” adds Nidhi. This meant not having to deal with cells and columns, but just arrays. “We defined the scope of the integration to be similar to that of a database. A single sheet was understood to be a table that we wanted to manipulate with the integration. We identified the relevant actions for this,” she says. Implementing this integration meant doing considerable research around how users interact with sheets. For us, reducing the friction for users was a key priority. “We introduced something called RowObjects in the integrations that makes sure that users don’t necessarily have to tinker with the data themselves,” says Nidhi.

Key Challenges

“We wanted to be able to support the DB integration style interaction and also allow users that want to work on it as an excel sheet to be able to continue to do so. This was a relatively easy solution because of how flexible our logic for these integrations is,” says Nidhi, adding that a user could say ‘get me rows 1-10’ and for the next page, ‘get me rows 11-20.’ But they can also do something like: ‘Get me cells D3:J8, and on the next page, get me D11:J16’ (or whatever other logic they would like to use).

While this may sound trivial, having the liberty to navigate across the sheet at will means that they can organize their data separately from how it is consumed in Appsmith. The biggest challenge in creating this integration was to use Appsmith credentials as a provider for all instances to make it easy. With this, users don't have to set up configuration on Google; Appsmith has already done that for them. Setting up the configuration on Google comes with painful scrutiny, and it’s not for everyone, especially for people who do not deal with tech. “Appsmith’s one-click approval makes it easier,” says Nidhi.

However, this has a downside; Google Sheets on self-hosted instances cannot be used unless they connect to Appsmith’s cloud API.

Th engineering team is also working on and hopes to solve shortly for storing authentication on a per-user basis. This will allow users to access the part of the sheets they have access to and limit access to those they don't. To read more about the roadmap for features, follow this link.

How to Use Google Sheets Integration on Appsmith

With Appsmith's inbuilt Google Sheet Integration Plugin, you can use any Google Sheet as a data source or a backend to build robust applications. 1 Set-up Google Sheets PluginCreate a new account on Appsmith (it’s free!), if you are already an existing login to your Appsmith account. 2 Create a new application by clicking on the Create New button under the Appsmith dashboard. 3 We’ll now see a new Appsmith app with an empty canvas and a sidebar with Widgets, APIs, and DB Queries. 4 Click on the + icon next to the APIs section and choose the Google Sheets option. Next, click on the New Datasource button, set the scope to Read and Write, and click Authorise. 5 This will ask us to log in from our Google Account, choose the account we want to access Google Sheets, and log in. After successful authorization, this will redirect back to your Appsmith account. 6 Now, you’ll find your Google Sheets Datasource under your APIs, and you can create the necessary queries by choosing this data source.

Excellent; now that you’ve completed the set-up, follow the instructions in our docs and get started on your app!

Learn How to Make an App with Google Sheets

Are you interested in learning more about our engineering processes? Follow us on Twitter and Linkedin to stay tuned!

---

Write to me, vishnupriya@appsmith.com, and I’d love to get to know what you’re building with Appsmith!