Azure Text to Speech API音频无法播放：Google Sheet脚本调用问题

一、音频无法播放的修复方案 #

你的代码核心问题在于二进制音频数据的处理方式，以下是具体修正点：

• 避免字节数组转换破坏音频结构：直接使用API返回的Blob创建文件，无需转成字节数组
• 修正文件名拼写错误：otuput_audio.mp3改为output_audio.mp3
• 匹配标准MIME类型：MP3格式对应audio/mpeg，WAV格式对应audio/wav

修正后的完整代码：

function sendTextToAzureTTS() {
  // Azure Text to Speech endpoint and API key
  var endpoint = "https://eastus.tts.speech.microsoft.com/cognitiveservices/v1";
  var apiKey = "hidden";

  // Get the plain text from Sheet2 B1
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var text = sheet.getRange("B1").getValue();

  // Create the request payload
  var requestData = {
    method: "POST",
    contentType: "application/ssml+xml",
    headers: {
      "Ocp-Apim-Subscription-Key": apiKey,
      "X-Microsoft-OutputFormat": "audio-24khz-160kbitrate-mono-mp3",
    },
    payload: '<speak version="1.0" xml:lang="en-US"><voice xml:lang="en-US" xml:gender="Female" name="en-US-EmmaNeural">' + text + '</voice></speak>',
    muteHttpExceptions: true // 启用后捕获更详细的错误信息
  };

  // Make the POST request to Azure Text to Speech
  var response = UrlFetchApp.fetch(endpoint, requestData);

  // Check if the response was successful
  if (response.getResponseCode() == 200) {
    // 直接使用API返回的Blob创建文件
    var audioBlob = response.getBlob().setName("output_audio.mp3").setContentType("audio/mpeg");
    var audioFile = DriveApp.createFile(audioBlob);

    // Get the URL of the created audio file
    var audioUrl = audioFile.getUrl();

    // Write the audio URL to Sheet2 B2
    sheet.getRange("B2").setValue(audioUrl);
  } else {
    Logger.log("Error: " + response.getResponseCode() + " - " + response.getContentText());
    // 可选：将错误信息写入Sheet方便查看
    sheet.getRange("B2").setValue("错误：" + response.getResponseCode() + " - " + response.getContentText());
  }
}

二、批量API请求技巧 #

• 批量读取数据：使用getRange(row, col, numRows, numCols).getValues()一次性读取多行文本，减少Sheet IO操作
• 控制请求频率：Azure TTS API有限额，每次请求后用Utilities.sleep(1000)暂停1秒，避免触发限流
• 错误重试机制：对失败请求添加最多3次重试逻辑，处理临时网络波动
• 批量写入结果：收集所有音频URL后，用setValues()一次性写入Sheet，提升效率

批量处理示例代码：

function batchTextToAzureTTS() {
  var endpoint = "https://eastus.tts.speech.microsoft.com/cognitiveservices/v1";
  var apiKey = "hidden";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  // 读取B列第1到第10行的文本（可根据实际调整范围）
  var textList = sheet.getRange("B1:B10").getValues();
  var urlResults = [];

  for (var i = 0; i < textList.length; i++) {
    var text = textList[i][0];
    if (!text) {
      urlResults.push(["空文本"]);
      continue;
    }

    var requestData = {
      method: "POST",
      contentType: "application/ssml+xml",
      headers: {
        "Ocp-Apim-Subscription-Key": apiKey,
        "X-Microsoft-OutputFormat": "audio-24khz-160kbitrate-mono-mp3",
      },
      payload: '<speak version="1.0" xml:lang="en-US"><voice xml:lang="en-US" xml:gender="Female" name="en-US-EmmaNeural">' + text + '</voice></speak>',
      muteHttpExceptions: true
    };

    // 最多重试3次
    var retryCount = 0;
    var success = false;
    while (retryCount < 3 && !success) {
      var response = UrlFetchApp.fetch(endpoint, requestData);
      if (response.getResponseCode() == 200) {
        var audioBlob = response.getBlob().setName("output_audio_" + (i+1) + ".mp3").setContentType("audio/mpeg");
        var audioFile = DriveApp.createFile(audioBlob);
        urlResults.push([audioFile.getUrl()]);
        success = true;
      } else {
        retryCount++;
        Utilities.sleep(2000); // 重试前暂停2秒
      }
    }

    if (!success) {
      var errorMsg = "重试3次失败：" + response.getResponseCode();
      urlResults.push([errorMsg]);
      Logger.log(errorMsg);
    }
    // 每次请求后暂停1秒，避免限流
    Utilities.sleep(1000);
  }

  // 把所有结果一次性写入C列对应行
  sheet.getRange("C1:C10").setValues(urlResults);
}

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