如何用Apps Script将API获取的JSON转CSV并导入Google表格？

function myFunction() {
  try {
    const apiKey = "MyApiKEY";
    const timeZone = "GMT+7";
    // 计算目标日期：当前日期减2天，起始/结束日期一致
    const targetDate = new Date();
    targetDate.setDate(targetDate.getDate() - 2);
    const dateFormatted = Utilities.formatDate(targetDate, timeZone, "yyyy-MM-dd");

    // 用模板字符串拼接URL，提升可读性
    const url = `https://public-api.vendor.com/v1/clicks?start_date=${dateFormatted}&end_date=${dateFormatted}`;
    const apiConfig = {
      method: "get",
      headers: {
        accept: "application/json",
        Authorization: apiKey
      }
    };

    const response = UrlFetchApp.fetch(url, apiConfig);
    const jsonData = JSON.parse(response.getContentText());

    // 适配API返回结构：如果数据嵌套在data字段则取data，否则直接用jsonData
    const dataArray = jsonData.data || jsonData;

    // 1. 转换为CSV格式
    const csvContent = convertJsonToCsv(dataArray);
    // 可选：将CSV保存到云盘
    // DriveApp.createFile("clicks_data.csv", csvContent, MimeType.CSV);

    // 2. 写入当前活动表格的第一个工作表
    writeDataToSpreadsheet(dataArray);

    console.log("数据处理完成");
  } catch (error) {
    console.error("处理失败:", error.message);
  }
}

// JSON转CSV核心函数
function convertJsonToCsv(jsonArray) {
  if (!Array.isArray(jsonArray) || jsonArray.length === 0) return "";

  // 提取表头（取第一个数据对象的所有键）
  const headers = Object.keys(jsonArray[0]);
  const escapedHeaders = headers.map(header => escapeCsvValue(header));

  // 处理每一行数据，转义特殊字符
  const rows = jsonArray.map(item => {
    return headers.map(key => escapeCsvValue(item[key] || ""));
  });

  // 拼接表头与行数据，生成标准CSV
  return [escapedHeaders.join(","), ...rows.map(row => row.join(","))].join("\n");
}

// 转义CSV特殊字符（逗号、双引号、换行）
function escapeCsvValue(value) {
  const strValue = String(value);
  // 若包含特殊字符，用双引号包裹并转义内部双引号
  if (strValue.includes(",") || strValue.includes('"') || strValue.includes("\n")) {
    return `"${strValue.replace(/"/g, '""')}"`;
  }
  return strValue;
}

// 写入表格并设置样式
function writeDataToSpreadsheet(dataArray) {
  if (!Array.isArray(dataArray) || dataArray.length === 0) return;

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheets()[0]; // 获取第一个工作表

  // 清除原有数据（若需追加可改为定位最后一行）
  sheet.clearContents();

  // 准备输出数据：表头 + 数据行
  const headers = Object.keys(dataArray[0]);
  const outputData = [headers, ...dataArray.map(item => headers.map(key => item[key] || ""))];

  // 批量写入数据
  const range = sheet.getRange(1, 1, outputData.length, outputData[0].length);
  range.setValues(outputData);

  // 设置表格样式
  const headerRange = sheet.getRange(1, 1, 1, headers.length);
  // 表头样式：加粗、浅灰背景、居中对齐
  headerRange.setFontWeight("bold")
            .setBackground("#f0f0f0")
            .setHorizontalAlignment("center");
  
  // 所有单元格垂直居中
  range.setVerticalAlignment("middle");
  
  // 自动调整列宽适配内容
  sheet.autoResizeColumns(1, headers.length);
}

关键细节说明 #

1. JSON结构适配 #

• 若API返回直接是数据数组，修改const dataArray = jsonData;即可
• 若数据有嵌套结构（如item.user.name），需提前扁平化处理为单独的键值对

2. CSV转换注意事项 #

• escapeCsvValue函数避免了逗号、引号导致的CSV格式错乱
• 不需要保存CSV文件时，可注释云盘写入代码

3. 表格样式自定义 #

• 可根据需求修改表头背景色、字体大小、单元格格式（如日期/数字格式化）
• 若需追加数据，替换sheet.clearContents()为const lastRow = sheet.getLastRow() + 1;后从该行写入

4. 原代码优化点 #

• 简化日期计算逻辑，避免重复创建Date对象
• 用const替代var提升代码安全性
• 添加异常捕获，快速排查API请求或数据解析错误

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