Advanced Google Sheets: IMPORTRANGE Formulas and Automations with AppScript for SMEs

What makes advanced Google Sheets different from a classic Excel spreadsheet?

If you've ever copied and pasted data between dozens of Excel files, you know how much time is wasted. And when that data changes daily, copy-paste becomes a full-time job. We at Meteora Web see it every time we walk into a company that manages price lists, inventory or reports manually. Advanced Google Sheets is not just a cloud spreadsheet: it's an integrated development environment that talks to the rest of the world.

The real difference? You can automate things that in Excel would require VBA, ODBC connections and a dedicated server. With Google Sheets you have formulas like IMPORTRANGE to link sheets in real time, and Google Apps Script to create custom functions, time-driven triggers and integrations with Gmail, Calendar and Drive. All without paying server licenses. For an SME, that means saving hours every week and reducing human errors.

We've been using these techniques for years. When we managed the ERP system of Hibrido Abbigliamento (a clothing store), we used IMPORTRANGE to consolidate warehouse data from multiple locations into a single control sheet. And with AppScript we automated daily report delivery to managers. You don't need to be a developer: you need to understand the logic.

How do you use the IMPORTRANGE formula to connect spreadsheets in advanced Google Sheets?

IMPORTRANGE is a formula that pulls data from another Google Sheets file. It seems like magic, but it has precise rules. The basic syntax is:

=IMPORTRANGE("URL_or_ID_of_destination_sheet", "SheetName!Range")

Concrete example: You have a sheet "Sales 2025" and want to bring columns A:C into a "Consolidated Report" sheet. Write:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123/edit", "Sales!A:C")

Watch out: the first time you use IMPORTRANGE on a new source sheet, Google Sheets asks for access permission. Click "Allow access" — a step many skip and then think the formula doesn't work.

Common IMPORTRANGE errors

• #REF! — usually means you haven't authorised access or the range doesn't exist.
• #N/A — the source sheet was deleted or the URL is wrong.
• Slowness: IMPORTRANGE isn't designed for millions of rows. For large volumes, use AppScript or BigQuery.

Operational tip: Use IMPORTRANGE for not-too-large data (a few thousand rows) and for links between sheets in the same Google Workspace domain — permissions are automatic. We used it to synchronise e-commerce price lists between the production sheet and the client-facing sheet: zero copy-paste, always up to date.

What automations can you achieve with Google Apps Script in advanced Google Sheets?

Google Apps Script is a JavaScript-based language that lets you add custom functions, create triggers (on cell change, every hour, on sheet open) and interact with other Google services. With a few lines of code you can turn a passive sheet into an active system.

Time-driven triggers: send automatic reports via email

A classic: every Monday morning you want a weekly sales summary. With AppScript you write a function and attach it to a time trigger. Here's a working example:

function sendWeeklyReport() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Summary");
  var data = sheet.getDataRange().getValues();
  var body = "Weekly report:\n\n";
  for (var i = 0; i < data.length; i++) {
    body += data[i].join(" - ") + "\n";
  }
  MailApp.sendEmail({
    to: "manager@company.com",
    subject: "Automatic Report - Week",
    body: body
  });
}

After writing the script, go to Triggers in the Apps Script editor, set a time-based trigger (every Monday 8-9 AM) and assign the sendWeeklyReport function. Done.

Custom functions: create your own formulas

Need a formula to convert currencies with live rates? Or to extract data from an API? With AppScript you can create custom functions that you can then use like native formulas:

function CONVERT_EUR_USD(amount) {
  // Call to an exchange rate API (simplified example)
  var rate = 1.12; // should be fetched from an external service
  return amount * rate;
}

Then in the sheet write =CONVERT_EUR_USD(A2) and it works. We built a custom formula to calculate contribution margin from fixed and variable costs, reading data from another sheet via IMPORTRANGE.

OnEdit trigger: log change history

If you want to track every change in a shared sheet, use an onEdit trigger:

function onEdit(e) {
  var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Log");
  logSheet.appendRow([new Date(), e.user.getEmail(), e.range.getA1Notation(), e.value]);
}

Note: e.user.getEmail() works only if access permissions allow it. In Workspace environments it's generally available.

How to build a practical application using IMPORTRANGE and AppScript in one flow?

A real case: monthly consolidation of sales data from 5 different stores. Each store has its own Google Sheet. You want a "Consolidated" sheet that updates data every night and sends an alert if a store hasn't uploaded its data. Here's the blueprint:

1. In the "Consolidated" sheet, use IMPORTRANGE to import from each store sheet the required columns (e.g., date, product, quantity, amount).
2. Create an AppScript with a daily trigger that:
   • Reads all imports (checking for empty cells or errors).
   • If OK, sends a summary email to the director.
   • If data is missing, sends an alert to the responsible store manager.

Example check script:

function checkConsolidation() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Consolidated");
  var data = sheet.getDataRange().getValues();
  var today = new Date();
  var lastRow = data.length;
  // If last row is empty or has error, report
  if (data[lastRow-1][0] === "") {
    MailApp.sendEmail("director@company.com", "Alert: consolidation incomplete", "The consolidated sheet is not up to date. Check the stores.");
  } else {
    MailApp.sendEmail("director@company.com", "Consolidation OK", "Data updated as of " + today.toDateString());
  }
}

We implemented a similar system for a Sicilian clothing company: they saved 3 hours a week and eliminated typing errors in reports.

What mistakes to avoid in Google Sheets automations to keep things running smoothly?

1. Ignoring Google Apps Script quota limits. Google imposes daily limits for calls to external services (e.g., 20,000 emails per day for Consumer accounts, much more for Workspace). If you send 20,000 emails randomly, your script stops. Solution: use batch processing (e.g., MailApp.sendEmail() in a loop with breaks) or upgrade to Google Workspace Business.

2. Triggers that fire on every edit and slow down the sheet. If you set an onEdit trigger on a shared sheet with 10 people writing simultaneously, the script runs every time. Solution: use a time-based trigger (every 5 minutes) to check for changes, or filter relevant edits (e.g., only on certain columns).

3. Forgetting to authorise the script. When you share a sheet with a trigger, everyone who opens it must authorise the script to run actions. If the sheet is shared with people who don't have proper permissions, the script won't execute. Solution: publish the script as an add-on or use a service account for corporate environments.

4. Not handling errors. If IMPORTRANGE returns an error because the source sheet was renamed, your script may crash. Solution: use try...catch blocks to handle exceptions and log errors.

5. Forgetting that IMPORTRANGE does not update in real time. Imported data refreshes at varying intervals (from minutes to hours). For immediate updates, use AppScript to read the source sheet directly with SpreadsheetApp.openById().

What to do next

1. Identify a repetitive process in your sheets: copy-paste between sheets? Manual report delivery? Change tracking?
2. Test IMPORTRANGE on a simple case: link two test sheets and verify that data synchronises.
3. Create your first AppScript: go to Extensions > Apps Script, paste the email example code, and enable a time trigger.
4. Document the flow: so next time someone asks "why didn't the report arrive?", you'll know exactly where to look.

This guide is part of our pillar on Google Workspace for Businesses. If you want to dive deeper into other aspects (Gmail, Drive, Calendar) or need help implementing custom automations, get in touch: since 2017 we've been helping Italian SMEs turn technology into revenue.