[Edited, for simplicity]
I want to set the value of a few cells in a Google Sheet from the values retrieved from a schema list (from a Google Workspace domain), using "AdminDirectory.Schemas.list('my_customer').schemas". So far I only achieve partial solutions...
In detail, I want each of the Google Sheet cells U4, U5, U6, and so on (one cell for each schema), to contain a single schema, with the entire array of fields, and the entire array of inner fields, as follows (below is the expected content of a single cell):
________________ Cell U4 ________________
| |
|⏵ Display Name: Test Schema 1 |
|⏵ Safe_Name: Test_Schema_1 |
|⏵ Fields: |
| • Field Nr.1 of 3: |
| ◦ Display Name: Field 1: |
| ◦ Field_Name: Field1: |
| ◦ Field Type: BOOL: |
| ◦ Multi-valued? :false: |
| ◦ Accessible by: ADMINS_AND_SELF |
| |
| • Field Nr.2 of 3: |
| ◦ Display Name: Field 2: |
| ◦ Field_Name: Field2: |
| ◦ Field Type: BOOL: |
| ◦ Multi-valued? :false: |
| ◦ Accessible by: ADMINS_AND_SELF |
| |
| • Field Nr.3 of 3: |
| ◦ Display Name: Field 3: |
| ◦ Field_Name: Field3: |
| ◦ Field Type: BOOL: |
| ◦ Multi-valued? :false: |
| ◦ Accessible by: ADMINS_AND_SELF |
|_______________________________________|
The next cell might be slightly different, as each schema will have a different number of fields, and each field has five inner fields (the one seen in the example above).
So far, the best I wasn't able to achieve better than this:
________________ Cell U4 ________________
| |
|⏵ Display Name: Test Schema 1 |
|⏵ Safe_Name: Test_Schema_1 |
|⏵ Fields: |
| • Field Nr.1 of 3: |
| ◦ Display Name: Field 1: |
| ◦ Field_Name: Field1: |
| ◦ Field Type: BOOL: |
| ◦ Multi-valued? :false: |
| ◦ Accessible by: ADMINS_AND_SELF |
| |
|_______________ Cell U6 _______________|
| |
|⏵ Display Name: Test Schema 1 |
|⏵ Safe_Name: Test_Schema_1 |
|⏵ Fields: |
| • Field Nr.2 of 3: |
| ◦ Display Name: Field 2: |
| ◦ Field_Name: Field2: |
| ◦ Field Type: BOOL: |
| ◦ Multi-valued? :false: |
| ◦ Accessible by: ADMINS_AND_SELF |
| |
|_______________ Cell U6 _______________|
| |
|⏵ Display Name: Test Schema 1 |
|⏵ Safe_Name: Test_Schema_1 |
|⏵ Fields: |
| • Field Nr.3 of 3: |
| ◦ Display Name: Field 3: |
| ◦ Field_Name: Field3: |
| ◦ Field Type: BOOL: |
| ◦ Multi-valued? :false: |
| ◦ Accessible by: ADMINS_AND_SELF |
|_______________________________________|
As you can see, the schema "Display Name" and "Schema Name" is repeated in every cell, and the fields spread into the follwoing cells, until the end of the fields loop. When there are no more fields in that schema, the same happens for the next one. What I want is to have everything related to each schema in a single cell. So, in short, what I need it to be able to join or concatenate the results of the fields loop (more details after the Script 2).
ㅤ
SCRIPT 1: [removed for simplicity. Kept this reference out of respect for who may have read it before]
ㅤ
SCRIPT 2:
function listSchemaB() {
const sheet = SpreadsheetApp.getActive().getSheetByName("Domain Schema");
const schemaLength = AdminDirectory.Schemas.list('my_customer').schemas.length;
for(var i=0;i<schemaLength;i++) {
var data = AdminDirectory.Schemas.list('my_customer').schemas[i];
var fieldsLenght = data.fields.length;
var schemaTitles = "⏵ Display Name: " + data.displayName + "\n\⏵ Safe_Name: " + data.schemaName + "\n\⏵ Fields:";
for(var x=0;x<fieldsLenght;x++) {
var schemaFields = ("\n\ • Field Nr." + (x+1) + " of " + (fieldsLenght+1) + ":\n\ ◦ Display Name: " + data.fields[x].displayName + ":\n\ ◦ Field_Name: " + data.fields[x].fieldName + ":\n\ ◦ Field Type: " + data.fields[x].fieldType + ":\n\ ◦ Multi-valued? :" + data.fields[x].multiValued + ":\n\ ◦ Accessible by: " + data.fields[x].readAccessType).concat("");
}
sheet.getRange(i+4,21).setValue(schemaTitles + schemaFields);
}
}
This one almost works, but I get the results from the loop with the x variable all separated from each other, so they all go to a different cell when I use "setValue", and I can't find a way to merge/join/concatenate the results from the inner loop into a single cell.
ㅤ
SCRIPT 3: [removed for simplicity. Kept this reference out of respect for @doubleunary, who tried to help based on this script]
Additionally - but secondary, for now -, I'd also like to know how I can use the output of "console.log(something)" as a variable to use with "setValue", to push the result to a Google Sheet.
Note: "console.log(ret)" gives me a perfect result but I can't find a way to use the logged result inside the "setValue", to push the result to the Google Sheet.
It is not entirely clear what your desired result is, but given that console.log(ret) gives what you want, try this:
function loopSchemaC() {
const sheet = SpreadsheetApp.getActive().getSheetByName('Domain Schema');
const data = AdminDirectory.Schemas.list('my_customer').schemas;
const output = [];
data.forEach(schema => {
const ret = {};
ret.displayName = schema.displayName;
ret.schemaName = schema.schemaName;
ret.fields = [];
for (let f of schema.fields) {
const obj = {};
obj.readAccessType = f.readAccessType;
obj.displayName = f.displayName;
obj.fieldType = f.fieldType;
obj.fieldName = f.fieldName;
obj.multiValued = f.multiValued;
ret.fields.push(obj);
}
output.push([JSON.stringify(ret, null, 2)]);
});
sheet.getRange('U1')
.offset(0, 0, output.length, output[0].length)
.setValues(output);
}