Google Sheets App Script：无需四层嵌套循环匹配二维数组生成拼接字符串

Optimize Mapping with a Dictionary #

First, convert your control_mapping array into a dictionary. This turns ID-to-name lookups from O(n) linear scans to O(1) instant access, which is way more efficient—especially with large datasets.

# Convert control_mapping to a fast-lookup dictionary
id_to_name = {item[0]: item[1] for item in control_mapping}

Generate the Target Array #

Use list comprehensions to process each sublist in ids_list: map each ID to its name via the dictionary, then join the names with newline characters.

ids_list = [[2, 3], [3, 5]]
control_mapping = [[1, "name-1"], [2, "name-2"], [3, "name-3"], [4, "name-4"], [5, "name-5"], [6, "name-6"]]

# Build the lookup dict
id_to_name = {item[0]: item[1] for item in control_mapping}

# Generate the result array
result = ["\n ".join([id_to_name[id] for id in sublist]) for sublist in ids_list]

print(result)  # Output: ["name-2\n name-3", "name-3\n name-5"]

Note: Your stated expected result has a typo (first entry shows "name-2\n name-2" instead of "name-2\n name-3")—the code above correctly maps each ID in the input sublists.

Integrate with Excel Worksheets #

Here’s how to adapt this logic for your Excel workflow (using openpyxl as an example):

from openpyxl import load_workbook

# Load your workbook
wb = load_workbook("your_spreadsheet.xlsx")

# Access relevant sheets
threats_sheet = wb['Threats']
controls_sheet = wb['Controls']

# Build ID-to-name mapping from Controls sheet (A=ID, B=Name)
id_to_name = {}
# Skip header row if your sheet has one (start at row 2)
for row in controls_sheet.iter_rows(min_row=2, values_only=True):
    control_id, control_name = row[0], row[1]
    id_to_name[control_id] = control_name

# Extract ID lists from Threats sheet (K column, which is index 10 in 0-based)
ids_list = []
for row in threats_sheet.iter_rows(min_row=2, max_col=11, values_only=True):
    # Adjust this line if your K column stores IDs in a different format (e.g., comma-separated)
    row_ids = [int(id_val) for id_val in row[10]]
    ids_list.append(row_ids)

# Generate the result array
result = ["\n ".join([id_to_name[id] for id in sublist]) for sublist in ids_list]

# Write results back to Threats sheet (e.g., column L, index 11)
for idx, value in enumerate(result, start=2):
    threats_sheet.cell(row=idx, column=12, value=value)

# Save the updated workbook
wb.save("your_spreadsheet_updated.xlsx")

Why This Is Better Than Nested Loops #

• Dictionary Lookup: Reduces time complexity from O(m*n) (four nested loops) to O(m + n), where m is the number of ID sublists and n is the number of control mappings.
• List Comprehensions: Faster and more concise than explicit nested loops in Python, keeping code clean and efficient.

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