I have a download in JSON format that I get through the API. Example:
{ "Employees": [ { "User": { "UserId": "4d132227-ea5c-4e57-b105-2f8b97872545", "Login": "test@gmail.com", "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>" } } }
Advertisement
Answer
Try this:
function myFunction() { // here is your object (parsed json) var obj = { "Employees": [ { "User": { "UserId": "4d132227-ea5c-4e57-b105-2f8b97872545", "Login": "test@gmail.com", "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.