📊 Lesson 6.2: Import, Export, IMPORTRANGE & Connecting Sheets

Getting Data In

Data arrives in files all the time — a CSV export from your bank, an .xlsx a colleague emailed, a table on a web page. Sheets takes them all.

For a file, use File → Import (or just drag the file onto your Drive). Sheets asks how to bring it in:

• Create new spreadsheet — the file becomes its own Sheet.
• Insert new sheet(s) — add the data as a new tab in the current file.
• Replace spreadsheet / Replace current sheet — overwrite what's there.
• Append to current sheet — add the rows to the bottom of existing data.

For a CSV, watch the separator and how dates/numbers are detected — a CSV is just plain text, so Sheets guesses types (and locale can affect date order). You can also simply paste tabular data copied from a web page or another app; use Paste special if you want values only.

IMPORTRANGE — A Live Link Between Sheets

IMPORTRANGE pulls a range from another spreadsheet into this one, and keeps it live: change the source and the copy updates. It's how you split data across files while keeping one master.

=IMPORTRANGE("spreadsheet_url_or_id", "Sheet1!A1:D100")

The first time you use it, the formula shows a #REF! with an "Allow access" prompt. Click it once to authorize the connection between the two files, and the data flows. (You need at least view access to the source.) From then on it stays connected.

The Other IMPORT Functions

Sheets has a small family of functions that pull data from the web. They're genuinely useful, but be honest with yourself about their fragility.

They're perfect for a quick, personal "pull the current standings from this page" trick. But they depend entirely on the source: if a website redesigns, your IMPORTHTML silently breaks, and heavy use can hit rate limits. Treat them as convenient, not dependable — for anything important, a manual import you control is safer.

Exporting & Round-Tripping With Excel

Getting data out is easy: File → Download offers Microsoft Excel (.xlsx), CSV (current sheet), PDF, and more. PDF export has handy print options (fit to width, gridlines on/off, which tabs) and is great for a clean report; CSV is the universal plain-data format; .xlsx hands a working spreadsheet to an Excel user.

About round-tripping — exporting to Excel, editing there, importing back — here's the honest picture:

• Usually survives: values, most common formulas and functions, standard number/date formatting, basic charts.
• May shift or drop: Sheets-only functions (like QUERY, IMPORTRANGE, and some Google-specific ones have no Excel equivalent), certain advanced formatting, and features unique to one app.
• Best practice: keep the "master" in one app and treat the other format as an export. Round-tripping repeatedly is where things quietly break.

⚠️ Important Note: You can also work with an Excel file directly in Sheets without converting it — Sheets can open and edit .xlsx files, though a few Sheets-only features are unavailable in that mode. For a true Sheets workbook with full features, convert it (File → Save as Google Sheets).

A Single Source of Truth

Connecting sheets unlocks a professional pattern: one master data file, many report files that read from it. Your team enters data in the master; each report or dashboard uses IMPORTRANGE to pull just what it needs. Update the master and every report reflects it — no copy-pasting, no version drift.

This mirrors the "keep raw data raw" habit from Module 3, scaled across files: the master is the truth, everything else is a view of it. It keeps big collaborations sane. The alternative — twelve people each keeping their own copy — is how organizations end up arguing about which spreadsheet is "the real one."

🎯 Project: Connect & Export

You'll practice both directions: pulling live data in and pushing snapshots out.

=IMPORTRANGE("https://docs.google.com/…", "Sheet1!A1:D20")

Swap Sheet1 for the source tab's real name (e.g. Data).
First run shows #REF! with an "Allow access" button —
click it ONCE to authorize the link. After that it stays live.
Keep the imported range no bigger than you actually need.

Best Practices for Connecting Sheets

✅ Do's

• Decide snapshot vs live link before you import.
• Keep one master and let reports read from it with IMPORTRANGE.
• Import only the range you need to keep things fast.

❌ Don'ts

• Don't rely on IMPORTHTML/XML for anything important — websites change.
• Don't round-trip repeatedly between Sheets and Excel; things quietly break.
• Don't link files you don't trust — a live connection shares data.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You can now move data freely — pulling it in from files and other sheets, linking a whole system of files around one source of truth, and sending polished exports to anyone, Excel users included. That's the connective tissue that turns individual sheets into a real workflow.

❓ Common Questions at This Stage

🔭 Looking Ahead

Next — Lesson 6.3: A Taste of Macros & Apps Script — we close Module 6 with an honest, optional taste of automation: recording a macro, peeking at the code it generates, and understanding what Apps Script can (and can't) do for you.

✅ Before the Next Lesson

• Get one IMPORTRANGE link working between two of your files
• Export a clean PDF of a report you're proud of
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• IMPORTRANGE function (Google Support)
• Import & convert data (Google Support)