10 Ways to Get Data into Google Sheets | Part 1

Oksana Pechenkina -

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!

But first, you need to fill your Google Sheet with data.

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. Check Part 2 and Part 3 to learn more about the remaining six.

We start with probably the most obvious yet definitely one of the most frequently used ways to get data into Google Sheets.

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:

Fetches current or historical securities information from Google Finance.

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://).
The URL must be enclosed in quotation marks or it can be a reference to a cell that contains the appropriate text. Only 50 ImportData functions can be used per Google Sheet.

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.

The function is written as =IMPORTHTML(URL, query, index). The syntax requires the following elements.

  • URL - The URL of the page to examine, including protocol (e.g. https://). It should be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
  • query - Either "list" or "table" depending on what type of structure contains the desired data.
  • index - The index, starting at 1, which identifies which table or list as defined in the HTML source should be returned.

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.

The function syntax includes the URL of the page, including a protocol (e.g., https://) and an xpath_query – the XPath query to be run on the structured data (for more information, please refer to this article)

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. Once you give the access, any user on the destination sheet can use this function to import data from the source spreadsheet. Permission can be revoked at any time. Also, if the data set you're trying to import is too big, then you'll get an error message.

The function syntax requires the URL of the spreadsheet you want to pull data from (including the protocol), enclosed in quotation marks, and a range string formatted as "[sheet_name!]range" to specify the range to import. The default function will pull from the given range on the first sheet.

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

Check Part 2 and Part 3 for more ways to get data into Google Sheets.