技术求助：如何通过宏将Google Cloud Translation API集成至Google Sheets实现跨工作表批量翻译

Hey Benjamin, I’ve got you covered on integrating the Google Cloud Translation API into your Google Sheets to handle that 60k-row translation task. Let’s break this down step by step—large datasets need a little extra care to avoid hitting API limits or timeouts, so we’ll do this right.

First, you need to enable the API and grab a secure key:

• Head to the Google Cloud Console, create or select your existing project.
• Search for "Cloud Translation API" and enable it (it’s free for small volumes, but check the quota for your 60k rows!).
• Go to Credentials > Create Credentials > API key, then restrict this key to only work with the Translation API and your Google Sheet (this prevents unauthorized misuse).

Open your Google Sheet, go to Extensions > Apps Script to open the script editor. Replace the default code with this (swap in your API key and sheet details):

// Replace these values with your own!
const API_KEY = "YOUR_GOOGLE_CLOUD_API_KEY";
const SOURCE_SHEET = "Sheet1"; // Name of your first sheet with raw data
const TARGET_SHEET = "Sheet2"; // Name of your second sheet for translations
const SOURCE_LANG = "auto"; // Auto-detect source language, or set to code like "en"
const TARGET_LANG = "zh"; // Target language code (e.g., "es" for Spanish, "fr" for French)

function translateLargeDataset() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName(SOURCE_SHEET);
  const targetSheet = ss.getSheetByName(TARGET_SHEET);
  
  // Pull all data from the source sheet (adjust range if you don't need all columns)
  const sourceData = sourceSheet.getDataRange().getValues();
  const translatedData = [];
  
  // Process in batches to avoid API rate limits (100 rows per batch is safe for most quotas)
  const BATCH_SIZE = 100;
  const totalRows = sourceData.length;

  for (let i = 0; i < totalRows; i += BATCH_SIZE) {
    const batch = sourceData.slice(i, i + BATCH_SIZE);
    const translatedBatch = translateBatch(batch);
    
    translatedBatch.forEach(row => translatedData.push(row));
    
    // Log progress so you can track how far along you are
    console.log(`Processed ${Math.min(i + BATCH_SIZE, totalRows)} of ${totalRows} rows`);
    
    // Small delay to avoid overwhelming the API (adjust if needed)
    Utilities.sleep(1000);
  }
  
  // Clear the target sheet and write all translated data
  targetSheet.clearContents();
  targetSheet.getRange(1, 1, translatedData.length, translatedData[0].length).setValues(translatedData);
  SpreadsheetApp.getUi().alert("Translation finished! Check your target sheet.");
}

function translateBatch(batch) {
  // Extract non-empty text from the batch to send to the API
  const texts = batch.flat().filter(cell => cell !== "");
  
  if (texts.length === 0) return batch.map(row => row); // Skip empty batches
  
  const apiUrl = `https://translation.googleapis.com/language/translate/v2?key=${API_KEY}`;
  const payload = {
    q: texts,
    source: SOURCE_LANG,
    target: TARGET_LANG,
    format: "text"
  };
  
  const requestOptions = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload)
  };

  try {
    const response = UrlFetchApp.fetch(apiUrl, requestOptions);
    const responseData = JSON.parse(response.getContentText());
    const translations = responseData.data.translations.map(t => t.translatedText);
    
    // Map translations back to the original row structure
    let translationIndex = 0;
    return batch.map(row => {
      return row.map(cell => {
        if (cell === "") return "";
        return translations[translationIndex++];
      });
    });
  } catch (error) {
    console.error(`Failed to translate batch starting at row ${i+1}:`, error);
    // Return original row if translation fails (you can expand this to log failures separately)
    return batch.map(row => row);
  }
}

• Swap YOUR_GOOGLE_CLOUD_API_KEY with the key you created earlier.
• Update SOURCE_SHEET and TARGET_SHEET to match your actual sheet names.
• Set SOURCE_LANG and TARGET_LANG using valid language codes (e.g., "de" for German, "ja" for Japanese).
• Adjust BATCH_SIZE if needed—check your Cloud Console quota to make sure you don’t exceed request limits.

• Save the script, then go back to your sheet.
• Head to Extensions > Macros > Import macro, select translateLargeDataset, and add it to your macro list.
• Run the macro—keep the sheet open while it runs (it’ll take a bit for 60k rows!) and you’ll get an alert when it’s done.

• Quota Checks: The free tier gives you 500,000 characters per month. If your 60k rows have more than that, you’ll need to upgrade to a paid tier in Google Cloud.
• Error Handling: The script returns original text if a batch fails, but you can add a separate sheet to log failed rows if you want to reprocess them.
• Performance: The 1-second delay between batches helps avoid rate limits—you can reduce it if your quota allows, but don’t remove it entirely.

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