请求编写Google Apps Script：实现Plan表与Database表的数据同步更新

Got it, here's a tailored solution using Google Apps Script that handles your exact requirements—updating the Budget column in the Database sheet when you enter a value in Plan's New Amount column, and appending new entries if the Item doesn't exist in Database.

How to Set Up the Script #

1. Open your Google Spreadsheet
2. Go to Extensions > Apps Script to launch the script editor
3. Delete any default code in the editor
4. Paste the script below and save the project (name it something like "Plan-Database Sync")
5. Run the script once manually (select syncAllPlanRows and click the run button) to authorize permissions—you'll need to allow access via the "Advanced" option since it's a custom script

The Script #

function onEdit(e) {
  // Trigger when a cell in "Plan" sheet's "New Amount" column is edited
  const sheet = e.source.getActiveSheet();
  const editedCol = e.range.getColumn();
  const editedRow = e.range.getRow();

  // Check if edit is in "Plan" sheet and "New Amount" column (adjust column index if needed)
  if (sheet.getName() === "Plan" && editedCol === 3 && editedRow > 1) {
    syncSingleRow(sheet, editedRow);
  }
}

function syncAllPlanRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const planSheet = ss.getSheetByName("Plan");
  const databaseSheet = ss.getSheetByName("Database");

  // Get all data from Plan sheet (skip header row)
  const planData = planSheet.getRange(2, 1, planSheet.getLastRow() - 1, 3).getValues();
  
  // Get all data from Database sheet (including header)
  const databaseData = databaseSheet.getDataRange().getValues();
  const databaseRows = databaseData.slice(1);

  // Loop through each row in Plan
  planData.forEach((row, index) => {
    const [item, department, newAmount] = row;
    // Skip rows where Item or New Amount is empty
    if (!item || !newAmount) return;

    // Find matching Item in Database
    const matchIndex = databaseRows.findIndex(dbRow => dbRow[0] === item);

    if (matchIndex !== -1) {
      // Update Budget column (index 2 in Database: Item=0, Department=1, Budget=2)
      databaseSheet.getRange(matchIndex + 2, 3).setValue(newAmount);
    } else {
      // Append new row to Database (fill Prev.Year and Forecast with empty values)
      databaseSheet.appendRow([item, department, newAmount, "", ""]);
    }
  });

  SpreadsheetApp.getUi().alert("Sync completed successfully!");
}

function syncSingleRow(sheet, rowNum) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const databaseSheet = ss.getSheetByName("Database");
  
  // Get data from the edited row in Plan
  const rowData = sheet.getRange(rowNum, 1, 1, 3).getValues()[0];
  const [item, department, newAmount] = rowData;

  if (!item || !newAmount) return;

  // Get Database data
  const databaseData = databaseSheet.getDataRange().getValues().slice(1);
  const matchIndex = databaseData.findIndex(dbRow => dbRow[0] === item);

  if (matchIndex !== -1) {
    databaseSheet.getRange(matchIndex + 2, 3).setValue(newAmount);
  } else {
    databaseSheet.appendRow([item, department, newAmount, "", ""]);
  }
}

Key Details & Customization #

• Automatic Trigger: The onEdit function runs instantly when you enter a value in the "New Amount" column (column C) of the Plan sheet. If your columns are in different positions, adjust the editedCol === 3 value (3 = column C).
• Manual Sync: Use syncAllPlanRows to process all existing rows in Plan at once—great for initial setup or if you bulk-edit data.
• Empty Value Handling: The script skips rows where Item or New Amount is empty to avoid syncing incomplete entries.
• Column Indices: The script assumes:
  • Plan sheet: Item (A), Department (B), New Amount (C)
  • Database sheet: Item (A), Department (B), Budget (C), Prev.Year (D), Forecast (E)
    Adjust the indices in the code if your columns are arranged differently.

Testing the Script #

• Try entering a value in Plan's New Amount column for an existing Item in Database—you'll see the Budget update right away.
• For a new Item, the script will add a new row to the bottom of Database with the Item, Department, New Amount, and blank values for Prev.Year/Forecast.

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