求助：在Google Sheets中用Google Apps Script实现宽表转长表及多阶段处理

Alright, let's tackle this Google Sheets transformation step by step. I'll cover both formula-based and Google Apps Script solutions so you can pick what works best for your workflow:

First, we need to unpivot your wide data into a long format—preserving all your Data Type attributes while merging T1/T2 into a single Time column.

Formula Solution #

Assume your raw data starts at cell A1, with headers like ID, Type, Category, T1_Sales, T2_Sales, T1_Impressions, T2_Impressions. Paste this formula into a new sheet's cell A1:

=ARRAYFORMULA(
  QUERY(
    {
      // T1 rows: combine attributes + Time, then pull T1 metrics
      FLATTEN(A2:A&"|"&B2:B&"|"&C2:C&"|T1"),
      FLATTEN(D2:D),
      FLATTEN(F2:F);
      // T2 rows: repeat the same structure for T2 metrics
      FLATTEN(A2:A&"|"&B2:B&"|"&C2:C&"|T2"),
      FLATTEN(E2:E),
      FLATTEN(G2:G)
    },
    "SELECT SPLIT(Col1, '|'), Col2, Col3 
     WHERE Col1 IS NOT NULL 
     LABEL SPLIT(Col1, '|')[0] 'ID', 
           SPLIT(Col1, '|')[1] 'Type', 
           SPLIT(Col1, '|')[2] 'Category', 
           SPLIT(Col1, '|')[3] 'Time', 
           Col2 'Sales', 
           Col3 'Impressions'",
    0
  )
)

This works by:

• FLATTEN duplicating your attribute rows (once for T1, once for T2)
• Merging attributes into a single string to keep them grouped
• SPLIT splitting the string back into individual columns
• QUERY cleaning up the output and setting proper headers

Google Apps Script Solution #

If formulas feel too clunky, use this script to automate the unpivot:

function convertToLongFormat() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const rawSheet = ss.getSheetByName("RawData"); // Replace with your raw data sheet name
  const outputSheet = ss.getSheetByName("LongFormat") || ss.insertSheet("LongFormat");
  
  const rawData = rawSheet.getDataRange().getValues();
  const headers = rawData[0];
  const attributeCols = headers.slice(0, headers.indexOf("T1_Sales")); // Adjust based on your header positions
  const timeCols = headers.filter(h => h.startsWith("T1") || h.startsWith("T2"));
  
  const outputData = [attributeCols.concat(["Time", "Sales", "Impressions"])]; // Adjust metrics to match your data
  
  for (let i = 1; i < rawData.length; i++) {
    const row = rawData[i];
    const attributes = row.slice(0, attributeCols.length);
    
    // Process T1 row
    outputData.push([...attributes, "T1", row[headers.indexOf("T1_Sales")], row[headers.indexOf("T1_Impressions")]]);
    // Process T2 row
    outputData.push([...attributes, "T2", row[headers.indexOf("T2_Sales")], row[headers.indexOf("T2_Impressions")]]);
  }
  
  outputSheet.clear();
  outputSheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
}

Just update the sheet names and metric headers to match your dataset, then run the script from the Apps Script editor.

Next, we'll group and merge data by the Time column (T1/T2). This example aggregates sales/impressions and merges IDs, but adjust based on what you need to merge.

Formula Solution #

Using the long format data from Phase 1 (let's say it's in Sheet2), paste this into a new sheet:

=ARRAYFORMULA(
  QUERY(Sheet2!A:F, 
        "SELECT Time, GROUP_CONCAT(ID, ', '), SUM(Sales), SUM(Impressions) 
         GROUP BY Time 
         LABEL GROUP_CONCAT(ID, ', ') 'Combined IDs', SUM(Sales) 'Total Sales', SUM(Impressions) 'Total Impressions'",
        1
  )
)

This groups all rows by Time, concatenates related IDs, and sums up the metrics.

Google Apps Script Solution #

For more control over merging logic, use this script:

function splitMergeByTime() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const longSheet = ss.getSheetByName("LongFormat");
  const outputSheet = ss.getSheetByName("TimeMerged") || ss.insertSheet("TimeMerged");
  
  const data = longSheet.getDataRange().getValues();
  const headers = data[0];
  const timeIndex = headers.indexOf("Time");
  const idIndex = headers.indexOf("ID");
  const salesIndex = headers.indexOf("Sales");
  const impressionsIndex = headers.indexOf("Impressions");
  
  const merged = {};
  
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const time = row[timeIndex];
    
    if (!merged[time]) {
      merged[time] = { ids: [], totalSales: 0, totalImpressions: 0 };
    }
    
    merged[time].ids.push(row[idIndex]);
    merged[time].totalSales += row[salesIndex];
    merged[time].totalImpressions += row[impressionsIndex];
  }
  
  const outputData = [["Time", "Combined IDs", "Total Sales", "Total Impressions"]];
  Object.keys(merged).forEach(time => {
    outputData.push([
      time,
      merged[time].ids.join(", "),
      merged[time].totalSales,
      merged[time].totalImpressions
    ]);
  });
  
  outputSheet.clear();
  outputSheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
}

Finally, we'll create dynamic ad copy based on the processed data.

Formula Solution #

Add a new column to your merged data sheet (e.g., column E) and use this formula to generate ads:

=ARRAYFORMULA(
  IF(A2:A="", "", 
     "Don't miss our "&Sheet2!B2:B&" collection in the "&Sheet2!C2:C&" category! In "&A2:A&", we drove "&C2:C&" sales with "&D2:D&" impressions—shop now!"
  )
)

Adjust the text to match your brand voice and metrics.

Google Apps Script Solution #

Automate ad generation and save to a dedicated sheet:

function generateAds() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mergedSheet = ss.getSheetByName("TimeMerged");
  const adSheet = ss.getSheetByName("Ads") || ss.insertSheet("Ads");
  
  const data = mergedSheet.getDataRange().getValues();
  const outputData = [["Ad Copy"]];
  
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const time = row[0];
    const combinedIds = row[1];
    const totalSales = row[2];
    const totalImpressions = row[3];
    
    // Customize this ad copy template to fit your needs
    const adCopy = `Hot update for ${time}! Our products with IDs ${combinedIds} hit ${totalSales} total sales from ${totalImpressions} impressions—grab yours before they're gone!`;
    outputData.push([adCopy]);
  }
  
  adSheet.clear();
  adSheet.getRange(1, 1, outputData.length, 1).setValues(outputData);
}

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