將JSON決議為Google電子表格（Apps腳本）-有解無憂

我通過 API 獲得了 JSON 格式的下載。例子：

{
    "Employees": [
        {
            "User": {
                "UserId": "4d132227-ea5c-4e57-b105-2f8b97872545",
                "Login": "[email protected]",
                "FullName": {
                    "LastName": "Фамилия",
                    "FirstName": "Имя",
                    "MiddleName": "Отчество"
                },
                "IsRegistered": true
            },
            "Permissions": {
                "UserDepartmentId": "b5072e57-1e96-490b-ae03-2fd52ef84a3a",
                "IsAdministrator": false,
                "DocumentAccessLevel": "SelectedDepartments",
                "SelectedDepartmentIds": [
                    "b5072e57-1e96-490b-ae03-2fd52ef84a3a",
                    "cd2e04dc-8d3f-4d63-88fd-f900c496e146",
                    "36e4434b-519d-4e40-9253-3464c10ed83e"
                ],
                "Actions": [
                    {
                        "Name": "CreateDocuments",
                        "IsAllowed": true
                    },
                    {
                        "Name": "DeleteRestoreDocuments",
                        "IsAllowed": true
                    },
                    {
                        "Name": "SignDocuments",
                        "IsAllowed": true
                    },
                    {
                        "Name": "AddResolutions",
                        "IsAllowed": true
                    },
                    {
                        "Name": "RequestResolutions",
                        "IsAllowed": true
                    },
                    {
                        "Name": "ManageCounteragents",
                        "IsAllowed": false
                    }
                ],
                "AuthorizationPermission": {
                    "IsBlocked": false
                }
            },
            "Position": "Специалист по снабжению",
            "CanBeInvitedForChat": true,
            "CreationTimestamp": {
                "Ticks": 637284074150000000
            }
        }
    ],
    "TotalCount": 214
}

應該發生的示例：在此處輸入影像描述

員工JSON串列與表中列的比例：

A: "User": {"UserId"} - Employee ID
B: "User": {""FullName""} - FULL NAME
C: "Position" - Position
D: "User": {"Login"} - Mail
E: "User": {"IsRegistered"} - Login activated?
F: "Permissions": {"IsAdministrator"} - Administrator?
G: "Permissions": {"Actions": [{"Name": "SignDocuments","isAllowed": true} - Can sign documents
H: "Permissions": {"Actions": [{"Name": "AddResolutions","isAllowed": true} - Can coordinate documents
I: "Permissions": {"Actions": [{"Name": "RequestResolutions","isAllowed": true} - Can request document approval
J: "Permissions": {"Actions": [{"Name": "CreateDocuments","isAllowed": true} - Can create documents and work with drafts
K: "Permissions": {"Actions": [{"Name": "DeleteRestoreDocuments","isAllowed": true} - Can delete documents and drafts, restore documents
L: "Permissions": {"Actions": [{"Name": "ManageCounteragents","isAllowed": true} - Can work with a list of counterparties

如何將 JSON 轉換為 300 多行的 Google 電子表格？目前我只有對 API 的請求。回應是 JSON。我的下一步是什么？

function GetEmployees(){

  var DdocAuthKey = GetAuthToken() 
  for (let i = 0; i < boxId.length; i  ) {                                                                            
    let url = `https://diadoc-api.kontur.ru/GetEmployees?boxId=`  boxId[i]                                            
    let options =                                                                                                     
    {
      method: "GET",
      contentType: 'application/json',
      headers: {authorization: "DiadocAuth ddauth_api_client_id="   DdocAPIkey   `,ddauth_token=`   DdocAuthKey}
    }

    var json = UrlFetchApp.fetch(url, options)
    var obj = JSON.parse(json)

    printValues(obj);enter code here
    }
}

function printValues(obj) {
  for(var k in obj) {
    if(obj[k] instanceof Object) {
      printValues(obj[k]);
    } else {
      return obj[k]   "<br>"
    }
  }
}

uj5u.com熱心網友回復：

這是代碼的最終版本。我希望這將有助于開發人員在使用 Diadoc API 時使用 JS 和 Apps Script。

由于我有3個組織，我需要做一個額外的回圈：

for (let i = 0; i < boxId.length; i  )

如有必要，可以洗掉此回圈。

function GetEmployees() {

  clearOrgSheets()

  var DdocAuthKey = GetAuthToken() 

  let options =
    {
      method: "GET",
      contentType: 'application/json',
      headers: {authorization: "DiadocAuth ddauth_api_client_id="   DdocAPIkey   `,ddauth_token=`   DdocAuthKey}
    }

  for (let i = 0; i < boxId.length; i  ) {

    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SheetNames[i])
    let pageNum = 1

    do {                             

      let url = `https://diadoc-api.kontur.ru/GetEmployees?boxId=`  boxId[i]   `&page=`   pageNum   `&count=50`
      
      pageNum          
                           

      var obj = JSON.parse(UrlFetchApp.fetch(url, options))

      var table = []; // it will be the 2d array

      for (var employee of obj.Employees) {

        var {LastName, FirstName, MiddleName} = employee.User.FullName;
        var name = [LastName, FirstName, MiddleName].join(' ').trim();

        var actions = {};
        employee.Permissions.Actions.forEach(a => actions[a.Name] = a.IsAllowed);

        var row = 
        [
          employee.User.UserId,
          name,
          employee.Position,
          employee.User.Login,
          employee.User.IsRegistered,
          employee.Permissions.IsAdministrator,
          actions.SignDocuments,          // Can sign documents
          actions.AddResolutions,         // Can coordinate documents
          actions.RequestResolutions,     // Can request document approval
          actions.CreateDocuments,        // Can create documents and work with drafts
          actions.DeleteRestoreDocuments, // Can delete documents and drafts, restore documents
          actions.ManageCounteragents,    // Can work with a list of counterparties
        ];

        table.push(row);
      }

      let lastRow = sheet.getLastRow()   1

      try{
      let range = sheet.getRange(lastRow, 1, table.length, table[0].length )
      range.setValues(table);
      } catch (err){
        break
      }
    } while (obj.Employees.length > 0);
  }
}

uj5u.com熱心網友回復：

試試這個：

function myFunction() {

  // here is your object (parsed json)
  var obj = {
    "Employees": [
      {
        "User": {
          "UserId": "4d132227-ea5c-4e57-b105-2f8b97872545",
          "Login": "[email protected]",
          "FullName": {
            "LastName": "Фамилия",
            "FirstName": "Имя",
            "MiddleName": "Отчество"
          },
          "IsRegistered": true
        },
        "Permissions": {
          "UserDepartmentId": "b5072e57-1e96-490b-ae03-2fd52ef84a3a",
          "IsAdministrator": false,
          "DocumentAccessLevel": "SelectedDepartments",
          "SelectedDepartmentIds": [ "b5072e57", "cd2e04dc", "36e4434b" ],
          "Actions": [
            { "Name": "CreateDocuments",        "IsAllowed": true  },
            { "Name": "DeleteRestoreDocuments", "IsAllowed": true  },
            { "Name": "SignDocuments",          "IsAllowed": true  },
            { "Name": "AddResolutions",         "IsAllowed": true  },
            { "Name": "RequestResolutions",     "IsAllowed": true  },
            { "Name": "ManageCounteragents",    "IsAllowed": false }
          ],
          "AuthorizationPermission": { "IsBlocked": false }
        },
        "Position": "Специалист по снабжению",
        "CanBeInvitedForChat": true,
        "CreationTimestamp": { "Ticks": 637284074150000000 }
      }
    ],
    "TotalCount": 214
  };

  var table = []; // it will be the 2d array

  for (var employee of obj.Employees) {

    var {LastName, FirstName, MiddleName} = employee.User.FullName;
    var name = [LastName, FirstName, MiddleName].join(' ');

    var actions = {};
    employee.Permissions.Actions.forEach(a => actions[a.Name] = a.IsAllowed);

    var row = [
      employee.User.UserId,
      name,
      employee.Position,
      employee.User.Login,
      employee.User.IsRegistered,
      employee.Permissions.IsAdministrator,
      actions.SignDocuments,          // Can sign documents
      actions.AddResolutions,         // Can coordinate documents
      actions.RequestResolutions,     // Can request document approval
      actions.CreateDocuments,        // Can create documents and work with drafts
      actions.DeleteRestoreDocuments, // Can delete documents and drafts, restore documents
      actions.ManageCounteragents,    // Can work with a list of counterparties
    ];

    table.push(row); // add the row to the 2d array
  }

  // put the 2d array on the sheet  
  SpreadsheetApp.getActiveSheet()
    .getRange(2,1,table.length,table[0].length)
    .setValues(table);

}

讓我知道它是否有效。

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/478609.html

標籤：javascript json 谷歌应用脚本谷歌表格

上一篇：我的查詢函式不斷回傳#VALUE錯誤，我不知道我做錯了什么

下一篇：如何將員工時間表資料從列轉換為行