Tips to add & import data

On this page

• Use Forms for automatic data entry
• Use BigQuery data
• Compile data from separate spreadsheets
• Create reports from Analytics data
• Track stock prices or earnings
• Find and update data
• Protect data from being updated
• Automate tasks with macros
• Do more with add-ons and scripts

Expand all  |  Collapse all

Use Forms for automatic data entry

Instead of manually entering survey responses in a spreadsheet, use Forms. Create your survey in Forms and send responses instantly to a spreadsheet in Sheets. The spreadsheet is linked to your survey, so new responses appear in real time.

Learn how

Create a survey in Forms:

To create a survey, see Get started with Forms.

Send survey responses to a new spreadsheet:

1. In Forms, on the Responses tab, click More Select response destination.
2. Select Create a new spreadsheet.
3. (Optional) To change the name, enter a new one.
4. Click Create.

Send survey responses to an existing spreadsheet:

1. In Forms, on the Responses tab, click More Select response destination.
2. Select Select existing spreadsheetSelect.
3. Select your spreadsheet and click Select.

To open your spreadsheet from Forms, click View responses in Sheets . The spreadsheet opens in a new window. To go back to Forms, click FormShow summary of responses or go back to the Forms window.

 

Use BigQuery data

You can access, analyze, visualize, and share billions of rows of data from your spreadsheet with Connected Sheets, the new BigQuery data connector.

• Collaborate with partners, analysts, or other stakeholders in a familiar spreadsheet interface.
• Ensure a single source of truth for data without additional .csv exports.
• Streamline collaboration using delegated access.

Learn how

To understand how Connected Sheets works, you can explore connected sheet examples with public data. 

1. On your computer, open the Google Sheets template gallery.
2. Choose one of the connected sheet Examples.

Learn more at the Google Docs Editors Help Center

 

Compile data from separate spreadsheets

If you keep data in separate Google Sheets, you can copy a range of data from one spreadsheet to another with the IMPORTRANGE function.

For example, you may track quarterly sales data for a product in a different spreadsheet for each region. To combine all that quarterly sales data, copy the data from each region's spreadsheet into a single spreadsheet using IMPORTRANGE.

Learn how

Import data from another spreadsheet

1. In Sheets, open a spreadsheet.
2. In an empty cell, enter =IMPORTRANGE.
3. In parenthesis, add the following specifications in quotation marks and separated by a comma*:
   • The URL of the spreadsheet in Sheets. Copy and paste the URL from the spreadsheet that contains the data you want to import.
   • The sheet name (optional) and the range of cells to import.

   *Note: If you are in a Spanish-speaking country, use the semicolon as a separator instead of the comma.

4. Press Enter.
5. Click Allow access to connect the 2 spreadsheets.

For example: To import cells A1 through C10 from sheet 1 of the abcd123abcd123 spreadsheet, you enter: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10"

Learn more at the Google Docs Editors Help Center

 

Create reports from Analytics data

Want to get insights from customers so you can make better business and marketing decisions? Use Google Analytics to see how visitors find and use your site—and keep them coming back. Then, use Analytics add-on to import your data into Sheets. After your data is in Sheets, you can clean it up, create graphs, and share your findings with your team.

Learn how

Get the Analytics add-on

1. If you haven’t already, sign up to start using Analytics.
2. Open Sheets.
3. Click ExtensionsAdd-onsGet add-ons.
4. Search for the Google Analytics add-on and click Install.
5. Click Continue.

Create an Analytics report

1. Open a spreadsheet in Sheets.
2. Click ExtensionsGoogle AnalyticsCreate new report.
3. Enter your information and click Create Report. Your analytics data appears in a new spreadsheet.
4. For help, see Google Analytics Spreadsheet Add-On.

 

Track stock prices or earnings

Monitor stock prices or earnings per share with the GOOGLEFINANCE function in Google Sheets.

• See revenue trends and changes.
• Compare prices and earnings with major competitors.
• Predict future stock trends and directions.

Learn how

1. In Sheets, open a spreadsheet.
2. In an empty cell, type =GOOGLEFINANCE.
3. In parenthesis, add any of the following, separated by a comma:
   • A ticker symbol in quotation marks.
   • (Optional) The attribute you want to show, such as price, in quotation marks.
   • (Optional) A start and an end date preceded by DATE and followed with the numerical date in parenthesis.
   • (Optional) Daily or weekly frequency in quotation marks.
4. Press Enter.

For example: To list the Google stock price, taken at the same time daily for 2019, you type: =GOOGLEFINANCE("GOOG", "price", DATE(2019,1,1), DATE(2019,12,31), "DAILY")

Learn more at the Google Docs Editors Help Center

 

Find and update data

Searching for data and updating it doesn’t have to be time-consuming. You can quickly find and update text or numbers in Sheets.

Learn how

1. In Sheets, open a spreadsheet and click EditFind and replace.

2. Next to Find, enter the text or numbers that you want to find.
3. Next to Replace with, enter the new data.
4. Next to Search, choose the sheets that you want to search.
5. (Optional) To refine your search, select additional options.
   You can make your search case-sensitive, find exact matches, use regular expressions, or search within formulas.
6. Choose an option to replace the data:
   • To replace instances one at a time, click FindReplace.
   • To replace all instances, click Replace all.

 

 

Protect data from being updated

If you don’t want people to change the content in a spreadsheet, you can protect it. This shouldn’t be used as a security measure. People can print, copy, paste, and import and export copies of a protected spreadsheet. Only share spreadsheets with people you trust.

Tip: If "Protected sheets and ranges" isn't visible, you're likely in Microsoft Office editing. To use sheets and ranges protection, convert your file to Google Sheets. Learn about Microsoft Office editing and how to convert Microsoft Office files.

Learn how

Important: When you protect a sheet, you can’t:

• Simultaneously lock the formatting of cells and allow users to edit input values
• Protect data with a password

1. Open a spreadsheet in Google Sheets.
2. Click Data Protect sheets and ranges. A box will open on the right.
3. Click Add a sheet or range or click an existing protection to edit it.
4. To protect a range, click Range. To protect a sheet, click Sheet.
   • Range: To change or enter the range you’re protecting, click the spreadsheet icon and highlight the range in the spreadsheet.
   • Sheet: Choose a sheet to protect. If you want a set of cells to be unprotected in a sheet, check the box next to "Except certain cells."
5. Click Set permissions or Change permissions.
6. Choose how you want to limit editing:

• To show a warning when anyone makes an edit: Select "Show a warning when editing this range." It doesn’t block people from editing, but they’ll see a message asking them to confirm if they really want to make an edit.
• To choose who can edit the range or sheet: select "Restrict who can edit this range." Choose:
  • Only you: Only you (and the owner if you’re not the owner) can edit the range or sheet.
  • Only domain: If you use Google Sheets for work or school, only people in your domain can edit the range or sheet. This option is only available when everyone in your domain can edit the spreadsheet.
  • Custom: Only the people you choose can edit the range or sheet.
  • Copy permissions from another range: Reuse the same permissions you set up on a different set of cells or sheet.

7. Click Save or Done.

To see protected cells, click View  Show  Protected ranges. A striped background will appear over the cells.

Learn more at the Google Docs Editors Help Center

 

Automate tasks with macros

Save time by automating repetitive tasks with macros in Google Sheets. Say you need to format new data imports or build the same chart across multiple sheets of quarterly data. Repeating the same steps manually can take hours. Use a Sheets macro to record these actions and easily use them again without having to write code.

Learn how

1. On your computer, open a spreadsheet at sheets.google.com.
2. At the top, click Extensions  Macros Record macro.
3. At the bottom, choose which type of cell reference you want your macro to use:

• Use absolute references: The macro will do tasks on the exact cell you record. For example, if you bold cell A1, the macro will only ever bold cell A1 regardless of which cell you clicked.
• Use relative references: The macro will do tasks on the cell you select and its nearby cells. For example, if you record bolding cells A1 and B1, the macro can later be used to bold cells C1 and D1.

4. Complete the task you want to record. When you’re done, click Save.
5. Name the macro, create a custom shortcut, and click Save.

• Note: When you create a macro in Google Sheets, an Apps Script is created. To edit this Apps Script, at the top, click Tools Script editor.

To perform a macro, click Extensions Macros the macro you want.

Learn more at the Google Docs Editors Help Center

 

Do more with add-ons & scripts

Use ready-made add-ons to do more with Gmail and Google Docs, Sheets, Slides, and Forms. You can also create your own scripts in any of the Docs editors with Google Apps Script.

• Add Google Analytics to your spreadsheet.
• Add a thesaurus to your document.
• Add form responses in Docs to create a poll.
• Add interactive questions to your presentation.
• Manage sales and customer relationships directly in Gmail.
• Add custom menus and windows with Google Apps Script.

Learn how

Install an add-on

1. On your computer, open a document, spreadsheet, or presentation.
2. Click Extensions Add-ons Get add-ons.
3. Point to an add-on to find a short description. To find a full description, click the add-on.
4. To install the add-on, click Install Continue.
5. For most add-ons, a message will appear requesting access to data that the add-on needs to work. Read the message, then click Allow.
6. After the add-on installs, click Done.

Create a script

1. In Docs, Sheets, Slides, or Forms, open a document, spreadsheet, presentation, or form.
2. If you’re using Docs, Sheets, or Slides, click ExtensionsApps Script.
3. If you’re using Forms, in the top-right corner, click MoreScript editor.
4. Create your script.

For help, see Overview of Google Apps Script.

Learn more at the Google Docs Editors Help Center

 

_{Google, Google Workspace, and related marks and logos are trademarks of Google LLC. All other company and product names are trademarks of the companies with which they are associated.}