Learn how to optimize your data ingestion

You might need to ingest a large amount of data that includes:

• Values
• Formulas
• Images

To be more efficient, use the best practices below for some common use cases.

Use Copy & Paste to ingest your data

You can use copy and paste when you ingest a small amount of data. For example, when you ingest a few hundred rows of values or images smaller than 1 MB.

Tip: To only paste values from your data without the format or formulas, click Paste special  Values only.

Use Import functions to ingest small amount of dynamic data 

These are a family of functions that allow Sheets users to dynamically read data when they’re subject to frequent changes.

Import functions & their use cases

IMPORTRANGE: Imports a range of cells from a specified spreadsheet.
IMPORTDATA: Imports data at a given URL in .csv or .tsv format.
IMPORTHTML: Imports data from a table or list within an HTML page.
IMPORTFEED: Imports an RSS or ATOM feed.
IMPORTXML: Imports data from any of various structured data types that includes:

• XML
• HTML
• CSV
• TSV
• RSS
• ATOM XML feeds

Tip: You can learn more about Import functions.

 

You can use Import functions when your data is dynamic or subject to frequent changes. For example, if the spreadsheet you read from is to record inventory data from another spreadsheet that updates every 2 hours, use Import functions to read the most up-to-date data, as Import functions update the result periodically.

Use Import or Upload to Drive to ingest data from Excel

If you have a lot of data that exists on an Excel file or other supported file type, you can use File  Import instead of direct copy and paste. It’s more reliable and faster, especially when you ingest a large amount of data. It also tends to keep as many features as possible from other file types. 

In the input dialog, to replace or append data in a sheet, you can select to import files.

1. Click File  Import.
2. Go to Upload  Select a file from your device.
3. Select your file.
4. Select an Import location.
5. Select a Separator type.
6. Click Import data.

You can also use Google Drive to upload your Excel file.

1. Open your Google Drive.
2. Click New  File upload.
3. Select your Excel file.
4. Click Upload.

Use Insert Image to ingest large images 

Important: Cells with an image can’t contain text.

If you have a large image to ingest, you can use Insert Image instead of Copy and Paste.

1. To insert an image in Google Sheets, click the cell you want.
2. Click Insert  Image. 
3. Select an option:
   • Insert image in cell
   • Insert image over cells
4. Select an image or take a screenshot.
5. Click Open or Select.

Use Copy to transfer a large amount of data from one Google sheet to another

When you have a lot of data from another Google sheet that‘s static and not subject to changes, you can copy spreadsheets.

1. On the bottom, next to the sheet's name, click Down arrow   Copy to.
2. Select an option:
   • To create a new spreadsheet with a copy of the data, click New spreadsheet.
   • To select another spreadsheet to copy the sheet into, click Existing spreadsheet.

Use Google Apps Script to create your own data ingestion functions

To create your own functions with more flexibility to ingest data, use Google Apps Script.

Use Google Sheets API with your programming language 

If you’re a developer or user who wants to use your preferred programming language to write and read Google Sheets data with more complex logic, use the Google Sheets API.

Use Connected Sheets when your data lives in BigQuery 

If your data lives in BigQuery, to ingest data to Google Sheets, use Connected Sheets.

Import Google Forms data into Google Sheets 

Google Forms and Google Sheets also support a seamless experience that makes it easier to ingest data from Forms into Sheets. It automatically generates a spreadsheet that updates your response. Learn more about linked spreadsheets with Google Forms.