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:
- Manual input
- Import from other formats
- Google services Functions
- Google Sheets Functions
- Automation software
- Import from APIs
- Web scraping
- Google Sheets Scripting
- 3-rd party built-in integrations
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:
- Create in-cell dropdown lists
- Add in-cell checkbox
- Use ARRAYFORMULA instead of copy-pasting a formula for each new row
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.
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:
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.
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.
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)
Use this function to import an RSS or Atom feed. Also, you can specify certain attributes of the feed you'd like to pull.
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.