TL;DR: hotglue has anawesome Google Sheets integrationthat handles a lot of the complexities out of the box and can be embedded directly into your product. Skip here for a demo.

Why would I need a Google Sheets integration?

If you’re in the SaaS space, chances are you’ve had to deal with importing data from a Google Sheets spreadsheet.

Spreadsheets have been powerful since they first started, but with the rise of cloud tools like Google Sheets and Airtable spreadsheets have only become more and more useful for thousands of business cases.

Beyond that, a Google Sheets integration is a great way to handle cases where you don’t have a formal integration for a platform a customer is using. Instead of turning customer who are using platforms that you don’t have native integrations with away, you could get them started with the Google Sheets integration and work on building a native integration for the platform later.

For those reasons, a Google Sheets integration is often expected out of the box by SaaS customers.

How does a typical Google Sheets integration work?

Okay, we’ve established the reasons for customers to request a Google Sheets integration. But how do you actually build the integration?

Get authorization from your user to access Google Sheets

All Google products use a standard OAuth process to provide third-party access. For those unfamiliar, this process allows a third-party to register with Google and request access to certain resources (such as Google Sheets and Google Drive).

For accessing Google Sheets, there are two scopes you will need to request access to:

• https://www.googleapis.com/auth/drive.metadata.readonly
• https://www.googleapis.com/auth/spreadsheets

The first gives you access to browse their Google Drive files, so they can select the relevant Google Sheets. The second gives access to the Google Sheets API, required for reading the actual data out of a Google Sheet.

Additionally, if you need long-term access to the users Google Sheets, you will need to set the access_type to offline. By including this in your request, Google will give you a refresh token to maintain connectivity with the users Google account.

Have the user select the relevant Google Sheets documents

Once you have access to their Google Sheets account via OAuth, you will need to have your user provide the relevant Google Sheets documents. There are two ways to do this:

1. You can use the Google Drive API to list all the Google Sheet documents and allow the user to pick the relevant files inside of your product. This is a great user experience, but requires a good amount of setup on both the backend (to request the list of documents and save the selections the user makes) and frontend (to show the list of files and allow user to select from them).
2. You have your user provide the id of the document themselves. This is generally a poor user experience, but can be okay for an MVP. For example, if you were viewing a Google Sheet at this url: https://docs.google.com/spreadsheets/d/1-cDxThi0ToJJazivDzKjfTzTyT5uAmWJZmnRvAUFlvw/edit#gid=0, the id of the document is 1-cDxThi0ToJJazivDzKjfTzTyT5uAmWJZmnRvAUFlvw

Clone the data from Google Sheets

Once you have all this info, you will need to clone the data from the Google Sheets API. Google has libraries (with examples) for many languages, such as Python, Node.js, PHP, Ruby, Java, and more.

You can access Google’s documentation on this here: https://developers.google.com/sheets/api/guides/values

Process the data

Now that you’ve cloned the relevant data, you’ll likely need to run some transformation process to clean the data and transform it into a schema your product can use.

There are plenty of tools for building these types of ETL pipelines. Here are some references for different approaches:

• ELT (transforming once in a data warehouse)

  • dbt

• ETL (transforming before sending to a database)

  • Python: open-source libraries like pandas, dask, and more
  • Apache Spark, especially good for large datasets
  • AWS Glue, and similar products

Since your customers will be providing the data payload, this processing piece will need to be generic to handle custom logic for each customer. This ensures that onboarding new customers will be a simple, repeatable process with just slightly different requirements.

As you can tell, there are a lot of steps involved in building out a robust Google Sheets integration. Each of these pieces has its own unique challenges, and will likely have a different architecture than an integration with a cloud-based SaaS platform like Salesforce or Quickbooks.

There’s a better way: hotglue

hotglue offers an integration platform that makes it 10x easier for developers to build integrations for their customers.

We have released an awesome Google Sheets integration that handles a lot of the complexities we outlined above out of the box! Check out the demo below:

Breaking it down

Let’s break down the hotglue Google Sheets integration into the same steps we did above.

Get authorization from your user to access Google Sheets

Just like we described above, you will need to implement Google’s standard OAuth process to request access to Google Sheets. The hotglue widget handles this out of the box, as pictured below.

Learn more about how to embed the widget in the docs: https://docs.hotglue.com/docs/embed-hotglue

Have the user select the relevant Google Sheets documents

Once we have obtained access to Google Sheets from the user, hotglue provides a document browser that allows the user to select the relevant Google Sheets they wish to import into your app.

This keeps everything super simple for your users, so they know exactly what access they’re granting you, and they can always change these settings on their own.

Clone the data from Google Sheets

hotglue handles cloning data directly from integrations via sync jobs. Jobs can be scheduled using cron expressions or triggered ad-hoc via the hotglue API or hotglue widget.

Sync jobs are highly scalable, and are designed to handle large amounts of data without issues. The hotglue admin panel allows you to manage all your users sync jobs, with built in logging and webhook functionality.

Process the data

hotglue includes a preprocessing layer based on Python. You can set a transformation script that processes the data from Google Sheets, and even customize it on a user level. This enables you to ensure that the process of ingesting the data your customers provide via Google Sheets will be a simple, repeatable process!

Learn more about how transformation scripts work in hotglue in the docs: https://docs.hotglue.com/docs/transformations-overview

Conclusion

In conclusion, offering a robust Google Sheets integration is not only an important feature for SaaS customers, but is often viewed as essential. Building a Google Sheets integration in-house includes many steps including implementing the OAuth flow, querying Google Drive to get relevant files, cloning the data at scale, preprocessing the data into an ingestible format, and ensuring the process is repeatable across multiple customers.

hotglue’s Google Sheets integration is designed to make the entire process of building a Google Sheets integration and supporting customers at scale easier via the embeddable hotglue widget, sync jobs infrastructure, and preprocessing layer.

If you’re interested in learning more you can book a demo here: https://hotglue.com/demo