主页 / coding / 问题 / 79251739
Accepted
Mark Fraher
Asked: 2024-12-04 23:13:28 +0800 CST

Google Apps 脚本中的 for 循环将 json 响应中的特定值添加到 Google 表格

  • 772

我一直在尝试使用 Cloud Identity API 将 API JSON 响应中的特定值提取到 Google Sheet 中，特别是策略部分。API 响应正在运行，我得到了我想要的值，但我似乎无法让它将所有响应添加到 Google Sheet 上的 228 行，因为我无法获取要填写的行。我似乎只能让它填写 A2 行，而不能填写其他行。我认为这是 for 循环的问题，但我搞不清楚。我将在下面展示代码、示例 JSON 响应和工作表。任何帮助都感激不尽。

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);



//console.log(settingsData);

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"
    },
    {
      "name": "policies/axynr375bwa3nh5uckijlmho3afcg",
      "customer": "customers/C03r7ld3j",
      "policyQuery": {
        "query": "entity.org_units.exists(org_unit, org_unit.org_unit_id == orgUnitId('03ph8a2z32dhpy9'))",
        "orgUnit": "orgUnits/03ph8a2z32dhpy9",
        "sortOrder": 101.00005
      },
      "setting": {
        "type": "settings/blogger.user_takeout",
        "value": {
          "takeoutStatus": "ENABLED"
        }
      },
      "type": "SYSTEM"
    },
    {
      "name": "policies/axynr375bx2oz6wsbpunrgxt7qbcg",
      "customer": "customers/C03r7ld3j",
      "policyQuery": {
        "query": "entity.org_units.exists(org_unit, org_unit.org_unit_id == orgUnitId('03ph8a2z32dhpy9'))",
        "orgUnit": "orgUnits/03ph8a2z32dhpy9",
        "sortOrder": 101.00016
      },
      "setting": {
        "type": "settings/books.user_takeout",
        "value": {
          "takeoutStatus": "ENABLED"
        }
      },
      "type": "SYSTEM"
    },
    {
      "name": "policies/axynr375bwi5l5wvb2t335wn7mlsg",
      "customer": "customers/C03r7ld3j",
      "policyQuery": {
        "query": "entity.org_units.exists(org_unit, org_unit.org_unit_id == orgUnitId('03ph8a2z32dhpy9'))",
        "orgUnit": "orgUnits/03ph8a2z32dhpy9",
        "sortOrder": 101.00021
      },
      "setting": {
        "type": "settings/location_history.user_takeout",
        "value": {
          "takeoutStatus": "ENABLED"
        }
      },
      "type": "SYSTEM"
    },
    {
      "name": "policies/axynr375bx25ndwad3wz7ehl3mkcg",
      "customer": "customers/C03r7ld3j",
      "policyQuery": {
        "query": "entity.org_units.exists(org_unit, org_unit.org_unit_id == orgUnitId('03ph8a2z32dhpy9'))",
        "orgUnit": "orgUnits/03ph8a2z32dhpy9",
        "sortOrder": 101.00025
      },
      "setting": {
        "type": "settings/play.user_takeout",
        "value": {
          "takeoutStatus": "ENABLED"
        }
      },
      "type": "SYSTEM"
    }

在此处输入图片描述

google-apps-script

1 个回答

  1. Best Answer

    修改点：

    • 在您的脚本中，json.policies[0]始终被推送到settingsData，并且ssdata只有一个元素。并且，ssdata尝试放入settingsSheet.getRange('A2:A229')。这在每个循环中运行。这样，每个循环中只放入一行。我猜这可能是您当前问题的原因。

    当这些点体现在你的脚本里时，做如下修改怎么样？

    修改后的脚本：

    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);


  // I modified the script below.
  var values = json.policies.map(o => [
    o.name || null,
    o.setting?.type || null,
    o.setting?.value?.serviceState || null,
    o.setting?.value?.enableAccountRecovery || null,
  ]);
  settingsSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}
    • 运行此脚本时，每个元素的预期值json.policies将作为行放入电子表格中。

    参考：

    • 1

相关问题