如何用Google Script实现Google表格多标签页指定列自动转百分比格式？

Absolutely feasible! You can use Google Apps Script to lock in the percentage formatting for your target columns across multiple sheets, even after Teradata refreshes overwrite the auto-formatting. Here's a practical, step-by-step solution tailored to your needs:

First, open your Google Sheet and navigate to Extensions > Apps Script to launch the script editor. Replace the default code with this custom function:

function formatColumnsToPercentage() {
  // Configure your target settings here
  const targetColumn = 3; // Column index (1 = A, 2 = B, 3 = C, etc. Adjust to your column)
  const targetSheetNames = []; // Optional: List specific sheet names, e.g., ["Sales", "Inventory"]
                               // Leave empty to apply formatting to ALL sheets in the spreadsheet

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheets = [];

  // Get target sheets (either specified ones or all sheets)
  if (targetSheetNames.length > 0) {
    sheets = targetSheetNames.map(name => spreadsheet.getSheetByName(name)).filter(sheet => sheet !== null);
  } else {
    sheets = spreadsheet.getSheets();
  }

  // Apply percentage formatting to the target column in each sheet
  sheets.forEach(sheet => {
    // Get the full range of the target column (from row 1 to the last row with data)
    const columnRange = sheet.getRange(1, targetColumn, sheet.getLastRow(), 1);
    // Set format to 2 decimal places percentage (adjust string for other formats, e.g., "0%" for whole numbers)
    columnRange.setNumberFormat("0.00%");
  });
}

Customize the values to match your spreadsheet:

• targetColumn: Set this to the index of your column (1 = A, 2 = B, etc.)
• targetSheetNames: If you only want to format specific sheets, list their names here. Leave it empty to apply formatting to every sheet.

To make this script run automatically after Teradata refreshes your data, you'll need an installable trigger (simple onEdit triggers don't detect external data imports like Teradata refreshes):

1. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar.
2. Click Add Trigger in the bottom right corner.
3. Configure the trigger settings:
   • Choose which function to run: formatColumnsToPercentage
   • Choose which deployment to run: Head
   • Select event source: From spreadsheet
   • Select event type: On change
   • Click Save (you'll need to grant permissions the first time—follow the prompts to allow the script access to your sheet).

Alternatively, if you know exactly when your Teradata refresh runs each day, you can set a time-driven trigger instead:

• Select event source: Time-driven
• Choose a schedule that runs shortly after your daily Teradata refresh (e.g., every day at 9:00 AM if refresh finishes at 8:30 AM)

• Permission Setup: The first time you run the script or set up the trigger, Google will ask you to authorize it. You'll need to click "Advanced" and then "Go to [Script Name]" to grant the necessary access (this is safe—you're giving permission to your own script).
• Format Customization: Adjust the setNumberFormat string if you need different percentage formatting:
  • "0%": Whole number percentages (e.g., 12%)
  • "0.0%": One decimal place (e.g., 12.3%)
  • "0.00%": Two decimal places (e.g., 12.34%)
• Testing: You can manually run the script at any time by clicking the play button in the Apps Script editor to verify it works.

This setup will ensure your target columns stay formatted as percentages, even after Teradata overwrites the sheet's auto-formatting during daily refreshes.

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