Google Sheets中结合IMPORTRANGE实现重复字段合并的技术求助

Hey there, let's work through this Google Sheets merging challenge you're dealing with! Your form's 3 sections are creating duplicate fields in the FormData sheet, and you need to pull that data into the Report sheet while merging those duplicates—only when all but one field in a group is empty. Let's break down how to solve this, both with formulas and scripts.

First: Pure Formulas Work Perfectly! #

Your core need is merging duplicate columns row-by-row (taking the only non-empty value in each field group for a single submission). Your initial formula was on the right track, but we can simplify it to operate per-row instead of stacking values vertically.

Step-by-Step Formula Setup #

Assume your Report sheet starts fresh at row 2 (with headers in row 1). We'll either pull data directly via IMPORTRANGE or optimize by first importing all raw data to a hidden sheet (more on that later).

1. Timestamp (Report Column A)

Directly reference the timestamp column since it’s unique:

=IMPORTRANGE("Your FormData Sheet URL","FormData!A2:A")

2. Name + Account Name (Report Column B)

Prioritize the Name field; use Account Name only if Name is empty:

=ARRAYFORMULA(IF(IMPORTRANGE("Your FormData Sheet URL","FormData!B2:B")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!B2:B"), IMPORTRANGE("Your FormData Sheet URL","FormData!C2:C")))

3. Contact, Notes, Task (Report Columns C-E)

These have no duplicates, so direct references work:

# Contact (C)
=IMPORTRANGE("Your FormData Sheet URL","FormData!D2:D")

# Notes (D)
=IMPORTRANGE("Your FormData Sheet URL","FormData!E2:E")

# Task (E)
=IMPORTRANGE("Your FormData Sheet URL","FormData!F2:F")

4. Location + Supplier (Report Column F)

Prioritize Location; fall back to Supplier if empty:

=ARRAYFORMULA(IF(IMPORTRANGE("Your FormData Sheet URL","FormData!G2:G")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!G2:G"), IMPORTRANGE("Your FormData Sheet URL","FormData!N2:N")))

5. Freight to Store, When Goods Arrive, Freight to Customer (Report Columns G-I)

Merge their duplicate pairs:

# Freight to Store (G)
=ARRAYFORMULA(IF(IMPORTRANGE("Your FormData Sheet URL","FormData!H2:H")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!H2:H"), IMPORTRANGE("Your FormData Sheet URL","FormData!O2:O")))

# When Goods Arrive (H)
=ARRAYFORMULA(IF(IMPORTRANGE("Your FormData Sheet URL","FormData!I2:I")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!I2:I"), IMPORTRANGE("Your FormData Sheet URL","FormData!P2:P")))

# Freight to Customer (I)
=ARRAYFORMULA(IF(IMPORTRANGE("Your FormData Sheet URL","FormData!J2:J")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!J2:J"), IMPORTRANGE("Your FormData Sheet URL","FormData!Q2:Q")))

6. Priority, Assign To, Status (Report Columns J-L)

Merge their triplicate columns, taking the first non-empty value:

# Priority (J)
=ARRAYFORMULA(IF(IMPORTRANGE("Your FormData Sheet URL","FormData!K2:K")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!K2:K"), IF(IMPORTRANGE("Your FormData Sheet URL","FormData!R2:R")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!R2:R"), IMPORTRANGE("Your FormData Sheet URL","FormData!U2:U"))))

# Assign To (K)
=ARRAYFORMULA(IF(IMPORTRANGE("Your FormData Sheet URL","FormData!L2:L")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!L2:L"), IF(IMPORTRANGE("Your FormData Sheet URL","FormData!S2:S")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!S2:S"), IMPORTRANGE("Your FormData Sheet URL","FormData!V2:V"))))

# Status (L)
=ARRAYFORMULA(IF(IMPORTRANGE("Your FormData Sheet URL","FormData!M2:M")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!M2:M"), IF(IMPORTRANGE("Your FormData Sheet URL","FormData!T2:T")<>"", IMPORTRANGE("Your FormData Sheet URL","FormData!T2:T"), IMPORTRANGE("Your FormData Sheet URL","FormData!W2:W"))))

Pro Tip: Reduce IMPORTRANGE Calls #

Multiple IMPORTRANGE requests trigger repeated permission checks and slow things down. Fix this by:

1. Creating a hidden sheet (name it RawData) in your Report workbook
2. Paste this in RawData!A1 to import all FormData at once:

   =IMPORTRANGE("Your FormData Sheet URL","FormData!A:W")
   

3. Replace all IMPORTRANGE references in your Report sheet formulas with RawData! (e.g., RawData!K2:K instead of the full IMPORTRANGE call).

Second: Script Solution (For Large Datasets or Auto-Sync) #

If you’re dealing with 100k+ rows, or want auto-sync when the form is submitted, use Google Apps Script for better performance and automation.

Example Script #

function mergeFormDataToReport() {
  const formDataUrl = "Your FormData Sheet URL";
  const formDataSheet = SpreadsheetApp.openByUrl(formDataUrl).getSheetByName("FormData");
  const reportSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report");
  
  // Pull raw data (skip header row)
  const rawRows = formDataSheet.getRange(2, 1, formDataSheet.getLastRow()-1, 23).getValues();
  
  // Merge each row's duplicate fields
  const mergedRows = rawRows.map(row => {
    const [timestamp, name, accountName, contact, notes, task, location, freightStore1, goodsArrive1, freightCust1, priority1, assignTo1, status1, supplier, freightStore2, goodsArrive2, freightCust2, priority2, assignTo2, status2, priority3, assignTo3, status3] = row;
    
    // Apply your merging rules
    return [
      timestamp,
      name || accountName,
      contact,
      notes,
      task,
      location || supplier,
      freightStore1 || freightStore2,
      goodsArrive1 || goodsArrive2,
      freightCust1 || freightCust2,
      priority1 || priority2 || priority3,
      assignTo1 || assignTo2 || assignTo3,
      status1 || status2 || status3
    ];
  });
  
  // Clear old report data (keep headers) and write new merged data
  reportSheet.getRange(2, 1, reportSheet.getLastRow()-1, reportSheet.getLastColumn()).clearContent();
  reportSheet.getRange(2, 1, mergedRows.length, mergedRows[0].length).setValues(mergedRows);
}

How to Use the Script #

1. Open your Report sheet → Go to Extensions > Apps Script
2. Paste the code, replace formDataUrl with your FormData sheet’s URL
3. Run the script once to grant necessary permissions
4. Set up a trigger (left sidebar → Triggers) to auto-run:
   • Choose time-driven (e.g., hourly) for regular syncs
   • Choose "On form submit" if FormData is a direct form response sheet

Final Notes #

• Small datasets (≤50k rows): Stick with formulas—they’re easy to edit and require no coding.
• Large datasets or auto-sync: Use the script for faster processing and hands-off updates.

内容的提问来源于stack exchange，提问作者Justin Fern