Advanced Google Sheets for Data Analysts

ARRAYFORMULA, QUERY with SQL-like syntax, IMPORTRANGE pipelines, Apps Script automation, array constants, dynamic named ranges, and dashboard patterns.

-- Without ARRAYFORMULA: enter in C2, drag to C1001
=IF(B2="active", A2*1.18, A2)

-- With ARRAYFORMULA: enter in C2 only — populates C2:C1001 automatically
=ARRAYFORMULA(IF(B2:B1001="active", A2:A1001*1.18, A2:A1001))

-- Practical: add GST suffix to all product names in column A
=ARRAYFORMULA(IF(A2:A<>"", A2:A & " (incl. GST)", ""))

-- Cumulative sum (running total) across rows
=ARRAYFORMULA(MMULT(
  (ROW(B2:B1001) >= TRANSPOSE(ROW(B2:B1001))),
  N(B2:B1001)
))

-- Dynamic row numbering — renumbers automatically when rows added/deleted
=ARRAYFORMULA(IF(A2:A<>"", ROW(A2:A)-ROW(A2)+1, ""))

-- Trim + proper case all names in one formula
=ARRAYFORMULA(IF(A2:A<>"", PROPER(TRIM(A2:A)), ""))

-- Regular VLOOKUP doesn't work in ARRAYFORMULA — use XLOOKUP or INDEX/MATCH
-- Option 1: XLOOKUP (Sheets supports it — also works with arrays)
=XLOOKUP(A2:A100, Products!A:A, Products!B:B, "Not found")

-- Option 2: INDEX/MATCH wrapped in ARRAYFORMULA
=ARRAYFORMULA(
  IFERROR(
    INDEX(Products!B:B, MATCH(A2:A100, Products!A:A, 0)),
    "Not found"
  )
)

-- Faster for large datasets: VLOOKUP still works if you accept one formula per row
-- Use ARRAYFORMULA only when the lookup range is small or for calculated columns

-- Syntax: =QUERY(data, query_string, [headers])

-- Basic SELECT with WHERE filter
=QUERY(Orders!A:F,
  "SELECT A, B, D, E
   WHERE C = 'North'
   ORDER BY E DESC",
1)

-- Aggregate: total revenue by region
=QUERY(Orders!A:F,
  "SELECT C, SUM(E)
   GROUP BY C
   ORDER BY SUM(E) DESC
   LABEL SUM(E) 'Total Revenue'",
1)

-- Multiple conditions with AND / OR
=QUERY(Orders!A:F,
  "SELECT A, B, C, E
   WHERE C = 'North' AND E > 10000
   ORDER BY E DESC
   LIMIT 20",
1)

-- Date filter — use date literal format
=QUERY(Orders!A:F,
  "SELECT A, B, E
   WHERE A >= date '2026-01-01'
   AND A <= date '2026-06-30'",
1)

-- PIVOT — cross-tab by region × product category
=QUERY(Orders!A:G,
  "SELECT C, SUM(E)
   GROUP BY C
   PIVOT D",
1)

-- Dynamic filter using cell reference — wrap in concatenation
=QUERY(Orders!A:F,
  "SELECT A, B, C, E
   WHERE C = '"&H1&"'
   ORDER BY E DESC",
1)
-- H1 contains the region name — change H1 to refilter the output

-- Pull data from another sheet in the same workbook
=QUERY({Sheet1!A:F; Sheet2!A:F},
  "SELECT Col1, Col2, SUM(Col5)
   GROUP BY Col1, Col2",
1)

-- {} stacks ranges vertically (union-style)
-- Note: col references change to Col1, Col2... when using {}

-- With IMPORTRANGE (from another Sheets file entirely):
=QUERY(
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/SPREADSHEET_ID", "Sheet1!A:F"),
  "SELECT Col1, Col3, SUM(Col5)
   WHERE Col2 = 'Active'
   GROUP BY Col1, Col3",
1
)

-- Basic IMPORTRANGE
=IMPORTRANGE("SPREADSHEET_URL_OR_ID", "SheetName!A1:F1000")

-- First use: Sheets will ask you to "Allow access" — grant it once per workbook pair

-- Stack multiple workbooks into one master sheet
={
  IMPORTRANGE("URL_NORTH",  "Data!A2:F"),
  IMPORTRANGE("URL_SOUTH",  "Data!A2:F"),
  IMPORTRANGE("URL_WEST",   "Data!A2:F")
}
-- Vertical stack with {range1; range2; range3}
-- Horizontal join with {range1, range2, range3}

-- Combine IMPORTRANGE + QUERY for a clean master dashboard
=QUERY(
  {
    IMPORTRANGE("URL_Q1", "Sales!A2:E");
    IMPORTRANGE("URL_Q2", "Sales!A2:E")
  },
  "SELECT Col1, SUM(Col5)
   WHERE Col3 = 'Online'
   GROUP BY Col1
   ORDER BY SUM(Col5) DESC",
0
)

IMPORTRANGE recalculates on every sheet open and on a periodic schedule. For very large ranges (10,000+ rows), use it to pull into a staging sheet, then run QUERY on that sheet — never chain IMPORTRANGE directly inside a QUERY on a large range without intermediate caching.

-- Named range: Data > Named ranges > define "SalesData" as Sales!A1:F1000
-- Then use it anywhere:
=QUERY(SalesData, "SELECT A, SUM(E) GROUP BY A", 1)

-- INDIRECT: build a range reference dynamically from text
=INDIRECT("Sales!A1:A"&COUNTA(Sales!A:A))
-- Always points to the last row, even as new data is added

-- OFFSET: expand a range dynamically
=SUM(OFFSET(B2, 0, 0, COUNTA(B:B)-1, 1))
-- Sums B2 down to the last non-empty row

-- Dynamic QUERY using INDIRECT
=QUERY(
  INDIRECT("'"&A1&"'!A:F"),  -- A1 contains the sheet name
  "SELECT A, SUM(E) GROUP BY A",
  1
)
-- Change A1 to point QUERY at a different sheet

-- Array constants: hardcoded arrays in formulas
={1, 2, 3, 4, 5}          -- horizontal array (1 row, 5 cols)
={1; 2; 3; 4; 5}          -- vertical array (5 rows, 1 col)
={"Mon","Tue","Wed","Thu","Fri"}  -- text array

-- TRANSPOSE: flip rows and columns
=TRANSPOSE(A1:A10)         -- vertical range → horizontal

-- MMULT: matrix multiplication (dot product)
-- Useful for weighted scores across multiple criteria:
=MMULT(B2:D10, {0.4; 0.35; 0.25})
-- B:D are scores; weights must sum to 1; returns weighted total per row

-- FREQUENCY: count values in bins
=ARRAYFORMULA(FREQUENCY(Scores!A2:A100, {50, 60, 70, 80, 90, 100}))

-- UNIQUE + SORT: dynamic distinct list, always updated
=SORT(UNIQUE(A2:A1000))

-- FILTER: extract rows meeting a condition — like SQL WHERE
=FILTER(A2:F1000, C2:C1000="North", E2:E1000>5000)
-- Returns all rows where region=North AND revenue>5000

// Access: Extensions > Apps Script

// 1. Custom function — use in any cell like a built-in
function MARGIN_LABEL(revenue, cost) {
  const margin = (revenue - cost) / revenue * 100;
  if (margin >= 40) return 'High';
  if (margin >= 20) return 'Medium';
  return 'Low';
}
// In a cell: =MARGIN_LABEL(D2, E2)


// 2. Auto-timestamp when a row is edited
function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() !== 'Orders') return;
  const row = e.range.getRow();
  if (row < 2) return;  // skip header
  sheet.getRange(row, 8).setValue(new Date());  // col H = timestamp
}


// 3. Send a weekly email summary (triggered by time-based trigger)
function sendWeeklySummary() {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Dashboard');
  const wal   = sheet.getRange('B2').getValue();
  const rev   = sheet.getRange('B3').getValue();

  const body = `Weekly Analytics Summary

WAL: ${wal}
Revenue: ₹${rev.toLocaleString('en-IN')}

View dashboard: ${ss.getUrl()}`;

  GmailApp.sendEmail(
    'manager@example.com',
    'Weekly Analytics — ' + new Date().toDateString(),
    body
  );
}
// Set trigger: Apps Script > Triggers > Add trigger > time-driven > weekly


// 4. Import JSON from an external API into a sheet
function importAPIData() {
  const url      = 'https://api.example.com/sales?key=YOUR_KEY';
  const response = UrlFetchApp.fetch(url);
  const data     = JSON.parse(response.getContentText());
  const sheet    = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('APIData');

  sheet.clearContents();
  sheet.appendRow(['Date', 'Region', 'Revenue']);
  data.forEach(row => sheet.appendRow([row.date, row.region, row.revenue]));
}

-- SPARKLINE: tiny chart inside a single cell
=SPARKLINE(B2:B13)                          -- line chart
=SPARKLINE(B2:B13, {"charttype","bar"})     -- bar chart
=SPARKLINE(B2:B13, {"charttype","column"; "color","#047857"})  -- green columns
=SPARKLINE(B2:B13, {
  "charttype","line";
  "color","#047857";
  "linewidth",2;
  "min",0
})