10 Ways to Get Data into Google Sheets

Google Sheets is a powerful collaborative online tool that can be great for data management, organization and analysis. And it can be used as a database for a website or app!

So here are 10 key ways to gather data into Google Sheets:

  1. Manual input
  2. Import from other formats
  3. Google services Functions
  4. Google Sheets Functions
  5. Forms
  6. Automation software
  7. Import from APIs
  8. Web scraping
  9. Google Sheets Scripting
  10. 3-rd party built-in integrations

Below, we are going to cover the first four options.

1. Manual input

Sure, sure, this is not the most exciting one, but sometimes it can be the best or even the only option.

If the data amount is small and doesn't update frequently, or data export is prohibited from the data source, manual input seems like the most reasonable way.

And you can make it more efficient by delegating this task to a virtual assistant, or using one of the ways below like:

2. Import from another format

If you already have your data stacked in a file, you can manually import it into Google Sheets.

The following formats are supported:

  • Microsoft Excel (.xls, .xlsx, .xlxm, .xltx, .xltm)
  • OpenOffice (.ods)
  • Comma Separated Variable (.csv)
  • Tab Separated Variable (.tsv)
  • Text files (.txt)
  • MapInfo (.tab)

3. Google Services Functions

There are several functions that let you use tools from the Google ecosystem right in your Sheet.

These are:

  • GOOGLEFINANCE
    Fetches current or historical securities information from Google Finance.

  • GOOGLETRANSLATE
    Allows you to translate text from one language to another, detect the text language, detect and translate from one language to another.

4. Google Sheets import functions

Being a cloud-based tool, Google Sheets allows you to automate data collection and import data from third-party APIs.

It can be done by using these built-in functions:

=IMPORTDATA
You can use the =IMPORTDATA function to import data from a given URL in CSV or TSV format. Just use the function =IMPORTDATA() and place the URL between the brackets. The URL must include the protocol (e.g., https://).

Example:

=IMPORTDATA("https://www.stats.govt.nz/assets/Uploads/Employment-indicators/Employment-indicators-August-2020/Download-data/employment-indicators-august-2020-filled-jobs-by-sex-and-age.csv")

=IMPORTHTML
Use this function to pull the data from a table or a list within an HTML page.

Example:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_The_Office_(American_TV_series)_episodes","table",2)

=IMPORTXML
With this function, you can import data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and Atom XML feeds.

Example:

=IMPORTXML("https://en.wikipedia.org/wiki/Moon_landing", "//a/@href")

=IMPORTFEED
Use this function to import an RSS or Atom feed. Also, you can specify certain attributes of the feed you'd like to pull.

Example:

=IMPORTFEED("http://news.google.com/?output=atom")

=IMPORTRANGE
This function allows you to import cells from another spreadsheet. To use this function you need to enable permission for the sheet to import data from another one.

Example:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10")