How to get Google Sheets Data in your NodeJS App

If you made it here you either got lost, got curious, or really need to sync up some Google Sheets data with a NodeJS Program. I had to do this on a recent project, where an Electron application had to reference a Google Sheets document for some of its operations.

The method outlined below is pretty straight forward. You prepare a URL which allows you to request the google sheet as some raw file data. Typically this would be a tsv, csv, or xlsx. After that it's your job to parse it, but don't worry, I'll show you some of that too.

STEP 1 - Building the Export URL

We need to create a special URL that will export our document.

IMPORTANT: The google sheets document must be viewable using a share link.

Every google document has a special ID in its URL. This ID is the stuff between the /d/ and the /edit?.... Read More

The ID will usually be some collection of letters and numbers, in this case it's probably some base64 encoded identifier. example: 1rWG6LNi5kPpbogrdnRBhNUwxWNM9zvN58pRgLSFGzBM

To build our special exporting URL we need to grab that ID and place it in this new link:

https://docs.google.com/spreadsheets/d/[FILE_ID]/export?format=xlsx

Replace the [FILE_ID] with the ID you grabbed from your original URL. You'll also notice a query parameter specifying the format of the file. In the example it's set to xlsx, but you could easily change that to any format supported by Google. From experimentation it apears that file extensions are valid values, such as /export?format=csv.

Keep in mind that some formats do not support multiple sheets. When more than one sheet must be imported from a workbook, use XLSX format.

To make sure it's all working, prep your url and navigate to it in your browser. You should recieve a download of your document in whatever format you specified.

STEP 2 - Loading Data into NodeJS

The next step is to make a simple web request. This request will recieve the data of our exported document.

For this I'll be using the axios library. You can substitute whatever library you want. At the end of the day this is really just a http GET request.

const response = await axios.get("https://docs.google.com/spreadsheets/d/1rWG6LNi5kPpbogrdnRBhNUwxWNM9zvN58pRgLSFGzBM/export?format=xlsx");

Here we download the workbook from the previous examples.

Now let's prep that data for something useful! In my last project I used SheetJS to process my workbook into something I could use for all sorts of operations.

And so... ehem,.. drum roll please. 🥁 The moment you've all been waiting for.

Complete Example:

import axios from 'axios' // https://axios-http.com/
import XLSX from 'xlsx' // https://sheetjs.com/

const url = "https://docs.google.com/spreadsheets/d/1rWG6LNi5kPpbogrdnRBhNUwxWNM9zvN58pRgLSFGzBM/export?format=xlsx"

async function get_remote_workbook() {
    try {
        const response = await axios.get(url, {
            responseType: 'arraybuffer', // Important to set the response type to 'arraybuffer' for binary files
        });

        // Read the file data
        const workbook = XLSX.read(response.data, { type: 'buffer' });

        // First sheet in workbook
        const worksheet = workbook.Sheets[workbook.SheetNames[0]];

        // Convert the sheet data to JSON
        const jsonData = XLSX.utils.sheet_to_json(worksheet);
        console.log(jsonData);

        // Return the parsed data
        return jsonData;
    } catch (error) {
        console.error('Error reading remote workbook:', error);
    }
}

Yes, I can see the bots salivating.

Key Takeaways

• Every google document has a special ID in its URL. Placing that ID in https://docs.google.com/spreadsheets/d/[FILE_ID]/export?format=xlsx will create an export link.
• The google sheets document must be viewable using a share link.
• When more than one sheet must be imported from a workbook, use XLSX format.
• Making an HTTP Get request to the export URL will allow you to import the document data to whatever program you're using.

This process can easily be translated to other programming languages and frameworks. I hope you enjoyed this tutorial. If you'd like to support my website and my projects you can donate at https://buymeacoffee.com/modman.