如何从Google Sheet单元格数据生成4×1数组并统计项目参与次数

Let's tackle this step by step—first we'll build the 4-element arrays that link each person to their project, then create the summary count table you need.

Step 1: Generate the 4×1 Array List #

Your current code only pulls the first set of person/week/team data. We need to iterate through each row, grab the project name, and check all three person groups (Per1/Per2/Per3) to build the full list of project-person associations.

Here's the updated code:

function generateProjectPersonArrays() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  // Get all rows with data (A2 to J, dynamically adjusts for new rows)
  const data = sh.getRange(2, 1, sh.getLastRow() - 1, 10).getValues();
  const projectPersonArrays = [];

  data.forEach(row => {
    const project = row[0]; // Column A: Project name
    // Define the three person/week/team groups (columns B-D, E-G, H-J)
    const personGroups = [
      [row[1], row[2], row[3]], // Per1, W1, Team1
      [row[4], row[5], row[6]], // Per2, W2, Team2
      [row[7], row[8], row[9]]  // Per3, W3, Team3
    ];

    // Loop through each group, only add entries with a non-empty person name
    personGroups.forEach(group => {
      const person = group[0];
      if (person) {
        projectPersonArrays.push([project, person, group[1], group[2]]);
      }
    });
  });

  // Log the result to verify
  Logger.log(projectPersonArrays);
  return projectPersonArrays;
}

What this does: #

• Pulls the full range of data (A2 to J) using getLastRow() so it works even if you add more rows later
• For each row, extracts the project name from column A
• Checks all three person/week/team groups; if a person's name isn't blank, it creates a 4-element array [Project, Person, Week, Team] and adds it to the list
• Skips empty person entries (like the blank Per3 in p1, p3, p5)

The output will match your desired format:

[[p1,Bill,.5,Tech], [p1,Alice,1,Other],[p2,Larry,1,Tech],[p2,Bill,1,Other], [p2,Tina,1,Other], [p3,Joe,2,Tech], [p3,Beth,1,Tech], [p4,Kathy,.5,Tech], [p5,Bill,1,Tech], [p5,Larry,1,Other]]

Step 2: Generate the Project Count Summary Table #

Now that we have the full list of project-person associations, we can count how many unique projects each person is part of. We'll use an object to track counts, then convert it into a table-ready format.

Add this function (or integrate it into the previous one):

function generateProjectCountTable() {
  const projectPersonArrays = generateProjectPersonArrays(); // Reuse the array list
  const projectCounts = {};

  // Count unique projects per person (avoids duplicate project counts for the same person)
  projectPersonArrays.forEach(entry => {
    const person = entry[1];
    const project = entry[0];
    // Use a Set to track unique projects for each person
    if (!projectCounts[person]) {
      projectCounts[person] = new Set();
    }
    projectCounts[person].add(project);
  });

  // Convert the counts to a table format
  const summaryTable = [['Name', 'Number Of Projects']]; // Header row
  Object.entries(projectCounts).forEach(([name, projects]) => {
    summaryTable.push([name, projects.size]);
  });

  // Log the summary table
  Logger.log(summaryTable);
  
  // Optional: Write the table to a new sheet for easy viewing
  const summarySheet = ss.getSheetByName('Summary') || ss.insertSheet('Summary');
  summarySheet.clear();
  summarySheet.getRange(1, 1, summaryTable.length, 2).setValues(summaryTable);
}

Key details: #

• Uses a Set to track unique projects per person (so if someone is listed multiple times for the same project, it only counts once)
• Converts the count object into a 2D array that matches your desired table structure
• Optional: Writes the summary directly to a new sheet named "Summary" so you can see it in your spreadsheet

The final summary table will look exactly like what you requested:

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