Google Apps Script循环问题：无法批量写入Cloud Identity API数据至表格

我正尝试通过Cloud Identity API将返回JSON响应中policies部分的特定值提取到Google Sheet的「07. settings api」工作表中。API响应正常，能获取到所需值，但无法将所有响应数据填充到表格的228行中，仅能填充A2行，其余行无法写入，推测是for循环存在问题。

原代码 #

function get_settings(){

const ss = SpreadsheetApp.getActiveSpreadsheet();
const settingsSheet = ss.getSheetByName("07. settings api");

const api_key = PropertiesService.getScriptProperties().getProperty('apikey');
var url = "https://cloudidentity.googleapis.com/v1beta1/policies?key=" + api_key;

const params = {
  headers: {
    "Authorization": 'Bearer ' + ScriptApp.getOAuthToken()
  }
};

var response = UrlFetchApp.fetch(url, params);
var data = response.getContentText();
var json = JSON.parse(data);

for (let i = 0; i < 229; i++){
let settingsData = [];
settingsData.push(json.policies[0].name);
settingsData.push(json.policies[0].setting.type);
settingsData.push(json.policies[0].setting.value.serviceState);
settingsData.push(json.policies[0].setting.value.enableAccountRecovery);

let ssdata = [];
ssdata.push(settingsData);

let targetRange = settingsSheet.getRange('A2:A229');
targetRange.setValues(ssdata);
}

}

JSON响应示例 #

{
  "policies": [
    {
      "name": "policies/axynr375bw46nm5dcsjnvrfpuefam",
      "customer": "customers/C03r7ld3j",
      "policyQuery": {
        "query": "entity.org_units.exists(org_unit, org_unit.org_unit_id == orgUnitId('03ph8a2z32dhpy9'))",
        "orgUnit": "orgUnits/03ph8a2z32dhpy9",
        "sortOrder": 101
      },
      "setting": {
        "type": "settings/security.super_admin_account_recovery",
        "value": {
          "enableAccountRecovery": true
        }
      },
      "type": "SYSTEM"
    },
    {
      "name": "policies/axynr375bw46nm5dctezjp4byyksa",
      "customer": "customers/C03r7ld3j",
      "policyQuery": {
        "query": "entity.org_units.exists(org_unit, org_unit.org_unit_id == orgUnitId('03ph8a2z32dhpy9')) && entity.licenses.exists(license, license in ['/product/101034/sku/1010340001','/product/Google-Apps/sku/1010020020','/product/Google-Apps/sku/1010020025','/product/Google-Apps/sku/1010020026','/product/Google-Apps/sku/1010020027','/product/Google-Apps/sku/1010020028','/product/Google-Apps/sku/1010020030','/product/Google-Apps/sku/1010020031'])",
        "orgUnit": "orgUnits/03ph8a2z32dhpy9",
        "sortOrder": 101
      },
      "setting": {
        "type": "settings/gmail.email_spam_filter_ip_allowlist",
        "value": {
          "allowedIpAddresses": [
            "172.217.25.142"
          ]
        }
      },
      "type": "SYSTEM"
    },
    {
      "name": "policies/axynr375bxlm5l5gc6cib7vrw4lsg",
      "customer": "customers/C03r7ld3j",
      "policyQuery": {
        "query": "entity.org_units.exists(org_unit, org_unit.org_unit_id == orgUnitId('03ph8a2z32dhpy9'))",
        "orgUnit": "orgUnits/03ph8a2z32dhpy9",
        "sortOrder": 101.00002
      },
      "setting": {
        "type": "settings/play_console.user_takeout",
        "value": {
          "takeoutStatus": "ENABLED"
        }
      },
      "type": "SYSTEM"
    }
  ]
}

表格情况 #

仅A2行有数据，其余行空白。

问题分析 #

1. 固定取第一个政策数据：循环中始终访问json.policies[0]，没有遍历所有policies元素，导致每次都写入同一行数据
2. 重复写入同一范围：每次循环都创建新数组并写入A2:A229范围，覆盖之前内容，最后仅保留最后一次写入的结果
3. 范围与数据不匹配：目标范围是单列A2:A229，但要写入的是4列数据（name、type、serviceState、enableAccountRecovery），数据维度不匹配
4. 循环次数不合理：固定循环229次，与实际policies数量不匹配，可能导致索引越界或写入空数据

修正后的代码 #

function get_settings() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const settingsSheet = ss.getSheetByName("07. settings api");
  
  const api_key = PropertiesService.getScriptProperties().getProperty('apikey');
  const url = `https://cloudidentity.googleapis.com/v1beta1/policies?key=${api_key}`;
  
  const params = {
    headers: {
      "Authorization": `Bearer ${ScriptApp.getOAuthToken()}`
    }
  };
  
  const response = UrlFetchApp.fetch(url, params);
  const json = JSON.parse(response.getContentText());
  
  // 初始化存储所有行数据的数组
  const allRows = [];
  
  // 遍历所有policies元素
  json.policies.forEach(policy => {
    const row = [
      policy.name,
      policy.setting.type,
      // 处理字段不存在的情况，避免报错
      policy.setting.value.serviceState || "",
      policy.setting.value.enableAccountRecovery || ""
    ];
    allRows.push(row);
  });
  
  // 清空原有数据（可选，根据需求决定）
  settingsSheet.getRange(2, 1, settingsSheet.getLastRow() - 1, 4).clearContent();
  
  // 写入数据：从A2开始，行数为allRows的长度，列数为4
  if (allRows.length > 0) {
    const targetRange = settingsSheet.getRange(2, 1, allRows.length, 4);
    targetRange.setValues(allRows);
  }
}

修正说明 #

• 用forEach遍历所有policies元素，确保每个政策数据都被处理
• 初始化一个数组存储所有行数据，最后一次性写入表格，提高执行效率
• 处理字段不存在的情况，避免因部分政策缺少serviceState或enableAccountRecovery字段导致报错
• 目标范围匹配数据维度（4列），确保数据正确写入对应列
• 可选清空原有数据，避免新旧数据混合

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