Skip to main content

Google Spreadsheet integration

Google Spreadsheets can be a powerful tool for managing and sharing data, and integrating them into your frontend application is easier than you might think. In this guide, we'll walk you through the process of using Google Spreadsheets in your frontend applications.

info

TLDR​

Make your Spreadsheet "public" and modify the link to be available as CSV:

# get the public share link
https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit?usp=sharing

# modify it to get a csv -> gviz/tq?tqx=out:csv
https://docs.google.com/spreadsheets/d/your-spreadsheet-id/gviz/tq?tqx=out:csv

Basic Structure​

Before we dive into the technical details, let's discuss some best practices for structuring your Google Spreadsheets:

  1. Prevent Empty Rows and Columns
    Ensure that your spreadsheet doesn't contain unnecessary empty rows and columns. This helps maintain data integrity and ensures that your frontend application only processes meaningful data.

  2. Provide Headers and Prevent Special Characters
    Include clear and descriptive headers for your columns. Avoid special characters, spaces/tabs or symbols in your headers, as they may cause issues when accessing the data programmatically.

For example, here's an example where we've successfully tested with 44,000 lines of fashion data.

To integrate Google Spreadsheet data into your frontend application, follow these steps:

  1. Create a Public Share Link
    First, you need to create a public share link for your Google Spreadsheet. Ensure that the sharing settings are set to "Anyone with this link" as a viewer. The link should look something like this: placeholder link.

  2. Adapt the Link for CSV Download
    To download the contents as a simple CSV, you'll need to modify the share link. Append /gviz/tq?tqx=out:csv to the end of the link. For example, if your share link is https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit, the CSV download link will be https://docs.google.com/spreadsheets/d/your-spreadsheet-id/gviz/tq?tqx=out:csv.

tip

You can pass the query parameter&gid=... to specify the tab of the spreadsheet you want to work with.

Integrate it in dcupl​

Now all you have to do is to add the csv link as a resource to your dcupl app loader configuration

"resources": [
    {
        "url": "https://docs.google.com/spreadsheets/d/1gubpGZongltNJnxSJTbyMNZANTSE8wpXTT0PnoNduvA/gviz/tq?tqx=out:csv&gid=1730858212",
        "type": "data",
        "model": "style",
        "options": {
            "autoGenerateProperties": true,
            "keyProperty": "styleID"
        }
    }
]

Limits​

It's important to be aware of the limitations of the Google Sheets API:

  1. Spreadsheet API Limits
    The Sheets API has per-minute quotas, and these quotas are refilled every minute. Keep in mind that these limits can affect the frequency and volume of requests you can make to the API. Google's documentation provides more details on these limits.

  2. Quota Increase Requests
    If you find that your application requires more API requests than the default limits allow, you can request a quota increase. However, there is no guaranteed approval, so plan your application's usage carefully.

Alternatives​

While Google Spreadsheets are a convenient option, consider these alternatives for more complex or high-traffic applications:

Some services specialize in converting spreadsheet data into APIs. This can be a more robust and scalable solution for production applications. Some examples are sheety.co or sheetdb.io. You can find a lot more with a simple google search.

During development and testing phases, continue to use Google Spreadsheets for easy data management. However, for production use, consider transitioning to a hard copy of the data or a spreadsheet-to-API service for better performance and reliability.

Summary​

In conclusion, integrating Google Spreadsheets into your frontend applications can be a valuable way to manage and share data. Keep in mind the API limits, consider alternatives for production use, and always structure your spreadsheets thoughtfully. With the right approach, you can leverage Google Spreadsheets effectively in your frontend development projects while ensuring data accuracy and efficiency.