Import Data From Google Sheets: A Low-Code Tutorial (2023)

A study from 2022 showed that office workers spend up to 38% of their time using Excel, and 12% of their spreadsheets contain errors that could costs thousands of dollars. With cloud technology going mainstream, it wouldn't be surprising if the situation is similar for Google Sheets.

When this happens, employees are better off switching to a more robust database solution. But how to migrate data from Google Sheets to a database? In this article, we'll show you how to use Google Sheets' API to automate data extract-transform-load and save precious hours while reducing risks of data loss. We'll use Rowy to get a database up and running from our Google Sheets data in minutes.

Manual Method: CSV Import

Google Sheets can export data as CSV files (File -> Download -> CSV):

Which you can then import into Rowy. Rowy handles data mapping, and in 1 minute you'll have a database up and running:

While this solution is clear and simple, you'll probably have a hard time doing this for hundreds of spreadsheets with dozens of tabs each. That's where the Google Sheets API comes in.

Google Sheets Limitations

Rowy looks like a familiar spreadsheet, but it's a database. It has a lot of features that Google Sheets doesn't have.

First, Rowy provides rich database features like Action and Derivative columns, webhooks, and a rich text editor to make the most of your data. You can integrate with any third-party service via their API, and you can even build your own custom apps on top of Rowy. In comparison, Google Sheets formulas are limited to simple calculations and text manipulation.

A database also allows for greater permission management, so you can give access to specific users or groups of users without risking security leaks. Combine it with strong data typing and validation rules, and you'll have a much more robust data management system to drastically reduce data errors.

Lastly, switching to a database opens up new collaborative opportunities to gather and analyze data from multiple sources. Spreadsheets are great for data entry and sharing with a few people, but they're not designed for tens of people contributing at the same time.

Automated Method: API Import

Importing Google Sheets data via API is trivial to scale―just copy/paste the link, run an API script via a Rowy Action column, and use the JSON result to populate your database:

1. How to get started with GSheets

Enable the Google Sheets API in the Google API Console.

Rowy takes care of all other configuration steps for you, so you can directly send requests to the API without worrying about setting up authentication and what not.

2. API call example

The code of the Action column used to import data from a google sheets URL looks as follow:

const action:Action = async ({ row, ref, logging }) => {
    // 1. import client library for NodeJS
    const sheets = require('@googleapis/sheets')

    // 2. extract spreadsheet ID from URL
    const url = row.link
    const ar = url.split('/')
    const docId = ar[5]

    // 3. authenticate with Google Auth
    const gauth = new sheets.auth.GoogleAuth({
        scopes: ['https://www.googleapis.com/auth/spreadsheets']
    });

    const service = sheets.sheets('v4');

    // 4. get all the data from Google Sheets
    const res = await service.spreadsheets.values.get({
        spreadsheetId: docId,
        auth: gauth,
        range: "Sheet1"
    })

    logging.log(res)

    // 5. store data in database
    ref.update({sheet: JSON.stringify(res.data)})

    return {
        success: true
    }
}

The spreadsheet range defines which part of the spreadsheet to import. In this example, we're importing the first sheet (or tab) of the spreadsheet file. You can also import multiple sheets at once by specifying a range like Sheet1:Sheet2.

Once we have retrieved the data from Google Sheets, we can store it in the database as a JSON string.

A spreadsheet is a 2D array of cells, so you'll need to parse this structure according to your import needs and how you designed your spreadsheet in the first place.

Join Us On Discord

Got questions? Join our Discord community to get help from our team and other users. It's free, and we don't bite. Promise!