如何让调用Spotify API的Google脚本超时后自动断点续跑？

核心实现思路 #

• 用**脚本属性（Script Properties）**持久化保存处理进度：当前播客索引、该播客的当前offset、是否首次执行
• 仅首次执行时清空Output工作表，后续续跑跳过清空操作
• 每次处理一个批次的剧集后更新进度，当检测到运行时间接近6分钟限制时，自动保存进度并触发下一次执行

修改后的完整代码 #

function getEpisodes() {
  // 替换为你的Spotify凭证和Sheet ID
  const clientId = "<your Spotify API client ID>";
  const clientSecret = "<your Spotify API client secret>";
  const sheetId = "<sheet ID>";

  const scriptProps = PropertiesService.getScriptProperties();
  const ss = SpreadsheetApp.openById(sheetId);
  const sListOfPodcasts = ss.getSheetByName("List of podcasts");
  const sOutput = ss.getSheetByName("Output");
  
  // 初始化或读取进度
  let progress = {
    currentPodcastIndex: parseInt(scriptProps.getProperty("currentPodcastIndex")) || 0,
    currentOffset: parseInt(scriptProps.getProperty("currentOffset")) || 0,
    isFirstRun: scriptProps.getProperty("isFirstRun") !== "false"
  };

  // 首次执行才清空Output
  if (progress.isFirstRun) {
    sOutput.getRange("A2:L").clear();
    scriptProps.setProperty("isFirstRun", "false");
  }

  // 获取播客列表（过滤空行）
  const arrPodcasts = sListOfPodcasts.getRange("A2:A").getValues()
    .filter(row => row[0] !== "")
    .map(row => row[0]);

  // 如果所有播客都处理完，清理进度和触发器
  if (progress.currentPodcastIndex >= arrPodcasts.length) {
    scriptProps.deleteAllProperties();
    deleteTriggers();
    Logger.log("所有播客处理完成");
    return;
  }

  // 获取Spotify访问令牌
  const token = getSpotifyToken(clientId, clientSecret);
  const parameters = {
    method: "GET",
    headers: { "Authorization": `Bearer ${token}` },
    json: true
  };

  // 从当前进度开始处理播客
  for (let i = progress.currentPodcastIndex; i < arrPodcasts.length; i++) {
    const showId = arrPodcasts[i];
    let offset = progress.currentOffset;

    // 获取播客基本信息（只调用一次，优化API请求）
    const showInfo = getShowInfo(showId, parameters);
    if (!showInfo) {
      Logger.log(`跳过无效播客ID: ${showId}`);
      progress.currentPodcastIndex = i + 1;
      progress.currentOffset = 0;
      scriptProps.setProperties(progress);
      continue;
    }
    const { name: nameShow, publisher } = showInfo;

    // 获取总剧集数和需要循环的次数
    const totEps = getTotalEpisodes(showId, parameters);
    const n = Math.floor(totEps / 50) + 1;
    Logger.log(`播客 ${nameShow} 总剧集数: ${totEps}, 需要循环 ${n} 次`);

    // 从当前offset开始处理剧集批次
    for (let c = Math.floor(offset / 50); c < n; c++) {
      // 检查运行时间，接近6分钟（350秒）就保存进度并触发下次执行
      if (new Date().getTime() - ScriptApp.getScriptTime() > 350000) {
        scriptProps.setProperties(progress);
        createTrigger();
        Logger.log("即将超时，保存进度并触发下次执行");
        return;
      }

      const fetchUrl = `https://api.spotify.com/v1/shows/${showId}/episodes?offset=${offset}&limit=50&market=GB`;
      Logger.log(`当前请求URL: ${fetchUrl}`);

      let podcastData;
      try {
        const response = UrlFetchApp.fetch(fetchUrl, parameters);
        podcastData = JSON.parse(response.getContentText());
      } catch (err) {
        Logger.log(`获取剧集失败，跳过该批次: ${err.message}`);
        offset += 50;
        progress.currentOffset = offset;
        scriptProps.setProperties(progress);
        continue;
      }

      // 写入剧集数据到Output表
      const rows = podcastData.items.map(episode => [
        nameShow,
        publisher,
        episode.name,
        episode.release_date,
        episode.description,
        episode.type,
        episode.explicit,
        episode.duration_ms,
        `https://open.spotify.com/embed/episode/${episode.id}`,
        episode.images?.[0]?.url || ""
      ]);

      if (rows.length > 0) {
        const rowStart = sOutput.getLastRow() + 1;
        sOutput.getRange(rowStart, 1, rows.length, 10).setValues(rows);
      }

      // 更新offset和进度
      offset += 50;
      progress.currentOffset = offset;
      scriptProps.setProperties(progress);

      // 处理完该播客所有批次，重置offset，进入下一个播客
      if (offset >= totEps) {
        progress.currentPodcastIndex = i + 1;
        progress.currentOffset = 0;
        scriptProps.setProperties(progress);
        break;
      }
    }
  }

  // 所有播客处理完成，清理进度和触发器
  scriptProps.deleteAllProperties();
  deleteTriggers();
  Logger.log("所有播客处理完成");
}

// 获取Spotify访问令牌
function getSpotifyToken(clientId, clientSecret) {
  const url = "https://accounts.spotify.com/api/token";
  const params = {
    method: "post",
    headers: { "Authorization": `Basic ${Utilities.base64Encode(`${clientId}:${clientSecret}`)}` },
    payload: { grant_type: "client_credentials" }
  };
  const res = UrlFetchApp.fetch(url, params);
  const obj = JSON.parse(res.getContentText());
  return obj.access_token;
}

// 获取播客基本信息
function getShowInfo(showId, parameters) {
  try {
    const response = UrlFetchApp.fetch(`https://api.spotify.com/v1/shows/${showId}/?market=gb`, parameters);
    return JSON.parse(response.getContentText());
  } catch (err) {
    Logger.log(`获取播客信息失败: ${err.message}`);
    return null;
  }
}

// 获取播客总剧集数
function getTotalEpisodes(showId, parameters) {
  try {
    const response = UrlFetchApp.fetch(`https://api.spotify.com/v1/shows/${showId}/episodes`, parameters);
    const data = JSON.parse(response.getContentText());
    return data.total || 0;
  } catch (err) {
    Logger.log(`获取总剧集数失败: ${err.message}`);
    return 0;
  }
}

// 创建1分钟后执行的触发器
function createTrigger() {
  deleteTriggers(); // 避免重复创建触发器
  ScriptApp.newTrigger("getEpisodes")
    .timeBased()
    .after(60000) // 1分钟后执行
    .create();
}

// 删除所有getEpisodes的触发器
function deleteTriggers() {
  const triggers = ScriptApp.getProjectTriggers();
  for (const trigger of triggers) {
    if (trigger.getHandlerFunction() === "getEpisodes") {
      ScriptApp.deleteTrigger(trigger);
    }
  }
}

关键功能说明 #

1. 进度持久化：通过PropertiesService保存当前处理到的播客索引和offset，即使脚本超时，下次执行也能从断点继续
2. 首次执行判断：用isFirstRun标记控制是否清空Output表，避免续跑时丢失已获取的数据
3. 超时检测：每次循环检查脚本运行时间，接近6分钟限制时自动保存进度并创建1分钟后的触发器，实现自动续跑
4. API请求优化：将播客基本信息和总剧集数的请求抽成独立函数，避免重复调用Spotify API，减少请求次数
5. 异常处理：对API请求失败的情况做捕获，避免脚本直接崩溃，跳过无效数据继续执行

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