Parsing JSON into Google spreadsheet (Apps Script)-CodePudding

I have a download in JSON format that I get through the API. Example:

{
    "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
}

An example of what should happen: enter image description here

The ratio of the JSON list of employees with columns in the table:

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

How can I convert JSON to a Google spreadsheet for 300 rows? At the moment I only have a request to the API. The response is JSON. What are my next steps?

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>"
    }
  }
}

CodePudding user response：

This is the final version of the code. I hope this will help developers on JS and Apps Script when working with the Diadoc API.

Due to the fact that I have 3 organizations, I need to do an additional cycle:

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

If necessary, this cycle can be removed.

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

CodePudding user response：

Try this:

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

}

Let me know if it works.