Google App Script新手求教：如何实现预算表从其他表格自动更新？

Hey there! Let's get your updateTable function up and running. I'll walk you through each part of the code, tailored to your needs—matching project names, copying the right expense columns, and playing nice with your existing custom menu.

First, let's fix the small typo in your onOpen function (you called item.addToUi() instead of adding the full menu to the UI):

// Use const for global variables to avoid unintended changes
const ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const customMenu = ui.createMenu('Custom Menu')
    .addItem('New Row', 'addRow')
    .addItem('Update Budget from Actual', 'updateTable');
  customMenu.addToUi(); // Correct way to add the menu to the sheet's UI
}

Now, let's build the core updateTable function. We'll use efficient array operations (way faster than editing cells one by one) and a lookup map to quickly match project data.

Full updateTable Function (with detailed comments) #

function updateTable() {
  // 1. Get references to your sheets (double-check names match your actual sheets!)
  const budgetSheet = ss.getSheetByName('Budget');
  const actualSheet = ss.getSheetByName('Actual');

  // Handle missing sheets with an alert
  if (!budgetSheet || !actualSheet) {
    SpreadsheetApp.getUi().alert("Error: Couldn't find Budget or Actual sheet! Verify sheet names.");
    return;
  }

  // 2. Pull all data from both sheets into arrays (skips empty rows automatically)
  const budgetData = budgetSheet.getDataRange().getValues();
  const actualData = actualSheet.getDataRange().getValues();

  // 3. Create a lookup object for Actual data: project name → expense values
  const actualLookup = {};
  // Skip header row (start at index 1, since arrays are 0-indexed)
  for (let i = 1; i < actualData.length; i++) {
    const row = actualData[i];
    const projectName = row[0]; // Adjust index if project name is in a different column
    const salary = row[1]; // Adjust index to match your Salary column in Actual
    const goods = row[2]; // Adjust index to match your Goods column in Actual
    const expenses = row[3]; // Adjust index to match your Expenses column in Actual

    // Only add to lookup if project name isn't empty
    if (projectName) {
      actualLookup[projectName] = { salary, goods, expenses };
    }
  }

  // 4. Prepare updates for the Budget sheet
  const budgetUpdates = [];
  // Skip header row in Budget sheet
  for (let i = 1; i < budgetData.length; i++) {
    const budgetRow = budgetData[i];
    const projectName = budgetRow[0]; // Adjust index if project name is in a different column in Budget

    // If project exists in Actual data, prepare update values
    if (actualLookup[projectName]) {
      const { salary, goods, expenses } = actualLookup[projectName];
      // Match columns to your Budget sheet structure (adjust numbers below!)
      budgetUpdates.push([
        i + 1, // Row number in Budget (sheets are 1-indexed, arrays are 0-indexed)
        2, // Column number for Salary in Budget (e.g., Column B = 2)
        salary
      ]);
      budgetUpdates.push([
        i + 1,
        3, // Column number for Goods in Budget
        goods
      ]);
      budgetUpdates.push([
        i + 1,
        4, // Column number for Expenses in Budget
        expenses
      ]);
    }
    // No match? Do nothing, as per your requirement
  }

  // 5. Write all updates back to Budget sheet in bulk (most efficient method)
  budgetUpdates.forEach(update => {
    const [row, col, value] = update;
    budgetSheet.getRange(row, col).setValue(value);
  });

  // Optional: Show success feedback
  SpreadsheetApp.getUi().alert(`Updated ${budgetUpdates.length / 3} projects successfully!`);
}

Key Adjustments for Your Sheet: #

• Column Indices: Tweak the numbers (like row[0], column 2) to match your actual sheet structure. For example:
  • If Project Name is in Column C of the Budget sheet, use budgetRow[2]
  • If Salary is in Column D of the Actual sheet, use row[3]
• Sheet Names: Ensure getSheetByName uses the exact, case-sensitive names of your sheets
• Efficiency: Using arrays and a lookup object avoids repeating searches through the Actual sheet, making the script fast even with large datasets

Fixed addRow Function (Optional) #

Replace the placeholder InsertFormulaRangeHere with the range of your formula-containing row (e.g., row 1 for headers):

function addRow() {
  const sh = ss.getActiveSheet();
  const lRow = sh.getLastRow();
  const lCol = sh.getLastColumn();
  const formulaRange = sh.getRange("1:1"); // Replace with your formula row range

  // Insert row after the last row (fixes off-by-one errors)
  sh.insertRowsAfter(lRow, 1);
  // Copy formulas to the new row (use {contentsOnly: true} to copy values instead)
  formulaRange.copyTo(sh.getRange(lRow + 1, 1, 1, lCol), { contentsOnly: false });
}

How to Test: #

1. Save your script, then refresh your Google Sheet—you'll see the "Custom Menu" appear
2. Click "Update Budget from Actual"—authorize the script the first time (follow the prompts, it's safe!)
3. Verify your Budget sheet's expense columns are updated with matching Actual data

Let me know if you need help adjusting column indices or troubleshooting any issues!

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